python编写存储过程_存储过程 - msjaxuexi - 博客园

什么是存储过程

存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似;

为什么使用存储过程

回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!

那我们以后都是用存储过程不就完了?

三种开发方式对比

1.应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)

优点:

应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高

应用程序开发者不需要编写sql语句,开发效率高

缺点:

python语法与mysql语法区别巨大,学习成本高

并且各种数据库的语法大不相同,所以移植性非常差

应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低

2.应用程序不仅编写业务逻辑,还需要编写所有的sql语句

优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高

缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql'优化问题

3.应用程序仅负责业务逻辑,sql语句的编写交给ORM框架,(常用解决方案)

优点:应用程序开发者不需要编写sql语句,开发效率高

缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedurepro_name(p_Type p_name data_type)beginsql语句......流程控制end

p_type 参数类型

in 表示输入参数

out 表示输出参数

inout表示既能输入又能输出

p_name 参数名称

data_type 参数类型 可以是mysql支持的数据类型

创建存储过程

delimiter //

create procedure p1(in m int,in n int,out res int)begin

select *from student where chinese > m and chinese

#select *from student where chineseXXX > m and chinese

使用mysql使用存储过程

set @res = 0;

#调用存储过程

call p1(70,80,@res);

#查看执行结果select @res;

python中调用存储过程

import pymysql

conn=pymysql.connect(user ="root",

password="root",database ="day48"

)

cur= conn.cursor(pymysql.cursors.DictCursor)

#调用存储过程

cur.callproc("p1",(70,80,0))

#p1是存储过程的名字，()是参数 每个位置设置变量@_p1_0 @_p1_1 @_p1_2#获取执行结果print(cur.fetchall())

cur.execute("select @_p1_2")print(cur.fetchall())

此处pymysql会自动将参数都设置一个变量所以可以直接传入一个值,当然值如果作为输出参数的话,传入什么都无所谓!

删除存储过程

drop procedure 过程名称;

修改存储过程意义不大,不如删除重写!

查看存储过程

select name from mysql.proc where db = "day48" and type = "procedure";

#查看所有存储过程

#查看创建语句

showcreate procedure p1;

存储过程中的事务应用

存储过程中支持任何的sql语句包括事务!

案例:模拟转账中发送异常,进行回滚

elimiter //

create PROCEDUREp5(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexceptionBEGIN

set p_return_code = 1;rollback;END;

#exit也可以换成continue 表示发送异常时继续执行DECLARE exit handler forsqlwarningBEGIN

set p_return_code = 2;rollback;END;

​

STARTTRANSACTION;update account set money = money - 1000 where id = 1;update account set moneys = money - 1000 where id = 1; # moneys字段导致异常COMMIT;set p_return_code = 0; #0代表执行成功

​END //delimiter ;

调用

set @res=123;

call p5(@res);select @res;

总结:抛开沟通成本,学习成本,存储过程无疑是效率最高的处理方式,面试会问,一些公司也有一些现存的存储过程,重点掌握!