СОЗДАНИЕ ФУНКЦИИ

Синтаксис

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body
func_parameter:
    [ IN | OUT | INOUT | IN OUT ]  param_name type
type:
    Any valid MariaDB data type
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
func_body:
    Valid SQL procedure statement

Описание

Используйте инструкцию CREATE FUNCTION для создания новой хранимой функции. Вам необходимо иметь базовый привилегии CREATE ROUTINE, чтобы использовать CREATE FUNCTION. Функция может принимать любое количество аргументов и возвращать значение из тела функции. Тело функции может быть любым допустимым SQL выражением, таким как выражение в операторе SELECT. При наличии соответствующих привилегий вы можете вызвать функцию так же, как и любую встроенную функцию. Подробности о привилегиях см. в разделе Безопасность ниже.

Вы также можете использовать вариант инструкции CREATE FUNCTION для установки пользовательской функции (UDF), определённой плагином. Подробности см. в СОЗДАНИЕ ФУНКЦИИ (UDF).

Вы можете использовать оператор SELECT в качестве тела функции, заключив его в скобки, точно так же, как вы используете подзапрос для любого другого выражения. Инструкция SELECT должна возвращать одно значение. Если при вызове функции возвращается более одной колонки, появляется ошибка 1241. Если при вызове функции возвращается более одной строки, появляется ошибка 1242. Используйте LIMIT предложение, чтобы гарантировать возврат только одной строки.

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

По умолчанию функция ассоциирована с текущей базой данных. Чтобы явно связать функцию с заданной базой данных, укажите полное имя как db_name.func_name при её создании. Если имя функции совпадает с именем встроенной функции, при вызове необходимо использовать полное имя.

Список параметров в скобках должен всегда присутствовать. Если параметров нет, должен использоваться пустой список параметров (). Имена параметров нечувствительны к регистру.

Каждый параметр может быть объявлен с использованием любого допустимого типа данных, за исключением атрибута COLLATE.

Допустимые идентификаторы для использования в качестве имён функций см. в Имена идентификаторов.

IN | OUT | INOUT | IN OUT

OUT, INOUT и его эквивалент IN OUT, действительны только если вызваны из SET и не из SELECT. Эти квалификаторы особенно полезны для создания функций с несколькими значениями возврата. Это позволяет функциям быть более сложными и вложенными.

DELIMITER $$
CREATE FUNCTION add_func3(IN a INT, IN b INT, OUT c INT) RETURNS INT
BEGIN
  SET c = 100;
  RETURN a + b;
END;
$$
DELIMITER ;
 
SET @a = 2;
SET @b = 3;
SET @c = 0;
SET @res= add_func3(@a, @b, @c);

SELECT add_func3(@a, @b, @c);
ERROR 4186 (HY000): OUT or INOUT argument 3 for function add_func3 is not allowed here

DELIMITER $$
CREATE FUNCTION add_func4(IN a INT, IN b INT, d INT) RETURNS INT
BEGIN
  DECLARE c, res INT;
  SET res = add_func3(a, b, c) + d;
  if (c > 99) then
    return  3;
  else
    return res;
  end if;
END;
$$

DELIMITER ;

SELECT add_func4(1,2,3);
+------------------+
| add_func4(1,2,3) |
+------------------+
|                3 |
+------------------+

АГРЕГАТНЫЕ

Также возможно создавать хранимые агрегатные функции. Подробности см. в Хранимые агрегатные функции.

ВОЗВРАЩАЕТ

Предложение RETURNS задаёт тип возвращаемого значения функции. NULL значения разрешены со всеми типами возвращаемых значений.

Что происходит, если предложение RETURN возвращает значение другого типа? Это зависит от SQL_MODE, действующего в момент создания функции.

Если SQL_MODE строгий (указаны флаги STRICT_ALL_TABLES или STRICT_TRANS_TABLES), будет сгенерирована ошибка 1366.

В противном случае, значение преобразуется к соответствующему типу. Например, если функция задаёт ENUM или SET значение в предложении RETURNS, но предложение RETURN возвращает целое число, возвращаемое значение функции — строка для соответствующего ENUM члена набора SET членов.

MariaDB сохраняет значение переменной SQL_MODE системы, которое действует в момент создания процедуры, и всегда выполняет процедуру с этим значением, независимо от установленного режима SQL сервера во время вызова процедуры.

ЯЗЫК SQL

LANGUAGE SQL — стандартное SQL предложение, и его можно использовать в MariaDB для обеспечения портативности. Однако это предложение не имеет значения, так как SQL — единственный поддерживаемый язык для хранимых функций.

Функция является детерминированной, если она может производить только один результат для заданного набора параметров. Если результат может быть повлиян хранимыми данными, переменными сервера, случайными числами или любым значением, которое не явно передано, то функция не является детерминированной. Также функция не является детерминированной, если она использует не детерминированные функции, такие как NOW() или CURRENT_TIMESTAMP(). Оптимизатор может выбрать более эффективный план выполнения, если известно, что функция является детерминированной. В таких случаях вы должны объявить процедуру с ключевым словом DETERMINISTIC. Если вы хотите явно указать, что функция не детерминированная (что по умолчанию), вы можете использовать ключевые слова NOT DETERMINISTIC.

Если вы объявляете не детерминированную функцию как DETERMINISTIC, вы можете получить неверные результаты. Если вы объявляете детерминированную функцию как NOT DETERMINISTIC, в некоторых случаях запросы будут выполняться медленнее.

ИЛИ ЗАМЕНИТЬ

Если используется необязательное предложение OR REPLACE, оно действует как сокращение для:

DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;

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

ЕСЛИ НЕ СУЩЕСТВУЕТ

Если используется предложение IF NOT EXISTS, MariaDB вернёт предупреждение вместо ошибки, если функция уже существует. Не может быть использовано вместе с OR REPLACE.

[НЕ] ДЕТЕРМИНИРОВАННАЯ

Предложение [NOT] DETERMINISTIC также влияет на бинарный журнал, потому что формат STATEMENT не может быть использован для хранения или репликации не детерминированных инструкций.

CONTAINS SQL, NO SQL, READS SQL DATA, и MODIFIES SQL DATA являются информативными предложениями, которые сообщают серверу, что делает функция. MariaDB никак не проверяет, правильно ли указано предложение. Если ни одно из этих предложений не указано, используется CONTAINS SQL по умолчанию.

ИЗМЕНЯЕТ ДАННЫЕ SQL

MODIFIES SQL DATA означает, что функция содержит операторы, которые могут изменить данные, хранящиеся в базах данных. Это происходит, если функция содержит операторы такие как DELETE, UPDATE, INSERT, REPLACE или DDL.

ЧИТАЕТ ДАННЫЕ SQL

READS SQL DATA означает, что функция считывает данные, хранящиеся в базах данных, но не изменяет никакие данные. Это происходит, если используются операторы SELECT, но не выполняются операции записи.

СОДЕРЖИТ SQL

CONTAINS SQL означает, что функция содержит по крайней мере один SQL оператор, но не читает и не записывает никакие данные, хранящиеся в базе данных. Примеры включают SET или DO.

БЕЗ SQL

NO SQL означает ничего, так как MariaDB в настоящее время не поддерживает языки, кроме SQL.

Режим Oracle

В дополнение к традиционной синтаксической конструкции MariaDB на базе SQL/PSM поддерживается подмножество языка Oracle PL/SQL. Подробности о внесённых изменениях при работе в режиме Oracle см. в режиме Oracle.

Безопасность

Вы должны иметь привилегию EXECUTE на функцию, чтобы её вызвать. MariaDB автоматически предоставляет привилегии EXECUTE и ALTER ROUTINE учётной записи, которая вызвала CREATE FUNCTION, даже если было использовано предложение DEFINER.

Каждая функция связана с учётной записью в качестве определяющего пользователя. По умолчанию определяющим пользователем является учётная запись, создавшая функцию. Используйте предложение DEFINER для указания другой учётной записи в качестве определяющего пользователя. Вам необходимо иметь привилегию SUPER, или, начиная с MariaDB 10.5.2, привилегию SET USER для использования предложения DEFINER. Подробности об указании учётных записей см. в Именах учётных записей.

Предложение SQL SECURITY определяет, какие привилегии используются при вызове функции. Если SQL SECURITY равно INVOKER, тело функции будет оцениваться с использованием привилегий пользователя, вызвавшего функцию. Если SQL SECURITY равно DEFINER, тело функции всегда будет оцениваться с использованием привилегий учётной записи определяющего пользователя. DEFINER — это значение по умолчанию.

Это позволяет создавать функции, предоставляющие ограниченный доступ к определённым данным. Например, предположим, что у вас есть таблица, хранящая информацию о сотрудниках, и что вы предоставили привилегии SELECT только на определённые колонки для учётной записи пользователя roger.

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

Чтобы разрешить пользователю получить максимальную заработную плату для отдела, определите функцию и предоставьте привилегию EXECUTE:

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

Так как SQL SECURITY по умолчанию DEFINER, всякий раз, когда пользователь roger вызывает эту функцию, подзапрос будет выполняться с вашими привилегиями. Пока у вас есть привилегии для выбора заработной платы каждого сотрудника, вызывающий функцию сможет получить максимальную заработную плату для каждого отдела, не имея возможности видеть отдельные заработные платы.

Наборы символов и сортировки

Типы возвращаемых значений функций могут быть объявлены с использованием любых допустимых наборов символов и сортировок. Если используется атрибут COLLATE, ему должен предшествовать атрибут CHARACTER SET.

Если кодировка символов и сортировка не заданы явно в операторе, будут использованы значения по умолчанию базы данных на момент её создания. Если значения по умолчанию базы данных изменятся на более позднем этапе, кодировка/сортировка сохранённой функции не изменятся автоматически; для обеспечения использования тех же кодировки/сортировки, что и у базы данных, функцию необходимо удалить и пересоздать.

Примеры

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

CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+

В функции можно использовать составной оператор для управления данными с помощью операторов, таких как INSERT и UPDATE. Следующий пример создаёт функцию счётчика, которая использует временную таблицу для хранения текущего значения. Поскольку составной оператор содержит операторы, завершаемые точкой с запятой, необходимо сначала изменить разделитель операторов с помощью оператора DELIMITER для разрешения использования точки с запятой в теле функции. Подробнее об этом см. Разделители в клиенте mariadb.

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;

Кодировка символов и сортировка:

CREATE FUNCTION hello2 (s CHAR(20))
  RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');

См. также

• Имена идентификаторов
• Сохранённые агрегатные функции
• CREATE FUNCTION (UDF)
• SHOW CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• SHOW FUNCTION STATUS
• Права на хранимые процедуры
• Таблица INFORMATION_SCHEMA.ROUTINES