FirebirdSQL logo
 TRIGGERFUNCTION 

Хранимая процедура (ХП) — это программный модуль, который может быть вызван с клиента, из другой процедуры, функции, выполнимого блока (executable block) или триггера.Хранимые процедуры, хранимые функции, исполняемые блоки и триггеры пишутся на процедурном языке SQL (PSQL). Большинство операторов SQL доступно и в PSQL, иногда с ограничениями или расширениями.Заметными исключениями являются DDL и операторы управления транзакциями.

Хранимые процедуры могут принимать и возвращать множество параметров.

CREATE PROCEDURE

Назначение

Создание новой хранимой функции.

Доступно в

DSQL, ESQL

Синтаксис
CREATE PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine body>

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<outparam>  ::=  <param_decl>

<value> ::=  {literal | NULL | context_var}

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

<type> ::= <datatype> | [TYPE OF] domain_name | TYPE OF COLUMN rel.col

<datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::=  См. Синтаксис скалярных типов данных

<blob_datatype> ::= См. Синтаксис типа данных BLOB

<routine-body> ::=
    <psql-routine-spec>
  | <external-routine-spec>

<psql-routine-spec> ::=
  [<rights-clause>] <psql-routine-body>


<rights-clause> ::=
  SQL SECURITY {DEFINER | INVOKER}

<psql-routine-body> ::=
  См. Синтаксис тела модуля

<external-routine-spec> ::=
  <external-routine-reference>
  [AS <extbody>]

<external-routine-reference> ::= EXTERNAL NAME <extname> ENGINE <engine>

<extname> ::= '<module-name>!<routine-name>[!<misc-info>]'
Table 1. Параметры оператора CREATE PROCEDURE
Параметр Описание

procname

Имя хранимой процедуры.Может содержать до 63 символов.

inparam

Описание входного параметра.

outparam

Описание выходного параметра.

literal

Литерал, совместимый по типу с параметром.

context_var

Любая контекстная переменная, тип которой совместим с типом параметра.

paramname

Имя входного или выходного параметра процедуры.Может содержать до 63 символов.Имя параметра должно быть уникальным среди входных и выходных параметров процедуры, а также её локальных переменных.

extbody

Тело внешней процедуры.Строковый литерал который может использоваться UDR для различных целей.

module-name

Имя внешнего модуля, в котором расположена функция.

routine-name

Внутреннее имя функции внутри внешнего модуля.

misc-info

Определяемая пользователем информация для передачи в функцию внешнего модуля.

engine

Имя движка для использования внешних функций.Обычно указывается имя UDR.

datatype

Тип данных SQL.

collation

Порядок сортировки.

domain_name

Имя домена.

rel

Имя таблицы или представления.

col

Имя столбца таблицы или представления.

Оператор CREATE PROCEDURE создаёт новую хранимую процедуру.Имя хранимой процедуры должно быть уникальным среди имён всех хранимых процедур, таблиц и представлений базы данных.

Note

Желательно также, чтобы имя хранимой процедуры было уникальным и среди имён процедур расположенных в PSQL пакетах (package), хотя это и допустимо.Дело в том, что в настоящее время вы не сможете вызвать функцию/процедуру из глобального пространства имён внутри пакета, если в пакете объявлена одноименная функция/процедура.В этом случае всегда будет вызвана процедура/функция пакета.

CREATE PROCEDURE является составным оператором, состоящий из заголовка и тела.

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

Тело процедуры состоит из необязательных объявлений локальных переменных, подпрограмм и именованных курсоров, и одного или нескольких операторов, или блоков операторов, заключённых во внешнем блоке, который начинается с ключевого слова BEGIN, и завершается ключевым словом END.Объявления локальных переменных и именованных курсоров, а также внутренние операторы должны завершаться точкой с запятой (“;”).

docnext count = 22

Терминатор оператора

Некоторые редакторы SQL-операторов — в частности утилита isql из комплекта Firebird,и возможно некоторые сторонние редакторы — используют внутреннее соглашение, которое требует, чтобы все операторы были завершены с точкой с запятой.

Это создает конфликт с синтаксисом PSQL при кодировании в этих средах.Если вы не знакомы с этой проблемой и её решением, пожалуйста, изучите детали в главе PSQL в разделе,озаглавленном Изменение терминатора в isql.

Параметры

У каждого параметра указывается тип данных.Кроме того, для параметра можно указать ограничение NOT NULL, тем самым запретив передавать в него значение NULL.

Для параметра строкового типа существует возможность задать порядок сортировки с помощью предложения COLLATE.

Входные параметры

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

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

Выходные параметры

Необязательное предложение RETURNS позволяет задать список выходных параметров хранимой процедуры.

Использование доменов при объявлении параметров

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

Если перед названием домена дополнительно используется предложение TYPE OF, то используется только тип данных домена — не проверяются его ограничения NOT NULL и CHECK (если они есть) и не используется значение по умолчанию.Если домен текстового типа, то всегда используется его набор символов и порядок сортировки.

Использование типа столбца при объявлении параметров

Входные и выходные параметры можно объявлять, используя тип данных столбцов существующих таблиц и представлений.Для этого используется предложение TYPE OF COLUMN, после которого указывается имя таблицы или представления и через точку имя столбца.

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

Привилегии выполнения

Необязательное предложение SQL SECURITY позволяет задать с какими привилегиями выполняется хранимая процедура.Если выбрана опция INVOKER, то хранимая процедура выполняется с привилегиями вызывающего пользователя.Если выбрана опция DEFINER, то хранимая процедура выполняется с привилегиями определяющего пользователя (владельца ХП). Эти привилегии будут дополнены привилегиями выданные самой хранимой процедуре с помощью оператора GRANT.По умолчанию хранимая процедура выполняется с привилегиями вызывающего пользователя.

Tip

Привилегии выполнения по умолчанию для вновь создаваемых объектов метаданных можно изменить с помощью оператора

ALTER DATABASE SET DEFAULT SQL SECURITY {DEFINER | INVOKER}

Тело хранимой процедуры

После ключевого слова AS следует тело хранимой процедуры.

Объявление локальных переменных, курсоров и подпрограмм

В необязательной секции <declarations> описаны локальные переменные процедуры, подпрограммы и именованные курсоры.В отношении спецификации типа данных локальные переменные подчиняются тем же правилам, что и входные и выходные параметры процедуры.Подробности вы можете посмотреть в главе “Процедурный язык PSQL” в разделахDECLARE VARIABLE иDECLARE CURSOR,DECLARE PROCEDURE,DECLARE FUNCTION.

После необязательной секции деклараций обязательно следует составной оператор.Составной оператор состоит из одного или нескольких PSQL операторов, заключенных между ключевыми словами BEGIN и END.Составной оператор может содержать один или несколько других составных операторов.Вложенность ограничена 512 уровнями.Любой из BEGIN …​ END блоков может быть пустым, в том числе и главный блок.

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

Хранимая процедура может быть расположена во внешнем модуле.В этом случае вместо тела процедуры указывается место её расположения во внешнем модуле с помощью предложения EXTERNAL NAME.Аргументом этого предложения является строка, в которой через разделитель указано имя внешнего модуля, имя процедуры внутри модуля и определённая пользователем информация.В предложении ENGINE указывается имя движка для обработки подключения внешних модулей.В Firebird для работы с внешними модулями используется движок UDR.После ключевого слова AS может быть указан строковый литерал — "тело" внешней процедуры, оно может быть использовано внешним модулем для различных целей.

Кто может создать хранимую процедуру?

Выполнить оператор CREATE PROCEDURE могут:

Пользователь, создавший хранимую процедуру, становится её владельцем.

Примеры

Example 1. Создание хранимой процедуры
CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Наследуются характеристики домена */
  NAME_EN TYPE OF D_BREEDNAME, /* Наследуется только тип домена */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME, /* Наследуется тип столбца таблицы */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END

То же самое, но процедура будет выполняться с правами определяющего пользователя (владельца процедуры).

CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Наследуются характеристики домена */
  NAME_EN TYPE OF D_BREEDNAME, /* Наследуется только тип домена */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME, /* Наследуется тип столбца таблицы */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
SQL SECURITY DEFINER
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END
Example 2. Создание внешней хранимой процедуры
Создание процедуры находящейся во внешнем модуле (UDR). Реализация процедуры расположена во внешнем модуле udrcpp_example. Имя процедуры внутри модуля — gen_rows. 
CREATE PROCEDURE gen_rows (
    start_n INTEGER NOT NULL,
    end_n INTEGER NOT NULL
) RETURNS (
    n INTEGER NOT NULL
)
    EXTERNAL NAME 'udrcpp_example!gen_rows'
    ENGINE udr;
См. также:

CREATE OR ALTER PROCEDURE,ALTER PROCEDURE,RECREATE PROCEDURE,DROP PROCEDURE.

ALTER PROCEDURE

Назначение

Изменение существующей хранимой процедуры.

Доступно в

DSQL, ESQL

Синтаксис
ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Подробнее см. CREATE PROCEDURE.

Оператор ALTER PROCEDURE позволяет изменять состав и характеристики входных и выходных параметров, локальных переменных, именованных курсоров и тело хранимой процедуры.Для внешних процедур (UDR) вы можете изменить точку входа и имя движка.После выполнения существующие привилегии и зависимости сохраняются.

Warning

Будьте осторожны при изменении количества и типов входных и выходных параметров хранимых процедур.Существующий код приложения может стать неработоспособным из-за того, что формат вызова процедуры несовместим с новым описанием параметров.Кроме того, PSQL модули, использующие изменённую хранимую процедуру, могут стать некорректными.Информация о том, как это обнаружить, находится в приложении Поле RDB$VALID_BLR.

Кто может изменить хранимую процедуру?

Выполнить оператор ALTER PROCEDURE могут:

  • Администраторы

  • Владелец хранимой процедуры;

  • Пользователи с привилегией ALTER ANY PROCEDURE.

Примеры

Example 1. Изменение хранимой процедуры
ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
См. также:

CREATE PROCEDURE,CREATE OR ALTER PROCEDURE,RECREATE PROCEDURE,DROP PROCEDURE.

CREATE OR ALTER PROCEDURE

Назначение

Создание новой или изменение существующей хранимой процедуры.

Доступно в

DSQL, ESQL

Синтаксис
CREATE OR ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Подробнее см. CREATE PROCEDURE.

Оператор CREATE OR ALTER PROCEDURE создаёт новую или изменяет существующую хранимую процедуру.Если хранимая процедура не существует, то она будет создана с использованием предложения CREATE PROCEDURE.Если она уже существует, то она будет изменена и откомпилирована, при этом существующие привилегии и зависимости сохраняются.

Примеры

Example 1. Создание или изменение хранимой процедуры
CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
См. также:

CREATE PROCEDURE,ALTER PROCEDURE,RECREATE PROCEDURE,DROP PROCEDURE.

DROP PROCEDURE

Назначение

Удаление существующей хранимой процедуры.

Доступно в

DSQL, ESQL

Синтаксис
DROP PROCEDURE procname
Table 1. Параметры оператора DROP PROCEDURE
Параметр Описание

procname

Имя хранимой процедуры.

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

Кто может удалить хранимую процедуру?

Выполнить оператор DROP PROCEDURE могут:

  • Администраторы

  • Владелец хранимой процедуры;

  • Пользователи с привилегией DROP ANY PROCEDURE.

Примеры

Example 1. Удаление хранимой процедуры
DROP PROCEDURE GET_EMP_PROJ;
См. также:

CREATE PROCEDURE, RECREATE PROCEDURE.

RECREATE PROCEDURE

Назначение

Создание новой или пересоздание существующей хранимой процедуры.

Доступно в

DSQL, ESQL

Синтаксис
RECREATE PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Подробнее см. CREATE PROCEDURE.

Оператор RECREATE PROCEDURE создаёт новую или пересоздаёт существующую хранимую процедуру.Если процедура с таким именем уже существует, то оператор попытается удалить её и создать новую процедуру.Операция закончится неудачей при подтверждении транзакции, если процедура имеет зависимости.

Note

Имейте в виду, что ошибки зависимостей не обнаруживаются до фазы подтверждения транзакции.

После пересоздания процедуры привилегии на выполнение хранимой процедуры и привилегии самой хранимой процедуры не сохраняются.

Примеры

Example 1. Создание новой или пересоздание существующей хранимой процедуры
RECREATE PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
См. также:

CREATE PROCEDURE,CREATE OR ALTER PROCEDURE,DROP PROCEDURE.