Сообщить об ошибке.

MySQL: Хранимые процедуры и функции

Содержание:

Дополнительно смотрите:


Синтаксис хранимых функции и процедур.

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

-- удаление процедуры (если она существует)
DROP PROCEDURE IF EXISTS proc_name
-- ОПРЕДЕЛЕНИЕ ХРАНИМОЙ ПРОЦЕДУРЫ
delimiter //
CREATE [DEFINER=user] PROCEDURE [IF NOT EXISTS] 
proc_name ([IN|OUT|INOUT])
    -- характеристика хранимой процедуры
    [COMMENT 'string'] 
    [[NOT] DETERMINISTIC]
    [CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
    [SQL SECURITY DEFINER | INVOKER]
    -- тело хранимой процедуры
    [BEGIN]
        -- Тело процедуры/функции может содержать объявления 
        -- переменных `DECLARE`, циклы, например `LOOP`, 
        -- встроенные в MySQL и хранимые процедуры/функции и
        -- другие операторы структуры и/или управления потоком.
    [END] //
delimiter ;

-- удаление процедуры (если она существует)
DROP FUNCTION IF EXISTS func_name
-- ОПРЕДЕЛЕНИЕ ХРАНИМОЙ ФУНКЦИИ
delimiter //
CREATE [DEFINER=user] FUNCTION [IF NOT EXISTS] 
func_name ([IN]) RETURNS type
    -- характеристика хранимой функции
    [COMMENT 'string'] 
    [[NOT] DETERMINISTIC]
    [SQL SECURITY DEFINER | INVOKER]
    -- тело хранимой функции
    [BEGIN]
        -- тело хранимой функции ни чем не отличается от 
        -- от хранимой процедуры, за исключением того, 
        -- что функция должна возвращать значение
        RETURN(expr);
    [END] //
delimiter ;

Комментарии к синтаксису хранимых функции и процедур.

  • delimiter - это команда, которая необходима для изменения разделителя SQL-инструкций с ; на // во время определения процедуры. Это позволяет разделитель ; использовать в теле процедуры для передачи на сервер, а не для интерпретации самой MySQL.
  • proc_name - уникальное имя хранимой процедуры, длиной не более 64 символа. Имена процедур не чувствительны к регистру, поэтому в одной схеме не может быть двух событий с именами procname и ProcName;
  • func_name - уникальное имя хранимой функции, длиной не более 64 символа. Имена функции не чувствительны к регистру, поэтому в одной схеме не может быть двух событий с именами funcname и FuncName;
  • type - тип данных возвращаемого значения хранимой функцией.

Чтобы вызвать хранимую процедуру, нужно использовать оператор CALL proc_name(). Чтобы вызвать сохраненную функцию, необходимо обратится к ней в выражении или просто вызвать ее, например SELECT func_name() AS rez;. Во время вычисления выражения, функция возвращает значение.

IF NOT EXISTS:

Инструкция IF NOT EXISTS предотвращает появление ошибки, если хранимая процедура/функция с таким именем уже существует. Эта опция поддерживается начиная с MySQL 8.0.29 (узнать версию SELECT VERSION();). Если встроенная функция в MySQL с таким именем уже существует, то попытка создать хранимую функцию с помощью CREATE FUNCTION ... IF NOT EXISTS завершается успешно с предупреждением о том, что она имеет то же имя, что и собственная функция MySQL.

[NOT] DETERMINISTIC:

Процедура/функция считается "DETERMINISTIC", если она всегда дает один и тот же результат для одних и тех же входных параметров, и "NOT DETERMINISTIC" в противном случае. Если в определении процедуры/функции не указан ее тип, то по умолчанию используется значение NOT DETERMINISTIC. Неправильное объявление типа может повлиять на результаты или производительность. Если процедура/функция является NOT DETERMINISTIC, а она объявлена как DETERMINISTIC, то это может привести к неожиданным результатам, заставив оптимизатор сделать неправильный выбор плана выполнения. А если тип процедуры/функции объявлен наоборот, то это может привести к снижению производительности из-за того, что доступные оптимизации не будут использоваться.

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA:

Эти характеристики предоставляют информацию о характере использования данных процедурой/функцией. В MySQL эти характеристики носят только рекомендательный характер. Сервер не использует их для ограничения того, какие операторы разрешено выполнять подпрограмме.

  • CONTAINS SQL - указывает, что SQL процедура/функция не содержит операторов чтения или записи данных. Это значение по умолчанию, если ни одна из этих характеристик не указана явно. Примерами таких операторов являются SET @x = 1 или DO RELEASE_LOCK('abc'), которые выполняются, но не считывают и не записывают данные.
  • NO SQL - указывает, что SQL процедура/функция не содержит операторов SQL.
  • READS SQL DATA - указывает, что SQL процедура/функция содержит инструкции для чтения данных (например, SELECT), но не содержит инструкций для записи данных.
  • MODIFIES SQL DATA - указывает, что SQL процедура/функция содержит операторы, которые могут записывать данные (например, INSERT или DELETE).

COMMENT:

Инструкция COMMENT является расширением MySQL и может использоваться для описания хранимой процедуры. Эта информация отображается при выполнении команд SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION.

BEGIN ... END:

Операторы BEGIN и END используется для написания процедур/функций, тело которых содержит несколько инструкций, каждая из которых заканчивается разделителем - точкой с запятой (;). Пустой блок инструкций (BEGIN END) допустим и в основном используется с оператором CASE.

Блоки BEGIN ... END могут быть вложенными, а также иметь метки (например mark: BEGIN). Эти метки служат для преждевременного выхода из вложенного блока (например LEAVE mark;) на уровень выше, а если блок имеет верхний уровень, то выхода из программы.

Тело процедуры/функции может содержать объявления переменных DECLARE, циклы LOOP и другие операторы структуры и/или управления потоком. Синтаксис этих операторов будет рассмотрен ниже. Также MySQL позволяет процедурам/функциям содержать операторы, такие как CREATE и DROP, а хранимым процедурам (но не хранимым функциям) содержать операторы транзакций SQL, такие как COMMIT.

MySQL-операторы, возвращающие список/набор результатов, можно использовать в хранимой процедуре, но не в хранимой функции. Этот запрет включает операторы SELECT ..., которые не содержат предложения INTO var_list, и другие операторы, такие как SHOW, EXPLAIN и CHECK TABLE.

IN | OUT | INOUT:

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

Любой аргумент, по умолчанию является IN. Чтобы указать для аргумента иное, то необходимо использовать ключевое слово OUT или INOUT перед именем параметра.

Примечание. Указание аргументов, таких как IN, OUT или INOUT допустимо только для процедуры. Для функции, аргументы всегда рассматриваются как IN.

  • Аргумент IN передает значение в процедуру/функцию. Процедура может изменить это значение, но когда процедура выполнится, это изменение не будет видно вызывающей стороне.
  • Аргумент OUT передает значение из процедуры обратно вызывающей стороне. Его начальное значение внутри процедуры равно NULL, и когда процедура выполнится, значение OUT видно вызывающей стороне.
  • Аргумент INOUT инициализируется вызывающей стороной и может быть изменен процедурой, и любое изменение значения INOUT, сделанное процедурой, видно вызывающей стороне при завершении процедуры.

Если процедура вызывается из другой хранимой процедуры или функции, то в качестве параметра OUT или INOUT можно передать аргумент процедуры или локальную переменную процедуры. Если процедура вызывается из триггера, то в качестве параметра OUT или INOUT можно передать NEW.col_name.

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

-- определение процедуры
delimiter //
CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
    BEGIN
        SELECT COUNT(*) INTO cities FROM world.city
        WHERE CountryCode = country;
    END//
delimiter ;

-- вызов процедуры
mysql> CALL citycount('FRA', @cities);
Query OK, 1 row affected (0.00 sec)
-- извлечение результата
mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+

RETURNS:

Оператор RETURNS может быть указан только для функции (является обязательным). Он указывает тип возвращаемого значения функции, а тело функции должно содержать оператор RETURN. Если оператор RETURN возвращает значение другого типа, то это значение приводится к типу, указанному в RETURNS. Например, если определение функции указывает RETURNS VARCHAR(50), а инструкция RETURN возвращает целое число, то значение, возвращаемое функцией, будет строкой VARCHAR.

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

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

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

SQL SECURITY:

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

DEFINER=user:

Инструкция DEFINER указывает учетную запись MySQL, которая будет использоваться при проверке прав доступа во время выполнения процедуры/функции, имеющих характеристику SQL SECURITY DEFINER.

Если присутствует инструкция DEFINER, то значением пользователя user должна быть учетная запись MySQL, указанная как 'user_name'@'host_name' или CURRENT_USER или как функция CURRENT_USER(). Разрешенные значения пользователей зависят от имеющихся привилегий.

Если инструкция DEFINER опущена, то определяющим по умолчанию является пользователь, выполняющий оператор CREATE PROCEDURE или CREATE FUNCTION. Это то же самое, что явно указать DEFINER=CURRENT_USER.

Рассмотрим следующую процедуру, которая отображает количество учетных записей MySQL, перечисленных в системной таблице mysql.user:

CREATE DEFINER='admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

Процедуре назначается учетная запись DEFINER 'admin'@'localhost' независимо от того, какой пользователь определяет ее. Она выполняется с привилегиями этой учетной записи независимо от того, какой пользователь вызывает его (т.к. характеристикой безопасности по умолчанию является SQL SECURITY DEFINER). Процедура завершается успешно или завершается ошибкой в ​​зависимости от того, имеет ли вызывающая сторона привилегию EXECUTE для нее, а 'admin'@'localhost' имеет привилегию SELECT для таблицы mysql.user.

Теперь предположим, что процедура определена с помощью характеристики SQL SECURITY INVOKER:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

Процедура по-прежнему имеет DEFINER 'admin'@'localhost', но в этом случае она выполняется с привилегиями вызывающего пользователя. Таким образом, процедура завершается успешно или завершается ошибкой в ​​зависимости от того, имеет ли вызывающая сторона привилегию EXECUTE для нее и привилегию SELECT для таблицы mysql.user.

По умолчанию, когда выполняется подпрограмма с характеристикой SQL SECURITY DEFINER, MySQL Server не устанавливает никаких активных ролей для учетной записи MySQL, указанной в инструкции DEFINER, только роли по умолчанию.

RETURN:

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

Оператор RETURN не используется в хранимых процедурах, триггерах или событиях EVENT. Для выхода из хранимых программ этих типов может использоваться оператор LEAVE.

Определение переменных DECLARE:

Оператор DECLARE объявляет локальные переменные в хранимых программах. Чтобы указать значение по умолчанию для переменной, необходимо использовать предложение DEFAULT. Значение DEFAULT не обязательно должно быть константой и может быть указано как выражение. Если предложение DEFAULT отсутствует, начальное значение равно NULL.

-- объявление локальной переменной `var_name` 
-- и типом `type`
DECLARE var_name type;
-- объявление локальной переменной `var_name`  
-- с типом `type` и со значением по умолчанию `value`
DECLARE var_name type DEFAULT value;
-- в одном объявлении можно указывать несколько 
-- переменных, если они будут одного типа `type`
DECLARE var_name, var_name, ... type;
-- и при этом для всех переменных будет одинаковое значение по умолчанию 
DECLARE var_name, var_name, ... type DEFAULT value;

Оператор DECLARE разрешен только внутри составного оператора BEGIN ... END и должен стоять в его начале перед любыми другими операторами. Локальные переменные доступны только во время выполнения хранимой программы.

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

Присваивание переменных в теле процедуры/функции.

Переменные могут быть установлены (присвоены) непосредственно с помощью оператора:

SET var = 9;
-- или
SET var1 = 3, var2 = 7;

Результаты запросов могут быть извлечены в локальные переменные с помощью:

SELECT col1, col2, ... INTO var1, var2, ... FROM tbl

или путем открытия курсора и использования FETCH.

FETCH cursor_name INTO var1, var2, ...

Локальная переменная не должна иметь то же имя, что и столбец таблицы. Если оператор SQL, такой как оператор SELECT ... INTO var_list, содержит ссылку на столбец и объявленную локальную переменную с тем же именем, то MySQL в настоящее время интерпретирует ссылку как имя переменной. Рассмотрим следующее определение процедуры:

CREATE PROCEDURE sp (x VARCHAR(5))
BEGIN
  -- объявление локальных переменных
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;

  -- присваивание значений колонок, извлекаемых 
  -- запросом `SELECT` - локальным переменным
  SELECT xname, id INTO newname, xid
    FROM table1 WHERE xname = xname;
  SELECT newname;
END;

MySQL интерпретирует xname в операторе SELECT как ссылку на переменную xname, а не на столбец xname. Следовательно, когда вызывается процедура CALL sp(), то переменная newname возвращает значение 'bob' независимо от значения столбца table1.xname.

Точно так же определение курсора в следующей процедуре содержит оператор SELECT, ссылающийся на xname. MySQL интерпретирует это как ссылку на переменную с таким именем, а не как ссылку на столбец.

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;
  DECLARE done TINYINT DEFAULT 0;
  -- объявление курсора
  DECLARE cur CURSOR FOR SELECT xname, id FROM tbl;
  -- объявление события, которое при срабатывании 
  -- присвоит переменной `done = 1`. Это событие срабатывает, 
  -- когда в курсоре кончатся строки для итерирования.
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    -- присваивание значений, извлекаемых 
    -- курсором - локальным переменным
    FETCH FROM cur INTO newname, xid;
    IF done THEN LEAVE read_loop; END IF;
    SELECT newname;
  END LOOP;
  CLOSE cur1;
END;

Использование курсоров Cursors в процедуре/функции.

MySQL поддерживает курсоры внутри хранимых процедур/функций.

Объявление курсора DECLARE ... CURSOR ....

Синтаксис DECLARE ... CURSOR:

DECLARE cursor_name CURSOR FOR SELECT ... FROM ..

Инструкция выше объявляет курсор и связывает его с оператором SELECT, который извлекает строки, которые должен пройти курсор. Чтобы получить строки позже, необходимо использовать оператор FETCH. Количество столбцов, извлекаемых оператором SELECT, должно совпадать с количеством выходных переменных, указанных в операторе FETCH.

  • Оператор SELECT не может иметь предложение INTO.
  • Объявления курсора должны стоять перед объявлениями обработчиков и после объявлений переменных и условий.
  • Хранимая процедура/функция может содержать несколько объявлений курсора, но каждый курсор, объявленный в данном блоке, должен иметь уникальное имя.

Инструкция курсора FETCH.

Синтаксис FETCH:

FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

Инструкция выше выбирает следующую строку для оператора SELECT, связанного с указанным курсором cursor_name (который должен быть объявлен и открыт оператором OPEN cur;), и перемещает указатель курсора вперед. Если строка существует, то выбранные столбцы сохраняются в именованных переменных var_name. Количество столбцов, извлекаемых оператором SELECTобъявлении курсора), должно совпадать с количеством выходных переменных, указанных в операторе FETCH.

Если курсор не закрыт явно оператором CLOSE cur;, то он закрывается в конце блока BEGIN ... END, в котором он был объявлен.

Если больше нет доступных строк, то возникает условие "No Data" со значением SQLSTATE "02000". Чтобы обнаружить это условие, можно настроить обработчик для него (или для условия "NOT FOUND"). Например:

-- определяем переменную `done=0`
DECLARE done TINYINT DEFAULT 0;
...
-- при выполнении условия NOT FOUND переменная `done=1`
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
...
-- открываем ранее объявленный курсор
OPEN cur;
  mark_loop: LOOP
  -- в цикле LOOP перебираем строки открытого курсора
  FETCH FROM cur INTO var1, var2;
    -- внутри цикла проверяем переменную,
    -- done, чтобы выйти из цикла `LEAVE mark_loop`
    IF done THEN LEAVE mark_loop; END IF;
  END LOOP;
-- закрываем курсор
CLOSE cur;

Если в процедуре/функции используются несколько открытых курсоров, то необходимо иметь ввиду, что другая операция, такая как SELECT или другая FETCH, также может привести к выполнению обработчика, вызвав то же условие. Если необходимо определить, какая операция вызвала условие "NOT FOUND", поместите операцию в собственный блок BEGIN ... END, чтобы ее можно было связать со своим собственным обработчиком.

Полный пример использования курсоров.

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  -- объявляем курсоры
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  -- объявляем обработчик условия NOT FOUND для курсоров
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- открываем курсоры
  OPEN cur1;
  OPEN cur2;
  -- запускаем цикл
  read_loop: LOOP
    -- перемещаем курсор `cur1` на строку вперед
    FETCH cur1 INTO a, b;
    -- перемещаем курсор `cur2` на строку вперед
    FETCH cur2 INTO c;
    -- проверяем обработчик `NOT FOUND`
    IF done THEN
      -- если итерации по одному из курсоров 
      -- кончилась то выходим из цикла `read_loop`
      LEAVE read_loop;
    END IF;
    -- далее идет работа с записями курсора 
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;
  -- закрываем курсоры
  CLOSE cur1;
  CLOSE cur2;
END;

Управление ветвлением процедуры/функции.

Выражение NULL = NULL нельзя использовать для проверки на равенство с NULL, поскольку является ложным. В MySQL есть специальный оператор для безопасной проверки на равенство с NULL, например выражение NULL <=> NULL возвратит истину (TRUE).

Заявление CASE ... END CASE;.

Оператор CASE для хранимых программ реализует сложную условную конструкцию.

Синтаксис CASE:

-- ВАРИАНТ 1
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

-- ВАРИАНТ 2
CASE
    WHEN condition THEN statement_list
    [WHEN condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

Для варианта 1: case_value - это выражение. Это значение сравнивается с выражением when_value в каждом предложении WHEN до тех пор, пока одно из них не станет равным. Когда найдено равное значение when_value, выполняется соответствующее предложение THEN список операторов statement_list. Если в значениях when_value нет равного, то выполняется предложение ELSE со своим списком операторов, если оно есть.

Для варианта 2: каждое выражение condition предложения WHEN оценивается до тех пор, пока одно из них не станет истинным, после чего выполняется соответствующее ему предложение THEN statement_list. Если ни одно из выражений condition не равно, то выполняется предложение ELSE statement_list, если оно есть.

Если ни одно из значений when_value или condition не выполняется, а оператор CASE не содержит предложения ELSE, то возникает ошибка оператора: "not found for CASE".

Каждый statement_list состоит из одного или нескольких операторов SQL; пустой statement_list не разрешен.

Для обработки ситуаций, когда ни одно из предложений WHEN не соответствует ни одному значению, необходимо использовать ELSE с пустым блоком BEGIN END, как показано в этом примере. (Отступ, используемый здесь в предложении ELSE, служит только для ясности и не имеет значения.)

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN END;
    END CASE;
  END;

Заявление IF ... END IF;.

Оператор IF для хранимых программ реализует базовую условную конструкцию.

Синтаксис IF:

IF condition THEN statement_list
    [ELSEIF condition THEN statement_list] ...
    [ELSE statement_list]
END IF

Если заданное условие condition оценивается как истинное, то выполняется соответствующее предложение THEN или ELSEIF. Если ни одно условие condition не оценивается как истинное, то выполняется предложение ELSE.

Каждый statement_list состоит из одного или нескольких операторов SQL; пустой statement_list не разрешен (для таких ситуаций в statement_list необходимо использовать пустой блок BEGIN END).

Блок IF ... END IF, как и все другие блоки управления потоком, используемые в хранимых программах, должен заканчиваться точкой с запятой, как показано в этом примере:

CREATE FUNCTION SimpleCompare(n INT, m INT)
  RETURNS VARCHAR(20)
  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END;

Как и другие конструкции управления потоком, блоки IF ... END IF могут быть вложены в другие конструкции управления потоком, включая другие операторы IF. Каждый IF должен заканчиваться своим END IF;, за которым следует точка с запятой. Можно использовать отступ, чтобы сделать вложенные блоки управления потоком более понятными для людей (хотя это не требуется для MySQL):

CREATE FUNCTION VerboseCompare (n INT, m INT)
  RETURNS VARCHAR(50)
  BEGIN
    DECLARE s VARCHAR(50);

    IF n = m THEN SET s = 'equals';
    ELSE
      IF n > m THEN SET s = 'greater';
      ELSE SET s = 'less';
      END IF;

      SET s = CONCAT('is ', s, ' than');
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m, '.');

    RETURN s;
  END;

В этом примере внутренний IF оценивается, только если n не равно m.

Использование циклов в процедурах/функциях.

Заявление ITERATE label может использоваться только в операторах LOOP, REPEAT и WHILE. Заявление ITERATE означает "перейти к следующей итерации цикла" с меткой label. Поведение ITERATE label можно сравнить с оператором continue языка Python.

Заявление LEAVE label используется для выхода из конструкции управления потоком с заданной меткой label. Если метка относится к самому внешнему сохраненному программному блоку, то LEAVE выходит из программы. LEAVE может использоваться в рамках BEGIN ... END или конструкций цикла (LOOP, REPEAT, WHILE).

Заявление LOOP ... END LOOP;.

Синтаксис LOOP:

[label:] LOOP 
  statement_list 
END LOOP [label];

Оператор LOOP реализует простую конструкцию цикла, позволяющую многократно выполнять список операторов, каждый из которых заканчивается точкой с запятой (;) в качестве разделителя операторов. Операторы внутри цикла повторяются до тех пор, пока цикл не будет завершен. Обычно, завершение цикла LOOP выполняется оператором LEAVE. Внутри хранимой функции также можно использовать RETURN, который полностью выходит из функции и возвращает значение.

Если внутри цикла LOOP ... END LOOP; нет оператора завершения цикла LEAVE, то это приводит к бесконечному циклу.

Оператор LOOP может иметь метку. Эта метка служит для пропуска итерации (например ITERATE label1;) или выхода из цикла (например LEAVE label1;).

Пример:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END;

Заявление WHILE ... DO ... END WHILE;.

Синтаксис WHILE:

[label:] WHILE condition DO
    statement_list
END WHILE [label];

Список операторов statement_list в инструкции WHILE повторяется до тех пор, пока истинно выражение condition. Список операторов statement_list состоит из одного или нескольких операторов SQL, каждый из которых заканчивается точкой с запятой (;) в качестве разделителя операторов.

Оператор WHILE может иметь метку. Эта метка служит для пропуска итерации (например ITERATE mark;) или преждевременного выхода из цикла (например LEAVE mark;).

Пример использования WHILE ... DO ... END WHILE;.

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  mark: WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;

Заявление REPEAT ... UNTIL ... END REPEAT;.

Синтаксис REPEAT:

[label:] REPEAT
    statement_list
UNTIL condition
END REPEAT [label];

Список операторов statement_list в инструкции REPEAT повторяется до тех пор, пока не станет истинным выражение condition. Таким образом, REPEAT всегда входит в цикл хотя бы один раз.

Список операторов statement_list состоит из одного или нескольких операторов, каждый из которых заканчивается точкой с запятой (;) в качестве разделителя операторов.

Оператор REPEAT может иметь метку. Эта метка служит для пропуска итерации (например ITERATE mark;) или преждевременного выхода из цикла (например LEAVE mark;)

CREATE PROCEDURE dorepeat(p1 INT)
  BEGIN
    SET @x = 0;
    mark: REPEAT
      SET @x = @x + 1;
    UNTIL @x > p1 END REPEAT;
  END;