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

Понятие привязки. Из всех SQL-операторов в программах PL/SQL напрямую можно использовать лишь операторы DML и операторы управления транзакциями. Операторы DDL использовать нельзя. Чтобы пояснить смысл этих ограничений, рассмотрим принцип создания программ PL/SQL.

В любом языке программирования привязка переменных может быть либо ранней, либо поздней. Привязка (binding) переменной – это процесс указания области памяти, соответствующей некоторому идентификатору программы. В PL/SQL в процесс привязки входит также проверка БД на наличие полномочий, позволяющих обращаться к объектам схем. В том языке, где используется ранняя привязка (early binding), этот процесс осуществляется на этапе компиляции программы, а в языке, где применяется поздняя привязка (late binding), откладывается до времени выполнения программы. Ранняя привязка означает, что этап компиляции программы будет занимать большее время (так как при этом нужно привязывать переменные), однако выполняться программа будет быстрее, поскольку к этому моменту привязка будет завершена. Таким образом, поздняя привязка сокращает время компиляции, но увеличивает время выполнения программы.

При разработке PL/SQL было принято решение об использовании ранней привязки, чтобы объекты БД к моменту выполнения блока были уже проверены, и чтобы блок мог быть выполнен максимально быстро. Это вполне оправданно, так как блоки PL/SQL можно хранить в БД как процедуры, функции, модули и триггеры. Такие объекты хранятся в скомпилированном виде, то есть при необходимости их можно загрузить из БД и выполнить.

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

Рассмотрим блок:

declare

v_SQLString varchar2 (100);

v_SetClause varchar 2( 100);

v_WhereClause varchar2 (100);

begin

v_SetClause:= ‘SET salary=:salary WHERE’;

v_WhereClause:=’position=:v_position’;

v_SQLString:= ‘UPDATE staff‘ || v_SetClause || v_WhereClause

DoIt (v_SQLString);

end;

Этот блок нельзя запустить на выполнение сразу же после его написания, так как предварительно нужно создать процедуру DoIt ().

Отметим, что посредством создания корректной функции DoIt () всегда можно выполнить произвольную SQL-инструкцию, задав набор подстрок этой инструкции, а затем, осуществив их конкатенацию (||).

Однако функция DoIt () в таком случае должна осуществлять:

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

2. связывание параметров SQL-инструкции;

3. проверку корректного выполнения ORACLE Server заданной инструкции.

Таким образом, реализовать функцию обрабатывающую любую SQL-инструкцию практически очень сложно из-за произвольности количества и типов связываемых параметров различных инструкций. Однако создать функцию наподобие DoIt (), которая бы выполняла бы инструкции с фиксированным количеством параметров определенного типа в принципе возможно.

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

Итак, если структура SQL-операторов известна уже во время компиляции, то программы являются статическими. Если же привязку параметров и определение прав доступа нужно определять «на лету», то речь идет о динамическом SQL, который обеспечивает возможность использования всех SQL-операторов в PL/SQL.

Существует два способа применения динамического SQL:

1. Стандартный пакет PL/SQL – DBMS_SQL.

2. Встроенный динамический SQL, который появился в ORACLE 8 i. При его использовании динамические операторы управления встраиваются непосредственно в язык PL/SQL и поэтому выполняются значительно быстрее, чем в пакете DBMS_SQL.

Пакет DBMS_SQL используется, если на этапе компиляции неизвестна структура курсора, число и типы переменных привязки. Он позволяет выяснить структуру курсора в реальном режиме времени, но имеет достаточно сложный синтаксис.

Встроенный динамический SQL (Native Dynamic SQL – NDS) основывается на применении оператора EXECUTE IMMEDIATE и курсорном цикле OPEN … FOR, которые имеют простой и короткий синтаксис, но при этом число и типы переменных привязки должны быть известны во время компиляции, как и структура курсора.

Пакет DBMS_SQL. Общий алгоритм выполнения SQL-операторов с помощью DBMS_SQL состоит из следующих этапов:

1. преобразование SQL-оператора в строку символов – синтаксический разбор;

2. грамматический разбор строки символов с помощью DBMS_SQL.PARSE;

3. привязка всех входных переменных с помощью DBMS_SQL.BIND_VARIABLE;

4. если выполняемый оператор это оператор DML (UPDATE, DELETE, INSERT) или DDL, то выполнение его с помощью DBMS_SQL.EXECUTE с последующим считыванием выходных переменных привязки с помощью DBMS_SQL.VARIABLE_VALUE (если нужно);

5. если оператор осуществляет выборку данных (SELECT), то сначала описание выходных переменных с помощью DBMS_SQL.DEFINE_COLUMN;

6. затем выполнение запроса на выборку с помощью DBMS_SQL.EXECUTE и выборка результатов при помощи DBMS_SQL.FETCH_ROWS и DBMS_SQL.COLUMN_VALUE.

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

create or replace procedure RecreateTempTable

/* описание таблицы передается в процедуру с помощью р_Description и должно быть содержимым оператора CREATE TABLE, расположенным после имени таблицы. Например: RecreateTempTable (‘(num_col NUMBER, char_col VARCHAR2(2000))’) */

(р_Description in varchar2) is

v_Cursor number;

v_CreateString varchar2(100);

v_ DropString varchar2(100);

begin

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

v_Cursor:= DBMS_SOL.OPEN_CURSOR;

--удаляем таблицу;

v_DropString:= ‘DROP TABLE temp table’;

/* проводим грамматический разбор команды ‘DROP TABLE’ и выполняем ее. Эти операции выполняются с помощью DBMS_SQL.PARSE. Если таблица не существует, устанавливается ошибка ORA-942.*/

begin

DBMS SQL.PARSE (v_Cursor, v_DropString, DBMS_SQL.NATIVE);

exception

when others then

if SQLCODE!= -942 then

RAISE;

end if;

end;

/* Далее создадим таблицу. Сначала необходимо инициализировать строку символов CREATE TABLE, а затем провести грамматический разбор и выполнить DDL-оператор вызовом DBMS_SQL.PARSE*/

v CreateString:= ‘CREATE TABLE temp table ’ || р_Description;

DBMS_SQL.PARSE (v_Cursor, v _ CreateString, DBMS_SQL.NATIVE);

--закрываем курсор;

DBMS_SQL.СLOSE_CURSOR (v_ Cursor);

exception

when others then

/* сначала закрываем курсор, а затем переустанавливаем ошибку, чтобы передать ее в вызывающий блок*/

DBMS_SQL.CLOSE_CURSOR (v _ Cursor);

raise;

end RecreateTempTable;

При анализе рассмотренного примера необходимо обратить внимание на следующее:

- строка символов, грамматический разбор которой выполняется, может быть константой (здесь v_DropString), а также может создаваться программой динамически, с помощью символьных функций, например с помощью функции конкатенации (здесь v_CreateString);

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

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

На рисунке приведена блок-схема алгоритма использования пакета DBMS_SQL.

Рисунок Блок-схема алгоритма обработки данных в DBMS_SQL

?ДА в блоке оператор DDL

С помощью DBMS_SQL можно обрабатывать операторы трех видов: SQL операторы (DML, DDL и ALTER SESSION), запросы и анонимные блоки PL/SQL. Каждый вид обрабатывается при помощи различных процедур, которые описаны ниже:

- OPEN_CURSOR. Как в статическом SQL, так и в динамическом РL/SQL каждый SQL-оператор выполняется в границах курсора. Кроме того, в динамическом РL/SQL можно управлять процессом обработки курсора. OPEN_CURSOR возвращает идентификационный номер курсора, используемый для обозначения контекстной области, в которой будет выполняться оператор. Этот номер будет указываться во всех последующих вызовах данного курсора.

- PARSE. Грамматический разбор оператора заключается в посылке его на сервер, где проверяются синтаксис и семантика данного оператора. Если он является запросом, то на этом этапе определяется план его выполнения. Если это оператор DDL, то он также и выполняется.

- BIND_VARIABLE. Привязка переменной к заполнителю аналогична процессу привязки, используемому в PL/SQL для статических SQL-операторов. Заполнитель (placeholder) – это специальный идентификатор в последовательности символов оператора. В процессе привязки устанавливается соответствие между заполнителем и фактической переменной, а пакету DBMS_SQL сообщается о типе и размере переменной. Привязка выполняется для входных переменных в SQL-операторах и для входных и выходных переменных в операторах PL/SQL. Переменные, использующиеся таким образом, называют переменными привязки.

- DEFINE_COLUMN. Описание элемента списка выбора аналогично привязке входной переменной. В данном случае переменными являются результаты запроса. Процедура DEFINE_COLUMN определяет тип и размер переменных PL/SQL, в которые будут записываться данные при считывании информации процедурой FETCH_ROWS. DEFINE_COLUMN используется только для элементов списка выбора (результатов запросов SELECT), а BIND_VARIABLE – для входных или выходных переменных (заполнителей, указанных в операторах).

- EXECUTE. Исполняет операторы, не являющиеся запросами (SELECT), и возвращает число обработанных строк. Для запросов же EXECUTE определяет активный набор (набор строк, удовлетворяющих условию WHERE запроса). После этого данные считываются процедурой FETCH_ROWS. Для любых операторов переменные привязки анализируются во время работы функции EXECUTE.

- FETCH_ROWS. С каждым вызовом процедуры FETCH_ROWS с сервера считывается все больший объем данных. Затем полученная информация преобразуется в типы данных, указанных процедурой DEFINE_COLUMN. Процедура EXECUTE_AND_FETCH позволяет объединить операции обработки и считывания данных в одном вызове.

- COLUMN_VALUE. Возвращает фактическую информацию после вызова FETCH_ROWS. В ней используются переменные того типа, который указан процедурой DEFINE_COLUMN. Следует применять только для запросов.

- CLOSE_CURSOR. После окончания обработки данных курсор закрывается. При этом ресурсы, использовавшиеся курсором, освобождаются.

ОБРАБОТКА ОПЕРАТОРОВ DML в DBMS_SQL. Для обработки операторов UPDATE, DELETE, INSERT средствами пакета DBMS_SQL необходимо последовательно выполнить следующие действия:

1. Открыть курсор. Осуществляется посредством вызова процедуры OPEN_CURSOR, описание которой в модуле выглядит следующим образом:

OPEN_CURSOR return integer;

Параметры в данной процедуре отсутствуют.

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

2. Выполнить грамматический разбор оператора. При выполнении грамматического разбора оператор направляется на сервер БД. Сервер проверяет его синтаксис и семантику и возвращает ошибку (устанавливая исключительную ситуацию), если нарушены требования грамматики. Кроме того, во время разбора определяется план выполнения оператора. Осуществляется грамматический разбор посредством вызова процедуры DBMS_SQL.PARSE, описание которой в пакете имеет следующий вид:

procedure PARSE (c in integer, statement in varchar2, language_flag in integer).

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

3. Привязка входных переменных. При выполнении операции, указанные в нем заполнители, связываются с фактическими переменными. Имена заполнителей обычно предваряют символом двоеточия. Процедура BIND_VARIABLE выполняет привязку и объявление имен заполнителей. Размер и тип данных фактических переменных также устанавливается BIND_VARIABLE посредством набора переопределенных вызовов:

procedure BIND_VARIABLE (c in integer, name in varchar2, value in number ),

procedure BIND_VARIABLE (c in integer, name in varchar2, value in varchar2 ),

procedure BIND_VARIABLE (c in integer, name in varchar2, value in varchar2, out_value_size in integer).

где name – это имя заполнителя, с которым будет связана переменная, value – реальные данные, которые будут привязываться, тип и размер этой переменной также считываются. При необходимости данные, содержащиеся в этой переменной, будут преобразованы. Параметр out_value_size – это параметр, задаваемый при привязке переменных varchar2 и char; если он указан, то это максимальный ожидаемый размер значения в байтах, если не указан, то используется размер, указанный в параметре value.

4. Выполнение оператора. Осуществляется посредством функции EXECUTE. Описание её в модуле выглядит следующим образом:

function EXECUTE (c in integer) return integer;

где с – идентификатор предварительно открытого курсора.

Функция EXECUTE возвращает число отработанных строк (в этом смысле возвращаемое значение аналогично курсорному атрибуту %ROWCOUNT). Следует учесть, что возвращаемое значение не определено для операторов выборки, а также и то, что EXECUTE вызывается из выражений программ.

5. Закрытие курсора. Закрытие курсора осуществляется посредством вызова процедуры CLOSE_CURSOR, описание которой выглядит следующим образом:

procedure CLOSE_CURSOR (c in out integer).

Передаваемое процедуре значение должно быть достоверным идентификатором курсора. После вызова фактический параметр устанавливается в NULL, что свидетельствует о закрытии курсора.

Приведем пример:

create or replace procedure update_address (p_lname in staff.lname %type, p_fname in staff.fname%type, p_newaddress in staff.address%type, p_rowsupdated out integeR)

is

v_cursor_id integer;

v_updatestmt varchar2(100);

begin

v_cursor_id:= DBMS_SQL.OPEN_CURSOR;

v_updatestmt:= ‘update staff

set address =: na

where fname =: fname and lname =: lname’;

DBMS_SQL.PARSE (v _ cursor _ id, v _ updatestmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE (v_cursor_id,: na, p _ newaddress);

DBMS_SQL.BIND_VARIABLE (v_cursor_id,: fname, p _ fname);

DBMS_SQL.BIND_VARIABLE (v _ cursor _id,: lname, p _ lname);

p _ rowsupdated:=DBMS_SQL.EXECUTE (v_cursor_id);

DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

exception

when others then

DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

raise;

end update_address;

ОБРАБОТКА ЗАПРОСОВ НА ИЗВЛЕЧЕНИЕ ИНФОРМАЦИИ происходит в следующей последовательности:

1. Открыть курсор (OPEN_CURSOR),

2. Выполнить грамматический разбор (PARSE),

3. Выполнить привязку входных переменных (BIND_VARIABLE),

4. Описать элементы списка выбора (DEFINE_COLUMN),

5. Выполнить запрос (EXECUTE),

6. Считать строки (FETCH_ROWS),

7. Записать результаты в переменные (COLUMN_VALUE),

8. Закрыть курсор (CLOSE_CURSOR).

Для динамического выполнения запросов необходимо использовать те же процедуры пакета DBMS_SQL, что и при выполнении инструкций DML, за исключением DEFINE_COLUMN, FETCH_ROWS и COLUMN_VALUE. Рассмотрим синтаксис этих процедур:

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

procedure DEFINE_COLUMN (c in integer, position in integer, column in number),

procedure DEFINE_COLUMN (c in integer, position in integer, column in varchar2, column _ size in integer).

Для переменных типа VARCHAR2 нужно обязательно указывать параметр column _ size, поскольку система поддержки PL/SQL должна знать максимальный размер этих переменных во время выполнения программы, так как в отличие от NUMBER, DATE данные этих типов не имеют фиксированной длины, заранее известной компилятору. Тип и предназначение параметров процедуры DEFINE_COLUMN приведены в таблице.

Таблица Параметры DEFINE_COLUMN

Параметр	Тип	Предназначение
с	integer	Идентификатор курсора.
position	integer	Позиция пункта списка выбора.
column	number, varchar2	Переменная, определяющая тип и размер выходной переменной. Имя переменной не играет особой роли, однако тип и размер важны. Однако как в DEFINE_COLUMN, так и в COLUMN_VALUE обычно используются одни и те же переменные.
column_size	integer	Максимальный ожидаемый размер данных. Обязателен для тех типов, длина которых не известна заранее системе поддержки PL/SQL.

FETCH_ROWS считывает строки набора запроса в буфер. Синтаксис процедуры:

function FETCH_ROWS (c in integer) return integer;

FETCH_ROWS возвращает число считываемых строк. FETCH_ROWS и COLUMN_VALUE вызывают в цикле несколько раз до тех пор, пока FETCH_ROWS не возвратит NULL.

COLUMN_VALUE используется для записи результатов в переменные PL/SQL. Эта процедура вызывается после успешного выполнения FETCH_ROWS. Если в выборке не были возвращены строки COLUMN_VALUE устанавливает для выходной переменной NULL-значение. Ниже приведено описание процедуры в пакете DBMS_SQL:

procedure COLUMN_VALUE (c in integer, position in integer, value out number);

procedure COLUMN_VALUE (c in integer, position in integer, value out number, column _ error out number, actual _ length out number);

procedure COLUMN_VALUE (c in integer, position in integer, value out varchar2);

procedure COLUMN_VALUE (c in integer, position in integer, value out varchar2, column _ error out number, actual _ length out number).

Тип и назначение параметров процедуры COLUMN_VALUE приведены в таблице.

Таблица Параметры процедуры COLUMN_VALUE

Параметр	Тип	Предназначение
с	integer	Идентификатор курсора.
position	integer	Относительная позиция в списке выбора, как и в DEFINE_COLUMN, позиция первого элемента списка =1.
value	number, varchar2	Выходная переменная, если тип этого параметра отличается от типа, указанного в DEFINE_COLUMN то возникает ошибка, что соответствует исключительной ситуации DBMS_SQL.INCONSISTENT_TYPES.
column_error	number	Код ошибки столбца, выдается в виде отрицательного числа. Ошибка будет устанавливать исключительную ситуацию, а column _ error позволяет определить, какой из столбцов стал причиной конкретной ошибки. Если столбец был успешно прочитан, то column _ error =0.
actual_length	number	Если указан, то в данной переменной будет находится исходный размер столбца (размер столбца перед его считыванием). Это удобно в случае, когда размер переменной недостаточен и значение усекается (это также приводит к ошибке).

Пример. Создается процедура, определяющая имена и фамилии сотрудников по заданной для данной процедуры должности сотрудника:

create or replace procedure DynamicQuery (p _ position in staff.position %type) is

v _ cursor _ id integer;

v_select_stmt varchar2(500);

v_first_name staff.fname %type;

v_last_name staff.lname %type;

v_dummy integer;

begin

v_cursor_id:= DBMS_SQL.OPEN_CURSOR;

v_select_stmt:= ‘select lname, fname

from staff

where position =: pos

order by lname’;

DBMS_SQL.PARSE (v _ cursor _ id, v _ select _ stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE (v_cursor_id, ‘:pos’, p _ position);

DBMS_SQL.DEFINE_COLUMN (v_cursor_id, 1, v_last _ name, 25);

DBMS_SQL.DEFINE_COLUMN (v_cursor_id, 2, v_first_name, 25);

v_dummy:= DBMS_SQL.EXECUTE (v_cursor_id);

loop

if DBMS_SQL.FETCH_ROWS (v_cursor_id)=0 then

exit;

end if;

DBMS_SQL.COLUMN_VALUE (v_cursor_id, 1, v_last_name);

DBMS_SQL.COLUMN_VALUE (v_cursor_id, 2, v_first_name);

insert into temp_table (name_col)

values (v_last_name || ’ ’ || v_first_name);

end loop;

DBMS_SQL.CLOSE_CURSOR (v _ cursor _ id);

commit;

exception

when others then

DBMS_SQL.CLOSE_CURSOR (v _ cursor _ id);

raise;

end DynamicQuery;

Дата добавления: 2014-01-04