Операторы встроенного динамического SQL

EXECUTE IMMEDIATE строка _SQL

[INTO { список_переменных | запись | объектная_переменная }]

[USING

[IN | OUT | IN OUT] связная_переменная [, [IN | OUT | IN OUT] связная_переменная ]...

]

[{RETURNING | RETURN} INTO список_связных_переменных ];

 

строка_ SQL может быть любым SQL-предложением за исключением SELECT с множественным результатом. Примеры:

 

EXECUTE IMMEDIATE ‘TRUNCATE TABLE emp’;

EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘ || table_name || ‘ TO ‘ || grantee_list;

 

Сравните последнее предложение с

 

EXECUTE IMMEDIATE ‘GRANT SELECT ON:table TO:grantees’

USING IN table_name, IN grantee_list;

 

(Результат тот же, но для СУБД оно в целом более предпочтительно).

 

Другое предложение можно использовать для SELECT с множественным результатом:

 

OPEN ссылка_на_курсор FOR предложение_ SELECT [USING список_связных_переменных ];

 

(Вместе с ним будут использоваться предложения

 

FETCH ссылка_на_курсор INTO { список_переменных | запись | объектная_переменная };

 

CLOSE ссылка_на_курсор;)

 

Пример:

 

DECLARE

           TYPE cv_type IS REF CURSOR;

           cv cv_type;

           vename VARCHAR2 (14);

           vsal NUMBER:= 1000;

BEGIN

           OPEN cv FOR ‘SELECT ename, sal FROM emp WHERE sal > ’ || TO_CHAR (vsal);

           LOOP

                           FETCH cv INTO vename, vsal;

                           EXIT WHEN cv%NOTFOUND;

                           DBMS_OUTPUT.PUT_LINE (RPAD (vename,15) || vsal);

-- дальше обрабатываем строку, как требуется …

--

           END LOOP;

           CLOSE cv;

END;

/

 

В отличие от статического, динамический SQL позволяет выполнять из программы не только DML- и DCL-, но и DDL-операторы.

 

 

8.2. Сравнительный пример двух способов работы с динамическим SQL

Для сравнения степени сложности работы с динамическим SQL средствами пакета DBMS_SQL и встроенного динамического SQL можно привести два простых примера определения одной и той же, в конечном счете, функции.

 

Пример с использованием пакета DBMS_SQL:

 

CREATE OR REPLACE PROCEDURE exec0 (string IN VARCHAR2)

AS

cursor_name INTEGER;

ret INTEGER;

BEGIN

cursor_name:= DBMS_SQL.OPEN_CURSOR;

 

-- DDL statements are run by the parse call, which performs the implied commit.

 

DBMS_SQL.PARSE (cursor_name, string, DBMS_SQL.NATIVE);

ret:= DBMS_SQL.EXECUTE (cursor_name);

DBMS_SQL.CLOSE_CURSOR (cursor_name);

END;

/

 

 

Пример с использованием встроенного динамического SQL:

 

CREATE OR REPLACE PROCEDURE exec1 (string IN VARCHAR2)

AS

BEGIN

EXECUTE IMMEDIATE string;

END;

/

 

Все же у пакета DBMS_SQL есть свои преимущества перед встроенным динамическим SQL. Например, этот пакет:

 

- обеспечивает работу с текстами запросов размером более 32 Кб

- транслирует запросы в клиентской программе и может многократно повторять запрос без перетрансляции, что эффективнее полных повторений всего цикла обработки запросов, отсылаемых на сервер встроенным динамическим SQL

 

 

Использование курсоров

Курсор – именованная область, выделяемая СУБД для обработки каждого SQL-запроса. В программе – переменная «типа курсор».

 

 

Явные курсоры

Курсоры для SQL-предложений, явно объявляемые в разделе описаний блока PL/SQL.

 

 

Объявление явных курсоров

Три возможных вида объявления явных курсоров.

 

Курсоры без параметров:

 

CURSOR employee_cur IS SELECT ename FROM emp;

 

Курсоры с параметрами:

 

CURSOR employee_cur (department IN NUMBER) IS

           SELECT ename FROM emp WHERE deptno = department;

 

В этом примере можно было бы написать (department IN NUMBER DEFAULT 10), и тогда бы это был параметр с умолчанием. Такие параметры можно не указывать при открытии курсора.

 

Заголовок курсора в интерфейсной части пакета (позволяет спрятать реализацию курсора в тело пакета, не предъявляя его в описание):

 

CURSOR employee_cur RETURN emp%ROWTYPE;

 

(В последнем случае в теле пакета может быть указано что-то вроде

 

CURSOR employee_cur RETURN emp%ROWTYPE IS SELECT * FROM emp;)

 

 

Открытие явных курсоров

OPEN имя_курсора [(список выражений для параметров)];

 

Если параметры имеются, но все допускают умолчание, можно написать (с равным успехом) OPEN имя_курсора () или же просто OPEN имя_курсора.

 

По команде OPEN будет вычислен план выполнения запроса; к участкам памяти в курсоре будут привязаны связные переменные и параметры курсора; будет вычислен формат результата; указатель результата встанет на первую строку выдачи.

 

В цикле FOR операция OPEN выполняется неявно.

 

 

Извлечение результата через явный курсор

FETCH имя_курсора INTO запись_или_список_переменных;

 

Извлекается ровно одна строка результата.

 

 

Закрытие явного курсора

CLOSE имя_курсора;

 

Закрытие курсора после исчезновения в нем необходимости освобождает память для других запросов. Курсор, открытый в анонимном блоке или в блоке процедуры или функции закрывается автоматически по окончанию работы блока. Глобальные курсоры пакета автоматически закрываются только по окончанию сеанса связи с БД.