最近业务需要翻阅了很多关于游标遍历使用的帖子 总结一下 直接上代码吧
BEGIN
/**
净值=固定资产原值-累计折旧
累计折旧=月折旧额*已计提月份
月折旧额=(固定资产原价-(固定资产原价*3%的残值率))/预计使用月限
(10000-(10000*3%))/360=26.94
**/
/**更新净值,累计折旧,月折旧额,已计提月份**/
DECLARE myid VARCHAR(100); -- id
DECLARE myybyz INT; -- 原币原值
DECLARE mykssyrq VARCHAR(36); -- 开始使用日期
DECLARE mysyyx INT; -- 使用月限
DECLARE yjtyf INT; -- 已计提月份
DECLARE yzje DECIMAL(16,2); -- 月折旧额
DECLARE ljzj DECIMAL(16,2); -- 累计折旧
DECLARE jz DECIMAL(16,2); -- 累计折旧
DECLARE a INT;
DECLARE b INT;
DECLARE c INT;
DECLARE d INT;
DECLARE done INT DEFAULT 0; -- 标记默认为0
DECLARE lists CURSOR FOR SELECT ID,YBYZ,KSSYRQ,SYYX FROM BO_EU_BPM_SWKZJB; -- 游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 没有下一条数据 修改标记为1
OPEN lists;
FETCH NEXT FROM lists INTO myid,myybyz,mykssyrq,mysyyx;
WHILE (done<>1) DO
BEGIN
SELECT year(NOW()) INTO a;
SELECT year(mykssyrq) INTO b;
SELECT month(NOW()) INTO c;
SELECT month(mykssyrq) INTO d;
SELECT (a-b)*12+(c-d)-1 INTO yjtyf ; -- 已计提月份
SELECT round((myybyz-(myybyz*0.03))/mysyyx,2) INTO yzje; -- 月折旧额
IF (yjtyf >= mysyyx)
THEN
UPDATE BO_EU_BPM_SWKZJB SET JZ=0,LJZJ=myybyz,YZJE=yzje,YJTYF=yjtyf WHERE ID = myid; -- 更新数据库
ELSE
SELECT yzje*yjtyf INTO ljzj; -- 累计折旧
SELECT myybyz-ljzj INTO jz; -- 净值
UPDATE BO_EU_BPM_SWKZJB SET JZ=jz,LJZJ=ljzj,YZJE=yzje,YJTYF=yjtyf WHERE ID = myid; -- 更新数据库
END IF;
END;
FETCH NEXT FROM lists INTO myid,myybyz,mykssyrq,mysyyx;
END WHILE;
CLOSE lists;
END
IN `bindid_in` char(36) --输入参数
BEGIN
/**根据LBBM 查询表中最大的 ZCBM
找到后加+1 赋值进去
如果没有 则 LBBM+0001 在赋值进去
根据类别查编码找财务库最大的编码+1 更新到财务库和实物库**/
DECLARE cwid VARCHAR(36); -- id
DECLARE swid VARCHAR(36); -- id
DECLARE mylbbm VARCHAR(36); -- 类别编码
DECLARE myzcbm VARCHAR(36); -- 资产编码
DECLARE nmyzcbm VARCHAR(36);-- 资产编码+1
DECLARE done INT DEFAULT 0; -- 标记默认为0
DECLARE lists CURSOR FOR select ID,LBBM from BO_EU_BPM_CWKPZJB WHERE YY_ID= bindid_in; -- 游标(财务表)
DECLARE lists1 CURSOR FOR select ID from BO_EU_BPM_SWKZJB WHERE YY_ID= bindid_in; -- 游标(实物表)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 没有下一条数据 修改标记为1
OPEN lists;
OPEN lists1;
FETCH NEXT FROM lists INTO cwid,mylbbm;
FETCH NEXT FROM lists1 INTO swid;
WHILE (done<>1) DO
BEGIN
SELECT MAX(ZCBM) INTO myzcbm FROM BO_EU_BPM_CWKPZJB WHERE LBBM = mylbbm;
IF myzcbm =''
THEN
SELECT CONCAT(mylbbm,'0000') INTO myzcbm;
SELECT (CAST(myzcbm AS SIGNED)+1)+'' INTO nmyzcbm;
UPDATE BO_EU_BPM_CWKPZJB set ZCBM = nmyzcbm,SWZCBM = CONCAT(nmyzcbm,'001') WHERE ID = cwid;
UPDATE BO_EU_BPM_SWKZJB set ZCBM = nmyzcbm,SWZCBM = CONCAT(nmyzcbm,'001') WHERE ID = swid;
ELSE
SELECT (CAST(myzcbm AS SIGNED)+1)+'' INTO nmyzcbm;
UPDATE BO_EU_BPM_CWKPZJB set ZCBM = nmyzcbm,SWZCBM = CONCAT(nmyzcbm,'001') WHERE ID = cwid;
UPDATE BO_EU_BPM_SWKZJB set ZCBM = nmyzcbm,SWZCBM = CONCAT(nmyzcbm,'001') WHERE ID = swid;
END IF;
END;
FETCH NEXT FROM lists INTO cwid,mylbbm;
FETCH NEXT FROM lists1 INTO swid;
END WHILE;
CLOSE lists;
CLOSE lists1;
END
/*双游标while循环遍历if嵌套真实案例*/
此文仅供参考学习,顺便留个痕迹,方便查阅,如有不足,还请多多指教.
