11. 存储过程

存储过程介绍

• 存储在数据库端的一组SQL语句集；
• 用户可以通过存储过程名和传参多次调用的程序模块；
• 存储过程的特点：
  • 使用灵活，可以使用流控制语句、自定义变量等完成复杂的业务逻辑；
  • 提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计；
  • 减少网络传输；
  • 提高代码维护的复杂度，实际使用需要结合业务评估；

 

创建存储过程

CREATE
 [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
 [characteristic ...] routine_body
 
proc_parameter: -- 注意，只有procedure才有in(传入),out(传出),inout(传入传出)参数，自定义函数（只有）默认就是 in。
 [ IN | OUT | INOUT ] param_name type
 
characteristic:
 COMMENT 'string'
 | LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
 Valid SQL routine statement

 

删除存储过程

DROP PROCEDURE procedure_name;

 

存储过程举例与流程控制语句

• IF 语法

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list] ...
 [ELSE statement_list]
END IF

• 例子

root@mysqldb 15:02:  [gavin]> delimiter //
root@mysqldb 15:03:  [gavin]> create procedure pcd_test_1 (in param_a int)  -- 创建一个存储过程
    -> begin
    -> declare a int; -- delcare声明了该变量的作用域在该procedure中
    -> if param_a > 10 then set a:=11; 
    -> elseif param_a = 10 then set a:=10;
    -> else set a:=9;
    -> end if;
    -> select a;
    -> end//
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 15:03:  [gavin]> delimiter ;

root@mysqldb 15:03:  [gavin]> select @a;  -- 查看当前会话中变量a的值
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

root@mysqldb 15:19:  [gavin]> call pcd_test_1(1); -- 调用存储过程得到a的值
+------+
| a    |
+------+
|    9 |
+------+
1 row in set (0.01 sec)

root@mysqldb 15:19:  [gavin]> select @a;  -- 再次查看a还是为NULL，因为使用了declare，使得a值的作用域在pcd_test_1这个存储过程中
+------+
| @a   |
+------+
| NULL |
+------+

 

• CASE WHEN 语法

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 或者是
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

•  例子

mysql> delimiter //
mysql> 
mysql> create procedure pcd_test_2(in param_1 int)
     -> begin
     -> case param_1 
        -- 当case后面有value时，该value会和when中的when_value进行"="判断
        -- 相等则执行then后面的语句，然后跳出；否则就进行下一次when的匹配
     -> when 2 then select 200;
     -> when 3 then select 300;
     -> else
     -> begin
        -- 当没有匹配时，且else中没有要执行的语句
        -- 则给一个begin/end的空语句；
        -- 或者不写else语句；
     -> end;
     -> end case;
     -> end;//
Query OK, 0 rows affected (0.03 sec)

root@mysqldb 08:54:  [test]> call pcd_test_2(2);
+-----+
| 200 |
+-----+
| 200 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@mysqldb 08:54:  [test]> call pcd_test_2(3);
+-----+
| 300 |
+-----+
| 300 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

•  WHILE 语法（循环）

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

• 例子

mysql> delimiter //
mysql> 
mysql> create procedure pcd_test_3(in param_1 int)
     -> begin
     -> declare a int default 1;
     -> while param_1 > 10 do
     -> set param_1 = param_1 - 1;
     -> set a = a + 1;
     -> end while;
     -> select a;
     -> end;//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;

mysql> call pcd_test_3(15); -- 15 - 10 = 5；需要5次循环
+------+
| a |
+------+
| 6 | -- a + 5 = 6
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

 

• REPEAT语法（循环）

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

• 例子

mysql> delimiter //
mysql> create procedure pcd_test_4(in param_1 int)
     -> begin
     -> SET @x = 0; -- 没有使用declare，所以x是会话级别的
     -> REPEAT
     -> SET @x = @x + 1;
     -> UNTIL @x > param_1 END REPEAT;
     -> end;//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call pcd_test_4(10);
Query OK, 0 rows affected (0.00 sec)
mysql> select @x; -- x是会话级别的
+------+
| @x |
+------+
| 11 | -- 一共循环11次(10>10 为False，11 > 10为True，才跳出)
+------+
1 row in set (0.00 sec)

 

• LOOP语法（循环）

[begin_label:] LOOP
    statement_list
END LOOP [end_label]
-- ITERATE 和label相结合，表示继续从label处执行
-- LEAVE 和label相结合，表示从label 标记的代码段离开

• 例子

mysql> delimiter //
mysql> create procedure pcd_test_5(in param_1 int)
     -> begin
     -> test_label: loop
     -> set param_1 := param_1 + 1; -- 参数累加
     -> if param_1 < 10 then -- 如果累加的值小于10
     -> iterate test_label; -- 继续执行 标签 test_label
     -> end if;
     -> leave test_label; -- 如果>=10则离开这个test_label(loop)
     -> end loop test_label; 
     -> set @x = param_1; -- 设置会话级别的变量
     -> end;//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

mysql> call pcd_test_5(5); -- 5<10 ，累加5次后>=10为true，离开循环
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x |
+------+
| 10 | -- 累加到10的 param_1 赋值给 x, 即为10
+------+
1 row in set (0.00 sec)

 

• 阶乘的例子

mysql> create table test_proc_1(a int, b int); -- 给一个存放数据的表
Query OK, 0 rows affected (0.15 sec)

mysql> delimiter //
mysql> create procedure proc_test1(in total int, out res int)
     -> begin
     -> declare i int;
     -> set i := 1;
     -> set res := 1;
     -> if total <= 0 then
     -> set total := 1;
     -> end if;
     -> while i <= total do
     -> set res := res * i;
     -> insert into test_proc_1 values(i, res);
     -> set i := i + 1;
     -> end while;
     -> end;//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> set @res_value := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_test1(5, @res_value); -- 因为res是out变量，要预先有这个变量，这里上面设置了res_value(实参和形参不必同名)
Query OK, 1 row affected (0.15 sec)

mysql> select @res_value;
+------------+
| @res_value |
+------------+
| 120 | -- 5的阶乘的结果是120
+------------+
1 row in set (0.00 sec)

mysql> select * from test_proc_1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 24 |
| 5 | 120 | -- 每次insert的结果
+------+------+
5 rows in set (0.00 sec)

 

自定义函数

• 自定义函数和存储过程很类似，但是必须要有返回值
• 与内置的函数sum(), max()使用方法类似
  • select fun(val);
  • select * from t where col=fun(val);
• 自定义函数可在遍历每条记录中使用

 

创建自定义函数

CREATE
 [DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type -- 必须有返回值
 [characteristic ...] routine_body
func_parameter:
 param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
 | LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
routine_body:
 Valid SQL routine statement

 

删除自定义函数

DROP FUNCTION fun_name;

 

• 例子：还是阶乘，用自定义函数的方式

mysql> delimiter //
mysql> 
mysql> create function fun_test_1(total int)
     -> returns int
     -> begin
     -> declare i int;
     -> declare res int;
     -> set i := 1;
     -> set res := 1;
     -> if total <= 0 then
     -> set total := 1;
     -> end if;
     -> while i <= total do
     -> set res := res * i;
     -> set i := i + 1;
     -> end while;
     -> return res;
     -> end;//
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 09:49:  [test]> delimiter ;
root@mysqldb 09:49:  [test]> select fun_test_1(6);
+---------------+
| fun_test_1(6) |
+---------------+
|           120 |
+---------------+
1 row in set (0.01 sec)