个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-28 22:32:09 27 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章mysql存储过程原理与使用方法详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

本文实例讲述了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=0
print (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()
-- 有参数，全in
call proc_name(1,2)
-- 有参数，有in，out，inout
set @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 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)

删除存储过程 。

?
1
drop procedure proc_name;

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

原文链接：https://www.cnblogs.com/mmyy-blog/p/9852986.html 。

最后此篇关于mysql存储过程原理与使用方法详解的文章就讲到这里了,如果你想了解更多关于mysql存储过程原理与使用方法详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

27 4 0
文章推荐： mysql慢查询操作实例分析【开启、测试、确认等】
文章推荐： Python使用PDFMiner解析PDF代码实例
文章推荐： 教你如何使用MySQL8递归的方法
文章推荐： Linux下运行jar包的方法介绍
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com