python学习笔记 day45 存储过程---事务

 1. 事务

就是执行的一对操作（很多sql语句）是原子性的，要么执行正确被一次性提交，要么就是执行过程发生错误直接回滚到原始状态，这样可以保证数据的安全性；

 

伪代码：

delimiter \\
create procedure p4()
begin 
  声明出现异常则执行{
  set status=1;
  rollback -- 如果执行失败 则回滚到原始状态；
}
开始事务
  执行一些列sql语句
  commit
  set status =2; -- success
END

 

2. 实现

注意：每一条语句都得加上 ; 否则会报错~

 

delimiter \\
create procedure p4(
  out p_return_code tinyint) -- 存储过程中的参数 是out 也就是可以返回给外部使用（主要是用来标记存储过程中sql语句执行状态）

BEGIN
  declare exit handler for sqlexception
  BEGIN
    set p_return_code=1; -- 如果存储过程的事务（一系列sql语句执行发生异常，状态码变为1）
    rollback; -- 并且回滚到原始状态
  END;

  start TRANSACTION;
    insert into student(name,tname) values("尝试","此条信息不会被插入") ;
    delete from student where tid=100; -- 会发生异常（student表没有tid字段）
  commit;
  set p_return_code=0;  -- 如果事务部分的sql语句正常执行， 则状态码就是0 否则为1 且会回滚 保持原始状态
END\\

delimiter ;
set @v1=2; -- 设置存储过程的out参数 为一个变量，为这个变量设置初始值
call p4(@v1); -- 将变量v1传递给out参数p_return_code
select @v1;

运行结果：

 

 

delimiter \\
create procedure p4(
  out p_return_code tinyint)

begin 
  declare exit handler for SQLEXCEPTION
  begin
    set p_return_code=1;
    rollback;
  end;
  
  start transaction;
    insert into student(name,tname) values("尝试一下","不会出错的");
    delete from teacher where id=1;
  commit;
  
  set p_return_code=0;

end\\

delimiter ;

set @v1=100;
call p4(@v1);
select @v1;
select * from teacher;
select * from student;

运行结果：
                                    

如果存储过程中事务部分的sql语句有执行错误的，就会触发执行rollback 回滚到原始状态，从而一定程度上保证了数据的安全操作；

import pymysql
conn=pymysql.connect(host="localhost",user="root",password="123",database="db666",charset="utf8")
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p4",(2,))  # 给存储过程的参数传值，由于只有一个out p_return_code tinyint 类型的参数，所以后续可以使用cursor.execute("select @_p4_0")获取参数的值
conn.commit()  # 因为存储过程的事务中sql语句有增删改操作，需要提交
result=cursor.fetchall()
print(result)

cursor.execute("select @_p4_0")  # 获取存储过程的out类型的参数p_return_code  根据该参数的值，可以判断事务中的sql语句执行的状态，p_return_code=0表明成功执行 =1表明执行失败，回滚到原始状态；
result=cursor.fetchall()
print(result)
if result[0]["@_p4_0"]==1:
    print("操作失败，回滚到原始数据状态")
else:
    print("存储过程的事务中sql语句执行成功")

运行结果：