Иван Сергеевич Задворьев
Язык PL/SQL
Работа с большими объектами
В Oracle имеются специальные типы данных для хранения больших объектов (Large Objects, LOB), размеры которых могут измеряться в террабайтах:
BLOB – тип для представления бинарных данных (значение содержит локатор на большой бинарный объект, хранящийся в базе данных);
CLOB – тип для представления символьных данных (значение содержит локатор на большой символьный объект, хранящийся в базе данных);
BFILE – тип данных для описания файлов (значение содержит указатель на файл, который находится вне базы данных Oracle).
Локатором называется хранящийся в базе данных указатель на данные большого объекта. Значение типа BLOB или CLOB может характеризоваться одним из трех состояний:
содержит NULL (не содержит локатор);
содержит локатор, указывающий данные большого объекта;
содержит локатор, не указывающий ни на какие данные.
Про последнее состояние говорят, что это «пустой» (empty) LOB-объект. «Пустые» LOB-объекты инициализируются встроенными функциями EMPTY_BLOB() и EMPTY_CLOB(). Для определения текущего состояния значения из трех возможных используется следующая логика:
IF some_clob IS NULL THEN
– нет ни данных, ни локатора
ELSIF DBMS_LOB.GETLENGTH(some_CLOB)=0 THEN
– пустой (empty) LOB-объект
ELSE
– данные в LOB-объекте есть
END IF
Значения типа BFILE используются только для чтения из файлов. Удаление в строке таблицы значения типа BFILE или его копирование никак не влияют на сам файл в каталоге операционной системы, с ним ничего не происходит. Все эти операции выполняются только над указателями на файлы.
Для работы с данными типа LOB нужно сначала извлечь локатор, а затем с помощью процедур и функций встроенного пакета DBMS_LOB прочитать или записать собственно данные.
Таблица 10. Программы пакета DBMS_LOB.
Программа
Описание программы
APPEND (процедура)
записывает данные в конец LOB-объекта
WRITE (процедура)
записывает данные в LOB-объект по смещению
COMPARE (функция)
сравнивает два LOB-объекта одного типа
GETLENGTH (функция)
возвращает длину LOB-объекта
INSTR (функция)
возвращает позицию вхождения строки в объект
READ (процедура)
считывает часть LOB-объекта
SUBSTR (функция)
возвращает часть LOB-объекта по смещению
FILECLOSE (процедура)
закрывает файл по указателю-значению BFILE
FILEEXISTS (функция)
проверяет наличие файла по указателю
FILEOPEN (процедура)
открывает файл для значения BFILE
COPY (процедура)
копирует LOB-объекты
ERASE (процедура)
удаляет LOB-объект полностью или частично
Работа с файлами с помощью пакета DBMS_LOB
В качестве примера использования пакета DBMS_LOB приведем процедуру f_compare, которая сравнивает файлы в каталоге dir1. Имена файлов передаются как параметры:
SQL> CREATE DIRECTORY dir1 AS 'C:\WORK';
Directory created.
SQL> CREATE OR REPLACE PROCEDURE f_compare
2 (fname1 IN VARCHAR2, fname2 IN VARCHAR2) IS
3 file_1 BFILE;
4 file_2 BFILE;
5 result INTEGER;
6 BEGIN
7 file_1 := BFILENAME('DIR1',fname1);
8 file_2 := BFILENAME('DIR1',fname2);
9 DBMS_LOB.FILEOPEN(file_1);
10 DBMS_LOB.FILEOPEN(file_2);
11 result := DBMS_LOB.COMPARE(file_1,file_2,
12 DBMS_LOB.LOBMAXSIZE,1,1);
13 IF (result != 0) THEN
14 DBMS_OUTPUT.PUT_LINE('Файлы различные');
15 ELSE
16 DBMS_OUTPUT.PUT_LINE('Файлы одинаковые');
17 END IF;
18 DBMS_LOB.FILECLOSE(file_1);
19 DBMS_LOB.FILECLOSE(file_2);
20 END;
21 /
Procedure created.
SQL> BEGIN
2 f_compare('fname.txt','fname.txt');
3 END;
4 /
Файлы одинаковые
SQL> BEGIN
2 f_compare('fname.txt','fname2.txt');
3 END;
4 /
Файлы различные
SQL> BEGIN
2 f_compare('fname.txt','fname3.txt');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "SYSTEM.F_COMPARE", line 9
ORA-06512: at line 2
При последнем вызове процедуры f_compare не удалось открыть указанный файл. Обратите внимание, ошибка произошла при попытке открыть файл, установка указателя BFILE произошла нормально.
Для загрузки файлов в базу данных как LOB-объектов предназначена пакетная процедура DBMS_LOB.LOADFROMFILE, которой в качестве параметров передается переменная типа BFILE, связанная с загружаемым файлом, количество байт, считываемое из файла, и указатель на объект-приемник.
SQL> CREATE TABLE tab1 (at1 NUMBER, at2 BLOB, at3 BFILE);
Table created.
SQL> INSERT INTO tab1 VALUES (2,EMPTY_BLOB(),NULL);
1 row created.
SQL> DECLARE
2 l_BLOB BLOB;
3 file_1 BFILE;
4 BEGIN
5 SELECT at2 INTO l_BLOB FROM tab1
6 WHERE at1=2 FOR UPDATE;
7 file_1 := BFILENAME('DIR1','fname.txt');
8 DBMS_LOB.FILEOPEN(file_1);
10 DBMS_LOB.LOADFROMFILE(l_BLOB,file_1,
11 DBMS_LOB.GETLENGTH(file_1));
12 COMMIT;
13 END;
14 /
PL/SQL procedure successfully completed.
В данном случае сначала строка таблицы с пустым LOB-объектом блокируется с помощью команды SELECT FOR UPDATE, а затем пакетная процедура DBMS_LOB.LOADFROMFILE осуществляет в него загрузку из файла.
Семантика SQL для LOB-объектов
Начиная с версии Oracle 9i, реализована поддержка семантики SQL для LOB-объектов. Это означает, что с BLOB и CLOB могут работать обычные встроенные функции как со значениями типов VARCHAR2 и CHAR (используются перегруженные версии встроенных функций):
SQL> CREATE TABLE clob_table (at1 CLOB);
Table created.
SQL> INSERT INTO clob_table VALUES ('I say :');
1 row created.
SQL> UPDATE clob_table SET at1 = 'Hello, world'||rpad(at1, 1000000, '!');
1 row updated.
SQL> SELECT LENGTH (at1) AS len, TO_CHAR (SUBSTR (at1, 1, 12)) AS words
2 FROM clob_table;
LEN WORDS
– –
1000012 Hello, world
Столбец at1 типа CLOB при выполнении предложений UPDATE и SELECT передавался как параметр встроенным функциям определения длины строки LENGTH, выделения подстроки в строке SUBSTR и дополнения строки до заданной длинны RPAD.
Динамический SQL
Предложения SQL, которые не изменяются с момента компиляции программы PL/SQL, называются статическими. Статические предложения SQL формируются компилятором PL/SQL по объявлениям явных курсоров, по командам SELECT INTO и остальным DML-командам языка PL/SQL. После формирования они сохраняются в байт-коде хранимых программ PL/SQL и больше не изменяются.
Термином «динамический SQL» (dynamic SQL) называются предложения SQL, которые динамически формируются как символьные строки непосредственно во время выполнения программ PL/SQL. Эти предложения SQL в байт-коде отсутствуют, поэтому для из выполнения используются специальные механизмы PL/SQL, рассматриваемые далее.
Динамический SQL в PL/SQL в основном применяется для решения следующих задач:
выполнение DDL-команд (CREATE, ALTER, DROP);
поддержка нерегламентированных SQL-запросов (SQL ad hoc queries).
«Создать табличку» в программе PL/SQL нельзя:
SQL> BEGIN
2 CREATE TABLE tab1(at1 INTEGER);
3 END;
4 /
CREATE TABLE tab1(at1 INTEGER);
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "CREATE" when expecting
one of the following: (begin case declare exit for goto if loop pragma …
По префиксу PLS видно, что ошибку выдал компилятор PL/SQL, а из текста сообщения следует, что она произошла на этапе синтаксического анализа кода программы. Даже в грамматике языка PL/SQL не предусмотрено наличие в коде PL/SQL команд, похожих на DDL-команды CREATE.
«…, но если очень хочется, то можно» – для это следует использовать динамический SQL, передавая DDL-команду как символьную строку:
SQL> BEGIN
2 EXECUTE IMMEDIATE 'CREATE TABLE tab1(at1 INTEGER)';
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM tab1;
no rows selected
Выборка из tab1 проходит без ошибок, значит, таблица существует.
Нерегламентированные запросы SQL
Нерегламентированным является SQL-запрос, у которого до этапа выполнения могут быть не определены следующие три составляющие:
текст SQL-запроса, включая список таблиц во фразе FROM, критерий отбора данных во фразе WHERE, фразы группировки и сортировки;
перечень возвращаемых столбцов;
список параметров.
Примером выполнения нерегламентированных SQL-запросов может быть подбор моделей телефонов по параметрам, похожий на соответствующий сервис на «Яндекс.Маркете».
Пусть таблица моделей телефонов имеет следующий вид:
CREATE TABLE phone_models (model VARCHAR2(100),
LTE INTEGER,
dual_sim INTEGER,
price INTEGER,
color VARCHAR2(100));
INSERT INTO phone_models VALUES('Xiaomi Redmi Note 2',1,1,12500,'black');
INSERT INTO phone_models VALUES('Meizu M2 mini',1,1,11400,'white');
…
Один человек может подбирать себе телефон по двум параметрам «вид – смартфон, цена – в пределах 10 000 – 15 000 рублей», другой человек может подбирать модель не по двум, а по трем параметрам «LTE – да, две SIM-карты – да, цвет – черный». Приложению потребуется сформировать и выполнить в базе данных два разных SQL-запроса. Для первого поиска это будет SQL-запрос с тремя связываемыми переменными:
SELECT * FROM phone_models
WHERE type=:p_1
AND price BETWEEN :p2 AND :p3
со значениями переменных :p1='smartphone', :p2=10000, :p3=15000.
Для второго поиска это будет SQL-запрос тоже с тремя связываемыми переменными, но для других ограничений:
SELECT * FROM phone_models
WHERE LTE=:p_1
AND dual_sim=:p2 AND color=:p3
со значениями переменных :p1=1, :p2=1, :p3='black'
Механизмы выполнения динамического SQL в PL/SQL
Для выполнения динамического SQL в PL/SQL есть два механизма:
встроенный динамический SQL (Native Dynamic SQL, NDS);
встроенный пакет DBMS_SQL.
Динамический SQL в Oracle принято делить на четыре категории.
Таблица 11. Категории динамического SQL в Oracle.
Категория
Описание категории
Категория 1
DDL-команды и предложения UPDATE, INSERT и DELETE без параметров
Категория 2
DDL-команды и предложения UPDATE, INSERT и DELETE с фиксированным количеством параметров
Категория 3
предложения SELECT с фиксированным количеством столбцов и параметров
Категория 4
DML-предложения, в которых количество выбранных столбцов (для запросов) или количество параметров (для всех предложений) неизвестно до стадии выполнения
С помощью встроенного пакета DBMS_SQL можно выполнить динамический SQL всех четырех категорий, с помощью NDS – первых трех категорий, на которые приходится, по некоторым оценкам, до 90% всего динамического SQL.
Встроенный динамический SQL
Главным достоинством NDS является его простота. Для выполнения динамического SQL в пакете DBMS_SQL в общем случае требуется 8 этапов, при этом код PL/SQL выглядит довольно громоздко и далее будет возможность в этом убедиться. С NDS обходятся вызовом одной команды EXECUTE IMMEDIATE («выполнить немедленно»), которая имеет следующий синтаксис:
EXECUTE IMMEDIATE предложение SQL
[ [ BULK COLLECT] INTO {переменная[, переменная]… | запись PL/SQL}]
[USING аргумент[,аргумент]…];
Сразу после ключевых слов EXECUTE IMMEDIATE в одинарных кавычках указывается текст предложения SQL, также в этом месте можно указать символьную переменную с текстом предложения SQL, причем эта переменная может иметь тип данных как VARCHAR2, так и CLOB.
Конструкция INTO со списком переменных предназначена для получения значений столбцов результирующей выборки и используется в том случае, если выполняется предложение SELECT. Число переменных и число столбцов должно совпадать. Переменные в конструкции INTO должны быть скалярных типов данных, соответствующих типам столбцов, или одной записью PL/SQL.
Конструкция USING со списком переменных и констант используется для передачи значений, которые должны быть связаны с имеющимися в тексте предложения SQL связываемыми переменными. Связывание значений в NDS осуществляется по позициям связываемых переменных. Количество передаваемых значений, естественно, должно совпадать c количеством связываемых переменных.
Для SQL-запросов команда EXECUTE IMMEDIATE фактически является аналогом команды SELECT INTO с таким же ограничением на результирующую выборку: запросом должна отбираться ровно одна строка, в противном случае инициируются предопределенные исключения.
Рассмотрим пример использования команды EXECUTE IMMEDIATE:
CREATE TABLE tab1 (at1 INT, at2 VARCHAR2(1));
INSERT INTO tab1 VALUES(1,'A');
INSERT INTO tab1 VALUES(2,'B');
SQL> DECLARE
2 l_tab1 tab1%ROWTYPE;
3 l_sql_text VARCHAR2(100) := 'SELECT * FROM tab1 WHERE at1>=:p_at1';
4 BEGIN
5
6 EXECUTE IMMEDIATE l_sql_text INTO l_tab1_rec USING 2;
7 DBMS_OUTPUT.PUT_LINE('Отобранная строка: '||l_tab1.at1||l_tab1.at2);
8
9 BEGIN
10 EXECUTE IMMEDIATE l_sql_text INTO l_tab1 USING 1;
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE(SQLERRM);
14 END;
15
16 BEGIN
17 EXECUTE IMMEDIATE l_sql_text INTO l_tab1 USING 3;
18 EXCEPTION
19 WHEN OTHERS THEN
20 DBMS_OUTPUT.PUT_LINE(SQLERRM);
21 END;
22
23 END;
24 /
Отобранная строка: 2B
ORA-01422: exact fetch returns more than requested number of rows
ORA-01403: no data found
PL/SQL procedure successfully completed.
Рассмотрим еще два примера использования NDS.
Пусть база данных спроектирована таким образом, что у каждой таблицы столбец первичного ключа называется id и имеет тип INTEGER.
Напишем процедуру, которая удаляет строку в любой таблице по значению ее первичного ключа. Параметрами процедуры будут имя таблицы и значение столбца id удаляемой строки.
CREATE TABLE tab1(id INTEGER PRIMARY KEY,at1 CHAR(1));
INSERT INTO tab1 VALUES(1,'a');
INSERT INTO tab1 VALUES(2,'b');
CREATE TABLE tab2(id INTEGER PRIMARY KEY,at1 CHAR(1));
INSERT INTO tab2 VALUES(20,'x');
INSERT INTO tab2 VALUES(30,'y');
SQL> CREATE OR REPLACE PROCEDURE delete_by_id (p_table_name IN VARCHAR2,
2 p_id IN INTEGER) IS
3 BEGIN
4 EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name||' WHERE id=:p_id'
5 USING p_id;
6 DBMS_OUTPUT.PUT_LINE('In table '||p_table_name||' '
7 ||SQL%ROWCOUNT||' rows deleted');
8 END;
9 /
Procedure created.
SQL> set serveroutput on
SQL> EXECUTE delete_by_id('tab1',1);
In table tab1 1 rows deleted
PL/SQL procedure successfully completed.
SQL> EXECUTE delete_by_id('tab1',-1);
In table tab1 0 rows deleted
PL/SQL procedure successfully completed.
SQL> EXECUTE delete_by_id('tab2',20);
In table tab2 1 rows deleted
PL/SQL procedure successfully completed.
Для NDS в PL/SQL поддерживаются средства массовой обработки данных (bulk processing). Конструкция BULK COLLECT указывается в том случае, когда известно, что SQL-запрос может иметь в результирующей выборке не одну, а несколько строк. Тогда переменной, в которую помещается результирующая выборка, должна быть коллекция, то есть и здесь прослеживается аналогия с командой SELECT INTO для статических предложений SQL. Также команда EXECUTE IMMEDIATE может использоваться совместно с рассматриваемой ранее командой FORALL.
Вернем содержимое таблиц tab1, tab2 в исходное состояние и создадим теперь процедуру print_id_list со считыванием в коллекцию всех строк результирующей выборки с помощью конструкции BULK COLLECT.
SQL> CREATE OR REPLACE PROCEDURE print_id_list(p_table_name IN VARCHAR2,
2 p_id IN INTEGER) IS
3 TYPE t_table IS TABLE OF INTEGER;
4 l_table t_table;
5 BEGIN
6 EXECUTE IMMEDIATE 'SELECT id FROM '||p_table_name||' WHERE id>:p_id'
7 BULK COLLECT INTO l_table
8 USING p_id;
9 FOR i IN 1..l_table.COUNT LOOP
10 DBMS_OUTPUT.PUT_LINE(l_table(i));
11 END LOOP;
12 END;
13 /
Procedure created.
SQL> EXECUTE print_id_list('tab1',0);
1
2
PL/SQL procedure successfully completed.
SQL> EXECUTE print_id_list('tab2',20);
30
PL/SQL procedure successfully completed.
Как видно, использование NDS позволяет писать очень компактный код.
Пакет DBMS_SQL
Использование встроенного пакета DBMS_SQL для выполнения динамического SQL предусматривает в общем случае последовательность из 8 этапов.
Таблица 12. Этапы выполнения динамического SQL с помощью DBMS_SQL.
Программа
Описание этапа
OPEN_CURSOR
открывается курсор DBMS_SQL
PARSE
производится синтаксический разбор предложения SQL в курсоре (DDL-команды сразу и выполняются на этом этапе)
BIND_VARIABLE
со всеми связываемыми переменными предложения SQL в курсоре связываются значения
DEFINE_COLUMN
для SQL-запросов указывается, значения каких столбцов выборки в какие переменные PL/SQL будут считываться
EXECUTE
для открытого курсора выполняется предложение SQL
FETCH_ROWS
для SQL-запросов считывается строка выборки (обычно считывание осуществляется в цикле по всей выборке)
COLUMN_VALUE
переменным PL/SQL присваиваются значения столбцов текущей считанной строки из курсора
CLOSE_CURSOR
закрывается курсор DBMS_SQL
Перепишем процедуру print_id_list с использованием вместо NDS встроенного пакета DBMS_SQL.
SQL> CREATE PROCEDURE print_id_list_dbms_sql(p_table_name IN VARCHAR2,
2 p_id IN INTEGER) IS
3 c_cursor INTEGER;
4 ignore INTEGER;
5 l_id INTEGER;
6 BEGIN
7 c_cursor := DBMS_SQL.open_cursor;
8 DBMS_SQL.parse(c_cursor,
9 'SELECT ID FROM '||p_table_name||' WHERE id>:p_id',
10 DBMS_SQL.NATIVE);
11 DBMS_SQL.define_column(c_cursor, 1, l_id);
12 DBMS_SQL.bind_variable(c_cursor, 'p_id', p_id);
13 ignore := DBMS_SQL.execute(c_cursor);
14 LOOP
15 IF DBMS_SQL.fetch_rows(c_cursor)>0 THEN
16 DBMS_SQL.column_value(c_cursor, 1, l_id);
17 DBMS_OUTPUT.PUT_LINE(l_id);
18 ELSE
19 EXIT;
20 END IF;
21 END LOOP;
22 DBMS_SQL.close_cursor(c_cursor);
23 END;
24 /
Procedure created.
SQL> EXECUTE print_id_list_dbms_sql('tab1',0);
1
2
PL/SQL procedure successfully completed.
SQL> EXECUTE print_id_list_dbms_sql('tab2',20);
30
PL/SQL procedure successfully completed.
Код новой версии процедуры print_id_list выглядит более громоздким. Этим и объясняется то, что пакет DBMS_SQL, как правило, используют только тогда, когда использовать NDS нельзя. В остальных случаях обходятся одной строчкой кода с командой EXECUTE IMMEDIATE.
Выполнение динамического SQL четвертой категории
Читатель, вероятно, уже заметил в синтаксисе команды EXECUTE IMMEDIATE ограничение, мешающее использовать встроенный динамический SQL во всех случаях – в EXECUTE IMMEDIATE после конструкций INTO и USING необходимо указывать жестко заданные перечни переменных и констант PL/SQL. Они фиксируются на этапе написания программы и изменяться не могут. Поэтому NDS не подходит для выполнения четвертой категории динамического SQL, когда до стадии выполнения неизвестно количество столбцов результирующей выборки или количество параметров.
Пакет DBMS_SQL позволяет выполнять динамический SQL четвертой категории, так как на стадии выполнения его процедуры и функции можно вызывать любое количество раз. То есть надо просто вызывать процедуру DBMS_SQL.DEFINE_COLUMN и функцию DBMS_SQL.COLUMN_VALUE по числу возвращаемых SQL-запросом столбцов, а процедуру DBMS_SQL.BIND_VARIABLE – по числу имен связываемых переменных.
Задание для самостоятельной разработки
Пусть список значений параметров, которые указывают пользователи на web-странице при подборе моделей телефонов, формируется frontend-приложением в виде символьной строки из пар «параметр=значение», разделенных символом «;». Для цены передается значение вида from/to с указанными пользователем границами диапазона. Название параметра соответствует названию столбца таблицы.
Примеры поисковых запросов:
1) LTE=1;dual_sim=1
2) price=10000/12000;color=black;LTE=1
Необходимо написать процедуру PL/SQL, которая печатает на экране список моделей телефонов, удовлетворяющих заданным условиям. Понятно, что это будет процедура, динамически формирующая и выполняющая нерегламентированные запросы SQL.
Задача облегчается тем, что список столбцов динамического SQL-запроса фиксирован (считаем, что всегда необходимо выводить два столбца – наименование модели и цену).
Как писать процедуру поиска моделей телефонов, в целом понятно. Строку параметров необходимо разобрать на пары «параметр=значение» по разделителям, потом в цикле дополнить шаблон SQL-запроса ограничениями со связываемыми переменными через AND по числу выделенных из строки пар, открыть курсор DBMS_SQL, разобрать в нем сформированное предложение SQL, связать значения переменных и так далее.
Рекомендуется использовать объявленные с помощью атрибута %ROWTYPE записи PL/SQL и считывание из курсора всех строк с помощью конструкции BULK COLLECT.
После проверки работоспособности процедуры можно попробовать добавить в таблицу phone_models новый столбец, например, manufacturer. Если код PL/SQL написан правильно и в хорошем стиле, то для того, чтобы процедура смогла обрабатывать запросы с новым параметром вида
3) LTE=1;dual_sim=1,manufacturer=Samsung
изменять в коде ничего не придется.