1204 Глава 16

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

разберемся, как использовать динамический SQL в программах с помощью средств стандартного пакета DBMS SQL;

изучим возможности встроенного (native) динамического SQL;

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

Для выполнения всех примеров динамического SQL в этой главе необходим сервер Oracle 8.1.5 или более новых версий. Встроенный динамический SQL появился именно в этой версии и является одной из важнейших возможностей всех последующих версий. Для выполнения большинства примеров, в которых используются средства пакета DBMS SQL, достаточно сервера Oracle версии 7.1 или более новых версий (правда, функции, обрабатывающие массивы, появились в пакете DBMS SQL в версии 8.0).

Сравнение динамического и статического SQL

Использование динамического SQL - естественная возможность работать с базой данных через функциональный интерфейс, такой как ODBC, JDBC и OCI. Статический SQL обычно принято использовать в средах с предварительной компиляцией кода, таких как Pro*C, SQLJ и PL/SQL (я не оговорился: компилятор PL/SQL можно рассматривать как прекомпилятор). При работе через функциональный интерфейс поддерживается только динамический SQL. Программист создает запрос в виде строки, а затем эта строка анализируется, связываются входящие в нее переменные, запрос выполняется, при необходимости выбираются строки из результирующего множества через курсор и, наконец, соответствующий курсор закрывается. В среде статического SQL эти действия выполняются автоматически. Для сравнения создадим две выполняющие одинаковые действия PL/SQL-процедуры: одну с - использованием динамического SQL, а вторую - с использованием статического. Вот версия на основе динамического SQL:

scott@TKYTE816> create or replace procedure DynEmpProc(p job in varchar2)

2 as

3 type refcursor is ref cursor; 4

5 -- При использовании диначеского SQL необходимо

6 -- создать хост-переменные и выделить ресурсы.

7 l cursor refcursor;

8 l ename emp.ename%type;

9 begin

11 -- Начинаем с анализа запроса

12 open l cursor for

13 select ename

14 from emp

15 where job = :x USING in p job;

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

17 loop

18 -- и явно ВЫБИРАЕМ данные через курсор.

19 fetch l cursor into l ename; 20

21 -- Необходимо самостоятельно обрабатывать ошибки

22 -- и делать выборку

23 exit when l cursor%notfound; 24

25 dbms output.put line(l ename);

26 end loop;

28 -- He забываем освободить ресурсы

29 close l cursor;

30 exception

31 when others then

32 -- а также перехватить и обработать все ошибки,

33 -- чтобы не допустить утечки ресурсов

34 -- при возникновении ошибок.

35 if (l cursor%isopen)

36 then

37 close l cursor;

38 end if; 3 9 RAISE;

40 end;

41 /

Procedure created.

А вот что мы имеем в случае статического SQL:

scott@TKrE816> create or replace procedure StaticEmpProc(p job in varchar2)

2 as

3 begin

4 for x in (select ename from emp where job = p job)

5 loop

6 dbms output.put line(x.ename);

7 end loop;

8 end;

Procedure created.

Эти две процедуры делают то же самое:

scott@TKYTE816> set serveroutput on size 1000000 scott@TKYTE816> exec DynEmpProc(CLERK)

SMITH ADAMS JAMES

MILLER

PL/SQL procedure successfully completed. scott@TKYTE816> exec StaticEmpProc(CLERK)

1206

Глава 16

SMITH ADAMS JAMBS MILLER

PL/SQL procedure successfully completed.

Понятно, однако, что версия с динамическим SQL требует от разработчика написания гораздо большего объема кода. По опыту знаю: статический SQL обеспечивает более высокую производительность труда программиста при написании кода (приложения разрабатываются быстрее), но динамический SQL обеспечивает большую гибкость при выполнении (программа в ходе работы может делать то, что не внесено в ее код явно). Кроме того, статический SQL (особенно в среде PL/SQL) будет выполняться намного эффективнее, чем динамический. Используя статический SQL, PL/SQL-машина при обработке одной строки интерпретируемого кода может сделать то, на что потребуется пять или шесть строк интерпретируемого кода с динамическим SQL. Поэтому я использую статический SQL где только возможно и применяю динамический, только если по-другому задачу решить нельзя. Оба они эффективны, ни один не имеет принципиальных преимуществ перед другим, и оба имеют свои специфические возможности и средства повышения производительности.

Когда использовать динамический SQL?

Многие задачи требуют использования динамического SQL в PL/SQL. Вот лишь некоторые из них.

Разработка обобщенных процедур, выполняющих стандартные действия вроде выгрузки данных в файлы. В главе 9 был представлен пример такой процедуры.

Разработка универсальных процедур загрузки данных в не известные заранее таблицы. Мы рассмотрим использование динамического SQL для загрузки данных в таблицу.

Динамический вызов других PL/SQL-процедур во время выполнения. Эта тема

затрагивается в главе 23. Здесь мы рассмотрим ее более детально.

Генерация условий (например, конструкции WHERE) в процессе работы на ос-

нове введенных пользователем данных. Это, пожалуй, основная причина использования динамического SQL большинством разработчиков. Я покажу в этой главе, как это надо (и как не надо!) делать.

Выполнение операторов ЯОД. Поскольку PL/SQL не разрешает включать статические операторы ЯОД в код приложения, остается использовать динамический SQL. Это позволит выполнять операторы, начинающиеся с ключевых слов CREATE, ALTER, GRANT, DROP и т.п.

Решаться перечисленные задачи будут с помощью двух средств языка PL/SQL. Сначала мы рассмотрим использование стандартного пакета DBMS SQL. Этот пакет существует уже достаточно давно, он появился в версии 7.1. Пакет обеспечивает процедурный метод выполнения динамического SQL, аналогичный использованию