服务器之家

服务器之家 > 正文

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

时间:2020-12-23 15:53     来源/作者:dawn-liu

本文实例讲述了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

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址 2020-08-12
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
返回顶部