Longda's Interesting World

Longda's Interesting World

深入浅出MySQL -- 存储过程

深入浅出MySQL -- 存储过程

存储过程

权限检查

  1. 用户需要有存储过程和函数的操作权限
  2. 创建 存储过程或函数, 需要 create routine 权限
  3. 修改 需要alter routine 权限
  4. 执行 需要 execute routine 权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
	[characteristic ...] routine_body

CREATE 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:
	Valid SQL procedure statement or statements

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
  { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'


CALL spname([parameter[,...]
  1. 存储过程, 允许DDL, 提交(COMMIT/ROLLBACK)
  2. 不允许 LOAD DATA INFILE
  3. 只支持create, 不支持create OR REPLACE, 否则执行alter
  4. LANGUAGE SQL：说明下面过程的 BODY 是使用 SQL 语言编写，这条是系统默认的，

为今后 MySQL 会支持的除 SQL 外的其他语言支持的存储过程而准备。

  1. [NOT] DETERMINISTIC：DETERMINISTIC 确定的，即每次输入一样输出也一样的程序，

NOT DETERMINISTIC 非确定的，默认是非确定的。当前，这个特征值还没有被优化
程序使用。

  1. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：这些特征值提供

子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些
特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写
数据的语句。NO SQL 表示子程序不包含 SQL 语句。READS SQL DATA 表示子程序包
含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写
数据的语句。如果这些特征没有明确给定，默认使用的值是 CONTAINS SQL。

  1. SQL SECURITY { DEFINER | INVOKER }：可以用来指定子程序该用创建子程序者的许

可来执行，还是使用调用者的许可来执行。默认值是 DEFINER。

  1. COMMENT ‘string’：存储过程或者函数的注释信息。


EXAMPLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> DELIMITER $$
mysql> 
mysql> CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count 
INT)
 -> READS SQL DATA
 -> BEGIN
 -> SELECT inventory_id
 -> FROM inventory
 -> WHERE film_id = p_film_id
 -> AND store_id = p_store_id
 -> AND inventory_in_stock(inventory_id);
 -> 
 -> SELECT FOUND_ROWS() INTO p_film_count;
 -> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> DELIMITER ;


mysql> CALL film_in_stock(2,2,@a);
  +--------------+
  | inventory_id |
  +--------------+
  | 10 |
  | 11 |
  +--------------+
  2 rows in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
  +------+
  | @a |
  +------+
  | 2 |
  +------+
  1 row in set (0.00 sec)

SHOW CREATE {PROCEDURE | FUNCTION} sp_name
mysql> select * from routines where ROUTINE_NAME = ‘film_in_stock’ \G

变量

1
2
3
4
5
6
DECLARE var_name[,...] type [DEFAULT value]
DECLARE last_month_start DATE;
SET var_name = expr [, var_name = expr] ...
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SELECT col_name[,...] INTO var_name[,...] table_expr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE FUNCTION get_customer_balance(p_customer_id INT, 
p_effective_date DATETIME) 
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
 …
 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
 …
 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
 FROM payment
 WHERE payment.payment_date <= p_effective_date
 AND payment.customer_id = p_customer_id;
 …
 RETURN v_rentfees + v_overfees - v_payments;
END $$

条件

条件定义

1
2
3
4
5
DECLARE condition_name CONDITION FOR condition_value

condition_value:
  SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

条件处理

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
  CONTINUE
  | EXIT
  | UNDO
condition_value:
  SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> delimiter $$
mysql> 
mysql> CREATE PROCEDURE actor_insert ()
 -> BEGIN
 -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
 -> SET @x = 1;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');
 -> SET @x = 2;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');
 -> SET @x = 3;
 -> END;
 -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call actor_insert();
Query OK, 0 rows affected (0.06 sec)
mysql> select @x,@x2;
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
--捕获 mysql-error-code：
DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
--事先定义 condition_name：
DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
--捕获 SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;

光标

1
2
3
4
DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_n
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> delimiter $$
mysql> 
mysql> CREATE PROCEDURE payment_stat ()
 -> BEGIN
 -> DECLARE i_staff_id int;
 -> DECLARE d_amount decimal(5,2);
 -> DECLARE cur_payment cursor for select staff_id,amount from payment;
 -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
 -> 
 -> set @x1 = 0;
 -> set @x2 = 0;
 -> 
 -> OPEN cur_payment;
 -> 
 -> REPEAT
 -> FETCH cur_payment INTO i_staff_id, d_amount;
 -> if i_staff_id = 2 then
 -> set @x1 = @x1 + d_amount;
 -> else 
 -> set @x2 = @x2 + d_amount;
 -> end if;
 -> UNTIL 0 END REPEAT;
 -> 
 -> CLOSE cur_payment;
 -> 
 -> END;
 -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

流程控制

1
2
3
4
IF search_condition THEN statement_list
  [ELSEIF search_condition THEN statement_list] ...
  [ELSE statement_list]
END IF
1
2
3
4
5
6
7
8
9
10
11
CASE case_value
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list] ...
  [ELSE statement_list]
END CASE
Or: 
CASE
  WHEN search_condition THEN statement_list
  [WHEN search_condition THEN statement_list] ...
  [ELSE statement_list]
END CASE
1
2
3
[begin_label:] LOOP
	statement_list
END LOOP [end_label]
1
2
3
4
5
6
7
8
9
10
11
mysql> CREATE PROCEDURE actor_insert ()
 -> BEGIN
 -> set @x = 0;
 -> ins: LOOP
 -> set @x = @x + 1;
 -> IF @x = 100 then
 -> leave ins;
 -> END IF;
 -> INSERT INTO actor(first_name,last_name) VALUES ('Test','201');
 -> END LOOP ins;
 -> END;


iterate 语句 —> 类似continue 语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> CREATE PROCEDURE actor_insert ()
 -> BEGIN
 -> set @x = 0;
 -> ins: LOOP
 -> set @x = @x + 1;
 -> IF @x = 10 then
 -> leave ins; 
 -> ELSEIF mod(@x,2) = 0 then
 -> ITERATE ins;
 -> END IF;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (@x+200,'Test',@x);
 -> END LOOP ins;
 -> END;
 -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> call actor_insert();
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where first_name='Test';
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 201 | Test | 1 |
| 203 | Test | 3 |
| 205 | Test | 5 |
| 207 | Test | 7 |
| 209 | Test | 9 |
+----------+------------+-----------+5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
-> REPEAT
 -> FETCH cur_payment INTO i_staff_id, d_amount;
 -> if i_staff_id = 2 then
 -> set @x1 = @x1 + d_amount;
 -> else 
 -> set @x2 = @x2 + d_amount;
 -> end if;
 -> UNTIL 0 END REPEAT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> delimiter $$
mysql> CREATE PROCEDURE loop_demo ()
 -> BEGIN
 -> set @x = 1 , @x1 = 1;
 -> REPEAT
 -> set @x = @x + 1;
 -> until @x > 0 end repeat;
  -> 
  -> while @x1 < 0 do
  -> set @x1 = @x1 + 1;
  -> end while;
  -> END;
  -> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call loop_demo();
Query OK, 0 rows affected (0.00 sec)
mysql> select @x,@x1;
+------+------+| @x | @x1 |
+------+------+| 2 | 1 |
+------+------+1 row in set (0.00 sec)