Хелен Борри - Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Одиночный оператор UPDATE, DELETE или INSERT в SQL может оперировать только с одной таблицей. Когда набор данных (набор записей) выбирается из обычной таблицы и содержит уникальный ключ таблицы, он может рассматриваться как "живой", потому что его методы могут передавать операторы UPDATE, DELETE или INSERT. Обычным термином для такого типа набора является естественно изменяемый. Набор, являющийся соединением нескольких таблиц, не будет естественно изменяемым. Выполняемые хранимые процедуры могут быть созданы с входными аргументами, которые принимают ключи и значения для множества таблиц и выполняют требуемые операции над каждой таблицей. Такая техника позволяет клиентским приложениям трактовать соединенные наборы, как если бы они были "живыми".

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

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

Рис. 30.1. Операции выполняемой процедуры

Когда процедура начинает выполняться, значения, переданные ей в качестве входных аргументов, становятся локальными переменными. Выходные аргументы являются переменными чтения/записи и могут изменять значения (но не тип данных) в процессе работы много тысяч раз. На рис. 30.1 иллюстрируется типичная деятельность выполняемой процедуры.

Выполняемая процедура DELETE_EMPLOYEE является версией процедуры, которую вы можете найти в базе данных EMPLOYEE в вашем каталоге Firebird /examples. Она реализует некоторые бизнес-правила для служащих, покидающих компанию.

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

CREATE EXCEPTION REASSIGN_SALES

'Reassign the sales records before deleting this employee.' ^

COMMIT ^

(Переназначьте записи продаж перед удалением этого служащего)

Теперь сама процедура. Входной аргумент EMP_NUM соответствует первичному ключу таблицы EMPLOYEE- EMP_NO. Он позволяет процедуре выбирать и работать с одной записью служащего, а внешние ключи из других таблиц ссылаются через него на эту запись.

CREATE PROCEDURE DELETE_EMPLOYEE (

EMP_NUM INTEGER )

AS

DECLARE VARIABLE any_sales INTEGER DEFAULT 0;

BEGIN

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

Конструкция SELECT ... INTO обычна для PSQL. Когда из таблицы запрашиваются значения, предложение INTO позволяет сохранить их в переменных - в локальных переменных или в выходных аргументах. В этой процедуре нет выходных параметров. Мы используем переменную ANY SALES, которую мы объявили и инициализировали в начале тела процедуры для хранения счетчика записей продаж. Обратите внимание на префикс двоеточия (:) у переменной ANY_SALES. Мы рассмотрим это, когда процедура будет готова.

SELECT count(po_number) FROM sales

WHERE sales_rep = :emp_num

INTO :any_sales;

IF (any_sales > 0) THEN

EXCEPTION reassign_sales;

В случае если такие записи заказов будут найдены, процедура аккуратно завершается на операторе EXCEPTION, который при отсутствии обработчика исключений передает выполнение прямо на самый последний оператор END процедуры. В этих условиях процедура завершается, а сообщение об исключении передается вызвавшей программе [115] Эта процедура является очень скромным примером программирования в PSQL. В SQL существует лучший способ проверить существование строк, чем их подсчет. В главе 32 мы снова будем обсуждать эту процедуру, выполнив некоторые изменения в ней, чтобы показать это. Если вы посмотрите исходные коды процедуры в базе данных, вы также заметите, что операторы SUSPEND и EXIT щедро разбросаны в разных местах, где они не нужны. Операторы SUSPEND и EXIT имеют идентичное использование в выполняемой процедуре. При этом в процедурах выбора оператор SUSPEND применяется особо. Для ясности и эффективности документирования предпочтительно исключить использование SUSPEND в качестве синонима EXIT. .

Если нет исключения, выполнение продолжается. Затем процедура должна выполнить небольшую работу по изменению некоторых позиций вакантной должности (NULL), если она сохраняется для нашего служащего, удалить служащего из проектов и удалить его (или ее) историю продаж. Под конец удаляется сама запись служащего.

UPDATE department

SET mngr_no = NULL

WHERE mngr_no = :emp_num;

UPDATE project

SET team_leader = NULL

WHERE team_leader = :emp_num;

DELETE FROM employee_project

WHERE emp_no = :emp_num;

DELETE FROM salary_history

WHERE errp_no = :emp_num;

DELETE FROM employee

WHERE errp_no = :emp_num;

Работа сделана, служащий ушел. Необязательный оператор EXIT может быть включен в текст с целью документирования. Он может быть весьма полезным, если вы просматриваете скрипты, содержащие множество определений процедур, а эти процедуры имеют много вложенных блоков BEGIN ... END:

EXIT;

END ^ COMMIT ^

В этой процедуре мы заметили два различных способа использования префикса двоеточия в переменных.

* Раньше применялось обращение к локальной переменной -.ANY SALES, когда она использовалась в предложении INTO для помещения элемента данных, возвращаемого оператором SELECT.

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

Эти два способа использования префикса двоеточия являются постоянными в PSQL. Если вы забыли, где их нужно применять, или используете их там, где PSQL не требует, то ваша процедура не будет компилироваться, а синтаксический анализатор вызовет исключение. Хуже, если переменная с тем же именем, что и столбец таблицы, используется в операторе SQL без двоеточия. Сервер считает, что это ссылка на столбец, выполняет оператор и вызывает исключение. Нечего и говорить, что результат такой операции будет непредсказуемым.