如何在mysql中使用存储过程

49次阅读

没有评论

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

自动写代码机器人，免费开通

如何在 mysql 中使用存储过程？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

存储过程包含了一系列可执行的 sql 语句，存储过程存放于 MySQL 中，通过调用它的名字可以执行其内部的一堆 sql

存储过程的优点

#1. 用于替代程序写的 SQL 语句，实现程序与 sql 解耦

#2. 可以通过直接修改存储过程的方式修改业务逻辑（或 bug），而不用重启服务器

#3. 执行速度快，存储过程经过编译之后会比单独一条一条执行要快

#4. 减少网络传输，尤其是在高并发情况下这点优势大，存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。

存储过程的缺点

1.SQL 本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），但不是 OO 的，本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤，即只能应用在逻辑简单的业务上。

2. 不便于调试。基本上没有较好的调试器，很多时候是用 print 来调试，但用这种方法调试长达数百行的存储过程简直是噩梦。好吧，这一点不算啥，C#/java 一样能写出噩梦般的代码。

3. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

4. 无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

无参的存储过程

delimiter //
create procedure p1()
BEGIN
 select * from blog;
 INSERT into blog(name,sub_time) values(xxx ,now());
END //
delimiter ;

# 在 mysql 中调用
call p1()

# 在 python 中基于 pymysql 调用
cursor.callproc(p1)
print(cursor.fetchall())

有参的存储过程

对于存储过程，可以接收参数，其参数有三类：

#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值

带 in 的存储过程

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  
 -  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)

# 在 python 中基于 pymysql 调用
cursor.callproc(p2 ,(1,3))
print(cursor.fetchall())

带有 out

mysql  delimiter //
mysql  create procedure p3( in n1 int, out res int)
 -  begin
 -  select * from emp where id  
 -  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)

# 在 python 中基于 pymysql 调用
cursor.callproc(p3 ,(3,0)) #0 相当于 set @res=0
print(cursor.fetchall()) # 查询 select 的查询结果
cursor.execute(select @_p3_0,@_p3_1;) #@p3_0 代表第一个参数，@p3_1 代表第二个参数，即返回值
print(cursor.fetchall())

带有 inout 的例子

delimiter //
create procedure p4(
 inout n1 int
BEGIN
 select * from blog where id   n1;
 set n1 = 1;
END //
delimiter ;

# 在 mysql 中调用
set @x=3;
call p4(@x);
select @x;

# 在 python 中基于 pymysql 调用
cursor.callproc(p4 ,(3,))
print(cursor.fetchall()) # 查询 select 的查询结果
cursor.execute(select @_p4_0;)
print(cursor.fetchall())

事务

# 介绍
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 ;

# 在 mysql 中调用存储过程
set @res=123;
call p5(@res);
select @res;

# 在 python 中基于 pymysql 调用存储过程
cursor.callproc(p5 ,(123,))
print(cursor.fetchall()) # 查询 select 的查询结果
cursor.execute(select @_p5_0;)
print(cursor.fetchall())

存储过程的执行

mysql 中执行

--  无参数
call proc_name()
--  有参数，全 in
call proc_name(1,2)
--  有参数，有 in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

pymsql 中执行

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = 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)

删除存储过程