本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考，具体如下：
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
存储过程的优点
#1. 用于替代程序写的SQL语句，实现程序与sql解耦
#2. 可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器
#3. 执行速度快，存储过程经过编译之后会比单独一条一条执行要快
#4. 减少网络传输，尤其是在高并发情况下这点优势大，存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。
存储过程的缺点
1.SQL本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），但不是OO的，本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤，即只能应用在逻辑简单的业务上。
2.不便于调试。基本上没有较好的调试器，很多时候是用print来调试，但用这种方法调试长达数百行的存储过程简直是噩梦。好吧，这一点不算啥，C#/java一样能写出噩梦般的代码。
3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
4.无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
无参的存储过程
|
1
2
3
4
5
6
7
|
delimiter //create procedure p1()BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now());END //delimiter ; |
|
1
2
|
#在mysql中调用call p1() |
|
1
2
3
|
#在python中基于pymysql调用cursor.callproc('p1')print(cursor.fetchall()) |
有参的存储过程
对于存储过程，可以接收参数，其参数有三类：
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
带in的存储过程
|
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
|
mysql> select * from emp;+----+----------+-----+--------+| id | name | age | dep_id |+----+----------+-----+--------+| 1 | zhangsan | 18 | 1 || 2 | lisi | 19 | 1 || 3 | egon | 20 | 2 || 5 | alex | 18 | 2 |+----+----------+-----+--------+4 rows in set (0.30 sec)mysql> delimiter //mysql> create procedure p2(in n1 int, in n2 int) -> begin -> select * from emp where id >n1 and id <n2; -> end //Query OK, 0 rows affected (0.28 sec)mysql> delimiter ;mysql> call p2(1,3) -> ;+----+------+-----+--------+| id | name | age | dep_id |+----+------+-----+--------+| 2 | lisi | 19 | 1 |+----+------+-----+--------+1 row in set (0.07 sec)Query OK, 0 rows affected (0.07 sec) |
|
1
2
3
|
#在python中基于pymysql调用cursor.callproc('p2',(1,3))print(cursor.fetchall()) |
带有out
|
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
|
mysql> delimiter //mysql> create procedure p3( in n1 int, out res int) -> begin -> select * from emp where id >n1; -> set res=1; -> end //Query OK, 0 rows affected (0.28 sec)mysql> delimiter ;mysql> set @res=0;Query OK, 0 rows affected (0.00 sec)mysql> call p3(3,@res);+----+------+-----+--------+| id | name | age | dep_id |+----+------+-----+--------+| 5 | alex | 18 | 2 |+----+------+-----+--------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)mysql> select @res;+------+| @res |+------+| 1 |+------+1 row in set (0.00 sec) |
|
1
2
3
4
5
|
#在python中基于pymysql调用cursor.callproc('p3',(3,0)) #0相当于set @res=0print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值print(cursor.fetchall()) |
带有inout的例子
|
1
2
3
4
5
6
7
8
9
|
delimiter //create procedure p4( inout n1 int)BEGIN select * from blog where id > n1; set n1 = 1;END //delimiter ; |
|
1
2
3
4
|
#在mysql中调用set @x=3;call p4(@x);select @x; |
|
1
2
3
4
5
|
#在python中基于pymysql调用cursor.callproc('p4',(3,))print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p4_0;')print(cursor.fetchall()) |
事务
|
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
35
36
37
38
39
40
41
42
43
44
45
|
#介绍delimiter // create procedure p4( out status int ) BEGIN 1. 声明如果出现异常则执行{ set status = 1; rollback; } 开始事务 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; END // delimiter ;#实现delimiter //create PROCEDURE p5( OUT p_return_code tinyint)BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tb1; #执行失败 insert into blog(name,sub_time) values('yyy',now()); COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功END //delimiter ; |
|
1
2
3
4
|
#在mysql中调用存储过程set @res=123;call p5(@res);select @res; |
|
1
2
3
4
5
|
#在python中基于pymysql调用存储过程cursor.callproc('p5',(123,))print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p5_0;')print(cursor.fetchall()) |
存储过程的执行
mysql中执行
|
1
2
3
4
5
6
7
8
|
-- 无参数call proc_name()-- 有参数，全incall proc_name(1,2)-- 有参数，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2) |
pymsql中执行
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p1', args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result) |
删除存储过程
|
1
|
drop procedure proc_name; |
希望本文所述对大家MySQL数据库计有所帮助。
原文链接：https://www.cnblogs.com/mmyy-blog/p/9852986.html
