MySQL存储过程实战：事务、输出参数与游标嵌套调用

设计目标与核心逻辑

该存储过程用于处理信息的增加、更新和删除操作，核心目标是每次仅同步上次以来未处理的数据。系统通过标志位判断业务状态，并采用嵌套调用的方式将“主流程控制”与“具体数据处理”解耦：

• 主存储过程（pro_rep_shadow_rs）：负责状态检查、事务启动/提交、日志更新及结果返回。
• 子存储过程（pro_rep_shadow_rs_do）：负责在指定编号范围内，使用游标逐行读取数据并根据操作类型执行INSERT或DELETE。

完整代码实现

第1步：清理旧定义并创建主存储过程

首先移除可能存在的同名存储过程，重新定义主流程。此过程接收一个输出参数 rtn 用于向调用方反馈执行结果。

drop procedure if exists pro_rep_shadow_rs;
delimiter |
----------------------------------
-- rep_shadow_rs
-- 用来处理信息的增加，更新和删除
-- 每次只更新上次以来没有做过的数据
-- 根据不同的标志位
-- 需要一个输出的参数，
-- 如果返回为0，则调用失败，事务回滚
-- 如果返回为1，调用成功，事务提交
-- 
-- 测试方法
-- call pro_rep_shadow_rs(@rtn);
-- select @rtn;
----------------------------------
create procedure pro_rep_shadow_rs(out rtn int)
begin
  -- 声明变量，所有的声明必须在非声明的语句前面
  declare iLast_rep_sync_id int default -1;
  declare iMax_rep_sync_id int default -1;

  -- 如果出现异常，或自动处理并rollback,但不再通知调用方了
  -- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉
  declare exit handler for sqlexception rollback;

  -- 查找上一次的同步ID
  select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';

  -- 如果不存在，则增加一行记录以初始化
  if iLast_rep_sync_id=-1 then
    insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');
    set iLast_rep_sync_id = 0;
  end if;

  -- 计算下一个待处理的起始ID
  set iLast_rep_sync_id=iLast_rep_sync_id+1;

  -- 设置默认的返回值为0：失败
  set rtn=0;

  -- 启动事务
  start transaction;

  -- 查找当前表中最大的同步ID
  select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;

  -- 如果存在新数据（最大ID大于等于起始ID），则执行处理
  if iMax_rep_sync_id>=iLast_rep_sync_id then
    -- 调用子存储过程进行实际数据处理
    call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
    -- 更新日志，记录本次处理的起止ID
    update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
  end if;

  -- 运行没有异常，提交事务
  commit;
  -- 设置返回值为1表示成功
  set rtn=1;
end;
delimiter ;

第2步：创建嵌套调用的子存储过程（游标处理）

子过程负责具体的数据清洗工作。它接收起止ID范围，利用游标遍历数据，并根据 Rep_operationtype 字段区分插入（'I'）、更新（'U'）和删除（'D'）操作。

drop procedure if exists pro_rep_shadow_rs_do;

delimiter |
---------------------------------
-- 处理指定编号范围内的数据
-- 需要输入2个参数
-- last_rep_sync_id 是编号的最小值
-- max_rep_sync_id 是编号的最大值
-- 无返回值
---------------------------------
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)
begin
  declare iRep_operationtype varchar(1);
  declare iRep_status varchar(1);
  declare iRep_Sync_id int;
  declare iId int;

  -- 这个用于处理游标到达最后一行的情况
  declare stop int default 0;
  -- 声明游标：查询指定范围内的数据
  declare cur cursor for 
    select id, Rep_operationtype, iRep_status, rep_sync_id 
    from rep_shadow_rs 
    where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
  
  -- 声明游标的异常处理，当找不到更多行时设置终止标记
  declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

  -- 打开游标
  open cur;

  -- 读取第一行数据到变量
  fetch cur into iId, iRep_operationtype, iRep_status, iRep_Sync_id;

  -- 循环处理直到游标结束
  while stop <> 1 do
    -- 根据操作类型执行不同逻辑
    if iRep_operationtype='I' then
      -- 新增操作：插入到目标表
      insert into rs0811 (id,fnbm) 
      select id, fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
      
    elseif iRep_operationtype='U' then
      begin
        if iRep_status='A' then
          -- 更新状态为A：视为新增，执行插入
          insert into rs0811 (id,fnbm) 
          select id, fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
        elseif iRep_status='B' then
          -- 更新状态为B：视为删除，从目标表移除
          delete from rs0811 where id=iId;
        end if;
      end;
      
    elseif iRep_operationtype='D' then
      -- 删除操作：直接从目标表移除
      delete from rs0811 where id=iId;
    end if;

    -- 读取下一行的数据
    fetch cur into iId, iRep_operationtype, iRep_status, iRep_Sync_id;
  end while; -- 循环结束
  
  close cur; -- 关闭游标
end;
delimiter ;

关键特性说明

事务管理与异常回滚

在主过程中使用了 start transaction 和 commit。配合 declare exit handler for sqlexception rollback;，一旦在执行过程中发生任何SQL异常，事务将自动回滚，保证数据一致性。此时输出参数 rtn 保持为初始值0，调用方可据此判断失败。

游标的使用规范

在子过程中，游标声明必须位于普通变量声明之后。关键在于 DECLARE CONTINUE HANDLER，它捕获了SQLSTATE '02000'（即No Data Found），从而优雅地结束循环，避免程序报错退出。

部署与验证

完成上述脚本执行后，可通过以下方式测试存储过程的返回值：

1. 调用主存储过程：call pro_rep_shadow_rs(@rtn);
2. 查看输出参数：select @rtn;

若返回1，表示事务成功提交；若返回0或触发异常，表示处理失败且已回滚。

以上就是MySQL存储过程实现事务、输出参数及嵌套调用的详细内容，更多关于MySQL高级特质的资料请关注本站其它相关文章！