Хранимые процедуры – это не просто удобные контейнеры для группы запросов, они позволяют реализовать достаточно сложную логику, используя операторы ветвления и циклы.
Оператор IF … THEN … ELSE
Этот оператор имеет следующий синтаксис:
IF условие_1 THEN операторы_1
[ELSEIF условие_2 THEN операторы_2] …
[ELSE операторы_3]
END IF
Отметим, что кроме оператора IF существует в MySQL функция IF ( ).
ОператорCASE
Этот оператор позволяет осуществить множественный выбор и имеет две формы. Первая форма имеет следующий синтаксис:
CASE значение_case
WHEN значение_1 THEN операторы_1
[WHEN значение_2 THEN операторы_2] …
[ELSE операторы_3]
END CASE
Оператор CASE сравнивает значение выражения значение_case со значением значение_1 , значение_2 и так далее. Как только соответствие будет найдено, то выполнится соответствующий блок операторов операторы_1, операторы_2 и так далее. Если ни одного соответствия не найдено, выполняются операторы_3.
Вторая форма оператора CASE позволяет осуществлять сравнение непосредственно в конструкции WHEN. При этом как только будет найдено первое истинное значение, выполняется соответствующий блок операторов и осуществляется выход из оператора CASE:
. . . . . . . . . .
CASE
WHEN cc = 0 THEN
SESECT . . . .
WHEN cc = 1 THEN
SESECT . . . .
WHEN cc = 3 THEN
SESECT . . . .
. . . . . . .
ELSE
SESECT . . . .
END CASE;
. . . . . . .
Оператор WHILE
Это – оператор цикла с предусловием. Синтаксис:
[метка:] WHILE условие DO
блок операторов
END WHILE [метка]
Блок операторов выполняется, пока условие истинно.
Пример. Создадим хранимую процедуру, которая выводит текущую дату num раз, где num - параметр, задаваемый пользователем.
mysql> CREATE PROCEDURE nown (IN num INT)
−> BEGIN
−> DECLARE i DEFAULT 0;
−> IF (num > 0) THEN
−> wet : WHILE i < num DO
−> SELECT NOW ( );
−> SET i = i + 1;
−> END WHILE wet;
−> ELSE
−> SELECT ‘Ошибочное значение параметра’;
−> END IF;
−> END
−> / /
Вызов процедуры:
mysql> CALL NOWN (2) / /
Метка в цикле предназначена не только для того, чтобы облегчить чтение кода при очень длинных циклах, она позволяет осуществлять досрочный выход из цикла с помощью оператора LEAVE, который имеет следующий синтаксис:
LEAVE метка
Оператор LEAVE прекращает выполнение блока, помеченного меткой, например:
. . . . . . . .
−> IF (num > 0) THEN
−> wet: WHILE i < num DO
−> IF i > 5 THEN LEAVE wet;
−> END IF;
. . . . . . . .
Еще одним оператором, выполняющим досрочное прекращение цикла, является оператор ITERATE. В отличие от оператора LEAVE оператор ITERATE не прекращает выполнение цикла, он лишь выполняет досрочное прекращение текущей итерации.
Оператор REPEAT
Этот оператор также реализует цикл, но с постусловием. Синтаксис:
[метка:] REPEAT
блок операторов
UNTIL условие
END REPEAT [метка]
Блок операторов выполняется, пока условие ложно. Проверка условия осуществляется после выполнения блока операторов.
Оператор LOOP
Этот оператор предназначен для реализации циклов и имеет следующий синтаксис:
[Метка:] LOOP
блок операторов
END LOOP [метка]
Этот цикл, в отличие от операторов WHILE и REPEAT, не имеет условий выхода. Поэтому выход из цикла нужно предусмотреть, например, с помощью оператора LEAVE.
Оператор GOTO
Этот оператор осуществляет безусловный переход по метке.
Метаданные
Существуют четыре способа просмотреть данные, относящиеся к хранимым процедурам или функциям:
1. Операторы SHOW PROCEDURE STATUS и SHOW FUNCTION STATUS;
2. Операторы SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION;
3. Запрос SELECT FROM mysq.proc;
4. Запрос SELECT FROM information_schema.
Оператор SHOW PROCEDURE STATUS
Этот оператор возвращает список хранимых процедур, исключая функции, которые удовлетворяют указанному шаблону. Синтаксис:
SHOW PROCEDURE STATUS [шаблон]
Например,
mysql> SHOW PROCEDURE STATUS LIKE ‘bin%’;
Аналогично для функций.
Оператор SHOW CREATE
Выводит оператор CREATE PROCEDURE, при помощи которого была создана хранимая процедура. Аналогично выполняется оператор SHOW CREATE FUNCTION для функций.
ОператорSELECT FROM mysq.proc
Этот оператор позволяет извлечь строки таблицы proc системной базы данных mysql, куда сохраняются все хранимые процедуры.
ОператорSELECT FROM information_schema
Каждая база данных имеет системную базу данных, в которой хранится самая разнообразная информация, начиная с привилегий и заканчивая хранимыми процедурами. Недостатком системной базы данных является ее непереносимость – каждая СУБД организует системную базу данных по-своему. Устранить недостаток несовместимости призван стандартный набор представлений системной таблицы, который называют информационной схемой.
Удаление хранимых процедур осуществляется оператором DROP PROCEDURE, который имеет синтаксис:
DROP PROCEDURE, который имеет следующий синтаксис:
DROP PROCEDURE [IF EXISTS] имя_процедуры
Удаление хранимой функции осуществляется оператором
DROP FUNCTION
Редактирование хранимой процедуры осуществляется оператором
ALTER PROCEDURE имя_процедуры [характеристика], а хранимой функции – оператором ALTER FUNCTION имя_функции [характеристика].
Характеристика может принимать следующие значения:
· SQL SECURITY {DEFINER ç INVOKER} – данное предложение определяет режим выполнения: хранимая процедура выполняется либо с правами создавшего ее пользователя (DEFINER), либо с правами вызвавшего ее пользователя (INVOKER);
· COMMENT ‘строка’ – данное предложение позволяет назначить комментарий для хранимой процедуры.