ТРИГГЕРЫ. ХРАНИМЫЕ ПРОЦЕДУРЫ И ФУНКЦИИ

Цель работы: формирование умений и навыков создания и модификации триггеров, хранимы процедур и функций.

Краткие теоретические сведения

Создание и модификация триггеров

Поддержка триггеров включена, начиная с MySQL 5.0.2. Триггер представляет собой именованный объект базы данных, который связан с таблицей, и будет активизирован, когда произойдет определенное событие для таблицы.

Синтаксис команды создания триггера следующий:

CREATE

[DEFINER = {< пользователь> | CURRENT_USER}]

TRIGGER < имя триггера> < время> < событие>

ON < имя таблицы> FOR EACH ROW < тело триггера>

Триггер становится связанным с < имя таблицы> .

Предложение DEFINER определяет имя пользователя MySQL, которое будет использовать при проверке привилегий доступа в вызове триггера. Значение по умолчанию: пользователь, который выполняет инструкцию CREATE TRIGGER, что равнозначно DEFINER = CURRENT_USER. < пользователь> указывается в формате 'user_name'@'host_name' (как в команде GRANT).

< время> задает время действия. Это может быть BEFORE или AFTER, чтобы указать, что триггер активизируется до или после модификации каждой строки. < событие> указывает вид инструкции, которая активизирует триггер. < событие> может быть одним из следующего:

INSERT: триггер активизируется, когда новая строка вставляется в таблицу. Например, через команды INSERT, LOAD DATA или REPLACE.

UPDATE: триггер активизируется, когда строка изменяется. Например, через инструкцию UPDATE.

DELETE: триггер активизируется, когда строка удаляется из таблицы. Например, через инструкции DELETE и REPLACE. Однако, инструкции DROP TABLE и TRUNCATE относительно таблицы НЕ активизируют триггер, потому что не используют DELETE.

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, не может быть двух триггеров BEFORE UPDATE для таблицы. Но может быть BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.

< тело триггера> задает инструкцию, которая будет выполнена, когда триггер активизируется. Если необходимо выполнить много инструкций, используется операторная конструкция BEGIN... END.

Обращаться к столбцам в подчиненной таблице можно используя псевдонимы OLD и NEW. OLD.< имя столбца> обращается к столбцу существующей строки прежде, чем она модифицируется или удалится. NEW.< имя столбца> обращается к столбцу новой строки, которая будет вставлена, или же к существующей строке после того, как она модифицируется.

Пример создания триггера:

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

b4 INT DEFAULT 0);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END;

DELIMITER;

Синтаксис удаления триггера:

DROP TRIGGER [< база данных> .]< имя триггера>

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

Замечание: в настоящее время триггеры не активизируются каскадными действиями внешнего ключа.

Создание и модификация хранимых процедур и функций

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. Хранимые функции являются разновидностью хранимых процедур, основное отличие состоит в том, что хранимые функции могут возвращать скалярное значение и вызываются из оператора точно так же, как и любые другие функции (то есть, через указание имени функции). Процедура вызывается с помощью оператора CALL и может только передавать значения обратно, используя выходные переменные.

Синтаксис создания хранимой процедуры и функции соответственно:

CREATE

[DEFINER = {< пользователь> | CURRENT_USER}]

ROCEDURE < имя хранимой процедуры> ([[IN | OUT | INOUT] < имя параметра> < тип> [,...]])

[< характеристика>...]

< тело процедуры>

CREATE FUNCTION

[DEFINER = {< пользователь> | CURRENT_USER}]

< имя хранимой процедуры> ([< имя параметра> < тип> [,...]]) [RETURNS < тип> ]

[< характеристика> ...]

< тело процедуры>

< тип> - любой допустимый тип данных MySQL.

< характеристика> :
LANGUAGE SQL

| [NOT] DETERMINISTIC

| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

| COMMENT 'строка'

Характеристики DETERMINISTIC и NOT DETERMINISTIC указывают, производит ли функция всегда тот же самый результат для входных данных. Характеристики CONTAINS SQL, NO SQL, READS SQL DATA и MODIFIES SQL DATA обеспечивают информацию относительно того, читает ли функция или записывает данные. NO SQL или READS SQL DATA указывают, что функция не изменяет данные. Характеристика SQL SECURITY может применяться для определения, должна ли процедура выполняться с использованием привилегий пользователя, создающего эту процедуру, или привилегий пользователя, ее вызывающего. Конструкция COMMENT является расширением MySQL и может использоваться для описания хранимой процедуры. Такая информация отображается операторами SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION.

По умолчанию характеристика имеет следующие значение:

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

< тело процедуры> состоит из допустимой инструкций хранимого кода SQL. Это может быть простая инструкция типа SELECT или INSERT, либо это может быть составная инструкция, использующая BEGIN и END. Составные инструкции могут содержать объявления, циклы и другие инструкции управляющей структуры.

Вызов процедуры производится командой CALL:

CALL < имя хранимой процедуры> ( [< параметр> [,...]])

Инструкции ALTER PROCEDURE и ALTER FUNCTION используются, чтобы изменить характеристики сохраненной процедуры или функции.

ALTER {PROCEDURE | FUNCTION} < имя хранимой процедуры> [< характеристика>...]

Изменить аргументы или тело функции и процедуры нельзя. По этому используйте конструкцию:

DROP {PROCEDURE | FUNCTION} [IF EXISTS] < имя хранимой процедуры>

Эта инструкция используется, чтобы удалить сохраненную процедуру или функцию. То есть определенная подпрограмма будет удалена с сервера.

Предложение IF EXISTS является расширением MySQL. Это предотвращает ошибку, если процедура или функция не существует.

Синтаксис хранимого кода

Для использования множественных инструкций необходимо, чтобы была возможность посылать строки запросов, содержащие разделитель операторов;. Добиться этого можно путем применения команды DELIMITER в командной строке клиента mysql. Замена завершающего запрос разделителя; (например, на разделитель $$) позволяет использовать; в теле процедуры.

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

[< метка начала>: ] BEGIN

[< блок инструкций> ]

END [< метка конца> ]

Значения < метка начала> и < метка конца>, если оба заданы, должны быть одинаковыми. Метка позволяет не путать операторы, если они вложены.

Конструкция DECLARE используется для объявления внутренних переменных, создания курсоров и конструкций исключений.

DECLARE < имя переменной> [,...] type [DEFAULT < значение> ]

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

Пример:

DECLARE a INT DEFAULT 5;

Курсоры – это указатели на ресурс выборки. Курсоры существуют только в хранимой процедуре или функции. Курсоры довольно медленные, поэтому использовать их стоит по необходимости. Создаются курсоры при помощи оператора DECLARE:

DECLARE < имя курсора> CURSOR FOR < select выражение>

Эта инструкция объявляет курсор. Каждый курсор должен иметь уникальное имя. < select выражение> – SELECT, не может иметь оператор INTO.

Инструкция OPEN открывает предварительно объявленный курсор:

OPEN < имя курсора>

Инструкция FETCH выбирает следующую строку (если строка существует), используя определенный открытый курсор, и продвигает указатель курсора:

FETCH < имя курсора> INTO < имя переменной> [, < имя переменной> ]...

Если больше нет доступных строк, происходит условие No Data. Чтобы обнаружить это условие, можно установить обработчик для этого:

CLOSE < имя курсора>

CLOSE Закрывает предварительно открытый курсор. Если курсор не закрыт, он все равно закроется в конце составной инструкции (обычно по наступлению END), в которой он был объявлен.

Следует обратить внимание: сначала объявляются переменные, потом курсоры:

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0;

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;

OPEN cur1;

OPEN cur2;

< блок инструкций>

CLOSE cur1;

CLOSE cur2;

END

Констукция SELECT … INTO так же позволяет «заполнить» переменную, прямо из таблицы:

SELECT < имя столбца> [,...]

INTO < имя переменной> [,...] < sql выражение>

Этот синтаксис SELECT сохраняет выбранные столбцы непосредственно в переменные. Следовательно, количество выбранных столбцов слева от INTO должно быть равно количеству переменных справа от INTO.

Пример:

SELECT id, data INTO x, y FROM test.t1 WHERE id_tm = 5;

Названия переменных не чувствительны к регистру. Важно: имена переменных SQL не должны совпадать с именами столбцов.

В конструкции управления потоком входят IF, CASE, LOOP, WHILE, REPLACE ITERATE и LEAVE. Многие из этих конструкций могут содержат другие инструкции. Такие конструкции могут быть вложены. Например, IF мог бы содержать цикл, который непосредственно содержит WHILE, который в свою очередь включает в себя оператор CASE.

Инструкция IF:

IF < условие> THEN < блок операторов>

[ELSEIF < условие> THEN < блок операторов> ]...

[ELSE < блок операторов> ]

END IF

Инструкция CASE:

CASE < выражение> WHEN < значение>

THEN < блок операторов>

[WHEN < значение> THEN < блок операторов> ]...

[ELSE < блок операторов> ]

END CASE

или

CASE WHEN < условие> THEN < блок операторов>

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

[ELSE < блок операторов> ]

END CASE

В первом варианте обрабатывается < блок операторов> , если < выражение> =< значение> . Во втором – результат для первого указанного условия < условие> , если оно истинно. Если соответствующая величина результата не определена, то обрабатывается < блок операторов> , указанное после оператора ELSE. Если часть ELSE в выражении отсутствует, возвращается NULL.

Пример:

SELECT CASE 1 WHEN 1 THEN " one" WHEN 2 THEN " two" ELSE " more" END;

SELECT CASE WHEN 1> 0 THEN " true" ELSE " false" END;

SELECT CASE BINARY " B" WHEN " a" THEN 1 WHEN " b" THEN 2 END;

Инструкция WHILE:

[< метка начала>: ]

WHILE < условие> DO < блок операторов>

END WHILE

[< метка конца> ]

Операторный список < блок операторов> внутри инструкции WHILE будет повторятся до тех пор пока < условие> равно true.

Инструкция LEAVE:

LEAVE label

Эта инструкция используется, чтобы из выйти любой указаной конструкции управления потоком данных. Может использоваться внутри BEGIN … END или же конструкций цикла (LOOP, REPEAT, WHILE). Можно сравнить c инструкцией break из С++.

Инструкция REPEAT:

[< метка начала> : ]

REPEAT < блок операторов>

UNTIL < условие>

END REPEAT

[< метка конца> ]

Пример с использование различных конструкций:

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0; # переменная, определяющая условие выполнения нашего цикла

DECLARE a CHAR(16);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT id, data FROM test.t1; # создаём курсоры

DECLARE cur2 CURSOR FOR SELECT id FROM test.t2;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; # статус, когда курсор истощатся

OPEN cur1; # открываем курсор

OPEN cur2;

REPEAT

FETCH cur1 INTO a, b; # забираем циклом данные из курсоров, пока не кончатся данные

FETCH cur2 INTO c;

IF NOT done THEN # пока done 0

IF b < c THEN INSERT INTO test.t3 VALUES (a, b); # записываем в таблицу наименьшее из b и c

ELSE INSERT INTO test.t3 VALUES (a, c);

END IF;

UNTIL done END REPEAT;

CLOSE cur1;

CLOSE cur2;

END

Практическая часть

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

1) В зависимости от варианта создать несколько триггеров для различных таблиц (возможные ситуации включения триггеров предусмотреть по собственному усмотрению).

2) Научиться использовать команду удаления триггеров.

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

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

1) С какими объектами БД связываются триггеры?

2) Какие виды инструкций активизируют триггер?

3) Каким образом можно удалить триггер?

4) В чем отличие хранимой процедуры от функции

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

⇐ Предыдущая 12