Использование динамического SQL в Oracle8i.

До появления Oracle8i для динамического конструирования и выполнения SQL-запросов использовался пакет DBMS_SQL. Однако в Oracle8i встроен dуnamic SQL-средство, позволяющее исполнять SQL-запросы, не прибегая к помощи пакета DBMS_SQL. Рассмотрим пример динамического конструирования и выполнения SQL-запросов с использованием пакета DBMS_SQL.

Этот пример делает следующее:

  1. Создает таблицу demo-tbl.

    Структура таблицы следующая:

    ID NUMBER(3)

    NAME VARCHAR2(20)

  2. Вставляет в нее 5 строк с ID 1..5.

  3. Удаляет строку с номером 4.

  4. Считывает и распечатывает строки из таблицы с ID=2,3,5.

  5. Удаляет таблицу demo_tbl.

Вот код примера:

DECLARE

-- курсор

vCursor NUMBER;

-- оператор для создания таблицы

vCreateTable VARCHAR2(200) := 'CREATE TABLE demo_tbl (ID NUMBER(3), NAME VARCHAR2(50))';

-- оператор для удаления таблицы

vDropTable VARCHAR2(200) := 'DROP TABLE demo_tbl';

-- оператор для вставки в таблицу

vInsertTable VARCHAR2(200) := 'INSERT INTO demo_tbl(ID, NAME) VALUES(:id, :name)';

-- оператор для удаления строки из таблицы

vDeleteTable VARCHAR2(200) := 'DELETE FROM demo_tbl WHERE ID=:id';

-- оператор для считывания строк из таблицы

vSelectTable VARCHAR2(200) := 'SELECT ID, NAME FROM demo_tbl WHERE ID BETWEEN :id_l AND :id_h';

vResult INTEGER;

-- выходные переменные для оператора SELECT

vId NUMBER(3);

vName VARCHAR2(50);

BEGIN

/* создаем таблицу */

-- откроем курсор

vCursor:=DBMS_SQL.OPEN_CURSOR;

-- выполним синтаксический разбор оператора и сам оператор

DBMS_SQL.PARSE(vCursor, vCreateTable, DBMS_SQL.V7);

/* таблица создана */

/* вставка в таблицу */

-- выполним синтаксический разбор оператора

DBMS_SQL.PARSE(vCursor, vInsertTable, DBMS_SQL.V7);

FOR vId IN 1..5 LOOP

-- установим значения для переменных привязки

DBMS_SQL.BIND_VARIABLE(vCursor,':id', vId);

DBMS_SQL.BIND_VARIABLE(vCursor,':name', 'Name ╪'||vId);

-- выполним оператор Insert

vResult:=DBMS_SQL.EXECUTE(vCursor);

END LOOP;

-- зафиксируем изменения

COMMIT;

/* вставка в таблицу окончена*/

/* удаляем строку с ID=4 */

-- выполним синтаксический разбор оператора

DBMS_SQL.PARSE(vCursor, vDeleteTable, DBMS_SQL.V7);

-- установим значения для переменных привязки

DBMS_SQL.BIND_VARIABLE(vCursor,':id', 4);

-- выполним оператор Insert

vResult:=DBMS_SQL.EXECUTE(vCursor);

-- зафиксируем изменения

COMMIT;

/* закончено удаление строки с ID=4 */

/* считываем строки с ID=2,3,4 */

-- выполним синтаксический разбор оператора

DBMS_SQL.PARSE(vCursor, vSelectTable, DBMS_SQL.V7);

-- установим значения для переменных привязки

DBMS_SQL.BIND_VARIABLE(vCursor,':id_l', 2);

DBMS_SQL.BIND_VARIABLE(vCursor,':id_h', 5);

-- определим выходные переменные

DBMS_SQL.DEFINE_COLUMN(vCursor, 1, vId);

DBMS_SQL.DEFINE_COLUMN(vCursor, 2, vName, 50);

-- выполним оператор Select

vResult:=DBMS_SQL.EXECUTE(vCursor);

LOOP

-- выходим если строки не найдены

EXIT WHEN DBMS_SQL.FETCH_ROWS(vCursor) = 0;

-- считываем строки из буфера в переменные PL/SQL

DBMS_SQL.COLUMN_VALUE(vCursor, 1, vId);

DBMS_SQL.COLUMN_VALUE(vCursor, 2, vName);

-- распечатаем полученные данные

DBMS_OUTPUT.PUT_LINE(vId||' '||vName);

END LOOP;

/* закончили считывание строк */

/* удаляем таблицу */

-- выполним синтаксический разбор оператора и сам оператор

DBMS_SQL.PARSE(vCursor, vDropTable, DBMS_SQL.V7);

/* таблица удалена */

-- закроем курсор

DBMS_SQL.CLOSE_CURSOR(vCursor);

EXCEPTION

WHEN OTHERS THEN

DBMS_SQL.CLOSE_CURSOR(vCursor);

RAISE;

END;

Для выполнения этого примера в SQL*Plus необходимо ввести команду:

SQL>set serveroutput on size 20000

Здесь вы можете взять этот файл.

Теперь перепишем этот пример с использованием dynamic SQL.

Вот код:

DECLARE

-- оператор для создания таблицы

vCreateTable VARCHAR2(200) := 'CREATE TABLE demo_tbl (ID NUMBER(3), NAME VARCHAR2(50))';

-- оператор для удаления таблицы

vDropTable VARCHAR2(200) := 'DROP TABLE demo_tbl';

-- оператор для вставки в таблицу

vInsertTable VARCHAR2(200) := 'INSERT INTO demo_tbl(ID, NAME) VALUES(:id, :name)';

-- оператор для удаления строки из таблицы

vDeleteTable VARCHAR2(200) := 'DELETE FROM demo_tbl WHERE ID=:id';

-- оператор для считывания строк из таблицы

vSelectTable VARCHAR2(200) := 'SELECT ID, NAME FROM demo_tbl WHERE ID BETWEEN :id_l AND :id_h';

vResult INTEGER;

-- выходные переменные для оператора SELECT

vId NUMBER(3);

vName VARCHAR2(50);

-- объявляем курсорную переменную

TYPE ref_cur IS REF CURSOR;

c ref_cur;

BEGIN

-- создадим таблицу

EXECUTE IMMEDIATE vCreateTable;

-- вставим данные в таблицу

FOR vId IN 1..5 LOOP

EXECUTE IMMEDIATE vInsertTable USING vId, 'Name ╪'||vId ;

END LOOP;

-- зафиксируем изменения

COMMIT;

-- удалим строку с ID=3 из таблицы

EXECUTE IMMEDIATE vDeleteTable USING 3;

-- зафиксируем изменения

COMMIT;

-- считаем строки с ID=2,3,5

-- здесь используются фактические значения,

-- но можно передавать и переменные, например USING v1, v2

OPEN c FOR vSelectTable USING 2, 5;

LOOP

FETCH c INTO vId, vName;

EXIT WHEN c%NOTFOUND;

-- распечатаем полученные данные

DBMS_OUTPUT.PUT_LINE(vId||' '||vName);

END LOOP;

CLOSE c;

-- если запрос возврашает не более одной строки можно записать так

-- возврашается строка с ID=4, так как сторока с ID=3 удалена

EXECUTE IMMEDIATE vSelectTable INTO vId, vName USING 3, 4;

-- распечатаем полученные данные

DBMS_OUTPUT.PUT_LINE('-------------------');

DBMS_OUTPUT.PUT_LINE(vId||' '||vName);

-- удалим таблицу

EXECUTE IMMEDIATE vDropTable;

END;

Для выполнения этого примера в SQL*Plus необходимо ввести команду:

SQL>set serveroutput on size 20000

Здесь вы можете взять этот файл.

Как говорится почувствуйте разницу. Второй пример гораздо меньше и проще в программировании. Но это еще не все преимущества dynamic SQL. Кроме всего прочего, код с использованием dynamic SQL выполняется в 1.5-3 раза быстрее нежели с использованием DBMS_SQL потому что интерпретатор PL/SQL имеет встроенную поддержку dynamic SQL. Dynamic SQL поддерживает работу со всеми имеющимися типапи данных PL/SQL, а DBMS_SQL не позволяет работать с коллекциями и ссылками и не позволяет выбирать данные в запись.

Однако есть приложения где пока без DBMS_SQL не обойтись. Так например DBMS_SQL поддерживает выражение RETURNING при обновлении и удалении нескольких строк, тогда как dynamic SQL осуществляет такую поддержку лишь в случае одной строки. Кроме того, в случае использования DBMS_SQL синтаксический разбор оператора SQL осуществляется лищь один раз, после чего он может использоваться сколько угодно, в случае же dynamic SQL синтаксический разбор оператора SQL осуществляется каждый раз, что может снизить быстродействие в некоторых случаях, однако механизм разделяемых курсоров Oracle8i и встроенная поддержка dynamic SQL интерпретатором PL/SQL позволяют эти потери практически свести на нет .

Так что мой выбор- dynamic SQL!