Иван Задворьев - Язык PL/SQL

Для загрузки файлов в базу данных как 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, которые не изменяются с момента компиляции программы 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.