首页 > > 网络编程 > Mysql >

MySQL之存储过程

2018-06-18 00:16:55来源：未知阅读 ()

存储过程和函数是事先经过编译并存储在数据库中的一段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)

查看人数@num：

SELECT @num

定义条件和处理

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

语法如下：

条件定义：
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中调用存储过程

在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}]