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

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, связать значения переменных и так далее.