深入浅出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)