存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

1、创建存储过程

无参数存储过程

--创建存储过程delimiter //create procedure p1()BEGIN    select * from t1;END//delimiter ;-- 执行存储过程call p1()

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

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

有参数的存储过程

-- 创建存储过程delimiter \\create procedure p1(    in i1 int,    in i2 int,    inout i3 int,    out r1 int)BEGIN    DECLARE temp1 int;    DECLARE temp2 int default 0;        set temp1 = 1;    set r1 = i1 + i2 + temp1 + temp2;    set i3 = i3 + 100;end\\delimiter ;-- 执行存储过程set @t1 =4;set @t2 = 0;CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;

注意：out inout 需先声明再执行存储过程

1. 结果集

delimiter //create procedure p1()begin    select * from v1;end //delimiter ;

2. 结果集+out值

delimiter //create procedure p2(    in n1 int,    inout n3 int,    out n2 int,)begin    declare temp1 int ;    declare temp2 int default 0;    select * from v1;    set n2 = n1 + 100;    set n3 = n3 + n1 + 100;end //delimiter ;

3.事物

delimiter \\create PROCEDURE p1(--返回值，若没有执行没有异常返回 0，若error返回 1，若warning返回 2    OUT p_return_code tinyint)BEGIN --申明数据库执行出现错误则执行  DECLARE exit handler for sqlexception   BEGIN     -- ERROR     set p_return_code = 1;     rollback;   END;    DECLARE exit handler for sqlwarning   BEGIN     -- WARNING     set p_return_code = 2;     rollback;   END; -- sql语句执行代码  START TRANSACTION;     DELETE from tb1;    insert into tb2(name)values('seven');  COMMIT;    -- SUCCESS   set p_return_code = 0;    END\\delimiter ;

游标

delimiter //create procedure p3()begin     declare ssid int; -- 自定义变量1      declare ssname varchar(50); -- 自定义变量2      DECLARE done INT DEFAULT FALSE;-- 申明游标    DECLARE my_cursor CURSOR FOR select sid,sname from student;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;        open my_cursor;        xxoo: LOOP            fetch my_cursor into ssid,ssname;            if done then                 leave xxoo;            END IF;            insert into teacher(tname) values(ssname);        end loop xxoo;    close my_cursor;end  //delimter ;

动态执行SQL（防SQL注入）

delimiter \\CREATE PROCEDURE p4 (    in nid int)BEGIN    PREPARE prod FROM 'select * from student where sid > ?';    EXECUTE prod USING @nid;    DEALLOCATE prepare prod; END\\delimiter ;

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

-- 无参数call proc_name()-- 有参数，全incall proc_name(1,2)-- 有参数，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)

pymysql执行存储过程

import pymysqlconn = 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)