mysql 储存过程

作用：替代sql语句，把sql语句封装

1、无参数

delimiter //
create procedure p1()
BEGIN
select * from class;
insert into teacher(tname) values('top');
END //
delimiter ;
a、sql调用
call p1();
b.pymysql调用
核心代码：
cursor.callproc('p1')
conn.commit()
ret = cursor.fetchall()
print(ret)

2、含参数(in out inout)

delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
select * from student where sid > n1;
END //
delimiter ;

a、msql调用
call p2(2, 0)

b、pymysql调用
cursor.callproc('p2', (5, 0))
conn.commit()
ret = cursor.fetchall()
print(ret)

3、传递参数(out)

out目的：设置值，用于检测储存过程是否成功(储存过程全是插入)

delimiter //
create procedure p3(
    in n1 int,
    out n2 int
)
BEGIN
set n2 = 6;
select * from student where sid > n1;
END //
delimiter ;

a、mysql调用
set @v1 = 0 设置session的变量
call p3(4, @v1)
select @v1

b、pymysql
# 结果集
cursor.callproc('p3', (5, 4))
res = cursor.fetchall()
print(res)
# 返回out,是伪造的返回值
cursor.execute('select @_p3_0, @_p3_1 ')
res = cursor.fetchall()
print(res)

4、 事务

    出现错误回滚，全部成功提交
伪代码
delimiter //
create procedure p4(
    out status int
)
BEGIN
声明出现异常执行的操作{
    set status = 1;
    rooback;
}
开始事物
操作
commit;
结束
set status = 2
END //
delimiter ;    

delimiter \
create PROCEDURE p4(
OUT p_return_code tinyint
)
BEGIN 
DECLARE exit handler for sqlexception 
BEGIN 
-- ERROR 
set p_return_code = 1; 
rollback; 
END; 

START TRANSACTION; 
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT; 

-- SUCCESS 
set p_return_code = 2; 

END\
delimiter ;

5、游标

delimiter //
create procedure p6()
begin 
declare row_id int; -- 自定义变量1  
declare row_num int; -- 自定义变量2 
declare done INT DEFAULT FALSE; -- 声明done 状态为False    
declare temp int;

declare my_cursor CURSOR FOR select id,num from A;    --声明游标
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 当没有数据时，done为True


open my_cursor;    -- 打开游标局部
    xxoo: LOOP
        fetch my_cursor into row_id,row_num; --取一行的数据
        if done then 
            leave xxoo;
        END IF;
        set temp = row_id + row_num;
        insert into B(number) values(temp);
    end loop xxoo;
close my_cursor;

end  //
delimter ;

注意：性能低，能不用就不用，对每一行数据进行单独操作，用游标

6、动态执行sql(防止sql注入)

delimiter //
create procedure p6(
    in tpl varchar(255),
    in arg int
)
begin 
    
    set @xo = arg;
    PREPARE xxx FROM 'select * from student where sid > ?';    --xxx是变量，任意命名，
    EXECUTE xxx USING @xo;     -- 与上面的SQL语句结合,格式化语句 @xo要是session的变量
    DEALLOCATE prepare prod;     --执行格式化语句
end  //
delimter ;

防sql注入

　　pymysql 和 动态执行mysql