CREATE OR REPLACE
PROCEDURE "PROC_CHZBANKMOTNREC" AS
vid NUMBER(19);--id
vzdrpriaccount VARCHAR2(12);
vzdrpriaccount2 VARCHAR2(12);
vamountNum NUMBER;--出现次数
vrentMonth VARCHAR2(8);
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;