存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。
一、创建存储过程这里创建存储过程与创建视图要进行区别,创建视图 create view, 创建存储过程 create procedure;而且创建存储过程的时候在存储过程名字后面要加上 括号。
对于存储过程,可以接收参数,其参数有三类:in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
在navicat中创建的存储过程:
二、使用存储过程注意:
使用的关键字 call。
无参数存储过程
无参数的存储国过程,直接使用 call 存储过程名();
有参数存储过程
1、in 仅用于传入参数用
使用:说明:
1、in:定义的参数只能由执行函数的时候传入,然后便可在函数中使用,同时要注意,当我们在创建的时候定义参数的类型,那么在传递参数的时候,类型必须一直,不然会报错。上面列在,定义的参数是 int,所以我们在执行方法的时候我们只能传int类型(整型)的数据。
2、declare:在方法内部定义变量使用的关键字(和js中var区别理解)
3、set:对变量进行设置值的时候,必须使用的。
4、delimiter:重新定义方法内部的结束符号,在sql中一般一个完成sql执行完之后都会使用分号“;”;进行结束,但是在定义存储过程的时候,内部定义变量、设置值、内部sql语句,都会形成一个完成的语句,而结尾就应该使用分号“;”结束,但是如果直接用分号结束那么会导致整个sql语句的结束,这样会导致sql语句执行不完整,因此的在函数内部定义一个符号。让其在函数内部表示一个语句的执行完成。而不会导致整个sql语句的结束。
代码中,开始定义了内部的执行结束的表示符号为 “$$”。而在结尾必须将其变会 “;”,不然会影响后面语句的执行。
5、drop procedure if exists p1; 这句表示先检测是否存在 p1存储过程,如果存在则删除。注意:其中 if exists 是判断的关键。
2、out 仅用于返回值用
在代码中现在有2个参数,第一个 in 是必传的,但是 out 并不是必传的,从它的定义上来说,它只是接收方法返回值。
问题:既然是接收返回值,那怎么接收那?
这里要涉及一个回话变量,回话变量:相当于我们在方法外面定义一个变量,然后将变量的引用 传入方法。
既然是在外部定义的,那么就可以直接访问这个变量。
结果:
说明:
sql中查看某个变量要使用 select。格式:select 变量名
3、inout 既可以传入又可以当作返回值
inout是必传字段。执行代码是要注意,inout既然是能传值也能获取值,就不能单单的传一个对应类型的值进去就行了的。
结果:
说明:
1、in 是必传字段。
2、out 可以不用传。但是如果想获取返回值的话,那就必须传。
3、inout:也是必传字段。
三、修改存储过程
修改存储过程很麻烦,因此一般不修改存储过程,而是直接删除,重新创建一个新的存储过程。
四、删除存储过程
五、pymysql执行存储过程
创建存储过程:
delimiter $$
drop PROCEDURE if EXISTS p1;
create PROCEDURE p1(
in i1 int,
inout i2 int,
out i3 int
)
BEGIN
DECLARE d1 int default 3;
set i2 = i2 + 1;
select * from part WHERE nid > i2;
if i1 = 1 then
set i2 = 100 + d1;
ELSEIF i1 = 2 THEN
set i2 = 200 + d1;
else
set i2 = 1000 + d1;
end if;
end $$
delimiter ;
存储过程
问题:在python中怎样获取方法返回的东西?
1、获取方法中sql语句操作的结果。
# !usr/bin/evn python
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='waijian')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
row = cursor.callproc('p1', args = (1,2,3))
# 获取存储过程中sql语句操作的结果
ret = cursor.fetchall()
print(ret)
# 获取存储过程的返回值,括号中的三个参数对应执行存储过程中接收的三个参数
effect_row = cursor.execute("select @_p1_0, @_p1_1, @_p1_2")
# 取返回值
r = cursor.fetchone()
print(r)
conn.commit()
cursor.close()
conn.close()
pymysql
执行结果:
说明:
1、pymysql执行语句中,传入参数为3个,前面分析,out不是必传字段。因此按上面这样操作,即使传了值在程序中,第三个值也会自动被忽略。
2、存储方法中的sql语句返回的结果是存在与 cursor(游标)中的。因此获取时只能通过游标获取,代码中只有一条查询语句,如果有多条的话,fetchall() 一次性获取所有的,fetchone() 一条条的获取。
3、sql语句中的 if判断语句,主要要和python中的区别记忆。
4、接收方法中返回参数, cursor.execute("select @_p1_0, @_p1_1, @_p1_2");代码是这句,注意括号中接收参数的写法,写法不对获取结果都为 none。
之前我使用的是 @_proc_p1_0,@_proc_p1_1,@_proc_p1_2;结果获取的都为None,后面改了名字就可以了。
python 获取存储过程中的信息,要分2步,获取查询是一步,获取返回值是一步,注意区分。
六、扩展
当在存储过程中,执行多条sql语句,并且某些sql语句执行的条件,是基于别的sql语句执行的结果。当然可以通过连表操作获取对应的信息。
但是这里讨论的是在存储过程中实现。
注意:
上面代码中 into 是关键。