Oracle 游标混合使用
2023-12-21 09:30:20
存储过程：cursor_4 （cursor_1?+cursor_2?+cursor_3?）
CREATE OR REPLACE PROCEDURE TKSWSHRZS.PROC_BBBBB(in_FYear NUMBER ,in_FPeriod NUMBER )
IS
v_name varchar2(100) :='';
v_adminnumber varchar2(100) :='';
v_Company_FNumber VARCHAR2(100) :='';
v_Company_FNumber_sub VARCHAR2(100) :='';
v_Company_FNumber_4 VARCHAR2(100) :='';
v_FYear number(10,0) := 0;
v_FPeriod number(10,0) := 0;
v_sum number(18,2) := 0;
cur_s1 number(18,2) := 0;
cur_s7 number(18,2) := 0;
cur_chaoE number(18,2) := 0; ---超额奖----
v_chaoE_sj number(18,2) := 0; ---超额奖----
cur_yuSuan number(18,2) := 0; ---预算
v_yuSuan number(18,2) := 0; ---预算----
CURSOR cursor_4 IS select FNUMBER from T_ORG_Admin where LENGTH(FLONGNUMBER )=4 order by FLONGNUMBER;
CURSOR cursor_1 IS
SELECT admin.FNUMBER ,
SCTABLE.FPERIODYEAR,
sum(NVL(scTable.s1,0) ),
SUM(NVL(scTable.s7,0) )
FROM T_HR_SCmpCalTable scTable
INNER JOIN T_ORG_Admin admin ON admin.fid=scTable.fadminorgUnitid
INNER JOIN T_HR_SCALSCHEME scheme on scTable.fcalschemeid=scheme.fid
WHERE
scTable.FPERIODYEAR = in_FYear ----in_FYear
and scTable.FPERIODMONTH <= in_FPeriod ----in_FPeriod
AND admin.fnumber LIKE CONCAT(v_Company_FNumber_4,'%') --CONCAT('--- --组织 -----',v_Company_FNUMBER) --'02%'
group by admin.fnumber,scTable.FPERIODYEAR ORDER BY admin.fnumber;
CURSOR cursor_2 IS
SELECT t5731.adminnumber as adminnumber,
t5731.nianduz as nianduz ,
sum( NVL(t5731.M4HP5,0)
+ NVL(t5731.M2HP3JO4,0)
+ NVL(t5731.M1HP2JYXO3JYX,0)
+ NVL(t5731.M3HP4JO5,0)
+ NVL(t5731.M8HM9,0)
+ NVL(t5731.M5DM7HP6JYS,0)
) as chaoE
FROM T_MGR_ManagerEx20230920125731 t5731
where
t5731.nianduz = in_FYear --今年 in_FYear
AND t5731.adminnumber = v_Company_FNumber_4
group by t5731.adminnumber, t5731.nianduz ORDER BY t5731.adminnumber ;
CURSOR cursor_3 IS
SELECT t405.adminnumber ,
t405.nianduz ,
sum( NVL(t405.xinchouZE,0) )
FROM T_MGR_ManagerEx20230920130405 t405
where
t405.nianduz = in_FYear --今年 in_FYear
AND t405.adminnumber = v_Company_FNumber_4
group by t405.adminnumber, t405.nianduz ORDER BY t405.adminnumber ;
BEGIN
v_FYear := in_FYear;
v_FPeriod := in_FPeriod;
DELETE FROM "t_GZYSZXQK_TBBBBB" WHERE FYEAR = v_FYear AND fperiod=in_FPeriod;
-- DELETE FROM t_GZYSZXQK_TBBBBB WHERE YEAR = v_FYear;
-- 外层循环-----组织
dbms_output.put_line('----------第 4 个游标------开始-----外层循环-----组织 -----');
begin
--循环开始
LOOP
IF NOT cursor_4%ISOPEN THEN
OPEN cursor_4;
END IF;
FETCH cursor_4 INTO v_Company_FNumber_4 ;
--退出循环的条件
EXIT WHEN cursor_4%NOTFOUND OR cursor_4%NOTFOUND IS NULL;
dbms_output.put_line(CONCAT('----------第 4 个游标-----------外层循环-----组织 -----',v_Company_FNumber_4));
v_sum := 0;
----第一个游标
dbms_output.put_line('----------第 1 个游标------开始----');
begin
--循环开始
LOOP
IF NOT cursor_1%ISOPEN THEN
OPEN cursor_1;
END IF;
FETCH cursor_1 INTO v_Company_FNUMBER ,
v_FYear ,
cur_s1 ,
cur_s7 ;
--退出循环的条件
EXIT WHEN cursor_1%NOTFOUND OR cursor_1%NOTFOUND IS NULL;
dbms_output.put_line(CONCAT('----第 1 个游标-----v_Company_FNUMBER--------',v_Company_FNUMBER));
v_sum := v_sum + cur_s1 + cur_s7;
END LOOP;
CLOSE cursor_1;
END;
dbms_output.put_line('----------第 1 个游标------结束----');
v_chaoE_sj := 0 ;
----第 2 个游标
dbms_output.put_line('----------第 2 个游标------开始----');
begin
--循环开始
LOOP
IF NOT cursor_2%ISOPEN THEN
OPEN cursor_2;
END IF;
FETCH cursor_2 INTO v_adminnumber ,
v_FYear ,
cur_chaoE;
--退出循环的条件
EXIT WHEN cursor_2%NOTFOUND OR cursor_2%NOTFOUND IS NULL;
dbms_output.put_line(CONCAT('-----第 2 个游标--------v_adminnumber--------',v_adminnumber));
v_chaoE_sj := v_chaoE_sj + cur_chaoE;
END LOOP;
CLOSE cursor_2;
END;
dbms_output.put_line('----------第 2 个游标------结束----');
v_yuSuan := 0;
----第3个游标
dbms_output.put_line('----------第 3 个游标------开始----');
begin
--循环开始
LOOP
IF NOT cursor_3%ISOPEN THEN
OPEN cursor_3;
END IF;
FETCH cursor_3 INTO v_adminnumber ,
v_FYear ,
cur_yuSuan;
--退出循环的条件
EXIT WHEN cursor_3%NOTFOUND OR cursor_3%NOTFOUND IS NULL;
dbms_output.put_line(CONCAT('-----第 3 个游标--------v_adminnumber--------',v_adminnumber));
v_yuSuan := v_yuSuan + cur_yuSuan;
END LOOP;
CLOSE cursor_3;
END;
dbms_output.put_line('----------第 3 个游标------结束----');
if( v_yuSuan != 0) then
-- INSERT INTO TKSWSHRZS."t_GZYSZXQK_TBBBBB" -----工资预算执行情况
-- (ADMINNUMBER, "FYEAR",FPERIOD, YUSUAN, SHIJI, CHAE, ZHIXINGBILI, CREATETIME)
-- VALUES(v_Company_FNumber_4,in_FYear,in_FPeriod, v_yuSuan, v_sum+v_chaoE_sj, v_yuSuan-(v_sum+v_chaoE_sj), ROUND((v_sum+v_chaoE_sj)/ v_yuSuan , 6) , SYSDATE);
--
INSERT INTO TKSWSHRZS."t_GZYSZXQK_TBBBBB" -----工资预算执行情况
(ADMINNUMBER, "FYEAR",FPERIOD, YUSUAN, SHIJI, CHAE, ZHIXINGBILI, CREATETIME)
VALUES(v_Company_FNumber_4,in_FYear,in_FPeriod, v_yuSuan, v_sum+v_chaoE_sj, v_yuSuan-(v_sum+v_chaoE_sj), CONCAT(TO_CHAR( ROUND((v_sum+v_chaoE_sj)/ v_yuSuan , 4) *100 ) ,'%') , SYSDATE);
-- SELECT CONCAT(TO_CHAR( ROUND(36385618.93/ 18040000 , 4) *100 ) ,'%') FROM dual;
else
INSERT INTO TKSWSHRZS."t_GZYSZXQK_TBBBBB" -----工资预算执行情况
(ADMINNUMBER, "FYEAR",FPERIOD, YUSUAN, SHIJI, CHAE, ZHIXINGBILI, CREATETIME)
VALUES(v_Company_FNumber_4,in_FYear,in_FPeriod, v_yuSuan, v_sum+v_chaoE_sj, v_yuSuan-(v_sum+v_chaoE_sj), 0, SYSDATE);
end if;
END LOOP;
CLOSE cursor_4;
END;
dbms_output.put_line('----------第 4 个游标------结束----');
--
--EXCEPTION
--when others then
-- P_RESULT:='插入失败';
end PROC_BBBBB;
--
--declare
-- FYEAR number(10) ;
-- FPERIOD number(10) ;
--begin
-- FYEAR := 2023;
-- FPERIOD := 10;
-- PROC_BBBBB( FYEAR , FPERIOD );
--end;
--
文章来源:https://blog.csdn.net/t15263857960/article/details/135122298
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！