mysql存储过程原理与使用方法详解

2023-08-04 07:56:04 82

本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考，具体如下：

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

存储过程的优点

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

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

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

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

存储过程的缺点

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

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

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

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

无参的存储过程

delimiter//
createprocedurep1()
BEGIN
select*fromblog;
INSERTintoblog(name,sub_time)values("xxx",now());
END//
delimiter;

#在mysql中调用
callp1()

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

有参的存储过程

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

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

带in的存储过程

mysql>select*fromemp;
+----+----------+-----+--------+
|id|name|age|dep_id|
+----+----------+-----+--------+
|1|zhangsan|18|1|
|2|lisi|19|1|
|3|egon|20|2|
|5|alex|18|2|
+----+----------+-----+--------+
4rowsinset(0.30sec)
mysql>delimiter//
mysql>createprocedurep2(inn1int,inn2int)
->begin
->select*fromempwhereid>n1andidend//
QueryOK,0rowsaffected(0.28sec)
mysql>delimiter;
mysql>callp2(1,3)
->;
+----+------+-----+--------+
|id|name|age|dep_id|
+----+------+-----+--------+
|2|lisi|19|1|
+----+------+-----+--------+
1rowinset(0.07sec)
QueryOK,0rowsaffected(0.07sec)

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

带有out

mysql>delimiter//
mysql>createprocedurep3(inn1int,outresint)
->begin
->select*fromempwhereid>n1;
->setres=1;
->end//
QueryOK,0rowsaffected(0.28sec)
mysql>delimiter;
mysql>set@res=0;
QueryOK,0rowsaffected(0.00sec)
mysql>callp3(3,@res);
+----+------+-----+--------+
|id|name|age|dep_id|
+----+------+-----+--------+
|5|alex|18|2|
+----+------+-----+--------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.01sec)
mysql>select@res;
+------+
|@res|
+------+
|1|
+------+
1rowinset(0.00sec)

#在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//
createprocedurep4(
inoutn1int
)
BEGIN
select*fromblogwhereid>n1;
setn1=1;
END//
delimiter;

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

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

事务

#介绍
delimiter//
createprocedurep4(
outstatusint
)
BEGIN
1.声明如果出现异常则执行{
setstatus=1;
rollback;
}
开始事务
--由秦兵账户减去100
--方少伟账户加90
--张根账户加10
commit;
结束
setstatus=2;
END//
delimiter;
#实现
delimiter//
createPROCEDUREp5(
OUTp_return_codetinyint
)
BEGIN
DECLAREexithandlerforsqlexception
BEGIN
--ERROR
setp_return_code=1;
rollback;
END;
DECLAREexithandlerforsqlwarning
BEGIN
--WARNING
setp_return_code=2;
rollback;
END;
STARTTRANSACTION;
DELETEfromtb1;#执行失败
insertintoblog(name,sub_time)values('yyy',now());
COMMIT;
--SUCCESS
setp_return_code=0;#0代表执行成功
END//
delimiter;

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

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

存储过程的执行

mysql中执行

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

pymsql中执行

#!/usr/bin/envpython
#-*-coding:utf-8-*-
importpymysql
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)

删除存储过程

dropprocedureproc_name;

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL存储过程技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

mysql存储过程原理与使用方法详解

热门推荐

随机推荐