`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.Объявления локальных переменных и именованных курсоров, а также внутренние операторы должны завершаться точкой с запятой (“;”).

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

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

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

docnext count = 21

Параметры

У каждого параметра указывается тип данных.Кроме того, для параметра можно указать ограничение 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 могут:

Администраторы
Пользователи с привилегией 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.