存储过程
权限检查
- 用户需要有存储过程和函数的操作权限
- 创建 存储过程或函数, 需要 create routine 权限
- 修改 需要alter routine 权限
- 执行 需要 execute routine 权限
1 | CREATE PROCEDURE sp_name ([proc_parameter[,...]]) |
- 存储过程, 允许DDL, 提交(COMMIT/ROLLBACK)
- 不允许 LOAD DATA INFILE
- 只支持create, 不支持create OR REPLACE, 否则执行alter
- LANGUAGE SQL:说明下面过程的 BODY 是使用 SQL 语言编写,这条是系统默认的,
为今后 MySQL 会支持的除 SQL 外的其他语言支持的存储过程而准备。
- [NOT] DETERMINISTIC:DETERMINISTIC 确定的,即每次输入一样输出也一样的程序,
NOT DETERMINISTIC 非确定的,默认是非确定的。当前,这个特征值还没有被优化
程序使用。
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:这些特征值提供
子程序使用数据的内在信息,这些特征值目前只是提供给服务器,并没有根据这些
特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写
数据的语句。NO SQL 表示子程序不包含 SQL 语句。READS SQL DATA 表示子程序包
含读数据的语句,但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写
数据的语句。如果这些特征没有明确给定,默认使用的值是 CONTAINS SQL。
- SQL SECURITY { DEFINER | INVOKER }:可以用来指定子程序该用创建子程序者的许
可来执行,还是使用调用者的许可来执行。默认值是 DEFINER。
- 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
35mysql> 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)
mysql> select * from routines where ROUTINE_NAME = 'film_in_stock' br />
## 变量 1
2
3
4
5
6DECLARE 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 | CREATE FUNCTION get_customer_balance(p_customer_id INT, |
## 条件 条件定义 1
2
3
4
5DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code1
2
3
4
5
6
7
8
9
10
11
12
13DECLARE 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_code1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23mysql> 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
4DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_n
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name1
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
29mysql> 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
4IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
1 | CASE case_value |
1 | [begin_label:] LOOP |
1 | mysql> CREATE PROCEDURE actor_insert () |
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
27mysql> 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 | -> REPEAT |
1 | mysql> delimiter $$ |