Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

Выполняемые хранимые процедуры

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

Хранимые процедуры выбора

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

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

Создание хранимых процедур

В вашем скрипте или в isql начните с установки символа терминатора, который будет использован для отметки конца синтаксиса CREATE PROCEDURE. Следующий пример устанавливает символ терминатора в &:

SET TERM &;

Синтаксис оператора:

CREATE PROCEDURE имя-процедуры

[(аргумент тип-данных [, аргумент тип-данных [...]])]

[RETURNS (аргумент тип-данных [, аргумент тип-данных [...]])

AS

< тело -процедуры>

<тело-процедуры> =

[DECLARE [VARIABLE] переменная тип-данных;

[...]]]

BEGIN

<составной-оператор>;

END <терминатор>

Элементы заголовка

Объявляйте в заголовке:

* имя процедуры, которое обязательно и должно быть уникальным в базе данных, например:

CREATE PROCEDURE MyProc

* любые необязательные входные параметры (аргументы), требуемые в процедуре, с их типами данных. Список заключается в скобки, параметры отделяются друг от друга запятыми, например:

CREATE PROCEDURE MyProc (invarl integer, invar2 date)

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

данных. Не требуется соответствия имен входных аргументов именам параметров в вызывающей программе;

* любые необязательные выходные параметры (аргументы), требуемые в процедуре, с их типами данных. Список следует за ключевым словом RETURNS. список заключается в скобки, параметры отделяются друг от друга запятыми, например:

CREATE PROCEDURE MyProc

(invar1 INTEGER, invar2 DATE)

RETURNS (outvar1 INTEGER, outvar2 VARCHAR(20), outvar3 DOUBLE PRECISION)

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

* ключевое слово AS, которое обязательно:

CREATE PROCEDURE MyProc

(invar1INTEGER, invar2 DATE)

RETURNS(outvar1 INTEGER, outvar2 VARCFAR(20), outvar3 DOUBLE

PRECISION)

AS

Элементы тела

Синтаксис:

< тело-процедуры> = [<список-объявлений-переменных>] <составной-оператор>

Локальные переменные

Если вам нужно объявить локальные переменные, то это следует сделать далее. Каждое объявление завершается точкой с запятой. В версии 1.5 переменные при их объявлении могут инициализироваться. Синтаксис:

<список-объявлений-переменных> =

DECLARE [VARIABLE] переменная тип-данных [{'=' | DEFAULT} значение];

[DECLARE [VARIABLE] переменная тип-данных; . . .]

Пример:

CREATE PROCEDURE MyProc (

invar1 INTEGER,

invar2 DATE)

RETURNS (

outvar1 INTEGER,

outvar2 VARCHAR(20),

outvar3 DOUBLE PRECISION)

AS

DECLARE VARIABLE localvar integer DEFAULT 0;

DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;

! ! !

ПРИМЕЧАНИЕ. Ключевое слово VARIABLE необязательно в версии 1.5 и выше.

. ! .

Главный блок кода

Следом идет главный блок кода, обозначенный в описании синтаксиса как <составной-оператор>. Он начинается ключевым словом BEGIN и заканчивается ключевым словом END.

Синтаксис:

<составной-оператор> =

BEGIN

<составной-оператор>

[<составной-оператор> ...]

END <терминатор>

Все структуры <составной-оператор> состоят из одного оператора и/или других структур <составной-оператор>, которые могут включать другие вложенные структуры, например:

CREATE PROCEDURE MyProc (

invar1 INTEGER,

invar2 DATE)

RETURNS (

outvar1 INTEGER,

outvar2 VARCHAR(20),

outvar3 DOUBLE PRECISION)

AS

DECLARE VARIABLE localvar integer DEFAULT 0;

DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;

BEGIN

< составной-оператор>

END &

Элементами в <составной-оператор> могут быть: любой одиночный оператор, блок операторов и вложенные блоки операторов, заключенные в операторные скобки BEGIN и END. Блоки могут включать:

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

* операторы SELECT для помещения значений столбцов в переменные. Операторы SELECT должны иметь предложение INTO в качестве последнего предложения и объявления соответствующих локальных переменных или выходных аргументов для каждого выбранного столбца;

* структуры циклов, такие как FOR SELECT ... DO и WHILE ... DO для выполнения условных или циклических задач;

* структуры ветвления с использованием IF ... THEN ... [ELSE];

* операторы EXECUTE PROCEDURE для вызова других процедур с необязательным предложением RETURNING_VALUES для получения значений переменных. Допустима рекурсия;

* операторы SUSPEND и EXIT, возвращающие управление и, возможно, значения вызвавшему приложению или модулю PSQL;

* комментарии для аннотирования кода процедуры;

* операторы EXCEPTION для возврата приложениям пользовательских сообщений об ошибках или для задания условий для обработчиков исключений;

* операторы WHEN для обработки особых или общих условий ошибок.

* операторы POST_EVENT для добавления в стек сообщений о событиях. Пример:

BEGIN

FOR SELECT COL1, COL2, COL3, C0L4

FROM TABLEA INTO :COL1, :COL2, :COL3 DO

BEGIN

<операторы>

END

<операторы>

END &

SET TERM ; &

COMMIT;

Обратите внимание на завершение всего объявления процедуры символом терминатора, ранее определенного оператором SET TERM. После текста тела процедуры символ терминатора устанавливается в значение по умолчанию точка с запятой. Так поступать следует не всегда. В скриптах DDL, где вы объявляете несколько модулей PSQL, вы можете сохранять альтернативный оператор текущим. Некоторые люди на практике используют альтернативный терминатор во всех своих скриптах, таким образом резервируя точку с запятой только для завершения оператора PSQL. Здесь дело в личных предпочтениях.

Выполняемые процедуры

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

Сложная обработка

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

Поддержка "живых" клиентских наборов

Многие клиентские интерфейсы реализуют классы наборов данных или наборов записей, которые получают выходные наборы от операторов SELECT. Такие клиентские классы обычно предоставляют методы DML, которые выбирают одну строку из буфера, хранящего выход курсора серверной стороны. Строка выбирается пользователем, и объект класса использует уникальный ключ строки для моделирования позиционированного изменения или удаления из таблицы базы данных. Для добавления объект "открывает пустую строку", вводит список столбцов того же типа, что и в буфере, и принимает ключ и другие значения в качестве входных данных для столбцов.

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