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