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

这篇文章主要介绍了mysql存储过程原理与使用方法,结合实例形式详细分析了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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/13593/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 8月 20日 1:57:36
下一篇 2022年 8月 20日 1:57:43

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!