mysql游标使用中的一些推断

最新推荐文章于 2025-06-11 09:17:35 发布

潭影空人心

最新推荐文章于 2025-06-11 09:17:35 发布

阅读量382

点赞数

CC 4.0 BY-SA版权

分类专栏： DB

本文链接：https://blog.youkuaiyun.com/zlk252620068/article/details/81229318

DB 专栏收录该内容

6 篇文章

订阅专栏

在MySQL中使用游标进行数据校验时遇到问题，如多个游标在同一存储过程中失效。若在不同表上操作，设置done=1可解决；若在本表操作，则可能导致其他游标失效。为避免此类问题，可以考虑将存储过程拆分为多个，每个单独处理一个游标。调试MySQL存储过程困难，推荐使用dbForge Studio for MySQL。Navicat虽然方便，但校验不强，错误会导致后续异常。需要逐步试验和学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql游标使用过程中，坑太多，只能一步一步摸索，在一个存储中，定义了多个游标，用于数据校验，一般都会定义

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1,done1 = 1,done2 = 1;
或
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

现有这么一个场景，先从表中查询定义多个游标，然后遍历游标，更新表中未在游标中的字段，当第一个游标遍历完成后，后面的游标都失效了，即fetch时拿不到值了，通过和其它场景对比，推测如下：

①定义多个游标，用来遍历，在其它的表上进行insert，update,对定义的游标无影响，第一次游标遍历结束后，遍历第二个游标时，设置：

set done = 1 即可。

②如果还在本表上进行insert、update，那么其它的游标将失效，游标未打开时，进行insert或update并不造成影响，一旦一个游标打开，遍历，当结束时，其余游标失效。

此种情况下，即使在同一个存储中定义多个begin...end块，并未起作用。

一种思路如下：

可存储拆分，定义多个存储过程，每个里面定义一个游标，处理对应的逻辑，然后将多个存储按顺序依次调用。

mysql的存储调试比较麻烦，不好加断点进行debug，dbforge studio for mysql 这个工具值得一试。

编写mysql存储时，采用 navicate，校验比较弱，有些错误校验不出来，但执行时，只要一条出错，之后整个的过程都会出现比较诡异的结果，if判断无法正常进行，程序会一条一条执行，即使是查询，也查不出对应的结果，给程序调试带来难度。

情景一：

-- 按顺序定义
DECLARE done INT DEFAULT 0;
DECLARE done1 INT DEFAULT 0;
DECLARE done2 INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT serid,seqnum,col2 from tbl_base_import where serid = in_batchid and seqnum > 2;
DECLARE cur2 CURSOR FOR SELECT i.serid,i.seqnum,i.col7 from tbl_base_import i where i.serid = in_batchid and i.seqnum > 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1,done1 = 1,done2 = 1;

select count(1) into v_total_count from tbl_base_import i where i.serid=in_batchid and i.seqnum > 2;
select count(1) into v_count from tbl_base_import i,tbl_base_dept d where i.serid=in_batchid and i.seqnum > 2 and trim(d.dept_name) = trim(i.col2) and d.status = '0';
if v_total_count != v_count then
open cur1;
repeat
 fetch cur1 into v_serid,v_seqnum,v_col2;
  if not done then 
  	select count(1) into v_count_per from tbl_base_dept d where trim(d.dept_name) = trim(v_col2) and d.status = '0';
  	if v_count_per = 0 then 
  		insert into tbl_party_import_error(id,excelcol,excelrow,error,batch_id,error_type)values(fun_base_getid(),'C',(v_seqnum+1),'所在党支部错误',v_serid,'error');
  	end if;
  end if;
 until done end repeat;
close cur1;
end if;

set done1 = 0; -- 重新声明
select count(1) into v_total_count from tbl_base_import i where i.serid=in_batchid and i.seqnum > 2;
select count(1) into v_count from tbl_base_import i,tbl_base_code_tree t where i.serid=in_batchid and i.seqnum > 2 and trim(t.base_name) = trim(i.col7) and t.status = '0';
if v_total_count != v_count then
open cur2;
repeat
 fetch cur2 into v_serid,v_seqnum,v_col2;
  if not done1 then
    select count(1) into v_count_per from tbl_base_code_tree t where trim(t.base_name) = trim(v_col2) and t.status = '0' and t.base_type = 'education';
	if v_count_per = 0 then
		insert into tbl_party_import_error(id,excelcol,excelrow,error,batch_id,error_type)values(fun_base_getid(),'H',(v_seqnum+1),'学历错误',v_serid,'error');
	end if;
  end if;
 until done1 end repeat;
close cur2;
end if;

情景二：

DECLARE cur3 CURSOR FOR SELECT distinct i.col1 from tbl_base_import i where i.serid = in_batchid and i.seqnum > 2;
DECLARE curp CURSOR FOR select distinct d.DEPT_NAME from tbl_base_import i,tbl_base_dept d where i.COL0 = d.DEPT_NAME and d.`STATUS` = '0' and i.SERID = in_batchid and i.SEQNUM > 2; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1，done1=1;

select count(1) into v_count from (select distinct i.col1 from tbl_base_import i where i.serid=in_batchid and i.seqnum > 2) t;
select count(1) into v_total_count from tbl_base_import i where i.serid=in_batchid and i.seqnum > 2;
if v_count != v_total_count then 	
	open cur3;
	repeat
	 fetch cur3 into v_col2;
	  if not done then
	  -- simple loop
	   REPEAT
	   select count(1) into v_count2 from tbl_base_import i where i.COL1 = v_col2 and i.col13 = '0' AND i.SERID = in_batchid AND i.SEQNUM > 2; 
	    if v_count2 > 1 then		
	     select seqnum,col12 into v_seq,v_id from tbl_base_import i where i.COL1 = v_col2 and i.serid=in_batchid and i.seqnum>2 and i.col13='0' limit 1; 
	     update tbl_base_import set col13 = '1' where serid = in_batchid and seqnum = v_seq;  -- 更新定义游标的表中其它字段
	    end if;
	   UNTIL v_count2 = 1 OR v_count2 = 0 END REPEAT;
	  end if;
	 until done end repeat;
	close cur3;			
end if;

SET done1 = 0;
 open curp;
  repeat
   fetch curp into v_colp; -- 此时并未fetch出值
    if not done1 THEN
      SELECT COUNT(1) into v_count4 FROM tbl_base_import tbi WHERE tbi.SERID = in_batchid AND tbi.SEQNUM > 2 AND tbi.COL0 = v_colp AND tbi.col13 = '0';
      IF v_count4 > 0 THEN 
    -- v_col2 父
     SELECT distinct d.dept_id into v_id from tbl_base_import i,tbl_base_dept d, tbl_party_dept pd where d.dept_id = pd.dept_id and d.`status` = '0' and i.COL0 = d.dept_name and i.SERID = in_batchid and i.SEQNUM > 2 and d.dept_name = v_colp;
     if v_id is not null and v_id <> '' then
         update tbl_base_import set col14 = v_id where serid=in_batchid and col13='0' and SEQNUM > 2 and col0 = v_colp;  -- 更新pid
         select max(d.DEPT_CODE) into v_code from tbl_base_dept d where d.DEPT_PARENT_ID = v_id;
	 -- 最末级
	     if v_code is null then 
		 select d.DEPT_CODE into v_code2 from tbl_base_dept d where d.DEPT_ID = v_id;
	         select concat(v_code2,lpad('1',4,'0')) into v_code3 from dual;
		 set v_code2 = v_code3;
		 select i.col12 into v_tem_id from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp limit 1;
		 update tbl_base_import set col15 = v_code2 where serid=in_batchid and col13='0' and SEQNUM > 2 and col12 = v_tem_id; 
		     -- 更新剩下的
		     -- simple loop
		     repeat
		     select count(1) into v_count3 from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col15 is null and i.col0 = v_colp;
		     if v_count3 > 0 then
		        select max(i.col15) into v_code3 from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp;
		        select col12 into v_tem_id from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp and i.col15 is null limit 1;
		        update tbl_base_import set col15 = fun_pbbase_init_code(v_code3) where serid=in_batchid and col13='0' and SEQNUM > 2 and col12 = v_tem_id; 
		     end if;
		     until v_count3 = 0 end repeat;
	     else
	 -- 中间级
	        set v_code2 = fun_pbbase_init_code(v_code);
	        select col12 into v_tem_id from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp limit 1;
	        update tbl_base_import set col15 = v_code2 where serid=in_batchid and col13='0' and SEQNUM > 2 and col12 = v_tem_id; 
	             -- 更新剩下的
	             -- simple loop
		     repeat
		     select count(1) into v_count3 from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp and i.col15 is null;
		     if v_count3 > 0 then
		        select max(i.col15) into v_code3 from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp;
		        select col12 into v_tem_id from tbl_base_import i where i.serid=in_batchid and i.seqnum>2 and i.col13='0' and i.col0 = v_colp and i.col15 is null limit 1;
		        update tbl_base_import set col15 = fun_pbbase_init_code(v_code3) where serid=in_batchid and col13='0' and SEQNUM > 2 and col12 = v_tem_id; 
		     end if;
		     until v_count3 = 0 end repeat;
	     end if;
     end if;
    END IF;
    end if;
   until done1 end repeat;
 close curp;
END

总之，需要一步一步去探索。