How to create multiple loops in plsql procedure
declare
cursor c_job is select * from job;
r_job c_job%ROWTYPE;
cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;
r_emp c_empInjob%ROWTYPE;
begin
open c_job;
loop
fetch c_job into r_job;
exit when c_job%NOTFOUND;
open c_empInjob (r_job.empno);
loop
fetch c_empInjob into r_emp;
exit when c_empInjob%NOTFOUND;
end loop;
close c_empInjob;
end loop;
close c_job;
end;
/
reference :
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Placingcursorsinnestedloops.htm
cursor c_job is select * from job;
r_job c_job%ROWTYPE;
cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;
r_emp c_empInjob%ROWTYPE;
begin
open c_job;
loop
fetch c_job into r_job;
exit when c_job%NOTFOUND;
open c_empInjob (r_job.empno);
loop
fetch c_empInjob into r_emp;
exit when c_empInjob%NOTFOUND;
end loop;
close c_empInjob;
end loop;
close c_job;
end;
/
reference :
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Placingcursorsinnestedloops.htm
Comments
Post a Comment