oracle在存储过程中写两个游标嵌套循环
CREATE OR REPLACE
PROCEDURE "PROC_CHZBANKMOTNREC" AS
vid NUMBER(19);--id
vzdrpriaccount VARCHAR2(12);
vzdrpriaccount2 VARCHAR2(12);
vamountNum NUMBER;--出现次数
vrentMonth VARCHAR2(8);
oracle游标的使用vrentMonth2 VARCHAR2(8);
vamount number;
---定义第⼀个游标-----
CURSOR cur_chz2 IS
SELECT ZDRPRIACCOUNT FROM 表名 GROUP BY ZDRPRIACCOUNT HAVING count(ZDRPRIACCOUNT)>1;
---定义第⼆个游标-----
CURSOR cur_chz1 IS
SELECT BANK.id,BANK.ZDRPRIACCOUNT,BANK.RENTMONTH FROM 表名  bank WHERE BANK.WTSCODE IS NOT NULL    AND BANK.ZDRPRIACCOUNT IN (SELECT ZDRPRIACCOUNT FROM 表名    GROUP BY ZDRPRIACCOUNT HAVING count(ZDRPRIACCOUNT)>1)
ORDER BY BANK.id DESC;
BEGIN
-------打开游标cursor------
open cur_chz1;
-
-----开始循环------
loop
--------fetch 赋值------------
FETCH cur_chz1 into vid,vzdrpriaccount,vrentMonth;
exit when cur_chz1%notfound;
---打开第⼆个游标cursor-----------------------
open cur_chz2;
loop
FETCH cur_chz2 into vzdrpriaccount2;
exit when cur_chz2%notfound;
dbms_output.put_line('Hello'||totalPaidAmount|| '!');
END loop;
--------------- 这⾥很重要------------
if cur_chz2%isopen then
close cur_chz2;
end if;
END loop;
if cur_chz1%isopen then
close cur_chz1;
end if;
END;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。