mysql 存储过程中双重循环嵌套的应用
程序员文章站
2022-09-03 19:59:22
话不多直接上代码,注释很详细,下面代码确认运行无误,自己去感悟吧朋友 CREATE PROCEDURE SYC_FILE_DOC_INFO() BEGIN DECLARE A_PK_FILEDOC char(24); DECLARE A_PK_CORP char(6); DECLARE A_VCOD ......
话不多直接上代码,注释很详细,下面代码确认运行无误,自己去感悟吧朋友
create procedure syc_file_doc_info() begin declare a_pk_filedoc char(24); declare a_pk_corp char(6); declare a_vcode varchar(30) default '' ; declare a_vname varchar(100) default '' ; declare a_vmemo varchar(200) default '' ; declare a_issealed char(1); declare a_dr numeric(8,0) default 0; declare a_ts timestamp; declare a_isleaf char(1); declare a_filelevel numeric(8,0) default 0; declare a_ifiletype numeric(8,0) default 0; declare b_pk_corp char(6); -- 定义结束标识 declare done int default 0; declare edone int default 0; begin -- 定义游标v_kf_datas以及结束标识 declare v_kf_datas cursor for select distinct pk_corp from ynt_kfiledoc y where ifnull(dr,0) = 0; declare continue handler for not found set done = 1;-- 结束标识 open v_kf_datas;-- 打开游标 vkfdatasloop:loop -- 循环游标开始 fetch v_kf_datas into b_pk_corp; if done = 1 then leave vkfdatasloop;#跳出循环 else set edone = 0;-- !!!!!重置标识 begin -- 定义游标v_file_data以及结束标识 declare v_file_data cursor for select * from ynt_filedoc y where y.pk_corp = '000001' and y.vcode like '99%'; declare continue handler for not found set edone = 1;#结束标识 open v_file_data;-- 打开游标 vfiledataloop:loop -- 循环游标开始 fetch v_file_data into a_pk_filedoc,a_pk_corp,a_vcode,a_vname,a_vmemo,a_issealed,a_dr,a_ts,a_isleaf,a_filelevel,a_ifiletype; if edone = 1 then leave vfiledataloop; else -- 对表的操作 insert into ynt_kfiledoc(pk_kfiledoc, pk_corp, pk_source, vcode, vname, vmemo, issealed, dr, ts, isleaf, filelevel, ifiletype) values(concat(b_pk_corp,substring(a_pk_filedoc,7, 12),fn_get_random_str(6)),b_pk_corp, a_pk_filedoc, a_vcode, a_vname, a_vmemo, a_issealed, 0, a_ts, a_isleaf, a_filelevel, a_ifiletype); end if; end loop; close v_file_data;-- 关闭游标 end; end if; end loop; -- 结束游标 close v_kf_datas;-- 关闭游标 commit; end; end;