20. Хранимые функции (`FUNCTION`)

Хранимая функция является программой, хранящейся в области метаданных базы данных и выполняющейся на стороне сервера. К хранимой функции могут обращаться хранимые процедуры, хранимые функции (в том числе и сама к себе), триггеры и клиентские программы. При обращении хранимой функции самой к себе такая хранимая функция называется рекурсивной.

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

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

Для создания хранимой функции используется оператор CREATE FUNCTION, синтаксис которого представлен в листинге .

Листинг 20.1 Синтаксис оператора создания хранимой функции CREATE FUNCTION

CREATE FUNCTION <имя хранимой функции>
   [(<входной параметр> [, <входной параметр> ...])]
RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC]
[SQL SECURITY {DEFINER | INVOKER}]
{EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней функции>]}|
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

<входной параметр> ::= <описание параметра> [{=|DEFAULT} <значение по умолчанию>]

<описание параметра> ::= <имя параметра> <тип> [NOT NULL]
                         [COLLATE <порядок сортировки>]

<тип> ::= <тип данных SQL>
        | [TYPE OF] <имя домена>
        | TYPE OF COLUMN <имя таблицы/представления>.<имя столбца>

<значение по умолчанию> ::= {<литерал> | NULL | <контекстная переменная>}

<внешний модуль> ::= '<имя внешнего модуля>!<имя функции в модуле>[! <информация>]'

<объявление> ::= <объявление локальной переменной>;
               | <объявление курсора>;
               | <объявление процедуры/функции>
               | <реализация процедуры/функции>

Таблица 20.1 Параметры оператора CREATE FUNCTION
Параметр	Описание
Имя хранимой функции	Имя хранимой функции. Может содержать до 63 символов.
Входной параметр	Описание входного параметра.
Литерал	Литерал, совместимый по типу с параметром.
Контекстная переменная	Любая контекстная переменная, тип которой совместим с типом параметра.
Имя параметра	Имя входного параметра функции. Может содержать до 63 символов. Имя параметра должно быть уникальным среди входных параметров функции, а также её локальных переменных.
Тело внешней функции	Тело внешней функции. Строковый литерал который может использоваться `UDR` для различных целей.
Имя внешнего модуля	Имя внешнего модуля, в котором расположена функция.
Имя функции в модуле	Внутреннее имя функции внутри внешнего модуля.
Информация	Определяемая пользователем информация для передачи в функцию внешнего модуля.
Имя движка	Имя движка для использования внешних функций. Обычно указывается имя `UDR`.

Оператор CREATE FUNCTION создаёт новую хранимую функцию. Имя хранимой функции должно быть уникальным среди имён всех хранимых функций и внешних (UDF) функций. Если только это не внутренняя функция ("подпрограмма"). Для внутренних функций достаточно уникальности только в рамках модулей, которые их "охватывают".

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Возвращаемое значение

Предложение RETURNS задаёт тип возвращаемого значения хранимой функции. Если функция возвращает значение строкового типа, то существует возможность задать порядок сортировки с помощью предложения COLLATE. В качестве типа выходного значения можно указать имя домена, ссылку на его тип (с помощью предложения TYPE OF) или ссылку на тип столбца таблицы (с помощью предложения TYPE OF COLUMN).

Детерминированные функции

Необязательное предложение DETERMINISTIC указывает, что функция детерминированная. Детерминированные функции каждый раз возвращают один и тот же результат, если предоставлять им один и тот же набор входных значений. Недетерминированные функции могут возвращать каждый раз разные результаты, даже если предоставлять им один и тот же набор входных значений. Если для функции указано, что она является детерминированной, то такая функция не вычисляется заново, если она уже была вычислена однажды с данным набором входных аргументов, а берет свои значения из кэша метаданных (если они там есть).

Тело хранимой функции

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

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

В необязательной секции <объявление> описаны локальные переменные функции, именованные курсоры и подпрограммы (подпроцедуры и подфункции). Локальные переменные подчиняются тем же правилам, что и входные параметры функции в отношении спецификации типа данных.

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

Внешние функции

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

Предупреждение

Не следует путать внешние функции, объявленные как DECLARE EXTERNAL FUNCTION, так же известные как UDF, с функциями расположенными во внешних модулях объявленных как CREATE FUNCTION ... EXTERNAL NAME, называемых UDR (User Defined Routine). Первые являются унаследованными (Legacy) из предыдущих версий РЕД Базы Данных. Их возможности существенно уступают возможностям нового типа внешних функций. В РЕД Базе Данных 5.0 UDF объявлены устаревшими.

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

Необязательное предложение SQL SECURITY {DEFINER | INVOKER} определяет, в контексте какого пользователя будет выполняться функция. Ключевое слово INVOKER (значение по умолчанию) указывает, что функция выполняется с правами вызвавшего ее пользователя. Задание ключевого слова DEFINER означает, что функция выполняется с правами к объектам базы данных ее владельца (создателя). Значение по умолчанию на уровне всей базы данных можно изменить оператором ALTER DATABASE SET DEFAULT SQL SECURITY.

Использование BLR вместо SQL кода

В РЕД Базе Данных 5.0 появилась возможность создать функцию с телом в виде двоичного BLR представления, закодированного в Base64, вместо обычного исходного кода на языке SQL. В этом случае исходные SQL коды функции будут недоступны. Оператор создания такой функции будет выглядеть так:

CREATE FUNCTION <имя> (<входные параметры>)
RETURNS <тип>
[SQL SECURITY {DEFINER | INVOKER}]
AS '<BLR код>';

Здесь <BLR код> - это BLR в его исходном двоичном виде, закодированный в Base64. Его можно получить, например, из запроса:

select BASE64_ENCODE(RDB$FUNCTION_BLR)
from RDB$FUNCTIONS
where RDB$FUNCTION_NAME = '<имя>';

20.2. Изменение хранимой функции

Для изменения существующей хранимой функции используется оператор ALTER FUNCTION. Синтаксис оператора представлен в листинге .

Листинг 20.2 Синтаксис оператора изменения хранимой функции ALTER FUNCTION

ALTER FUNCTION <имя хранимой функции>
   [(<входной параметр> [, <входной параметр> ...])]
RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней функции>]}
|
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

Примечание

Изменять хранимую функцию может администратор, владелец хранимой функции, пользователь с привилегией ALTER ANY FUNCTION.

Оператор ALTER FUNCTION позволяет изменять:

состав и характеристики входных параметров;
тип выходного значения;
локальные переменные, курсоры, подпрограммы;
в контексте какого пользователя будет выполняться функция;
тело хранимой функции на языке SQL;
исходный SQL код функции на двоичный BLR код;
точку входа и имя движка (для внешних функций)

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

Внешние функции, объявленные как DECLARE EXTERNAL FUNCTION, так же известные как UDF, невозможно преобразовать в PSQL функции и наоборот.

Примечание

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

Предупреждение

Если у вас уже есть внешняя функция в Legacy стиле (DECLARE EXTERNAL FUNCTION), то оператор ALTER FUNCTION изменит её на обычную функцию без всяких предупреждений. Это было сделано умышлено для облегчения миграции на новый стиль написания внешних функций известных как UDR.

Параметр DETERMINISTIC можно изменять без указания тела функции:

ALTER FUNCTION <имя хранимой функции> {DETERMINISTIC | NOT DETERMINISTIC}

Изменить значение SQL SECURITY можно без указания тела функции:

ALTER FUNCTION <имя хранимой функции>
  SQL SECURITY {DEFINER | INVOKER}
| DROP SQL SECURITY

20.3. Создание новой или изменение существующей хранимой функции

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

Листинг 20.3 Синтаксис оператора создания новой или изменения существующей хранимой функции CREATE OR ALTER FUNCTION

CREATE OR ALTER FUNCTION <имя хранимой функции>
   [(<входной параметр> [, <входной параметр> ...])]
RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней функции>]}
|
{
   {AS '<BLR код>'}
   |{AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

20.4. Удаление хранимой функции

Для удаления существующей хранимой функции используется оператор DROP FUNCTION. Синтаксис оператора представлен в листинге .

Листинг 20.4 Синтаксис оператора удаления хранимой функции DROP FUNCTION

DROP FUNCTION <имя хранимой процедуры>

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

Примечание

Удалить хранимую функцию может администратор, владелец хранимой функции и пользователь с привилегией DROP ANY FUNCTION.

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

Оператор RECREATE FUNCTION позволяет создать новую хранимую функцию, если функция с тем же именем отсутствует в базе данных, или пересоздать существующую в базе данных функцию. Если функция с этим именем уже существует, то оператор попытается удалить её и создать новую функцию, при этом существующие привилегии и зависимости не сохраняются. Синтаксис оператора представлен в листинге .

Листинг 20.5 Синтаксис оператора создания новой или изменения существующей хранимой функции RECREATE FUNCTION

RECREATE FUNCTION <имя хранимой функции>
   [(<входной параметр> [, <входной параметр> ...])]
RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней функции>]}
|
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

Операция закончится неудачей при подтверждении транзакции, если функция имеет зависимости.

20.6. Объявление подфункции

В хранимых функциях и хранимых процедурах можно объявлять подфункции. Синтаксис оператора представлен в листинге .

Листинг 20.6 Синтаксис оператора объявления подфункции DECLARE FUNCTION

DECLARE FUNCTION <имя подфункции> [(<входной> [, <входной> ...])]
      RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC];

Подфункция не может быть вложена в другую подпрограмму. Они поддерживаются только в основном модуле (хранимой процедуре, хранимой функции и анонимном PSQL блоке).

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

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

20. Хранимые функции (FUNCTION)

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

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

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

Возвращаемое значение

Детерминированные функции

Тело хранимой функции

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

Внешние функции

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

Использование BLR вместо SQL кода

20.2. Изменение хранимой функции

20.3. Создание новой или изменение существующей хранимой функции

20.4. Удаление хранимой функции

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

20.6. Объявление подфункции

20. Хранимые функции (`FUNCTION`)