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

Категория 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.