ИСПОЛЬЗОВАНИЕ DBMS_SQL

DBMS_SQL.PARSE (v_Cursor, v_CreateString, DBMS_SQL.NATIVE);

МОДУЛЬ DBMS_SQL

Алгоритм выполнения операторов с помощью DBMS_SQL состоит из следующих шагов:

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

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

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

4. Если выполняемый оператор это оператор DML или DDL (UPDATE, DELETE, INSERT) – выполнение его с помощью 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’ И ВЫПОЛНИМ ЕЕ. ПОСКОЛЬКУ ЭТО

* ОПЕРАТОР DDL, ОБЕ ОПЕРАЦИИ ВЫПОЛНЯЮТСЯ

* С ПОМОЩЬЮ DBMS_SQL.PARSE. ЕСЛИ ТАБЛИЦА

* НЕ СУЩЕСТВУЕТ, УСТАНОВИМ ОШИБКУ ORA-942.

*/

begin

-- DBMS SQL.NATIVE – КОНСТАНТА, ОПИСАННАЯ В

-- ЗАГОЛОВКЕ МОДУЛЯ

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.СLOSE_CURSOR (v_Cursor);

exception

when others then

/* СНАЧАЛА ЗАКРОЕМ КУРСОР, ЗАТЕМ ПЕРЕУСТАНОВИМ

* ОШИБКУ ТАК, ЧТОБЫ ПЕРЕДАТЬ ЕЕ В ВЫЗЫВАЮЩИЙ

* БЛОК. */

DBMS_SQL.CLOSE_CURSOR (v_Cursor);

raise;

end RecreateTempTable;

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

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

• Обработка ошибок осуществляется так же, как и в статическом SQL, – ошибки устанавливаются и обрабатываются с помощью исключительных ситуаций. Отличие в том, что теперь во время выполнения блока, могут возвращаться ошибки компиляции (например ORA-942). В статическом РL/SQL такие ошибки обнаруживаются на этапе компиляции, еще до запуска блока на выполнение.

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

На рисунке 37 приведена блок-схема, иллюстрирующая порядок, в котором обычно вызывается DBMS_SQL.

Рисунок 37 Блок-схема алгоритма обработки

данных в DBMS_SQL

С помощью DBMS_SQL можно обрабатывать операторы трех видов: операторы DML, DDL и ALTER SESSION, запросы и анонимные блоки PL/SQL, Каждый из видов обрабатывается различными процедурами. Ниже приведено краткое описание этих процедур, а более детальное объяснение каждой из них будет дано далее, в разделе “Использование DBMS_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 процедура COLUMN_VALUE возвращает фактическую информацию. В ней используются переменные того типа, который указан процедурой DEFINE_COLUMN. COLUMN_VALUE следует применять только для запросов.

• 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),

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

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

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

Определение элементов списка выбора напоминает процесс привязки входных переменных, за исключением того, что элементы списка выбора должны быть не привязаны, а только определены. В процедуре 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 приведены в таблице 5.

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

Считывание строк: строки набора запроса считываются в буфер посредством вызова функции FETCH_ROWS. Функция описана в модуле следующим образом:

function FETCH_ROWS (c in integer) return integer.

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

Для записи результатов в переменные PL/SQL следует вызывать процедуру COLUMN_VALUE. Эта процедура вызывается после успешного выполнения FETCH_ROWS. Если в выборке не были возвращены строки, (что указывается возвратом 0 функцией 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 приведены в таблице 6.

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

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

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;

24.6.3 ФАЙЛОВЫЙ ВВОД/ВЫВОД (МОДУЛЬ UTL_FILE)

Выше было рассказано о том, что в PL/SQL не предусмотрены встроенные средства для ввода и вывода информации, однако эти функции реализуют через вспомогательные программные модули. Ввод/вывод данных на экран осуществляется с помощью модуля DBMS_OUTPUT. PL/SQL 2.3 расширяет эту функциональную возможность, обеспечивая ввод/вывод текстовых файлов посредством модуля UTL_FILE. С помощью UTL_FILE невозможно осуществлять вывод информации непосредственно в двоичные файлы.

В ORACLE можно считывать файлы двоичного формата при помощи объектов BFILE, которые представляют собой особую форму внешних объектов LOB. В данном параграфе рассказывается о том, как функционирует модуль UTL_FILE.