5.13 数据库存储过程

存储过程

概念

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

存储过程的优点：
　　1.程序与数据实现解耦
　　2.减少网络传输的数据量

使用

创建

　　创建无参的存储过程

===========创建无参的存储过程===============
delimiter //
create procedure p1()
begin
    select * from test;
    insert into test(username,dep_id) VALUES('egon',1);
end //
delimiter ;

#调用存储过程
#在mysql中调用
call p1();
#在python程序中调用
cursor.callproc('p1')

　　对于存储过程，可以接收参数，其参数有三类：

# in 　　仅用于传入参数用
# out 　　仅用于返回值用
# inout 　　既可以传入又可以当作返回值

==========创建有参的存储过程(in)===============
delimiter //
create procedure p2(
    in m int, #从外部传进来的值
    in n int
)
begin
    insert into test(username,dep_id) VALUES ('haha',2),('xixi',3),('sasa',1),('yanyan',2);
    select * from test where id between m and n;
end //
delimiter ;

#调用存储过程
call p2(3,7); #在mysql中执行
#在python程序中调用
cursor.callproc('p2',arg(3,7))

===========创建有参的存储过程(out)===============
delimiter //
create procedure p3(
    in m int, #从外部传进来的值
    in n int,
    out res int
)
begin
    select * from test where id between m and n;
    set res = 1;#如果不设置，则res返回null
end //
delimiter ;

#调用存储过程
set @res = 11111;
call p3(3,7,@res);
select @res; #在mysql中执行

#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

=============创建有参存储过程之inout的使用==========
delimiter //
create procedure p4(
    inout m int
)
begin
    select * from test where id > m;
    set m=1;
end //
delimiter ;

#在mysql中
set @x=2;
call p4(@x);
select @x;

===========================
delimiter //
create procedure p5(
    inout m int
)
begin
    select * from test11111 where id > m;
    set m=1;
end //
delimiter ;

#在mysql中
set @x=2;
call p5(@x);
select @x;  #这时由于不存在那个表就会报错，查看的结果就成2了。

执行

-- 无参数
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)

删除

drop procedure proc_name;

补充

程序与数据库结合使用的三种方式

#方式一：
    MySQL：存储过程
    程序：调用存储过程

#方式二：
    MySQL：
    程序：纯SQL语句

#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）

import pymysql
conn = pymysql.connect(host = 'localhost',user = 'root',password='123456',database = 'lianxi',charset = 'utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)  #以字典的形式输出
# rows = cursor.callproc('p1')  #1.调用存储过程的方法 ，没参数时
# rows = cursor.callproc('p2',args=(3,7))  #有参数时
rows = cursor.callproc('p3', args=(3,7,123))  #@_p3_0=3,@_p3_1=7 ,@_p3_2=123  #有参数时
conn.commit()  #执行
print(cursor.fetchall())
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall())
cursor.close()
conn.close()