Встроенный процедурный язык Хранимые подпрограммы MySQL Переменые, (преп. - Беляев А.С.)

Переменные
Область видимости=

• 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/mysql/mysql-triggers-info/
Триггер- Именованный объект, ассоциированный с таблицей, который активируется при наступлении определенного события.
Для создания триггера необходима привилегия 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(‘Вася’);

Ограничения

Запрещено=

1. LOCK TABLES
2. LOAD DATA
3. В триггерах и ф-циях- PREPARE, EXECUTE, etc
4. INSERT DELAYED

Запрещено в функциях

1. COMMIT/ROLLBACK
2. FLUSH
3. Рекурсия
4. Мутация таблиц