Wednesday, July 16, 2008

Oracle: How to use CURSOR Expression in PL/SQL


CREATE OR REPLACE Procedure aaa as
Cursor cCtry Is Select countrykey,countrycode ,cursor (Select casereference from cases c where c.countrykey = t.countrykey )

from tablecountry t;
nCtrykey tablecountry.countrykey%type;
sCtryCode tablecountry.countrycode%type ;
caseee sys_refcursor;
type tCase_ref is table of cases.casereference%type index by pls_integer;
tCaseRef tCase_ref;
begin
Open cCtry;
loop
Fetch cCtry into nCtrykey,sCtryCode,caseee;
exit when cCtry%notfound;

fetch caseee bulk collect into tCaseRef;
for i in tCaseRef.first.. tCaseRef.last loop
null;
--Do Something
end loop;

end loop;
Close cCtry;
end;
/

No comments:

Post a Comment