Динамический SQL

Виды предложений SQL

Пакет DBMS_SQL.
Основные функции и процедуры
OPEN_CURSOR RETURN INTEGER
Открывает новый курсор. По окончании работы необходимо закрыть открытый курсор, используя функцию CLOSE_CURSOR.
PARSE
(cursor IN INTEGER,
statement IN VARCHAR2,
language IN INTEGER)
Немедленная разборка предложения в курсоре. Если разбираемое предложение является DDL-предложением, процедура PARSE также выполняет это DDL-предложение.
EXECUTE
(cursor IN INTEGER) RETURN INTEGER
Выполняет предложение, находящееся в курсоре, и возвращает число обработанных в процессе выполнения строк.
CLOSE_CURSOR
(cursor IN OUT INTEGER)
Закрывает открытый курсор.

Пакет DBMS_SQL.
Основные функции и процедуры
BIND_VARIABLE
(cursor IN INTEGER,
variable IN VARCHAR2,
value IN NUMBER | VARCHAR2 | DATE | MLSLABEL
[ , size IN INTEGER ] )
Связывает значение с переменной в предложении, разбираемом в курсоре. Когда переменная является входной или входной/выходной, связанное значение должно быть правильно определено. Если переменная – выходная, вызов игнорирует связанное значение.
DEFINE_COLUMN
(cursor IN INTEGER,
possition IN INTEGER,
value IN NUMBER | VARCHAR2 | DATE | MLSLABEL
[ , size IN INTEGER ] )
Определяет колонку, указанную в курсоре. Эта процедура используется только в SELECT-курсорах.

Пакет DBMS_SQL.
Основные функции и процедуры
FETCH_ROWS
(cursor IN INTEGER) RETURN INTEGER
Извлекает очередную строку из курсора. После извлечения строки в локальный буфер (с помощью FETCH_ROWS) программа должна вызвать процедуру COLUMN_VALUE, чтобы прочитать извлеченную строку. Программа может неоднократно использовать функцию FETCH_ROWS, чтобы извлекать строки из курсора, до тех пор пока не будут исчерпаны все строки.
COLUMN_VALUE
(cursor IN INTEGER,
possition IN INTEGER,
value OUT NUMBER | VARCHAR2 | DATE | MLSLABEL
[, error OUT NUMBER
, length OUT INTEGER ] )
Получает значение колонки. Эта процедура применяется для доступа к данным, предварительно выбранным посредством вызова функции FETCH_ROWS.

Пакет DBMS_SQL.
Вспомогательные функции
IS_OPEN (cursor IN INTEGER) RETURN BOOLEAN
Проверяет, открыт ли курсор.
LAST_ERROR_POSITION RETURN INTEGER
Если имеет место ошибка во время выполнения операции, находящейся в курсоре, функция возвращает относительную позицию колонки в курсорном предложении, которая послужила причиной ошибки.
LAST_ROW_COUNT RETURN INTEGER
Функция возвращает суммарное количество строк, извлеченных до сих пор из курсора.
LAST_ROW_ID RETURN ROWID
Функция возвращает значение ROWID последней строки, обработанной в курсоре.
LAST_SQL_FUNCTION_CODE RETURN INTEGER
Функция возвращает код функции SQL-предложения.

Пример 1. Процедура создания таблицы с помощью пакета dbms_sql (метод 1)
CREATE PROCEDURE create_temp_dept(tname IN OUT VARCHAR2) AS
cur INTEGER; -- хранит идентификатор (ID) курсора
ret INTEGER; -- хранит возвращаемое по вызову значение
str VARCHAR2(250); -- хранит команды
BEGIN
/* генерация временной таблицы по имени DEPT, используя заранее
* заданное (hard-coded) имя и возврат значения функции
* DBMS_SESSION.UNIQUE_SESSION_ID */
tname := 'dept' || dbms_session.unique_session_id;
-- генерация команды CREATE TABLE по заранее заданному тексту
-- и переменной tname
str := 'CREATE TABLE '||tname
|| ' (deptno INTEGER,'
|| ' dname VARCHAR2(14),'
|| ' loc VARCHAR2(13), '
|| 'TABLESPACE temp '
|| 'STORAGE ('|| 'INITIAL 10K NEXT 10K MAXEXTENTS 2 )';
-- Динамически формируемое DDL SQL-предложение по методу 1
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, str, dbms_sql.v7);
ret := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
END;

Пример 2. Использование пакета dbms_sql (методы 1,2,3)
Перечень действий примера:
Создать таблицу demo_tbl. (Незапросное предложение, DDL.)
Структура таблицы следующая:
ID NUMBER(3),
NAME VARCHAR2(20)
Вставить в неё 5 строк с ID 1..5. (Незапросное предложение, DML.)
Удалить строку с номером 4. (Незапросное предложение, DML.)
Считать и распечатать строки из таблицы с ID=2,3,5.
(Запросное предложение.)
Удалить таблицу demo_tbl. (Незапросное предложение, DDL.)

Текст примера 2 (использование пакета dbms_sql)
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);

Продолжение текста примера 2 (использование пакета dbms_sql)
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; -- фиксация изменений

Завершение текста примера 2 (использование пакета dbms_sql)
/* считывание строк с 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 (начиная с версии 8i)
(Native dynamic SQL, NDS)
При работе со встроенным динамическим SQL для выполнения предложений, не возвращающих множественные результаты, используется следующая конструкция:
EXECUTE IMMEDIATE предложение_SQL
[ INTO { переменная1 [, переменная2 ] ... | запись } ]
[ USING [ IN | OUT | IN OUT ] связанный_аргумент1 [,
[ IN | OUT | IN OUT ] связанный_аргумент2 ] ... ];
INTO – определяет приёмник результата.
USING – определяет типы и порядок аргументов, используемых для передачи значений в запрос и приёма данных результата. Замена аргументов на их значения происходит позиционно.
Ограничения на передаваемые аргументы:
Нельзя передавать значения типа boolean, index-by таблицы, записи.
Нельзя передавать NULL как литерал (только переменную со значением null или функцию, которая возвращает null).

Примеры использования NDS
Пример 1. Удаление таблицы.
execute immediate 'drop table temp_table';
Пример 2. Изменение зарплаты сотрудникам определенного отдела.
execute immediate 'update EMP set salary=salary*:num where depno=:did'
USING v_num, v_did;
Пример 3. Получение информации о количестве записей в таблице.
…
v_name varchar2(40);
v_cnt number;
begin
execute immediate 'select count(*) from ' || v_name INTO v_cnt;
Пример 4. Получение информации об определенном сотруднике.
…
v_emp emp%ROWTYPE;
begin
execute immediate 'select count(*) from ' || name
into var1;

Специальные конструкции NDS
Специальные конструкции для работы с запросами, порождающими множественные результаты:
OPEN { курсорная_переменная | :хост_переменная }
FOR предложение_SQL
[ USING связанный_аргумент1 [, связанный_аргумент2 ] ... ];
Оператор OPEN исполняет запрос, позиционирует курсор на первую запись и устанавливает %ROWCONT в 0.
FETCH { курсорная_переменная | :хост_переменная }
INTO { переменная1 [, переменная2 ] ... | запись };
Оператор FETCH извлекает очередную строку (запись) из курсора и увеличивает значение %ROWCONT на 1. Если строка считана, устанавливает %FOUND в TRUE; если курсор исчерпан, устанавливает %NOTFOUND в TRUE.
CLOSE { курсорная_переменная | :хост_переменная };
Оператор CLOSE закрывает открытый курсор.

Примеры пакетного и встроенного динамического SQL
-- Решение с помощью пакета DBMS_SQL
CREATE OR REPLACE PROCEDURE runddl (ddl_in IN VARCHAR2) IS
cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
fdbk INTEGER;
BEGIN DBMS_SQL.PARSE (cur, ddl_in, DBMS_SQL.NATIVE);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( 'RunDDL Failure on ' || ddl_in);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/
-- Решение с помощью встроенного SQL
CREATE OR REPLACE PROCEDURE runddl81 (ddl_in IN VARCHAR2)
AUTHID CURRENT_USER IS
BEGIN
EXECUTE IMMEDIATE ddl_in;
END;
/

Реализация примера 2 с помощью NDS
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;

Реализация на NDS примера 2 (продолжение)
BEGIN
EXECUTE IMMEDIATE vCreateTable; -- создаём таблицу FOR vId IN 1..5 LOOP -- добавляем данные
EXECUTE IMMEDIATE vInsertTable USING vId, 'Name ╪'||vId ;
END LOOP;
COMMIT;
EXECUTE IMMEDIATE vDeleteTable USING 3; -- удаляем строку с ID=3
COMMIT;
OPEN c FOR vSelectTable USING 2, 5; -- считаем строки с ID=2,3,5
LOOP FETCH c INTO vId, vName;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vId||' '||vName);
END LOOP;
CLOSE c;
-- если запрос возвращает не более одной строки, можно записать так:
EXECUTE IMMEDIATE vSelectTable INTO vId, vName USING 3, 4;
DBMS_OUTPUT.PUT_LINE('-------------------');
DBMS_OUTPUT.PUT_LINE(vId||' '||vName);
EXECUTE IMMEDIATE vDropTable; -- удаляем таблицу
END;