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

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

create or replace procedure DynEmpProc(p_job in varchar2)
as
type refcursor is ref cursor;
 -- При использовании динамического SQL необходимо
 -- создать хост-переменные и выделить ресурсы.
 l_cursor refcursor;
 l_ename emp.ename%type;
 begin
 -- Начинаем с анализа запроса
 open l_cursor for
 'select ename
 from emp
 where job = :x' USING in p_job;
 loop
 -- и явно ВЫБИРАЕМ данные через курсор.
 fetch l_cursor into l_ename;
 -- Необходимо самостоятельно обрабатывать ошибки
-- и делать выборку
 exit when l_cursor%notfound;
 dbms_output.put_line(l_ename);
 end loop;
 -- He забываем освободить ресурсы
 close l_cursor;
 exception
 when others then
 -- а также перехватить и обработать все ошибки,
 -- чтобы не допустить утечки ресурсов
 -- при возникновении ошибок.
 if (l_cursor%isopen)
 then
 close l_cursor;
 end if;
 RAISE;
 end;
 /
Procedure created.

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

create or replace procedure StaticEmpProc(p_job in varchar2)
as
begin
 for x in (select ename from emp where job = p_job)
 loop
 dbms_output.put_line(x.ename);
 end loop;
 end;
  /
Procedure created.

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

set serveroutput on size 1000000
exec DynEmpProc('CLERK')
SMITH
ADAMS
JAMES
MILLER
PL/SQL procedure successfully completed.
exec StaticEmpProc('CLERK')
SMITH
ADAMS
JAMBS
MILLER
PL/SQL procedure successfully completed.

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