'

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

Понравилась презентация – покажи это...





Слайд 0

Динамический SQL (использование в Oracle)


Слайд 1

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


Слайд 2

Пакет 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) Закрывает открытый курсор.


Слайд 3

Пакет 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-курсорах.


Слайд 4

Пакет 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.


Слайд 5

Пакет 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-предложения.


Слайд 6

Пример 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;


Слайд 7

Пример 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.)


Слайд 8

Текст примера 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);


Слайд 9

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


Слайд 10

Завершение текста примера 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;


Слайд 11

Встроенный 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).


Слайд 12

Примеры использования 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;


Слайд 13

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


Слайд 14

Примеры пакетного и встроенного динамического 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; /


Слайд 15

Реализация примера 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;


Слайд 16

Реализация на 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;


Слайд 17

Сравнение возможностей пакетного и встроенного динамического SQL Встроенный динамический SQL: Работает со всеми без исключения типами данных Oracle, включая и типы объектов, заданные пользователем, и типы коллекции (переменные массивы, вложенные таблицы, индексированные таблицы). Позволяет извлекать множественные данные (серию строк) непосредственно в конструкцию PL/SQL. Допускает использование RETURNING только в единственном запросе. Пакетный динамический SQL: Поддерживает “Метод 4” пакетного SQL, при котором во время компиляции не фиксируется число извлекаемых столбцов или число переменных привязки. Позволяет описывать столбцы динамического курсора так, чтобы те получали значения из столбцов индексированной (index-by) таблицы записей. Работать с SQL-предложениями длиной более 32К. Возвращать данные с помощью RETURNING в массив переменных. Повторно использовать динамические курсоры, что улучшает производительность. Выполняться на клиентской части приложения, например, в Oracle Developer. DBMS_SQL позволяет работать лишь с типами данных, совместимыми с Oracle7. В DBMS_SQL данные извлекаются построчно в отдельную запись.


×

HTML:





Ссылка: