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

Хранимые процедуры и функции представляют собой подпрограммы, создаваемые с помощью операторов CREATE PROCEDURE и CREATE FUNCTION. Подпрограмма является либо процедурой, либо функцией. Процедура вызывается с помощью оператора call и может только передавать значения обратно, используя выходные переменные. Функции могут возвращать скалярное значение и вызываются из оператора точно так же, как и любые другие функции (то есть, через указание имени функции). Хранимые процедуры могут вызывать другие хранимые процедуры.
В настоящее время MySQL поддерживает контекст только для базы данных по умол­чанию. То есть при задании в процедуре USE имя_базы_данных на выходе подпрограммы восстанавливается исходная база данных по умолчанию. Подпрограмма наследует базу данных по умолчанию от вызывающего оператора, поэтому в целом в процедурах либо должен использоваться оператор USE имя_базы_данных, либо все таблицы должны ука­зываться с явной ссылкой на базу данных, то есть, имя_базы__данных.имя_таблицы.
В MySQL поддерживается самое удобное расширение, позволяющее использовать обычные операторы SELECT (применять курсоры и локальные переменные не нужно) внутри хранимой процедуры. Набор результатов такого запроса просто посылается непосредственно клиенту. Множественные операторы SELECT генерируют и множествен­ные наборы результатов, поэтому клиенту следует использовать клиентскую библиотеку MySQL, поддерживающую множественные наборы результатов. Это означает, что кли­ентская библиотека должна быть из версии MySQL, по крайней мере, не ниже 4.1.
В следующем разделе описывается синтаксис, применяемый для создания, измене­ния, удаления и запроса хранимых процедур и функций.

Обслуживание хранимых процедур

CREATE PROCEDURE и CREATE FUNCTION

CREATE PROCEDURE имя_хранимой_процедуры([параметр[,...]]) [характеристика ...] тело_процедуры
CREATE FUNCTION имя_хранимой__процедуры([параметр[,...]]) [RETURNS тип] [характеристика ...] тело_процедуры
параметр:
[ IN | OUT | INOUT ] имя_параметратип
тип:
ЛюбойдопустимыйтипданныхMySQL

Характеристика:
LANGUAGE SQL
I [NOT] DETERMINISTIC
I SQL SECURITY {DEFINER I INVOKER}
| COMMENT 'строка1
тело_процедуры:
Допустимый оператор (операторы) SQL процедуры
Конструкция RETURNS может быть определена только для FUNCTION. Она используется для указания типа результата функции, при этом в теле функции должен присутствовать оператор RETURN значение.
Список аргументов, заключенный в круглые скобки, должен присутствовать всегда. Если аргументы отсутствуют, следует использовать пустой список аргументов (). Каж­дый аргумент по умолчанию является аргументом IN. Чтобы по-другому определить аргумент, перед его названием укажите ключевое слово OUT или INOUT. Значения IN, OUT или INOUT являются допустимыми только для PROCEDURE.
Оператор CREATE FUNCTION используется в более ранних версиях MySQL для под­держки функций UDF (определяемых пользователем). UDF-функции продолжают под­держиваться даже с появлением хранимых функций. UDF-функция может рассматри­ваться как внешняя хранимая функция. Однако стоит обратить внимание на то, что хра­нимые функции и функции, определяемые пользователем, разделяют одно и то же пространство имен.
Структура для внешних хранимых процедур будет представлена в ближайшем буду­щем. Это позволит записывать хранимые процедуры на языках, отличных от SQL. Ско­рее всего, одним из первых поддерживаемых языков станет РНР, потому что базовый механизм РНР невелик по размерам, безопасен в отношении потоков и легко встраива­ется. Поскольку структура будет общедоступной, ожидается поддержка и многих других языков.
Функция считается "детерминированной", если она всегда возвращает один и тот же результат для одних и тех же входных аргументов, в противном случае функция являет­ся "недетерминированной". В настоящее время характеристика DETERMINISTIC уже вос­принимается, но еще не используется оптимизатором.
Характеристика SQL SECURITY может применяться для определения, должна ли процедура выполняться с использованием привилегий пользователя, создающего эту проце­дуру, или привилегий пользователя, ее вызывающего. Значение по умолчанию -DEFINER. Это новая функция в SQL:2OO3.
MySQL пока еще не использует привилегию GRANT execute.
MySQL поддерживает системную переменную sqljnode, действующую во время создания процедуры, и при выполнении данной процедуры будет всегда применять именно то значение.
Конструкция COMMENT является расширением MySQL и может использоваться для описания хранимой процедуры. Такая информация отображается операторами SHOW CREATE PROCEDURE И SHOW CREATE FUNCTION.
MySQL разрешает, чтобы подпрограммы содержали DDL-операторы (такие как CREATE и DROP) и SQL-операторы транзакций (такие как COMMIT). Стандарт этого не тре­бует, поэтому все зависеть будет от реализации.

На заметку!
В настоящее время хранимые функции, создаваемые с помощью CREATE FUNCTION, не могут содержать ссылки на таблицы. Пожалуйста, обратите внимание, что это включает некоторые операторы SET, но и исключает некоторые операторы SELECT. Данные ограничения будут сняты, как только это станет возможным.
Ниже представлен пример простой хранимой процедуры с аргументом OUT. В приме­ре во время определения процедуры используется команда delimiter клиента mysql для изменения разделителя оператора с ; на //. Это позволяет передать на сервер раздели­тель ;, указанный в теле процедуры, тем самым освобождая mysql от самостоятельной интерпретации данного разделителя.
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT paraml INT)
-> BEGIN
-> SELECT COUNT(*) INTO paraml FROM t;
-> END
-> // Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;

Ниже представлен пример функции, которая принимает аргумент, выполняет опера­цию с помощью SQL-функции и возвращает результат: mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> //
Query OK, 0 rows affected (0.00 sec)
raysql> delimiter ;
mysql> SELECT hello('world');

ALTER PROCEDURE и ALTER FUNCTION

ALTER {PROCEDURE | FUNCTION] шя_храншой_процедуры [характеристика ...] характеристика:
NAME новое_имя
I SQL SECURITY {DEFINER | INVOKER} I COMMENT 'строка'
Данный оператор можно использовать для переименования хранимой процедуры или функции, а также для изменения ее характеристик. В операторе ALTER PROCEDURE или ALTER FUNCTION разрешается указывать и более одной замены.

DROP PROCEDURE и DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] имя_хранимой_процедуры
Данный оператор используется для удаления хранимой процедуры или функции, то есть указанная подпрограмма будет удалена с сервера.
Конструкция IF EXISTS является расширением MySQL. Она предотвращает появле­ние ошибки, если процедура или функция не существует. В таких случаях появляется предупреждение, просмотреть которое можно с помощью SHOW WARNINGS.

SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} имя_хранимой_процедуры
Данный оператор является расширением MySQL. Он похож на оператор SHOW create TABLE и возвращает точную строку, которую можно использовать для воссоздания име­нованной процедуры.

SHOW PROCEDURE STATUS и SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'шаблон1]
Данный оператор является расширением MySQL. Он возвращает характеристики подпрограмм, такие как имя, тип, имя создателя, а также даты создания и изменения. Если определенный шаблон (шаблон) не задан, отображается информация для всех хранимых процедур или функций, в зависимости от используемого оператора.

Оператор CALL

CALL имя_хранимой_процедуры( [параметр•[,...]])
Оператор CALL используется для вызова процедуры, которая была ранее определена с ПОМОЩЬЮ CREATE PROCEDURE.

Составной оператор BEGIN ... END

[метка_начала: ] BEGIN
statement (s) END [метка_ конца]
Хранимые процедуры могут включать множественные операторы, благодаря состав­ному оператору BEGIN.. .END.
Значения метка_начала и метка_конца, если оба заданы, должны быть одинаковыми.
Обратите внимание, что необязательная конструкция [NOT] ATOMIC пока не поддер­живается. Это означает, что нет установленной транзакционной точки сохранения в начале блока команд, и что используемая в таком контексте конструкция BEGIN на теку­щую транзакцию не влияет.
Для использования множественных операторов необходимо, чтобы у клиента была возможность посылать строки запросов, содержащие разделитель операторов ;. Добить­ся этого можно путем применения команды delimiter в командной строке клиента mysql. Замена завершающего запрос разделителя ; (например, на разделитель //) позволяет использовать ; в теле процедуры.

Оператор DECLARE

Оператор DECLARE используется для определения различных локальных для данной операции элементов, то есть для определения локальных переменных (см. раздел Переменные в хранимых процедурах), условий и обработчиков (см. раздел Условия и обработчики), а также курсоров (см. раздел Курсоры). Операторы SIGNAL и RESIGNAL в настоящее время не поддерживаются.
DECLARE может использоваться только внутри составного оператора BEGIN...END и размещается в самом его начале, перед любыми другими операторами.

Переменные в хранимых процедурах

Внутри подпрограммы можно объявлять и использовать переменные.

Локальные переменные DECLARE

DECLARE имя_переменной[,...] тип [DEFAULT значение]
Этот оператор используется для объявления локальных переменных. Контекст переменной ограничен блоком BEGIN ... END.

Оператор установки переменных SET

SET имя_переменной = выражение [,имя_переменной = выражение] ...]
Оператор SET в хранимых процедурах представляет собой расширенную версию об­щего оператора set. Запрашиваемые переменные могут быть как переменными, объяв­ленными внутри процедуры, так и глобальными переменными сервера.
Оператор SET в хранимых процедурах вводится как часть существовавшего ранее синтаксиса Set, что разрешает использование расширенного синтаксиса: SET а=х, Ь=у,..., в котором могут смешиваться разные типы переменных (локально объявленные пере­менные, переменные сервера, а также глобальные и сеансовые переменные сервера). Также, благодаря этому, допустимым становится применение комбинаций локальных переменных и некоторых опций, имеющих смысл только для глобальных/системных переменных; в этом случае, опции принимаются, но игнорируются.

Оператор SELECT...

SELECT имя_столбца[,...] INTO имя_переменной[,...] табличное_выражение
При таком синтаксисе SELECT выбранные столбцы сохраняются непосредственно в переменных. Поэтому извлечена может быть только одна единственная строка. Данный оператор также крайне полезен при его применении в сочетании с курсорами.
SELECT id,data INTO x,y FROM test.tl LIMIT 1;

Условия и обработчики

При некоторых условиях не исключена необходимость в особом типе обработки. Эти условия могут касаться как ошибок, так и общего управления потоком данных внутри подпрограммы.

Условия DECLARE

DECLARE имя__условия CONDITION FOR значениеj/словия
значение__условия:
SQLSTATE [VALUE] 3Ha4eHne_sqlstate I KOM_ouiM6KH__mysql
Данный оператор определяет условия, при которых потребуется определенный тип обработки. Он связывает имя с указанным условием ошибки. Имя может впоследствии использоваться в операторе DECLARE HANDLER. См. раздел Обработчики DECLARE
Кроме значений SQLSTATE также поддерживаются коды ошибок MySQL.

Обработчики DECLARE

DECLARE тип_обработчикаHANDLER FOR значение__условия[,... ] оператор_хранимой_процедуры
типобработчика:
~ CONTINUE EXIT UNDO
значение__условия:
SQLSTATE [VALUE] 3Ha4eH№_sqlstate
имя_условия
SQLWARNING
NOT FOUND [ SQLEXCEPTION I KOM_omst6KV!_mysql
С помощью данного оператора задаются обработчики, каждый из которых может от­вечать за выполнение одного или более условий. При появлении одного из таких усло­вий сразу выполняется указанный оператор.
Для обработчика CONTINUE выполнение текущей операции продолжается после вы­полнения оператора обработчика. Для обработчика EXIT выполнение текущего составно­го оператора BEGIN... END завершается. Тип обработчика UNDO пока не поддерживается.

1. SQLWARNING - сокращенный вариант для всех кодов SQLSTATE, начинающихся с 01.
2. NOT FOUND - сокращенный вариант для всех кодов SQLSTATE, начинающихся с 02.
3. SQLEXCEPTION - сокращенный вариант для всех кодов SQLSTATE, не захваченных

SQLWARNING ИЛИ NOT FOUND.
Кроме значений SQLSTATE также поддерживаются коды ошибок MySQL. Например:
mysql> CREATE TABLE test.t (si int,primary key (si)); Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
-> SET 8x = 1;
-> INSERT ШГ0 test.t VALUES (1);
-> SET @x = 2;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 3;
-> END;
-> // Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL handlerdemo0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+--------- +
I @x ]
+--------- +
! 3 |
+------------ +■
1 row in set (0.00 sec)
Обратите внимание: @х равен З, что указывает на то, что MySQL выполнил процедуру ДО конца. ЕСЛИ бы строка DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; представлена не была, MySQL принял бы значение пути по умолчанию (EXIT) после второй неудачной из-за ограничения PRIMARY KEY попытки выполнить INSERT, и опера­тор SELECT @x возвратил бы значение 2.

Курсоры

В хранимых процедурах и функциях поддерживаются простые курсоры. Синтаксис такой же, как во встроенном SQL. На данный момент курсоры являются нечувствитель­ными, не перемещающимися и доступными только для чтения. "Нечувствительные" оз­начает, что сервер может или не может делать копию таблицы результатов.
Например:
CREATE PROCEDURE curdemo() BEGIN
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE curl CURSOR FOR SELECT id,data FROM test.tl; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE a CHAR(16);
DECLARE b,c INT;
OPEN curl; OPEN cur2;
REPEAT
FETCH curl INTO a, b; FETCH cur2 INTO c;

IF NOT done THEN IF b < с THEN
INSERT INTO test.t3 VALUES (a,b); ELSE
INSERT INTO test.t3 VALUES (a,c); END IF; END IF;
UNTIL done END REPEAT; CLOSE curl; CLOSE cur2; END

Объявление курсоров

DECLARE имя_курсора CURSOR FOR onepaTop_sql
Во время процедуры можно определять многочисленные курсоры, но имя каждого из них должно быть уникальным.

Оператор открытия курсора OPEN

ОРЕЫ имя_курсора
Данный оператор открывает объявленный ранее курсор.

Оператор выборки курсора FETCH

FETCH имякурсора INTO имяпеременной [, имя_ переменной] ...
Данный оператор выполняет выборку следующей строки (если строка существует) с помощью указанного открытого курсора и продвигает указатель курсора.

Оператор закрытия курсора CLOSE

CLOSE имя_курсора
Данный оператор закрывает открытый ранее курсор.

Конструкции управления потоком данных

Конструкции IF, CASE, LOOP, WHILE, ITERATE и LEAVE реализованы полностью.
Каждая из этих конструкций может включать как единственный оператор, так и блок операторов при использовании составного оператора BEGIN.. .END. Конструкции можно представлять в форме вложений.
Циклы FOR на данный момент не поддерживаются.

Оператор IF

IF условие_поискаTHEN оператор(ы)
[ELSEIF условие_поискаTHEN оператор (ы)]
[ELSE оператор (ы)]
END IF
IF реализует базовую конструкцию условия. Если значение условие_поиска является истинным, будет выполнен соответствующий SQL-оператор. Если совпадения с усло-вие_поиска не найдены, выполняться будет оператор, указанный в конструкции ELSE.
Обратите внимание на то, что существует также и функция IF(). См. раздел Функции управления потоком выполнения

Оператор CASE

CASE значение_сазе
WHEN значение_мпеп THEN оператор
[WHEN значение_when THEN оператор ...]
[ELSE оператор] END CASE
Или:
CASE
WHEN условие_поиска THEN оператор
[WHEN условие__поиска THEN оператор ...]
[ELSE оператор] END CASE
CASE реализует сложную конструкцию условия. Если значение условие__поиска явля­ется истинным, будет выполнен соответствующий SQL-оператор. Если совпадения с условие_поиска не найдены, выполняться будет оператор из конструкции ELSE.
На заметку!
Синтаксис оператора CASE внутри хранимой процедуры немного отличается от синтаксиса SQL-выражения CASE. Оператор CASE не может содержать конструкцию ELSE NULL, и его выполне­ние завершается с помощью END CASE, а не END. См. раздел Функции управления потоком выполнения

Оператор LOOP

[метка__начала\] LOOP
оператор (ы) END LOOP [метка_конца]
LOOP реализует простую конструкцию цикла, допуская повторное выполнение како­го-то конкретного оператора или группы операторов. Операторы в цикле повторяются до выхода из этого цикла, для чего обычно используется оператор LEAVE.
Значения метка__начала и метка_конца, если заданы оба, должны быть одинаковыми.

Оператор LEAVE

LEAVE метка
Данный оператор используется для выхода из конструкции управления потоком вы­полнения.

Оператор ITERATE

ITERATE метка
ITERATE может появиться только при использовании операторов LOOP, REPEAT и WHILE. ITERATE означает "повторить цикл снова". Например:
CREATE PROCEDURE doiterate(pi INT) BEGIN
labell: LOOP
SET pi = pi + 1;
IF pi < 10 THEN ITERATE labell; END IF; LEAVE labell; END LOOP labell;

SET @х = pi; END

Оператор REPEAT

[ыетка_начала:] REPEAT
оператор (ы) UNTIL условие_поиска END REPEAT [метка_конца]
Команды, указанные внутри оператора REPEAT, повторяются до тех пор, пока будет истинным условие условие^поиска.
Значения метка_начала и метка__конца, если заданы оба, должны быть одинаковыми.
Например:
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(pl INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > pi END REPEAT;
-> END
-> // Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL dorepeat(lOOO);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
I 1001 |
+--------- +
1 row in set (0.00 sec)

Оператор WHILE

[метканачала: ] WHILE условие_поискаDO
оператор (ы) END WHILE [метка__конца]
Команды, указанные внутри оператора WHILE повторяются до тех пор, пока будет истинным условие условие_поиска.
Значения метка_начала и метка_конца, если заданы оба, должны быть одинаковыми. Например:
CREATE PROCEDURE dowhile() BEGIN
DECLARE vl INT DEFAULT 5;
WHILE vl > 0 DO
SET vl = vl - 1; END WHILE; END