Читать «Язык PL/SQL» онлайн - страница 91

Таблица 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 с указанными пользователем границами диапазона. Название параметра соответствует названию столбца таблицы.