CodeNet / Приложения / Базы данных / Interbase / Справочник по инструкциям и функциям InterBase SQL

CREATE PROCEDURE

Описание
Синтаксис
Примеры
Смотри так же

Описание

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

Язык процедур и триггеров InterBase включает все инструкции SQL манипулирования данными и некоторые мощные улучшения, включающие: IF ... THEN ... ELSE, WHILE ... DO, FOR SELECT ... DO, исключительные ситуации и обработку ошибок.

Имеются два вида процедур:

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

Сохраненные процедуры состоят из заголовка и тела.

Заголовок процедуры содержит:

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

Тело процедуры содержит:

Факультативный список локальных переменных и их типов данных.
Блок инструкций на языке процедур и триггеров InterBase, ограниченный BEGIN и END. Блок может включать в себе другие блоки, так, чтобы имелось несколько уровней вложения.

Важно: Так как каждая инструкция в теле сохраненной процедуры должна завершатся точкой с запятой, вы должны определить другой символ для завершения инструкции CREATE PROCEDURE в ISQL. Используйте SET TERM пред CREATE PROCEDURE чтобы определить терминатор отличный от точки с запятой. После инструкции CREATE PROCEDURE, включите SET TERM, что бы изменить терминатор обратно к точке с запятой.

InterBase не позволяет изменения базы данных, которые воздействуют на поведение существующих процедур (т.к. DROP TABLE, DROP EXCEPTION). Для просмотра, всех процедур определенных для текущей базы данных или текста и параметров именованной процедуры, используйте внутренние команды ISQL, SHOW PROCEDURES или SHOW PROCEDURES procedure.

Язык процедур и триггеров InterBase это полный язык программирования для сохраненных процедур и триггеров. Он включает:

Инструкции SQL манипулирования данными: INSERT, UPDATE, DELETE и singleton SELECT.
Операторы и выражения SQL, включая UDF's связанные с базой данных и генераторы.
Мощно расширяет SQL, включая инструкции присвоения, control-flow инструкции, контекстные переменные (для триггеров), even-posting инструкции, исключительные ситуации и инструкции обработки ошибок.

Следующая таблица суммирует расширения языка для сохраненных процедур:

Инструкция	Описание

`BEGIN ... END`	Определяет блок инструкций, которые выполняются как одно. Ключевое слово BEGIN начинает блок; ключевое слово END завершает блок. Не должен сопровождаться точкой с запятой.
`variable = expression`	Инструкция присвоения, которая присваивает значение выражения переменной, локальной переменной, входному параметру или выходному параметру.
`/* comment_text */`	Комментарий к программы, где `comment_text` может быть текстом содержащим любое количество сток.
`EXCEPTION exception_name`	Поднимает именованную исключительную ситуацию. Исключительная ситуация - определенная пользователем ошибка, которая может быть обработана инструкцией WHEH.
`EXECUTE PROCEDURE proc_name [var [,var ...]] [RETUNING_VALUES var [,var ...]]`	Выполняет сохраненную процедуру `proc_name` с входными аргументами следующими за именем процедуры, возвращаемыми значениями в выходных аргументах перечисленных следом за RETURNING_VALUES.
`EXIT`	Переходы к завершающей инструкции END в процедуре.
`FOR <select_statement> DO <compound_statement>`	Повторение инструкции или блока, который следует за DO, для каждой допустимой строки возращенной `<select_statement>`. `<select_statement>`: нормальная инструкция SELECT, за исключением, INTO предложение требуется и должно быть последним. `<compound_statement>`: или одиночная инструкция на языке процедур и триггеров или блок инструкций заключенный в BEGIN и END.
`IF (<condition>) THEN <compound_statement> [ELSE <compound_statement>]`	Проверяет `<condition>`, и если оно TRUE, выполняет инструкцию или блок, следующий за THEN; иначе, выполняет инструкцию или блок следующий за ELSE, если он существует. `<condition>`: булево выражение (TRUE, FALSE или UNKNOWN), в основном, это два выражения, используемые как операнды оператора сравнения.
`POST event_name`	Отправляет сообщение `event_name.`
`SUSPEND`	В проседуре выбора SUSPEND возвращает выходные значения, если любой, вызываемому приложению. Не рекомендуется для исполняемых процедур
`WHILE (<condition>) DO <compound_statement>`	Пока `<condition>` TRUE, выполняется `<compound_statement>` Первое `<condition>` проверяется и если оно TRUE, то выполняется `<compound_statement>`. Эта последовательность повторяется пока `<condition>` не перестанет быть TRUE.
`WHEN {<error> [, <error> ...] \| ANY} DO <compound_statement> <error> = {EXEPTION exeption_name \| SQLCODE errcode \| GDSCODE number}`	Инструкция обработки ошибок. Когда одна из определенных ошибок происходит, выполняется `<compound_statement>`. Инструкция WHEN, если присутствует, должна находится в конце блока, непосредственно перед END. `<error>`: ошибка определенного вида. ANY: обрабатываются все типы ошибок.

Синтаксис

CREATE PROCEDURE name
[(param <datatype> [, param <datatype> ...])]
  [RETURNS <datatype> [, param <datatype> ...])]
  AS <procedure_body> [terminator]

<procedure_body> =
[<variable_declaration_list>]
  <block>

<variable_declaration_list> =
DECLARE VARIABLE var <datatype>;
[DECLARE VARIABLE var <datatype>; ...]

<block> =
BEGIN
<compound_statement>
  [<compound_statement> ...]
END

<compound_statement> = {<block> | statement;}

<datatype> = {
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | DATE
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
    [CHARACTER SET charname]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
    [VARYING] [(int)]}

Аргумент	Описание

`name`	Имя процедуры. Должно быть уникальным среди процедур, таблиц и видов.
`param <datatype>`	Входной параметр, который вызывающая программа использует, чтобы передать значения процедуре. `param`: Имя входного параметра, уникально для переменных внутри процедуры. `<datatype>`: Любой тип данных InterBase.
`RETURNS param <datatype>`	Выходной параметр, который процедура использует, чтобы вернуть значения в вызывающую программу. `param`: Имя выходного параметра, уникально для переменных внутри процедуры. `<datatype>`: Любой тип данных InterBase. Процедура возвращает значения выходных параметров, когда достигает инструкции SUSPEND в теле процедуры.
`AS`	Ключевое слово, которое разделяет заголовок процедуры и тело процедуры.
`DECLARE VARIABLE var <datatype>`	Объявляет локальные переменные используемые только в процедуре. Каждому объявлению должно предшествовать DECLARE VARIABLE и должно завершатся точкой с запятой (;). `param`: Имя локальной переменной, уникально для переменных внутри процедуры. `<datatype>`: Любой тип данных InterBase.
`statement`	Любая одиночная инструкция в языке процедур и триггеров InterBase. Каждая инструкция (исключая BEGIN и END) должна завершатся точкой с запятой (;).
`terminator`	Терминатор определенный SET TERM, который указывает завершение тела процедуры. Используется только в ISQL

Примеры

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

/* Compute total, average, smallest, and largest department budget.
*Parameters:
* department id
*
*Returns:
* total budget
* average budget
* min budget
* max budget
*/

SET TERM !! ;
CREATE PROCEDURE sub_tot_budget (head_dept CHAR(3))
RETURNS (tot_budget DECIMAL(12, 2), avg_budget DECIMAL(12, 2),
  min_budget DECIMAL(12, 2), max_budget DECIMAL(12, 2))
AS
BEGIN
  SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
    FROM department
    WHERE head_dept = :head_dept

INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
    EXIT;
END !!
SET TERM ; !!

Следующая процедура ORG_CHART выводит диаграмму организации:

/*Display an org-chart.
*
*  Parameters:
*    --
*  Returns:
*    parent department
*    department name
*    department manager
*    manager's job title
*    number of employees in the department
*/
CREATE PROCEDURE org_chart
RETURNS (head_dept CHAR(25), department CHAR(25),
    mngr_name CHAR(20), title CHAR(5), emp_cnt INTEGER)
AS
  DECLARE VARIABLE mngr_no INTEGER;
  DECLARE VARIABLE dno CHAR(3);
BEGIN
  FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
    FROM department d
    LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
    ORDER BY d.dept_no
    INTO :head_dept, :department, :mngr_no, :dno
  DO
  BEGIN
    IF (:mngr_no IS NULL) THEN
    BEGIN
      mngr_name = "--TBH--";
      title = "";
    END

    ELSE
      SELECT full_name, job_code
      FROM employee
      WHERE emp_no = :mngr_no
      INTO :mngr_name, :title;

    SELECT COUNT(emp_no)
    FROM employee
    WHERE dept_no = :dno
    INTO :emp_cnt;

    SUSPEND;
  END
END !!

Когда ORG_CHART вызвана, например, следующей инструкцией:

SELCT * FROM ORG_CHART

Она выведет для каждого отдела: название отдела, отдел которому подчинен, имя начальника отдела и его должность, и количество служащих в отделе.

ORG_CHART должна быть использована как процедура выбора для вывода информации о всей организации. Если будет вызвана с помощью инструкции EXECUTE PROCEDURE, то в первый раз, когда процедура сталкивается с инструкцией SUSPEND, она завершается, возвращая информацию только о Штаб-квартире Корпорации.

Ваш аккаунт

Последние темы форума

Почтовая рассылка