[日期:2017-09-25] | [字体:大 中 小] |
存储过程和函数是事先经过编译并存储在数据库中的一段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)
- [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}]
好了今天就写到这里,后面如果有其他的内容再补充。
来源: http://www.linuxidc.com/Linux/2017-09/147055.htm