MySQL之存储过程

共计 9036 个字符，预计需要花费 23 分钟才能阅读完成。

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，存储和和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用 IN、OUT、INOUT 类型，而函数的参数只能是 IN 类型。本次博客就来讲一下存储过程，MySQL 版本:

mysql> select VERSION(); --select 调用函数 
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

语法如下：

 创建：CREATE PROCEDURE sp_name([proc_parameter[,...]])
    [characteristic...] routine_body
    
proc_parameter:
[IN|OUT|INOUT] 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 {DEFINAER|INVOKER}|COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements

修改：ALTER PROCEDURE sp_name [characteristic...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'

调用:
CALL sp_name([parameter[,...]])

删除:
DROP PROCEDURE sp_name

查看:
show PROCEDURE STATUS [like 'pattern']
SHOW CREATE PROCEDURE sp_name

MySQL 的存储过程和函数中允许包含 DDL 语句，也允许在存储过程中执行提交或者回滚，但是存储过程和函数不允许执行 LOAD DATA INFILE 语句，存储过程和函数可以调用其他的过程或者函数。

插入小知识点 @:

1. 用户变量：以 "@" 开始，形式为 "@变量名 "
用户变量跟 mysql 客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。2. 全局变量：定义方式 set GLOBAL 变量名  或者  set @@global. 变量名 
对所有客户端生效，只有具有 super 权限才可以设置全局变量。

现在有表如下：

mysql> select * from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   1 | 男     |        1 | 李杰   |
|   2 | 女     |        1 | 钢蛋   |
|   3 | 男     |        1 | 张三   |
|   4 | 男     |        1 | 张一   |
|   5 | 女     |        1 | 张二   |
|   6 | 男     |        1 | 张四   |
|   7 | 女     |        2 | 铁锤   |
|   8 | 男     |        2 | 李三   |
|   9 | 男     |        2 | 李一   |
|  10 | 女     |        2 | 李二   |
|  11 | 男     |        2 | 李四   |
|  12 | 女     |        3 | 如花   |
|  13 | 男     |        3 | 刘三   |
|  14 | 男     |        3 | 刘一   |
|  15 | 女     |        3 | 刘二   |
|  16 | 男     |        3 | 刘四   |
|  17 | 男     |        1 | 刘一   |
+-----+--------+----------+--------+
17 rows in set (0.00 sec)

创建存储过程，传入性别（男或女），显示对应性别的学生 id，返回对应性别的人数：

DELIMITER $$
CREATE PROCEDURE myprocedure(IN sex CHAR,OUT num INT)
BEGIN
    SELECT  sid FROM student WHERE gender=sex;
    SELECT FOUND_ROWS() INTO num;   
END $$
DELIMITER ;

调用:

CALL myprocedure(' 女 ',@num)

MySQL 之存储过程

查看人数 @num：

SELECT @num

MySQL 之存储过程

条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

语法如下：

 条件定义：DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
    |mysql_error_code
条件处理:
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

举个例子吧！
现在有表如下：

mysql> select * from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   1 | 男     |        1 | 李杰   |
|   2 | 女     |        1 | 钢蛋   |
|   3 | 男     |        1 | 张三   |
|   4 | 男     |        1 | 张一   |
|   5 | 女     |        1 | 张二   |
|   6 | 男     |        1 | 张四   |
|   7 | 女     |        2 | 铁锤   |
|   8 | 男     |        2 | 李三   |
|   9 | 男     |        2 | 李一   |
|  10 | 女     |        2 | 李二   |
|  11 | 男     |        2 | 李四   |
|  12 | 女     |        3 | 如花   |
|  13 | 男     |        3 | 刘三   |
|  14 | 男     |        3 | 刘一   |
|  15 | 女     |        3 | 刘二   |
|  16 | 男     |        3 | 刘四   |
|  17 | 男     |        1 | 刘一   |
+-----+--------+----------+--------+
17 rows in set (0.00 sec)

（1）当没有进行条件处理的时候：

mysql> delimiter $$
mysql> create procedure student_insert()
    -> begin
    -> set @x=1;
    -> insert into student(sid,gender,class_id,sname) values(18,' 男 ',1,'frank');
    -> set @x=2;
    -> insert into student(sid,gender,class_id,sname) values(1,' 男 ',1,'coco');
    -> set @x=3;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call student_insert();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select @x;
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

从上面的例子可以看出，当插入 sid=1，主键重复了，直接退出了，并没有执行余下的语句，所以 @x 的值为 2。

（2）可以对主键重复进行处理：

mysql> delimiter $$
mysql>
mysql>
mysql> create procedure student_insert()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x=1;
    -> insert into student(sid,gender,class_id,sname) values(19,' 男 ',1,'jack');
    -> set @x=2;
    -> insert into student(sid,gender,class_id,sname) values(1,' 男 ',1,'bob');
    -> set @x=3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call student_insert;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x,@x2;
+------+------+
| @x   | @x2  |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.00 sec)

这次在调用存储过程的时候，并没有报错，而是在遇到主键重复的时候，会安装定义的 continue 去执行，所以继续向下执行。

condition_value 的值可以是通过 declare 定义的 condition_name，可以是 SQLSTATE 的值或者 mysql_error_code 的值会在是 SQLWARNING、NOT FOUND、SQLEXCEPTION，这个 3 个值是 3 种定义好的错误类别，分别代表不同的含义：

SQLWARNING: 是对所有以 01 开头的 SQLSTATE 代码的速记

NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记

SQLEXCEPTION 是对所有没有被 SQLWARNING 或者 NOT FOUND 捕获的 SQLSTATE 代码的速记。

以上的 declare continue handler for sqlstate ‘23000’ set @x2=1; 也可以用以下几种方式来写：

# 捕获 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;

mysql 支持的流程控制有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT 和 WHILE 语句。

1.IF

语法如下：

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

举例：求两个数的最大值

DELIMITER $$
CREATE PROCEDURE comp(IN n1 INT,IN n2 INT)
BEGIN
    SET @res=0;
    IF n1 > n2 THEN
        SET @res=n1;
    ELSEIF n1 = n2 THEN
        SET @res=n1;
    ELSE
        SET @res=n2;
    END IF;
END $$
DELIMITER ;

测试：

mysql> call comp(100,2);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> call comp(100,100);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

2.CASE 语句

语法如下：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
或者：CASE 
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE

将以上例子使用 case 来实现：

DELIMITER $$
CREATE PROCEDURE comp1(IN n1 INT,IN n2 INT)
BEGIN
    SET @res=0;
    CASE  
        WHEN n1>n2 THEN 
            SET @res=n1;
        WHEN n1=n2 THEN 
            SET @res=n1;
        ELSE 
            SET @res=n2;
    END CASE;
END $$
DELIMITER ;

测试：

mysql> call comp1(10,2);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> call comp1(100,100);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> call comp1(2,11);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

3.LOOP 和 LEAVE 语句

LOOP 可以实现简单的循环，通常和 LEAVE 一起使用,LOOP 语法如下：

[begin_label:]LOOP
    statement_list
END LOOP[end_label]

现在有表如下：

mysql> select * from userinfo;
+----+--------+--------+
| id | uname  | passwd |
+----+--------+--------+
|  1 | alex   | 123    |
|  2 | frank  | 123    |
|  3 | rose   | 312    |
|  4 | tom    | qqq    |
|  5 | jack   | qwer   |
|  6 | coco   | 123    |
|  7 | lancer | 123    |
+----+--------+--------+
7 rows in set (0.00 sec)

使用循环向里面插入 100 行数据：

DELIMITER $$
CREATE PROCEDURE userinset()
BEGIN
    SET @x=0;
    ins: LOOP    -- 标签为 ins
        SET @x=@x+1;
        IF @x=100 THEN   
            LEAVE ins;   -- 当 @x=100 的时候，则退出循环 
        END IF;
        INSERT INTO userinfo(uname,passwd) values('test','123');
    END LOOP ins;
END $$
DELIMITER ;

测试：

mysql> call userinset();
Query OK, 0 rows affected (0.17 sec)
mysql> select count(1) from userinfo;
+----------+
| count(1) |
+----------+
|      106 |
+----------+
1 row in set (0.00 sec)

行数增加到了 106 行，表示成功。

4.ITERATE 语句

必须在循环中使用，作用是跳过当前循环的剩下的语句，直接进入下一轮循环，相当于一些高级语言中的 continue。

现在有表如下：

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from info;
Empty set (0.00 sec)

只向表中插入奇数行：

delimiter $$
CREATE PROCEDURE inserinfo()
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 info(id,name) VALUES(@x,'test');
    END LOOP ins;
END $$
delimiter ;

测试：

mysql> call inserinfo();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    3 | test |
|    5 | test |
|    7 | test |
|    9 | test |
+------+------+
5 rows in set (0.00 sec)

5.REPEAT 语句

有条件的循环控制语句，当满足条件的时候退出循环，语法如下：

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

举例: 再在上面例子中插入 10 行：

delimiter $$
CREATE PROCEDURE inserinfo2()
BEGIN
    DECLARE x INT DEFAULT 9;
    ins: REPEAT
       SET x=x+1;
       INSERT INTO info(id,name) VALUES(x,'test');
    UNTIL x>18 END REPEAT;
      
END $$
delimiter ;

测试：

mysql> call inserinfo2();
Query OK, 1 row affected (0.03 sec)
mysql> select count(1) from info;
+----------+
| count(1) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

6.WHILE 语句

WHILE 是满足条件才执行。

语法如下：

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

以上的例子如果用 while 来实现如下：

delimiter $$
CREATE PROCEDURE inserinfo2()
BEGIN
    DECLARE x INT DEFAULT 9;
    ins: WHILE X<=18 DO
       SET x=x+1;
       INSERT INTO info(id,name) VALUES(x,'test');
    END WHILE;
END $$
delimiter ;

这里就不在敖述了。

在存储过程和函数中，可以使用光标对结果进行循环的处理，语法如下：

 声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN 光标:
OPEN cursor_name
FETCH 光标:
FETCH cursor_name INTO var_name[,var_name]...
CLOSE 光标:
CLOSE cursor_name

举例：

现在有表如下，分别求 id 为 1 或者 id 为 2 的 num 的和：

mysql> select * from testcursor;
+------+------+
| id   | num  |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    2 |    5 |
|    2 |    6 |
|    2 |    7 |
+------+------+
6 rows in set (0.00 sec)

创建存储过程：

CREATE PROCEDURE numsum()
BEGIN
    DECLARE i_id INT;
    DECLARE i_num INT;
    DECLARE cursor_sum CURSOR FOR SELECT id,num FROM testcursor;
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cursor_sum;
    SET @x1=0;
    SET @x2=0;
    OPEN cursor_sum;
    REPEAT 
        FETCH cursor_sum INTO i_id,i_num;
            IF i_id = 1 THEN
                SET @x1=@x1+i_num;
            ELSE
                SET @x2=@x2+i_num;
            END IF;
    UNTIL 0 END REPEAT;
END $$
delimiter ;

测试：

mysql> call numsum();
Query OK, 0 rows affected (0.00 sec)

mysql> select @x1,@x2;
+------+------+
| @x1  | @x2  |
+------+------+
|    9 |   18 |
+------+------+
1 row in set (0.00 sec)

在 pymysql 中有 callproc() 方法可以实现存储过程的调用。

举例：取两个数中的最大数：

delimiter $$
CREATE PROCEDURE maxone(IN x INT,IN y INT)
BEGIN
    SET @k=0;
    IF x>y THEN
      SET @k=x;
    ELSE
      SET @k=y;
   END IF;
END $$
delimiter ;

Python 代码如下：

import pymysql

config={"host":"127.0.0.1",
    "user":"root",
    "password":"LBLB1212@@",
    "database":"db2",
    "charset":"utf8"
}
db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
    cursor.callproc('maxone',(18,10))   # 调用存储过程 
    cursor.execute('select @k')
    res = cursor.fetchall()
    print(res)
    cursor.close()
db.close()

# 运行结果 
[{'@k': 18}]