Обзор хранимых функций

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

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

Вот примерный шаблон, чтобы увидеть хранимую функцию в действии:

DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
 DECLARE x TINYINT;
 SET x = 42;
 RETURN x;
END 

//

DELIMITER ;

Сначала изменяется разделитель, так как определение функции будет содержать обычный разделитель точка с запятой. См. Разделители в клиенте mariadb для получения дополнительной информации. Затем функция называется FortyTwo и определяется для возврата tinyin. Ключевое слово DETERMINISTIC не обязательно во всех случаях (хотя, если включён журнал двоичных логов, его отсутствие приведёт к ошибке), и оно помогает оптимизатору запросов выбрать план запроса. Детерминированная функция — это функция, которая, при одинаковых аргументах, всегда возвращает одинаковый результат.

Далее, тело функции размещается между операторами BEGIN и END. Оно объявляет tinyint, X, который просто устанавливается в 42, и это возвращаемое значение.

SELECT FortyTwo();
+------------+
| FortyTwo() |
+------------+
|         42 |
+------------+

Конечно, функция, которая не принимает никаких аргументов, мало полезна. Вот более сложный пример:

DELIMITER //
CREATE FUNCTION VatCents(price DECIMAL(10,2)) RETURNS INT DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END //
Query OK, 0 rows affected (0.04 sec)
DELIMITER ;

Эта функция принимает аргумент, price, который определяется как DECIMAL, и возвращает INT.

Подробности см. на странице CREATE FUNCTION.

Начиная с MariaDB 10.3.3, также можно создавать хранимые агрегатные функции.

Списки и определения хранимых функций

Чтобы найти, какие хранимые функции работают на сервере, используйте SHOW FUNCTION STATUS.

SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

или напрямую запросите таблицу routines в базе данных INFORMATION_SCHEMA:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE
  ROUTINE_TYPE='FUNCTION';
+--------------+
| ROUTINE_NAME |
+--------------+
| VatCents     |
+--------------+

Чтобы узнать, что делает хранимая функция, используйте SHOW CREATE FUNCTION.

SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
            Function: VatCents
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
    DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

Удаление и обновление хранимых функций

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

DROP FUNCTION FortyTwo;

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

Разрешения в хранимых функциях

См. статью Разрешения на хранимые процедуры.

См. также

• CREATE FUNCTION
• SHOW CREATE FUNCTION
• DROP FUNCTION
• Разрешения на хранимые процедуры
• SHOW FUNCTION STATUS
• Таблица ROUTINES схемы INFORMATION_SCHEMA
• Хранимые агрегатные функции.