mysql execute 存储过程_mysql之存储过程

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

存储过程的优点

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

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

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

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

存储过程的缺点

1.SQL本身是一种结构化查询语言，加上了一些控制(赋值、循环和异常处理等)，但不是OO的，本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤，即只能应用在逻辑简单的业务上。2.不便于调试。基本上没有较好的调试器，很多时候是用print来调试，但用这种方法调试长达数百行的存储过程简直是噩梦。好吧，这一点不算啥，C#/java一样能写出噩梦般的代码。

3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。4.无法适应数据库的切割(水平或垂直切割)。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

无参的存储过程

delimiter //create procedure p1()

BEGIN

select* fromblog;

INSERT into blog(name,sub_time) values("xxx",now());

END//delimiter ;#在mysql中调用

call p1()#在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 |

+----+----------+-----+--------+

4 rows in set (0.30sec)

mysql> delimiter //mysql> create procedure p2(in n1 int, inn2 int)->begin-> select * from emp where id >n1 and id end //Query OK, 0 rows affected (0.28sec)

mysql>delimiter ;

mysql> call p2(1,3)->;+----+------+-----+--------+

| id | name | age | dep_id |

+----+------+-----+--------+

| 2 | lisi | 19 | 1 |

+----+------+-----+--------+

1 row in set (0.07sec)

Query OK, 0 rows affected (0.07 sec)

#在python中基于pymysql调用

cursor.callproc('p2',(1,3))print(cursor.fetchall())

带有out

mysql> delimiter //mysql> create procedure p3( inn1 int, out res int)->begin-> select * from emp where id >n1;-> set res=1;-> end //Query OK, 0 rows affected (0.28sec)

mysql>delimiter ;

mysql> set @res=0;

Query OK, 0 rows affected (0.00sec)

mysql> call p3(3,@res);+----+------+-----+--------+

| id | name | age | dep_id |

+----+------+-----+--------+

| 5 | alex | 18 | 2 |

+----+------+-----+--------+

1 row in set (0.00sec)

Query OK, 0 rows affected (0.01sec)

mysql>select @res;+------+

| @res |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

#在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 //create procedure p4(

inout n1 int

)

BEGIN

select* from blog where id >n1;

set n1= 1;

END//delimiter ;#在mysql中调用

set @x=3;

call p4(@x);

select @x;#在python中基于pymysql调用

cursor.callproc('p4',(3,))print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p4_0;')print(cursor.fetchall())

#介绍

delimiter //create procedure p4(

out status int

)

BEGIN1. 声明如果出现异常则执行{

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 handlerforsqlexception

BEGIN--ERROR

set p_return_code= 1;

rollback;

END;

DECLARE exit handlerforsqlwarning

BEGIN--WARNING

set p_return_code= 2;

rollback;

END;

START TRANSACTION;

DELETEfrom tb1; #执行失败

insert into blog(name,sub_time) values('yyy',now());

COMMIT;--SUCCESS

set p_return_code= 0; #0代表执行成功

END//delimiter ;#在mysql中调用存储过程

set @res=123;

call p5(@res);

select @res;#在python中基于pymysql调用存储过程

cursor.callproc('p5',(123,))print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p5_0;')print(cursor.fetchall())

事务

存储过程的执行

--无参数

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)

mysql中执行

#!/usr/bin/env python#-*- 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)

pymsql中执行

删除存储过程

drop procedure proc_name;