FirebirdSQL logo

CREATE FUNCTION

Назначение

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

Доступно в

DSQL

Синтаксис
CREATE FUNCTION funcname [(<inparam> [, <inparam> ...])]
  RETURNS <type> [NOT NULL] [COLLATE collation]
  [DETERMINISTIC]
  <routine-body>

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

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

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

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | 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 FUNCTION
Параметр Описание

funcname

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

inparam

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

literal

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

context_var

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

paramname

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

module-name

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

routine-name

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

misc-info

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

extbody

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

engine

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

datatype

Тип данных SQL.

collation

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

domain_name

Имя домена.

rel

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

col

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

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

Note

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

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

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

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

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

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

Кто может создать функцию?

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

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

Примеры

Example 1. Создание хранимой функции
CREATE FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A+B;
END

Вызов в запросе:

SELECT ADD_INT(2, 3) AS R FROM RDB$DATABASE

Вызов внутри PSQL кода, второй необязательный параметр не указан:

MY_VAR = ADD_INT(A);
Example 2. Создание детерминистической хранимой функции
CREATE FUNCTION FN_E()
RETURNS DOUBLE PRECISION DETERMINISTIC
AS
BEGIN
  RETURN EXP(1);
END
Example 3. Создание хранимой функции с параметрами типа столбца таблицы

Функция, возвращающая имя мнемоники по имени столбца и значения мнемоники.

CREATE FUNCTION GET_MNEMONIC (
    AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
    ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
AS
BEGIN
  RETURN (SELECT RDB$TYPE_NAME
          FROM RDB$TYPES
          WHERE RDB$FIELD_NAME = :AFIELD_NAME
            AND RDB$TYPE = :ATYPE);
END

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

CREATE FUNCTION GET_MNEMONIC (
    AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
    ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
SQL SECURITY DEFINER
AS
BEGIN
  RETURN (SELECT RDB$TYPE_NAME
          FROM RDB$TYPES
          WHERE RDB$FIELD_NAME = :AFIELD_NAME
            AND RDB$TYPE = :ATYPE);
END
Example 4. Создание внешней хранимой функции

Создание функции находящейся во внешнем модуле (UDR). Реализация функции расположена во внешнем модуле udrcpp_example.Имя функции внутри модуля — wait_event.

CREATE FUNCTION wait_event (
   event_name varchar(63) CHARACTER SET ascii
) RETURNS INTEGER
EXTERNAL NAME 'udrcpp_example!wait_event'
ENGINE udr
Example 5. Создание хранимой функции содержащую подфункцию

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

CREATE FUNCTION INT_TO_HEX (
    ANumber BIGINT,
    AByte_Per_Number SMALLINT = 8)
RETURNS CHAR(66)
AS
DECLARE VARIABLE xMod SMALLINT;
DECLARE VARIABLE xResult VARCHAR(64);
DECLARE FUNCTION TO_HEX(ANum SMALLINT) RETURNS CHAR
AS
BEGIN
  RETURN CASE ANum
           WHEN 0 THEN '0'
           WHEN 1 THEN '1'
           WHEN 2 THEN '2'
           WHEN 3 THEN '3'
           WHEN 4 THEN '4'
           WHEN 5 THEN '5'
           WHEN 6 THEN '6'
           WHEN 7 THEN '7'
           WHEN 8 THEN '8'
           WHEN 9 THEN '9'
           WHEN 10 THEN 'A'
           WHEN 11 THEN 'B'
           WHEN 12 THEN 'C'
           WHEN 13 THEN 'D'
           WHEN 14 THEN 'E'
           WHEN 15 THEN 'F'
           ELSE NULL
         END;
END
BEGIN
  xMod = MOD(ANumber, 16);
  ANumber = ANumber / 16;
  xResult = TO_HEX(xMod);
  WHILE (ANUMBER > 0) DO
  BEGIN
    xMod = MOD(ANumber, 16);
    ANumber = ANumber / 16;
    xResult = TO_HEX(xMod) || xResult;
  END
  RETURN '0x' || LPAD(xResult, AByte_Per_Number * 2, '0');
END