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

吾爱主题阅读：195 2024-04-05 14:24:24 评论：0

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