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

К ним относятся операторы IF….THEN…ELSEIF, CASE, LOOP, WHILE, ITERATE и LEAVE. Каждая из этих конструкций может включать как единственный оператор, так и блок операторов, созданный с помощью составного оператора BEGIN ... END (см. выше).

Оператор IF

IF условие_поиска THEN оператор(ы)

[ELSEIF условие_поиска THEN оператор (ы)]

...

[ELSE оператор (ы)]

END IF;

IF реализует базовую конструкцию условия. Если значение условие_поиска является истинным, будет выполнен соответствующий SQL-оператор. Если совпадения с условие_поиска не найдены, выполняться будет оператор, указанный в конструкции ELSE.

Оператор CASE

CASE значение_сasе

WHEN значение_when THEN оператор

[WHEN значение_when THEN оператор ...]

[ELSE оператор]

END CASE;

или:

CASE

WHEN условие_поиска THEN оператор

[WHEN условие_поиска THEN оператор ...]

[ELSE оператор]

END CASE;

CASE реализует сложную конструкцию условия. Если значение условие_поиска является истинным, будет выполнен соответствующий SQL- оператор. Если совпадения с условие_поиска не найдены, выполняться будет оператор из конструкции ELSE.

Оператор 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;

После того, как данная процедура будет успешно создана, следующие команды, выполненные из консоли:

CALL doiterate (10);

SELECT @x;

вернут:

Оператор REPEAT

[метка_начала:] REPEAT

оператор (ы)

UNTIL условие_поиска

END REPEAT [метка_конца];

Команды, указанные внутри оператора REPEAT, повторяются до тех пор, пока будет истинным условие условие_поиска. Значения метка_начала и метка_конца, если заданы оба, должны быть одинаковыми.

Рассмотрим пример:

CREATE PROCEDURE dorepeat(pi INT)

BEGIN

SET @x = 0;

REPEAT SET @x = @x + 1; UNTIL @x > pi END REPEAT;

END

После того, как данная процедура будет успешно создана, следующие

команды, выполненные из консоли:

CALL dorepeat (3);

SELECT @x;

Вернут:

Оператор WHILE

[метка начала:] WHILE условие_поиска DO

оператор (ы)

END WHILE [метка_конца];

Команды, указанные внутри оператора WHILE повторяются до тех пор, пока будет истинным условие условие_поиска. Значения метка_начала и метка_конца, если заданы оба, должны быть одинаковыми.

В качестве примера,обобщающего различные конструкции процедурного языка MySQL, решим с помощью хранимой процедуры следующую задачу по обработке данных в некоторой БД:

Состав студенческой группы меняется во времени, необходимо создать хранимую процедуру, которая нумеровала бы студентов в алфавитном порядке и сохраняла данный номер в базе данных (для этого к таблице students будет добавлено поле order_num).

Таблица students с учетом добавления поля принимает следующий вид:

CREATE TABLE `students` (

`student_id` int(11) NOT NULL auto_increment,

`group_id` int(11) default NULL,

`FirstName` varchar(20) default NULL,

`LastName` varchar(20) default NULL,

`order_num` int(11) default NULL,

PRIMARY KEY (`student_id`),

KEY `students_group_id` (`group_id`),

KEY `group_id` (`group_id`,`FirstName`),

CONSTRAINT `students_fk` FOREIGN KEY (`group_id`) REFERENCES

`groups` (`group_id`) ON DELETE NO ACTION

);

Соответствующая хранимая процедура sort_studens задается следующим

образом:

CREATE PROCEDURE `sort_studens`( IN _group_id INTEGER ( 11 ))

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE ordercount INT DEFAULT 1;

DECLARE curstudent int;

DECLARE students_cur CURSOR FOR select student_id from students where

group_id = _group_id order by lastname, firstname;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN students_cur;

REPEAT

FETCH students_cur into curstudent;

update students set order_num = ordercount where student_id=curstudent;

SET ordercount = ordercount + 1;

UNTIL done END REPEAT;

CLOSE students_cur;

END;

Здесь параметр done используется как критерий останова (02000 – попытка прочесть запись из курсора все записи в котором прочтены). Обратите внимание, что имя входного параметра не должно совпадать с полем по которому идет сравнение, в случае если входной параметр используется в курсоре (в данном случае входной параметр _group_id не должен совпадать с group_id, так как в противном случае, в курсор будут помещены все записи из таблицы).

Триггеры

MySQL5 поддерживает следующий синтаксис команды CREATE для создания триггера:

CREATE

[DEFINER = {имя пользователя | CURRENT_USER }]

TRIGGER имя_триггера

время_срабатывания_триггера

событие_вызывающее_триггер

ON имя_таблицы

FOR EACH ROW

тело триггера

При этом параметры этого определения таковы:

время_срабатывания_триггера - задается параметрами BEFORE и AFTER,

(соответственно до и после события);

событие_вызывающее_триггер – может принимать значения INSERT,

UPDATE и DELETE и, соответственно данный триггер будет вызван перед

или после выполнения этих операций;

имя_таблицы – специфицирует таблицу, для которой задан этот триггер;

конструкция FOR EACH ROW означает, что условие срабатывания триггера

проверяется при обработке каждой строки соответствующей таблицы (по

умолчанию ….).

тело триггера

BEGIN

/* набор допустимых операторов SQL */

END;

Начиная с версии MySQL 5.1 триггеры могут обращаться к внешним таблицам непосредственно используя их имена, для более ранних версий MySQL триггеры могут взаимодействовать только с таблицей для которой они были вызваны.

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

CREATE TABLE `students` (

`student_id` int(11) NOT NULL auto_increment,

`group_id` int(11) default NULL,

`FirstName` varchar(20) default NULL,

`LastName` varchar(20) default NULL,

`order_num` int(11) default NULL,

`FIO` varchar(2) default NULL,

PRIMARY KEY (`student_id`),

KEY `students_group_id` (`group_id`),

KEY `group_id` (`group_id`,`FirstName`),

CONSTRAINT `students_fk` FOREIGN KEY (`group_id`) REFERENCES

`groups` (`group_id`) ON DELETE NO ACTION );__

Триггер students_before_ins_tr задается следующим образом:

CREATE TRIGGER `students_before_ins_tr`

BEFORE INSERT ON `students`

FOR EACH ROW

BEGIN

SET NEW.FIO = CONCAT(SUBSTRING(NEW.LastName,1,

1),SUBSTRING(NEW.FirstName,1, 1));

END;

Здесь NEW, как уже было сказано выше идентификатор записи которая вставляется (триггер создан на срабатывание перед вставкой), SUBSTRING – функция, вырезающая из строки подстроку (необходима для получения первого символа), CONCAT – функция конкатенации (сложения подстрок в одну строку).

В EMS Manager для работы с триггерами существует удобный интерфейс (см Рисунок 7.2). Данный интерфейс доступен из подменю Triggers выбранной таблицы. В полях Type и Event можно быстро менять правила по которым вызывается триггер (соответственно время и событие).

Рисунок 7.2 – Утилита EMS SQL Manager Lite for MySQL: интерфейс пользователя в режиме редактирования триггера.

Порядок выполнения работы

В соответствии с моделью данных, полученной в ходе выполнения предыдущих лабораторных работ и пользуясь приведенным выше описанием правил разработки ОБЛ необходимо:

(1) создать представление;

(2) создать хранимую процедуру;

(3) создать триггер.

Прим. При этом создаваемые ОБЛ должны существенно отличаться от приведенных в качестве примеров в данном документе и иметь смысл применительно к предметной области, используемой в лабораторной работе.

Контрольные вопросы

1) Знать определения ОБЛ: представление, хранимая процедура, триггер, и понимать логические схемы их функционирования.

2) Знать синтаксис команд создания / удаления представлений.

3) Знать синтаксис команд создания / изменения / удаления хранимых процедур (синтаксис изменения / удаления в данном документе не приведен и выносится на самостоятельную проработку с использованием рекомендованной литературы – см. ниже).

4) Знать синтаксис команд создания/удаления триггеров (синтаксис удаления в данном документе не приведен и выносится на самостоятельную проработку с использованием рекомендованнойлитературы – см. ниже).

Список литературы

1) Лекции по курсу «Организация Баз Данных и Знаний» / каф. АСУ, НТУ «ХПИ».

2) Дэйт К. Дж. Введение в системы баз данных – М-С.Пб-К: Вильямс,2001. – 1072 с.

3) Грабер Мартин. Введение в SQL – М.: Лори, 1996 – 382 с.

4) MySQL 5.1 Reference Manual // http://downloads.mysql.com/docs/refman5.1-en.chm

5) www.mysql.ru

6) Справка SQL Manager 2005 for MySQL

ЛАБОРАТОРНАЯ РАБОТА № 6