CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;
Создание процедур и функций
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Внесение изменений
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'Удаление процедур и функций
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
Вызов процедур и функций
CALL sp_name([parameter[,...]])CALL sp_name[()]Оператор CALL позволяет вызвать ранее определенную процедуру.
CREATE PROCEDURE p1 (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END;
Перед вызовом процедуры инициализируйте переменную указанные в параметрах INOUT . После вызова процедуры значения будут установлены или изменены.
mysql> SET @increment = 10;mysql> CALL p(@version, @increment);mysql> SELECT @version, @increment;+-----------------+------------+| @version | @increment |+-----------------+------------+| 5.1.12-beta-log | 11 |Пример2
CREATE PROCEDURE `p2`(IN param1 CHAR(2) )
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from s where snum=param1;
END;
Вызов процедурыcall p2 ('S1')Пример3
CREATE PROCEDURE `My_proc2`(IN param1 CHAR(2) )
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF param1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
select variable1; /* statement */
END
Вызов процедурыcall p3 (10)Три́ггер (англ. trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) — по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификаци UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции. Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.)
Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Пример создания и работы триггера:
CREATE TABLE t22 (s1 INTEGER)
CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = ‘Trigger was activated!’;
SET NEW.s1 = 55;
END;
После этого при выполнении запросов получим:
mysql> INSERT INTO t22 VALUES (1)
mysql> SELECT @x, t22.* FROM t22 // вызывается триггер
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)
Иметь доступ к значениям метаданных – совершенно необходимое требование к современной СУБД. Ранее такая возможность в MySQL достигалась различными SHOW-командами, но такой подход имеет очевидные недостатки. Эти команды нельзя использовать в простых запросах с соединениями, и, что существенно, они не соответствовали стандартам, будучи специфичными для MySQL.
В новой версии СУБД появилась новая служебная база данных – INFORMATION_SCHEMA. Её наличие продиктовано тем же стандартом SQL:2003, и именно она решает задачу реализации словаря данных (data dictionary). INFORMATION_SCHEMA содержит таблицы, описывающие состояние и параметры сервера, в том числе определения и сущности таблиц. Это виртуальная база данных – физически (в виде файлов на диске) она не существует, вся информация динамически предоставляется сервером. Пример использования этой таблицы:
mysql> SELECT table_name, table_type, engine
-> FROM INFORMATION_SCHEMA.tables
-> WHERE table_schema = ‘tp’
-> ORDER BY table_type ASC, table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| t2 | BASE TABLE | MyISAM |
| t1 | BASE TABLE | InnoDB |
| v1 | VIEW | NULL |
+------------+------------+--------+
Другой пример работы со словарём данных – просмотр привелегий:
mysql> SELECT * FROM
-> INFORMATION_SCHEMA.COLUMN_PRIVILEGES\G
************************ 1. row ************************
GRANTEE: 'peter'@'%'
TABLE_CATALOG: NULL
TABLE_SCHEMA: tp
TABLE_NAME: t1
COLUMN_NAME: col1
PRIVILEGE_TYPE: UPDATE
IS_GRANTABLE: NO
************************ 2. row ************************
GRANTEE: 'trudy'@'%'
TABLE_CATALOG: NULL
TABLE_SCHEMA: tp
TABLE_NAME: t2
COLUMN_NAME: col1
PRIVILEGE_TYPE: SELECT
IS_GRANTABLE: YES
Объявление переменных
Объявление. DECLARE Local Variables
Следующая команда позволяет объявлять локальные переменные, содержит возможность задания значения по умолчанию. Переменная может быть объявлена как выражения, не обязательна константа. Если значение по умолчанию не определено то равно NULL.DECLARE var_name[,...] type [DEFAULT value]Присваивание Variable SET Statement
SET var_name = expr [, var_name = expr] ...SELECT ... INTO Statement
Оператор SELECT может перенаправить результат в переменные. Таким образом может быть преобразована только одна строка.
ПРИМЕР SELECT col_name[,...] INTO var_name[,...] table_exprSELECT id,data INTO x,y FROM test.t1 LIMIT 1;Условия и ограничения
Объявление условий
DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_codeОбъявление ограничений
DECLARE handler_type HANDLER FOR condition_value[,...] statement handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_codeПример
mysql> CREATE TABLE test.t (s1 int,primary key (s1));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 @x = 1; -> INSERT INTO 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> CALL handlerdemo()//Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x// +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec)Если вы хотите игнорировать условие вы должны сгенерировать ограничение и ассоциировать его с пустым блоком .
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;Пример
CREATE PROCEDURE p ()BEGIN DECLARE i INT DEFAULT 3; retry: REPEAT BEGIN DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN ITERATE retry; # illegal END; END; IF i < 0 THEN LEAVE retry; # legal END IF; SET i = i - 1; UNTIL FALSE END REPEAT;END;Курсор — в некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.