Информационное обеспечение
жүктеу/скачать 256,49 Kb.
|
| оператор_SQL – любой оператор SQL или PL/SQL-блок;
переменная1, переменная2 или запись – переменные PL/SQL, в которые необходимо выбрать данные (столбцы одной строки результатов оператора SELECT); связываемый_аргумент1, связываемый_аргумент2 – набор переменных PL/SQL, используемых для передачи входных данных/результатов; результат1,…, результатN – набор PL/SQL-переменных, используемых для размещения результатов, возвращаемых конструкцией RETURN. Ограничения на передаваемые аргументы: Нельзя передавать значения типа boolean, таблицы index-by, записи. Нельзя передавать NULL как литерал (только переменную со значением NULL или функцию, которая возвращает NULL). DML-команда в EXECUTE IMMEDIATE выполняется в контексте текущей транзакции. DDL-команда в EXECUTE IMMEDIATE выполнит COMMIT всех измененных данных. Рассмотрим примеры использования, встроенного SQL в процедурах и функциях: Для создания, изменения или удаления таблицы в коде PL/SQL: EXECUTE IMMEDIATE 'DROP TABLE temp_table'; Для изменения заработной платы сотрудникам определенного отдела, указываемого только при выполнении: EXECUTE IMMEDIATE 'UPDATE emp SET salary=salary*:num WHERE depno=:did' USING v_num, v_did; Для получения информации о количестве записей в неизвестной заранее таблице: EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || v_name INTO v_cnt; Для получения информации об определенном сотруднике: EXECUTE IMMEDIATE 'SELECT name, post FROM ' || tabname || 'WHERE tabno=:id' INTO vname, vpost USING vid; Для выполнения запроса, подсчитывающего количество строк в любой таблице базы данных, к которой пользователь имеет доступ: CREATE OR REPLACE FUNCTION get_row_cnts(p_tname in VARCHAR2) return NUMBER AS l_cnt number; BEGIN EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || p_tname INTO l_cnt; RETURN l_cnt; END; / Для работы с запросами, порождающими множественные результаты, существуют три конструкции: OPEN{ курсорная_переменная | :хост_переменная} FOR предложение_SQL [ USING связанный_аргумент1 [, связанный_аргумент2] ... ]; Оператор OPEN исполняет запрос, позиционирует курсор на первую запись и устанавливает %ROWCONT в 0. FETCH{ курсорная_переменная | :хост_переменная} INTO{ переменная1[, переменная2 ] ... | запись }; Оператор FETCH извлекает очередную строку (запись) из курсора и увеличивает значение %ROWCONT на 1; если строка считана, устанавливает %FOUND в TRUE; если курсор исчерпан, устанавливает %NOTFOUND в TRUE. CLOSE{ курсорная_переменная | :хост_переменная}; Оператор CLOSE закрывает открытый курсор. Рассмотрим приведенные конструкции на примере. С помощью курсорных переменных и динамического SQL реализуем обобщенную процедуру запроса к таблицe в зависимости от входных данных и возвращения результирующего множества клиенту для дальнейшей обработки. CREATE OR REPLACE PACKAGE my_pkg AS type refcursor_type IS REF CURSOR; PROCEDURE get_emps (p_ename IN VARCHAR2 DEFAULT NULL, p_deptno IN VARCHAR2 DEFAULT NULL, p_cursor IN OUT refcursor_type); END; / CREATE OR REPLACE PACKAGE BODY my_pkg AS PROCEDURE get_emps (p_ename IN VARCHAR2 DEFAULT NULL, p_deptno IN VARCHAR2 DEFAULT NULL, p_cursor IN OUT refcursor_type) IS l_query LONG; l_bind VARCHAR2(30); BEGIN l_query := 'SELECT first_name, job_id FROM emp'; IF (p_ename IS NOT NULL) THEN l_query := l_query || ' WHERE first_name LIKE(:x)'; l_bind := '%' || UPPER(p_ename) || '%'; ELSIF (p_deptno IS NOT NULL) THEN l_query := l_query || ' WHERE job_id = TO_NUMBER(:x)'; l_bind := p_deptno; ELSE RAISE_APPLICATION_ERROR (-20001,'Missing search criteria'); END IF; OPEN p_cursor FOR l_query USING l_bind; END; END; / Для выполнения процедуры в SQL*Plus можно использовать команду EXEC или анонимный блок: VARIABLE C REFCURSOR SET AUTOPRINT ON BEGIN my_pkg.get_emps (p_ename=>'a', p_cursor=>:C); END; / Приведем еще пример – анонимный блок, в котором создается и заполняется данными таблица: DECLARE
val2 VARCHAR2(5) := 'test'; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE test_dyn_sql (value1 NUMBER(2), value2 VARCHAR2(5))'; EXECUTE IMMEDIATE 'INSERT INTO test_dyn_sql (value1, value2) VALUES (:1, :2)' USING val1, val2; COMMIT; END; / При выполнении динамической SQL-инструкции (это DML- или DDL- строки, не оканчивающиеся точкой с запятой) параметр подстановки необхо- димо задать для каждой позиции, даже если их имена повторяются. При выполнении динамического блока PL/SQL параметр подстановки необходимо указывать для каждого уникального формального параметра. CREATE OR REPLACE PROCEDURE updnumval ( coljn IN VARCHAR2, startjn IN DATE, endjn IN DATE, valjn IN NUMBER) IS dml_str VARCHAR2(32767) := 'UPDATE emp SET ' || coljn || ' = :val WHERE hiredate BETWEEN: lodate AND: hidate AND: val IS NOT NULL'; BEGIN EXECUTE IMMEDIATE dml_str USING valjn, startjn, endjn, valjn; END; / CREATE OR REPLACE PROCEDURE updnurwal ( coljn IN VARCHAR2, startjn IN DATE, endjn IN DATE, val IN NUMBER) IS dml_str VARCHAR2(32767) := 'BEGIN UPDATE emp SET ' || coljn || ' - :val WHERE hiredate BETWEEN startjn AND endjn AND :val IS NOT NULL; END;'; BEGIN EXECUTE IMMEDIATE dml_str USING val, startjn, endjn; END; / По сравнению с пакетом DBMS_SQL использование EXECUTE IMMEDIATE значительно проще и понятнее. Однако в отличие от EXECUTE IMMEDIATE пакет DBMS_SQL позволяет выполнять выражения динамическо- го SQL, в которых неизвестны число параметров и набор столбцов, возвращае- мых запросом или, другими словами, пакет необходимо использовать для ди- намического SQL с заранее неизвестным количеством входных или выходных переменных. жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
|