Встроенный процедурный язык Хранимые подпрограммы MySQL Переменые, (преп. - Беляев А.С.)
Primary tabs
Forums:
Переменные
Область видимости=
- Connection
- Program block
Имя содержит буквы, цифры, «.», «_», «$»
Регистронезависимые
SET @var_name [:]= expr [, @var_name = expr] ... SET @t1=1, @t2=2, @t3:=4; SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; SELECT id_parent INTO @t1 FROM table WHERE id = @t2;
Оператор IF
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
Пример:
IF n > m THEN SET s = '>'; ELSEIF n = m THEN SET s = '='; ELSE SET s = '<'; END IF;
Операторы цикла
Общий вид:
[label:]LOOP statement_list [LEAVE label] END LOOP [label]
Конкретные виды:
REPEAT statement_list UNTIL search_condition END REPEAT ===================== WHILE search_condition DO statement_list END WHILE
Курсоры
Пример:
CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO a, b; IF NOT done THEN IF a < b THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,1); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; END
Динамический SQL
пример=
SET @c = "c1"; SET @s = CONCAT("SELECT ", @c, " FROM t"); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
ещё=
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; SET @a = 3; SET @b = 4; EXECUTE stmt1 USING @a, @b; DEALLOCATE PREPARE stmt1;
Триггеры
http://webcrunch.ru/library/development/...
Триггер- Именованный объект, ассоциированный с таблицей, который активируется при наступлении определенного события.
Для создания триггера необходима привилегия TRIGGER
Может быть запущен с различными привилегиями (DEFINER)
Пример триггера MYSQL=
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
Возможные значения параметров:
Trigger time
- BEFORE
- AFTER
Trigger event
- INSERT (insert, load data, replace)
- UPDATE
- DELETE
- REPLACE
Ещё пример триггера:
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 ;
--
INSERT INTO foo VALUES (bar, baz)ON DUPLICATE KEY UPDATE field = value;
- BEFORE INSERT - ?
- BEFORE UPDATE - ?
- AFTER INSERT - ?
- AFTER UPDATE - ?
ещё два пример:
(какой-то один из них - видимо второй может быть неправильным)
1
CREATE TRIGGER sdata_insert BEFORE INSERT ON `sometable` FOR EACH ROW BEGIN SET NEW.guid = UUID(); END;
2
CREATE TRIGGER sdata_insert AFTER INSERT ON `sometable` FOR EACH ROW BEGIN SET NEW.guid = UUID(); END;
Хранимые процедуры и функции
Процедура
- Выполняется как независимый набор операторов
- Вызывается при помощи CALL
- Не возвращает значения
- Модифицирует параметры
Функция
- Выполняется как часть выражения (SELECT)
- Возвращает значение
Привилегии
- CREATE ROUTINE
- ALTER ROUTINE
- EXECUTE
Хранимые процедуры
Вид:
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
Параметры
- IN – только для чтения (по умолчанию)
- OUT – только для записи
- INOUT – для чтения и записи
Характеристика
Deterministic
- NOW(), RAND()
CONTAINS SQL
- SET @x = 1
NO SQL
READS SQL DATA
- SELECT
MODIFIES SQL DATA
- INSERT
Примеры:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count() BEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; END;
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count() SQL SECURITY INVOKER BEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; END;
Ещё пример - Хранимые процедуры:
delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM mytable; END// delimiter ; CALL simpleproc(@a); SELECT @a;
Хранимые функции
Общий вид:
CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sf_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body func_parameter: param_name type
Пример хранимой функции mysql:
delimiter // CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC BEGIN RETURN CONCAT('Hello, ',s,'!'); END// SELECT hello(‘Вася’);
Ограничения
Запрещено=
- LOCK TABLES
- LOAD DATA
- В триггерах и ф-циях- PREPARE, EXECUTE, etc
- INSERT DELAYED
Запрещено в функциях
- COMMIT/ROLLBACK
- FLUSH
- Рекурсия
- Мутация таблиц
- Log in to post comments
- 4482 reads