Обработка операторов 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 i nteger;

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).

В отличие от динамической обработки SQL-операторов insert, update, delete, обработка инструкций select включает дополнительно описание элементов списка выбора, считывание строк и запись результатов в переменные PL/SQL.

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

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 Таблица 1

После выполнения запроса, строки набора необходимо считать в буфер посредством вызова функции FETCH_ROWS. Эта функция описана в модуле DBMS_SQL следующим образом:

function FETCH_ROWS (c in integer)

return integer;

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

После успешно выполненного считывания строк производят запись результатов в переменные PL/SQL посредством процедуры COLUMN_VALUE. Если в выборке не были возвращены строки, (что указывается возвратом 0) COLUMN_VALUE устанавливает для выходной переменной null – значение. Ниже приведено описание этой процедуры в модуле DBMS_SQL, а описание её параметров в табл. 2:

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);

Таблица 2 — Параметры процедуры 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;

· Варианты заданий

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

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

3. Создать динамическую процедуру, осуществляющую поиск сотрудника по заданному шаблону и выводящую во временную таблицу всю не nul -информацию о нем, в выходном параметре процедуры передавать значение “ неопределено ”,в случае если поиск не дал результатов.

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

5. Создать динамическую процедуру, изменяющую значения заработной платы сотрудников на указанный в аргументе процент, адрес офиса с сотрудниками получающими надбавку; указывать в качестве второго входного аргумента.

6. Создать динамическую функцию, определяющую, были ли осмотры на число указанное в качестве её аргумента; возвращать количество найденных осмотров или null, если таковых не было.

7. Создать динамическую процедуру, осуществляющую добавление информации о новых объектах с автоматическим закреплением данного объекта за сотрудниками, у которых меньше объектов, чем в среднем по отделению; если таковых несколько, объект закрепить за первым в упорядоченном по алфавиту списке сотрудником.

8. Создать динамическую функцию, осуществляющую добавление информации о новых объектах с закреплением их за определенным сотрудником, имя и фамилия которого указываются во входных аргументах функции. Если у данного сотрудника количество закрепленных за ним объектов и так превышает среднее количество на человека по отделению, то отменить ввод. Возвращать TRUE или FALSE в зависимости от того, успешно или нет было выполнено добавление.

9. Создать динамическую процедуру, определяющую средний возраст сотрудников. Значение передавать в вызывающую среду посредством выходного параметра.

· Контрольные вопросы

1. Поясните принципы серверного вывода в ORACLE 8 i?

2. В чем преимущества процедур с динамическим SQL?

3. Расскажите о порядке выполнения DML – операторов средствами модуля DBMS_SQL.

4. Какие особенности в обработке динамических запросов на извлечение информации?

Литература

1. Коннолли Т., Бегг К. Базы данных: Проектирование, реализация и сопровождение. Теория и практика. –М –К: Вильямс, 2000.

2. Бобровски С. ORACLE 8: Архитектура: Основные принципы построения и структура баз данных ORACLE. –М: Лори, 1998.

3. Урман С. ORACLE 8 i: Новые возможности программирования на языке PL/SQL. –М: Лори, 2001.

Св. план 2006, поз. 36

Учебное издание

Капанов Николай Анатольевич

Поиск по сайту: