BEGIN
DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2
set @studentname=studentname;
set @studentid=studentid;
set @createby=createby;
set @schoolid=school_id;
set @physicnum=physic_num;
set @cardnum=card_num;
set @DBT=dbtablenames;
set @operator_id=operator_id;
START TRANSACTION; -- 开始事务
IF @DBT=NULL or LENGTH(@DBT)=0 THEN
set ret_code = '4000'; #
set show_msg = '缺少必要参数dbtablenames';
ELSE
set @preparable_stmt0=CONCAT("select count(*) into @checktable from information_schema.TABLES t where t.TABLE_SCHEMA ='db' and t.TABLE_NAME ='",@DBT,"';");
prepare stmt0 from @preparable_stmt0;
EXECUTE stmt0;
DEALLOCATE PREPARE stmt0;
IF @checktable=0 THEN
set ret_code = '4000'; #
set show_msg = CONCAT(@DBT,"这个表不存在");
ELSE
SELECT COUNT(*) INTO @count_school from d_school where SchoolId=@schoolid;
IF @count_school=1 THEN
SELECT SchoolName INTO @schoolname from d_school where SchoolId=@schoolid;
select COUNT(*) INTO @count_stu from d_student where SchoolId=@schoolid and SutdentId=@studentid;
IF @count_stu=1 THEN
select Balance INTO @balance from d_student where SchoolId=@schoolid and SutdentId=@studentid;
IF @balance>0 THEN
UPDATE d_student set Balance=0,allConsume=allConsume-@balance where SchoolId=@schoolid and SutdentId=@studentid;
set @RefundWater=CONCAT('GTL',DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),FLOOR(RAND() * 10000));#
set @Business_Type='0';#
set @Business_Sign='41';#
set @preparable_stmt3=CONCAT("Insert into ",@DBT,"(SchoolId,SchoolName,UserNo,UserId,UserName,PhysicNum,PrintNum,CardNum,UserType,Price,OldBalance,CurrentBalance,BusinessType,
BusinessTime,UploadTime,BusinessWater,IdentifyCard,BusinessSign,OperatorId,CreateBy,CreateTime,OperatorName,MachineId,MachineCode,PlatForm,PlatFormWater,GradeId,ClassId,Meno,MerchantId,MerTitle)
SELECT SchoolId,@schoolname,StudentNo,SutdentId,StudentName,PhysicNum,PrintNum,CardNum,'0',@balance,@balance,0,@Business_Type,NOW(),NOW(),
@RefundWater,identifyCard,@Business_Sign,@operator_id,@createby,NOW(),@createby,null,null,null,null,Grade,ClassId,'销户,余额提现',null,null
from d_student
where SchoolId=",@schoolid," and SutdentId=",@studentid,";");
prepare stmt3 from @preparable_stmt3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END IF;
#Routine body goes here...
SELECT COUNT(*) INTO @count_num from d_losscard where SchoolId=@schoolid AND CardNum=@cardnum AND PhysicCard=@physicnum;
IF @count_num=1 THEN
update d_losscard set State=1,UpdateTime=NOW() where SchoolId=@schoolid AND CardNum=@cardnum AND PhysicCard=@physicnum AND `Owner`=@studentname;
CALL AutoMachinesCmd(@schoolid, 'GS', @cardnum, '销户挂失', 0);
INSERT INTO d_cancellation select * FROM d_student where SchoolId=@schoolid and SutdentId=@studentid;
UPDATE d_student set StudentState=0 WHERE SchoolId=@schoolid and SutdentId=@studentid;
#delete from d_student where SchoolId=@schoolid and SutdentId=@studentid;
set ret_code = '9999';
set show_msg = '销户成功';
ELSEIF @count_num=0 THEN
INSERT INTO d_cancellation select * FROM d_student where SchoolId=@schoolid and SutdentId=@studentid;
UPDATE d_student set StudentState=0 WHERE SchoolId=@schoolid and SutdentId=@studentid;
#delete from d_student where SchoolId=@schoolid and SutdentId=@studentid;
set ret_code = '9999';
set show_msg = '异常账户销户成功';
ELSE
set ret_code = '0002';
#set show_msg = '卡异常（挂失表）';
set show_msg = CONCAT('卡异常（挂失表）',@studentid,@schoolid,@count_stu,@count_num);
END IF;
ELSE
set ret_code = '0004';
set show_msg = CONCAT('学生信息异常',@studentid,@schoolid,@count_stu);
END IF;
ELSEIF @count_school=0 THEN
set ret_code = '0005';
set show_msg = '学校不存在';
END IF;
END IF;
END IF;
IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
ROLLBACK;
set ret_code = '0009';
set show_msg = '出现异常，数据回滚';
ELSE
COMMIT;
END IF;
END
create table t_table_recove --新表
as
select * from ac01 --原表
as of timestamp to_timestamp('2018-07-25 11:12:41','yyyy-mm-dd hh24:mi:ss');
意思就是将回滚的事物表再次回滚到当前。这样我们会有两张表，a（修改后的表），b（回滚后的表）。两边关联主键，查出多出的数据，就是数据变化
在捕捉异常的时候，可以打印一些信息
DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION
begin SET result_code = 1;
select 变量；
end;
这样在发出异常时，会打印一些异常信息；如果还想知道更具体的，可以在代码里加一些select 变量；打印日志