存储过程

在sql中，有些语句的长度过长，如果需要重复使用，每次都要自己写的话，就会降低效率

mysql提供了存储过程，将多条sql语句存储起来，起个别名，将存储过程存储在服务端，在执行其时，服务端根据名称找到其文件，执行并将sql整体结果返回给客户端

存储过程的功能与视图相似，都是讲sql语句起个名称，使用时直接操作名称，但是视图只能在select语句时使用，一条视图只能有一条语句

存储过程可以包含多种sql语句，且存储过程中的sql语句可以有很多

创建存储过程：

存储过程就像代码中的函数，可以使用传入参数来获取不同的值：

存储过程中参数的类型有3种，in,out,inout

　　in只往存储过程中传入参数

　　out只往存储过程中取出参数

　　inout即传入参数又能取出参数

变量的声明和赋值：

　　declare 在存储过程种声明变量时必须使用   

　　set 在存储过程中给变量赋值必须使用

如

declare v int ;创建一个int型的v,其值为空
set v=12;     给v赋值为12
declare v int default 9;  创建一个int型的v,其值为9

如下是一段创建存储过程的代码：

 delimiter //         (修改终止符，如果使用原终止符；，在end后的;会与语句和逻辑中的；发生冲突，时程序填结束)
    create procedure 名称(
    in i1 int,
    out r1 int,
    inout c1 int
    ...
    )
    begin
        语句，逻辑
    end //
    delimiter ;          （将终止符修改为原来的;）

使用存储过程：

call 名称(1,@t2,@t3);  根据参数的类型传入

python使用存储过程：

cursor.callporc('名称'，args=[参数])  #不管参数的类型是In还是out，随便传参数，是out和inout的话自动忽略传入的参数

cursor.execute('select @_名称_参数索引@_名称_参数索引@_名称_参数索引')   格式固定，索引值是几，就取传入参数的哪个值

如下是一个存储过程的创建和使用代码

（注意使用存储过程时参数的使用方法）

创建过程：
delimiter //
create procedure tt(
in l1 int,
in l2 int,
out l3 int,
inout l4 int)
begin
DECLARE com1 int ;
DECLARE com2 int DEFAULt 4;
set com1=l2;
set l3=com2;
set l4=com2+l1;
select * from class WHERE cid=l1;
end //
delimiter ;

python使用存储过程的代码：
import pymysql
con=pymysql.Connect(host='127.0.0.1',port=3306,user='root',passwd='666',database='test',charset='utf8')
cursor=con.cursor(cursor=pymysql.cursors.DictCursor)
a=cursor.callproc('tt',args=(1,2,3,4))
d=cursor.fetchall()#获取存储过程的结果集，将返回值设置给@_名称_索引
print(d)
c=cursor.execute('select @_tt_0,@_tt_1,@_tt_2,@_tt_3')
e=cursor.fetchall()  #获取返回值
print(e)
cursor.close()
con.close()

执行结果：
[{'cid': 1, 'caption': '三年二班'}]
[{'@_tt_0': 1, '@_tt_1': 2, '@_tt_2': 4, '@_tt_3': 5}]

删除存储过程：

drop procedure 名称；

存储过程中写条件语句，循环语句：

条件语句：

begin
DECLARE i int default 1;
if i=1 then  (then必须加，作用就像python中的:，if就是条件判断语句的开头)
select 1;
elseif i=2 then
select 2;
else
select 5;
end if;  条件判断语句的结尾
end //

循环语句：

1.while循环
    declare i int;
    set i=0;
    while i<100 do （while循环的开头,do一定要添加）
    select i;
    set i=i+1;
    end while;   （while循环的结尾）
    end //
2，repeat 循环
    declare i int;
    ste i=0;
    repeat   (repeat循环的开头)
    select i;
    set i=i+1;
    until i>=100
    end repeat;   （repeat循环的结尾）
    end //
3.loop循环
    declare i int;
    ste i=0;
    looplable(随便写) loop (loop循环的开头)
    select i;
    set i=i+1;
    if i>=100 then
    leave looplable;
    end if;
    end loop;     （loop循环的结尾）
    end //

动态执行sql语句

在存储过程中，可以传入参数，来多参数进行操作。因此如果将参数的内容改成sql语句，想要在存储过程中执行该str类型的sql语句，同时可以将语句中的条件改成其他的参数，根据输入的内容来执行sql语句

就需要特殊的方法进行拼接，在mysql中防止sql注入

declare i int;
set p1=11;
set @p1=p1;
prepare prod(名称，随便起，代指后面的字符串) from 'select * from tb2 where nid >?';  (mysql中占位符为？)
execute prod using @p1;   (格式化字符串，将11传入mysql语句中，传入的变量必须为@开头)
deallocate prepare prod;   (执行sql语句)

就可以在python代码中，设置直接让用户输入sql语句，和条件，直接执行
cursor.callproc('tt',('select * from tb2 where id>?',13))
就会在存储过程中自己执行，select * from tb2 where id>13