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

ше кода, чем при использовании встроенного динамического SQL, но производительность при этом существенно возрастает.

Проблемы

Как и в случае любого средства, при использовании динамического SQL есть ряд нюансов, которые необходимо учитывать. В этом разделе мы рассмотрим их последовательно. При использовании динамического SQL в хранимых процедурах возникает три основных проблемы:

нарушается цепочка зависимостей;

код становится более хрупким ;

настройка, обеспечивающая предсказуемое время выполнения, существенно усложняется.

Нарушение цепочки зависимостей

Обычно при компиляции процедуры в базе данных все объекты, на которые она ссылается, а также все объекты, ссылающиеся на нее, регистрируются в словаре данных. Например, я создам функцию:

ops$tkyte@DEV816> create or replace function count emp return number

2 as

3 l cnt number;

4 begin

5 select count(*) into l cnt from emp;

6 return l cnt;

7 end;

Function created.

ops$tkyte@DEV816> select referencedname, referencedtype

2 from user dependencies

3 where name = COUNT EMP

4 and type = FUNCTION

REFERENCED NAME REFERENCED T

STANDARD PACKAGE

SYS STUB FOR PURITY ANALYSIS PACKAGE

EMP TABLE

3 rows selected.

Сравним это с тем, что зарегистрировано при последнем создании использующей встроенный динамический SQL функции GET ROW CNTS:

ops$tkyte@DEV816>selectreferenced name,referenced type

2 from user dependencies

3 where name = GET ROW CNTS

1244

Глава 16

4 and type = FUNCTION

REFERENCED NAME REFERENCED T

STANDARD PACKAGE

SYS STUB FOR PURITY ANALYSIS PACKAGE

2 rows selected.

Для функции, статически ссглающейся на таблицу ЕМР, эта ссглка зарегистрирована в таблице зависимостей. Для функции же с динамическим SQL - нет, поскольку она не зависит от таблицы ЕМР. В данном случае это вообще не проблема, поскольку использование динамического SQL дает другое существенное преимущество - возможность определить количество строк в таблице. Ранее, однако, мы иногда использовали динамический SQL без особой нужды, и эта нарушенная цепочка зависимостей - очень плохой побочный эффект. Необходимо знать, на какие объекты ссглаются процедуры и где они используются. При использовании динамического SQL такие взаимосвязи не отслеживаются.

Хрупкость кода

При использовании только статического SQL можно быть уверенным, что если ух программа успешно скомпилирована, во встроенных в нее SQL-операторах нет синтаксических ошибок, т.к. при компиляции все проверяется. При использовании динамического SQL его корректность будет определена только на этапе выполнения. Более того, поскольку SQL-операторы создаются динамически, необходимо проверять возможнге ветвления кода, чтобы убедиться в корректности генерируемого SQL-кода. Если при определенных входных данных генерируется корректный SQL-оператор, это еще не означает, что все будет работать при любых входнгх данных. Это верно для любого кода, но использование динамического SQL делает код несколько уязвимым.

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

Сложность настройки

Это неочевидно, но приложение, динамически создающее запросы, настраивать сложно. Обычно можно получить полный список запросов, используемых приложением, выделить те из них, которые влияют на производительность, и бесконечно их настраивать. Если набор выполняемых приложением запросов не известен до того, как приложение начнет работать, нельзя точно узнать, какой будет его производительность. Предположим, создается хранимая процедура, динамически строящая запрос на основе введен-н1х пользователем в Web-форме данных. Если не протестировать все возможные запросы, которые могут быть сгенерированы этой процедурой, нельзя понять, все ли необходимые индексы созданы и можно ли вообще считать настройку системы законченной. Даже при небольшом количестве столбцов (скажем, пяти) могут быть заданы десятки условий. Это не означает, что следует отказаться от использования динамичес-

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

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

Резюме

В этой главе мы детально изучили использование динамического SQL в хранимых процедурах; рассмотрели различия между его реализацией средствами встроенного динамического SQL и пакета DBMS SQL; выяснили, когда использовать тот или иной подход. Оба подхода имеют свои преимущества и назначение.

Динамический SQL позволяет создавать процедуры, реализовать которые иначе просто невозможно, - универсальные утилиты выгрузки и загрузки данных и т.п. На сайте издательства Wrox вы можете найти программы, использующие динамический SQL, утилиту для загрузки файлов dBASE III в базу данных Oracle с помощью PL/SQL, сценарий для печати результатов выполнения запросов в SQL*Plus по столбцам (об этом мы поговорим подробно в главе 23), программы для транспонирования результирующих множеств и многое другое.