SQL-Ex blog

Прежде чем дальше разбираться с хранимым функциями, важно отметить, что имеется три различных типа функций в MySQL, которые вы можете добавить на уровне базы данных или на уровне сервера:

• Хранимые функции. Функции, которые вы создаете как объекты базы данных с помощью оператора CREATE FUNCTION.


• Подгружаемые функции. Функции, которые компилируются как библиотечные файлы, а затем загружаются на сервер динамически при выполнении оператора CREATE FUNCTION.


• Естественные функции. Функции, которые добавляются на сервер путем модификации исходного кода MySQL и компиляции его в mysqld.

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

Подготовка среды MySQL

Как и ранее, примеры в этой статье будут основаны на базе данных travel. Вы уже должны иметь её, в этом случае пропустите данный раздел. Если нет, начните с выполнения следующего скрипта SQL для создания базы данных и таблиц в ней:

DROP DATABASE IF EXISTS travel;

CREATE DATABASE travel;

USE travel;

CREATE TABLE manufacturers (

  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  manufacturer VARCHAR(50) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=1001;

CREATE TABLE airplanes (

  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  plane VARCHAR(50) NOT NULL,

  manufacturer_id INT UNSIGNED NOT NULL,

  engine_type VARCHAR(50) NOT NULL,

  engine_count TINYINT NOT NULL,

  max_weight MEDIUMINT UNSIGNED NOT NULL,

  wingspan DECIMAL(5,2) NOT NULL,

  plane_length DECIMAL(5,2) NOT NULL,

  parking_area INT GENERATED ALWAYS AS 

        ((wingspan * plane_length)) STORED,

  icao_code CHAR(4) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (plane_id),

  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 

    REFERENCES manufacturers (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=101;

Таблица airplanes имеет внешний ключ, который ссылается на таблицу manufacturers, поэтому вы должны создавать таблицы в указанном здесь порядке. После создания таблиц вы можете добавить в них некоторые данные, чтобы можно было протестировать вашу функцию. Для заполнения таблиц выполните следующие операторы INSERT:

INSERT INTO manufacturers (manufacturer)

VALUES ('Airbus'), ('Beechcraft'), ('Piper');

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    max_weight, wingspan, plane_length, icao_code)

VALUES 

  ('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),

  ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),

  ('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 

               117.45, 123.27, 'A320'),

  ('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 

               175.50, 'A30B'),

  ('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 

               2, 12500, 44.50, 46.00, 'PRM1'),

  ('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet', 

              2, 15780, 43.50, 48.42, 'BE40'),

  ('1900D', 1002, 'Turboprop', 2,17120,  57.75, 57.67, 'B190'),

  ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),

  ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 

         43.17, 29.60, 'P46T'),

  ('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');

Как и в случае с операторами CREATE TABLE, вы должны выполнять операторы INSERT в указанном порядке, чтобы не нарушать ограничение внешнего ключа на таблице airplanes. Теперь мы можем начать создавать хранимые функции.

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

Чтобы добавить хранимую функцию в базу данных MySQL, вы можете использовать оператор CREATE FUNCTION. Этот оператор подобен в некоторых аспектах оператору CREATE PROCEDURE. В обоих случаях вы должны задать имя объекта и определить тело процедуры. Вы можете также включить необязательное предложение DEFINER, одну или более характеристик и один или более параметров.

Есть и следующие важные отличия оператора CREATE FUNCTION:

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


• Хранимая функция поддерживает только входные параметры. Хранимая процедура поддерживает параметры IN, OUT и INOUT в любом сочетании.


• Хранимая функция может включать предложение RETURNS в определении перед телом функции. Это предложение указывает тип данных возвращаемого функцией значения. Хранимые процедуры не имеют этого предложения.


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

Держа это в голове, давайте рассмотрим простой пример оператора CREATE FUNCTION, который определяет хранимую функцию, которая преобразует фунты в килограммы:

DELIMITER //

CREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)

RETURNS MEDIUMINT UNSIGNED 

DETERMINISTIC

BEGIN

  RETURN (lbs * 0.45359237);

END//

DELIMITER ;

Функция называется lbs_to_kg и включает один входной параметр с именем lbs. Вы не обязаны включать параметр при определении функции, но обычно вы захотите иметь хотя бы один. Если вы добавляете больше одного, необходимо разделять их запятыми.

Определение параметров заключается в скобки и включает тип данных параметра, MEDIUMINT UNSIGNED. Я выбрал этот тип данных, поскольку я в конечном итоге хочу использовать эту функцию для столбца max_weight в таблице airplanes, который также определен с этим типом данных.

Кроме того, я использовал тип данных MEDIUMINT UNSIGNED в предложении RETURNS. Предложение указывает, что возвращаемое функцией значение должно быть целым в диапазоне, допускаемым этим типом данных. Я выяснил, что этот тип данных надежен, поскольку один фунт эквивалентен 0.45359237 килограммов, поэтому возвращаемое значение не сможет превзойти максимальное значение в столбце max_weight.

Если вы захотите поддерживать более широкий диапазон значений, можете вместо этого использовать тип данных INT или BIGINT для параметра lbs и предложения RETURNS. Это обеспечит вам большую гибкость, если вы захотите использовать функцию для преобразования значений, превосходящих значения в столбце max_weight.

За предложением RETURNS следует характеристика DETERMINISTIC. Характеристика - это один из нескольких вариантов, которые вы можете добавить в определение функции и которые по разному влияют на поведение функции. Например, вы можете добавить характеристику для указания языка тела функции или определения его природы. Это те же самые характеристики, которые могут использоваться в хранимых процедурах.

Характеристика DETERMINISTIC указывает, что функция будет возвращать одни и те же результаты для одних и тех же входных параметров при каждом вызове функции. По умолчанию функция считается недетерминистической, если не указано обратное. Использование характеристики DETERMINISTIC может помочь оптимизатору выбрать лучший план выполнения. Однако назначение этой характеристики для недетерминистической функции может привести оптимизатор к некорректному выбору.

Тело функции идет после перечисленных характеристик. В нашем случае я использовал синтаксис BEGIN…END для установки составного оператора, хотя здесь имеется только один оператор RETURN. Часто ваш код будет включать составной оператор - блок одного или нескольких операторов SQL - и я хочу быть уверенным, что вы понимаете, как включить их в определение функции. Как и для хранимых процедур, ничего необычного нет в том, что разработчики используют составной оператор, даже если он включает единственный оператор SQL.

Оператор RETURN определяет простое математическое выражение, которое умножает значение входного параметра lbs на 0.45359237 для получения числа килограммов для заданного веса. Результат этих вычислений является возвращаемым значением функции при ее выполнении.

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

Проверка вновь созданной хранимой функции

После выполнения оператора CREATE FUNCTION вы можете проверить, что функция была добавлена в базу данных travel, обратившись к навигатору, как показано на Рис.1. (Вам может потребоваться обновить навигатор, чтобы увидеть новую функцию.)


Рис.1 Просмотр функции в навигаторе

Из навигатора вы можете открыть определение функции на вкладке Routine, щелкнув по иконке с гаечным ключом рядом с именем функции. На рис.2 показано определение функции на вкладке Routine. Оператор CREATE FUNCTION почти идентичен тому, который вы создали, за исключением добавления предложения DEFINER после ключевого слова CREATE.


Рис.2 Просмотр определения функции на вкладке Routine

Как и в случае с представлениями и хранимыми процедурами, предложение DEFINER указывает, какой аккаунт был назначен в качестве создателя объекта. Я выполнял оператор CREATE FUNCTION, когда был зарегистрирован под аккаунтом root на моем экземпляре локальной MySQL, поэтому это имя пользователя было добавлено в определение. По умолчанию MySQL использует аккаунт пользователя, который выполняет оператор CREATE FUNCTION, но вы можете указать другой аккаунт, если он имеет соответствующие разрешения.

Вы могли обратить внимание, что определение функции на вкладке Routine не имеет операторов DELIMITER или пользовательского разделителя. Однако если бы вы обновили определение и щелкнули Apply, Workbench добавил бы эти элементы за вас. (Могли также добавиться оператор DROP function, который необходимо выполнить перед оператором CREATE FUNCTION.)

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

SELECT * FROM information_schema.routines

WHERE routine_schema = 'travel';

Результаты должны включать функцию lbs_to_kg вместе с другими хранимыми процедурами и хранимыми функциями, которые были созданы в базе данных. Если вы используете установку из последней статьи, ваша база данных может включать хранимую процедуру get_plane_info.

В предыдущем примере я включил предложение WHERE, которое ограничивает результаты базой данных travel. Однако вы можете еще более ограничить результаты, указав также имя функции в предложении WHERE, и какой столбец или столбцы будут возвращаться. Например, следующий оператор SELECT ограничивает результаты столбцом routine_definition и функцией lbs_to_kg в базе данных travel:

SELECT routine_definition

FROM information_schema.routines

WHERE routine_schema = 'travel'

  AND routine_name = 'lbs_to_kg';

Теперь оператор должен вернуть единственное значение, хотя его может быть трудно прочитать. Как и в случае с хранимыми процедурами, вы можете просмотреть значение целиком в отдельном окне. Выполните щелчок правой кнопкой на значении непосредственно в результатах и щелкните Open Value in Viewer. MySQL откроет окно, в котором будет показано значение, как на рис.3. (Выберите вкладку Text, если она еще не выбрана.)


Рис.3 Проверка тела функции в просмотрщике

Как видно, в окне выводится только тело функции, которое в нашем случае представляет составной оператор, включающий оператор RETURN.

Использование хранимых процедур в запросе MySQL

После проверки создания вашей функции вам следует проверить, что она работает как предполагалось. Один способ - это создать простой оператор SELECT, который только лишь вызывает функцию lbs_to_kg. Например, следующий оператор SELECT включает только предложение SELECT, и это предложение содержит только одно выражение:

SELECT lbs_to_kg(132) AS max_kg;

Выражение вызывает функцию lbs_to_kg, передавая значение параметра 132. Выражение также дает имя выходному столбцу (max_kg). Оператор должен вернуть значение 60.

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

SELECT a.plane, max_weight AS max_lbs, 

  lbs_to_kg(max_weight) AS max_kg

FROM airplanes a INNER JOIN manufacturers m

  ON a.manufacturer_id = m.manufacturer_id

WHERE m.manufacturer = 'airbus'

ORDER BY a.plane;

Оператор соединяет таблицы airplanes и manufacturers по столбцу manufacturer_id в каждой таблице. Предложение SELECT оператора включает выражение, которое использует функцию lbs_to_kg для преобразования столбца max_weight в килограммы и возвращения столбца с именем max_kg. Возвращаемые оператором результаты показаны на рис.4.


Рис.4 Использование хранимой функции в запросе

Результаты включают исходный вес (в фунтах) в столбце max_lbs и вес в килограммах в столбце max_kg после преобразования значений max_weight. Включив оба веса, вы можете быстро сравнить их, чтобы получить общее представление о том, что функция возвращает ожидаемые результаты.

Обновление хранимой функции в MySQL

Как говорилось ранее, хранимые функции MySQL несколько подобны хранимым процедурам. Например, они обе поддержвают характеристики, входные параметры и предложение DEFINER. Есть еще одно важное сходство. Вы можете менять только характеристики. Вы не можете изменить тело ли другие элементы оператора. Вместо этого вы должны сначала удалить функцию, а затем снова создать её, вводя новые элементы.

Чтобы удалить хранимую процедуру, вы можете использовать оператор DROP FUNCTION, как показано в следующем примере:

DROP FUNCTION IF EXISTS lbs_to_kg;

Предложение IF EXISTS не является обязательным, но это удобный способ избежать генерации ошибок при попытке удалить несуществующую функцию. Это предложение особенно полезно, когда вы разрабатываете схему базы данных и регулярно обновляете объекты.

После удаления функции вы можете модифицировать определение под новые требования. Например, следующий оператор CREATE FUNCTION снова создает функцию lbs_to_kg, но теперь добавляет оператор DECLARE и конструкцию IF в составной оператор:

DELIMITER //

CREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)

RETURNS VARCHAR(50) 

DETERMINISTIC

BEGIN

  DECLARE msg VARCHAR(50);

  IF lbs > 999999 THEN SET msg = 

    CONCAT(ROUND((lbs * 0.45359237), 0), 

            ' kg exceeds airport weight limits.');

  ELSEIF lbs >= 100000 AND lbs <= 999999 THEN SET msg = 

    CONCAT(ROUND((lbs * 0.45359237), 0), 

            ' kg exceeds runway weight limits.');

  ELSE SET msg = CONCAT(ROUND((lbs * 0.45359237), 0), 

            ' kg within weight limits.');

  END IF;

  RETURN msg;

END//

DELIMITER ;

Оператор DECLARE объявляет локальную переменную msg и назначает ей тип данных VARCHAR. Обратите внимание, что предложение RETURNS также обновилось, чтобы соответствовать по типу переменной msg. Эта переменная затем может использоваться в финальном операторе RETURN для формированя значения, возвращаемого функцией.

Составной оператор также включает оператор IF. Оператор начинается с предложения начального условия, за которым следует предложение ELSEIF, а затем предложение ELSE. Каждое предложение применяет одну и ту же логику на основе входного параметра lbs. Если значение lbs попадает в заданный диапазон, переменная msg устанавливается в предопределенное значение для этого диапазона. (Мы обсудим условные операторы более подробно в следующих статьях.)

Значение msg сначала определяется преобразованием значения lbs в килограммы, а затем конкатенацией результатов со строкой (тело сообщения). Например, если значение lbs больше чем 99999, то переменная msg устанавливается в число килограммов плюс сообщение ' kg exceeds the airport weight limits.' ( kg превышает предел по весу для аэропорта).

Чтобы реализовать эту логику, каждое условное предложение включает также две встроенные функции: ROUND и CONCAT. Функция ROUND округляет килограммы до целого числа, а функция CONCAT соединяет округленные килограммы с указанным текстом. Напрмер, если вес в фунтах составляет 120000, оператор IF установит значение переменной в '54431 kg exceeds runway weight limits.' (54431 кг превышает пределы по весу для взлетной полосы). Вы сами можете увидеть это, выполнив следующий оператор SELECT:

SELECT lbs_to_kg(120000) AS max_kg;

Оператор должен вернуть результаты, показанные на рис.5.


Рис.5 Просмотр результатов, возвращаемых обновленной хранимой процедурой

Вы можете также использовать функцию lbs_to_kg в более сложном операторе SELECT, точно так же, как вы делали ранее:

SELECT m.manufacturer, a.plane, 

  max_weight AS max_lbs, 

  lbs_to_kg(max_weight) AS max_kg

FROM airplanes a INNER JOIN manufacturers m

  ON a.manufacturer_id = m.manufacturer_id

ORDER BY m.manufacturer, a.plane;

Теперь каждая из возвращаемых строк включает одно из трех сообщений в столбце max_kg. Сообщение основано на числе фунтов в столбце max_weight, которое передается в функцию через параметр. На рис.6 показаны результаты, возвращаемые оператором SELECT.


Рис.6 Использоване хранимой функции в выражениях запроса

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

Изменение хранимой функции в MySQL

Как упоминалось ранее, единственными элементами определения хранимой функции, которые вы можете изменить, являются характеристики. Для этого вы можете использовать оператор ALTER FUNCTION. Например, следующий оператор добавляет характристику COMMENT и характеристику SQL SECURITY:

ALTER FUNCTION lbs_to_kg

COMMENT 'converts weight to kilograms and generates message'

SQL SECURITY INVOKER;

Характеристика COMMENT просто добавляет комментарий, который описывает назначение функции. Характеристика SQL SECURITY предписывает MySQL выполнять код в контексте безопасности аккаунта пользователя, который вызывает функцию, а не использование аккаунта определителя (значение по умолчанию).

После выполненя оператора ALTER FUNCTION вы можете проверить, что характеристики были добавлены, просмотром определения функции на вкладке Routine, что показано на рис.7.


Рис.7 Просмотр определения хранимой функции на вкладке Routine

Оператор CREATE FUNCTION теперь включает три характеристики - одну исходную и две, добавленные при выполнении оператора ALTER FUNCTION.

Работа с хранимыми функциям в MySQL

Хотя хранимые функции похожи на хранимые процедуры, они предназначены совершенно для других целей: для возврата значения, которое может использоваться выражением при расчетах. По этой причине хранимые функции могут быть исключительно полезны и достойны для включения в ваш арсенал инструментов, особенно в силу того, что они легко создаются и выполняются. Однако они могут также повлиять на производительность, если плохо реализованы. Например, если ваш запрос возвращает тысячи строк, то чересчур сложная функция может уронить вашу систему, т.к. MySQL пытается применить логику функции к каждой строке. При надлежащем использовании хранимые функции могут стать невероятно выгодными, особенно если вы станете более искусными в построении запросов SQL.