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

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

Оператор 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 ‘строка’ – данное предложение позволяет назначить комментарий для хранимой процедуры.

Пример использования оператора ALTER PROCEDURE:

mysql> CREATE PROCEDURE test ( )

−> SELECT VERSION ( );

mysql> SELECT name, type, security_type, comment

−> FROM mysql.proc

−> WHERE db = ‘test’;

Результат выполнения запроса:

Поиск по сайту: