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

Что произойдет, если процедура достигнет финального оператора END, зависит от ее типа.

* В процедуре SELECT код SQLCODE будет установлен в 100 для указания того, что больше нет найденных строк, а управление перейдет вызвавшей программе.

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

В выполняемых процедурах SUSPEND имеет точно такой эффект, что и END.

Рекурсивные процедуры

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

Каждое обращение к процедуре называется экземпляром (instance), поскольку каждое обращение к процедуре является отдельной сущностью, которая выполняется так же, как если бы она была вызвана из приложения; она резервирует память и стек с учетом требований выполнения ее задач.

Хранимые процедуры могут иметь глубину вложений не более 1000. Такое ограничение помогает предотвратить бесконечные циклы, которые могут появиться, если в процедуре не задано условие завершения цикла. Однако ограничения памяти и размера стека могут сделать количество вложенных уровней меньше 1000.

Процедура DEPT_BUDGET в примере базы данных EMPLOYEE иллюстрирует работу рекурсивной процедуры. Она принимает в качестве входа код DNO, эквивалентный коду DEPT_NO - ключу таблицы DEPARTMENT. Таблица DEPARTMENT имеет многоуровневую древовидную структуру: каждый отдел, не являющийся головным отделом, имеет внешний ключ HEAD_DEPT, ссылающийся на DEPT_NO своего непосредственного "родителя".

Процедура обращается к таблице DEPARTMENT по этому полученному ключу. Она сохраняет значение BUDGET этой строки в выходной переменной тот. Она также выполняет подсчет количества отделов, непосредственно предшествующих данному отделу в структуре отделов. Если нет подотделов, оператор EXIT осуществляет переход сразу на финальный оператор END ^. Текущее значение тот становится выходом процедуры, и процедура завершается.

SET TERM ^;

CREATE PROCEDURE DEPT_BUDGET (

DNO CHAR(3) )

RETURNS (

TOT DECIMAL(12,2) )

AS

DECLARE VARIABLE sumb DECIMAL(12, 2);

DECLARE VARIABLE rdno CHAR(3);

DECLARE VARIABLE cnt INTEGER;

BEGIN

tot = 0;

SELECT budget FROM department WHERE dept_no = :dno INTO :tot;

SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;

IF (cnt = 0) THEN

EXIT;

Если существуют подотделы, то выполнение продолжается. Входной код DNO используется в предложении WHERE курсора FOR ... SELECT (см. разд. "Курсоры в PSQL") для выделения по очереди каждой строки из таблицы DEPARTMENT, которая содержит в коде HEAD_DEPT то же значение, что и в DNO, и помещения значение из DEPT_NO этой строки в локальную переменную RDNO:

FOR SELECT dept_no FROM department

WHERE head_dept = :dno

INTO :rdno DO

BEGIN

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

EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;

tot = tot + sumb;

END

Под конец возвращаемое значение, аккумулированное в рекурсиях, передается вызвавшей программе, и процедура завершается:

EXIT; /* оператор EXIT необязателен */

END^

COMMIT^

На этот раз наша процедура имеет входные параметры. Наш простой вызов в DSQL может выглядеть следующим образом:

EXECUTE PROCEDURE DEPT BUDGET ('600');

Или же мы можем использовать заменяемый параметр:

EXECUTE PROCEDURE DEPT_BUDGET (?);

Курсоры в PSQL

Курсоры состоят из трех основных элементов:

* набора строк, определенных выражением SELECT;

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

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

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

PSQL имеет две реализации курсора, одна хорошо известная, а другая не была ранее документирована.

* Хорошо известная реализация представлена конструкцией FOR ... SELECT, которая полностью реализует синтаксис цикла и широко используется в процедурах выбора. Она подробно обсуждается в следующем разделе. При этом также допустимо выполнение цикла курсора FOR ... SELECT и внутри выполняемых процедур, как мы видели в предыдущем примере.

* Менее известной реализацией является ранняя реализация, иногда называемая изменяемым или именованным курсором, которая была наследована от ESQL и долгое время была осуждаема. Она представляет объект курсор и позволяет выполнять изменения и удаления. Ее синтаксис будет более понятен тем, кто использовал курсоры в языках процедур в других СУБД. Сейчас мы его вкратце рассмотрим.

! ! !

ПРИМЕЧАНИЕ. Синтаксис и механизм именованного курсора были заново реализованы в процессе разработки Firebird 2. Это краткое описание является лебединой песней старых курсоров, поскольку новый синтаксис вряд ли будет совместимым со старым.

. ! .

Его синтаксис выглядит следующим образом:

. . .

FOR SELECT <список-столбцов>

FROM <имя-таблицы>

FOR UPDATE

INTO <переменные>

AS CURSOR <имя-курсора>

DO

/* либо UPDATE ... */

BEGIN

UPDATE <имя-таблицы>

SET ... WHERE CURRENT OF <имя-курсора>;

. . .

END

/* или DELETE */

BEGIN

DELETE FROM <имя-таблицы>

WHERE CURRENT OF <имя-курсора>

END

. . .

Используйте его сейчас, но ждите изменений в более поздних версиях Firebird. Это очень быстрый способ добавить множество операций в хранимую процедуру, поскольку здесь используется RDB$DB_KEY для локализации записей. RDB$DB_KEY (или просто DBKEY) является внутренней возможностью, которая может быть использована во множестве ситуаций. См. разд. "Темы оптимизации: использование внутреннего RDB$DBKEY".

! ! !

СОВЕТ. Просмотрите заметки по релизу для нового синтаксиса курсора PSQL. На момент написания данной книги описание отсутствовало.

. ! .

Более поздняя реализация курсоров, обсуждаемая далее, является более гибкой.

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

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

Использование процедур выбора

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

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

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

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

CREATE PROCEDURE BREAKAPART(

INPUTLIST VARCHAR(1024))

RETURNS (

NUMERO SMALLINT, ITEM VARCHAR (20)

)

AS

DECLARE CHARAC CHAR;

DECLARE ISDONE SMALLINT = 0;

BEGIN

NUMERO = 0;

ITEM = ' ' ;

WHILE (ISDONE = 0) DO

BEGIN

CHARAC = SUBSTRING (INPUTLI ST FROM 1 FOR 1);

IF (CHARAC = '') THEN

ISDONE = 1;

IF (CHARAC = OR CHARAC = '') THEN

BEGIN

NUMERO = NUMERO + 1;

SUSPEND;

/* Отправляет строку в буфер строк */

ITEM = '';

END

ELSE

ITEM = ITEM || CHARAC;

INPUTLIST = SUBSTRING(INPUTLIST FROM 2);

END

END ^

COMMIT;

/* */

SELECT * FROM BREAKAPART (' ALPHA, BETA, GAMMA, DELTA ') ;

NUMERO ITEM

1 ALPHA

2 BETA

3 GAMMA

4 DELTA

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