select * from empinfo;
"ID" "FNAME" "LNAME" "DEPTNO"
1 "arv" "red" 100
2 "sam" "kum" 200
3 "tom" "com" 100
4 "Hef" "Kom" 200
5 "fam" "ily" 300
select * from deptinfo;
"ID" "DEPTNO" "DEPTNAME" "MGR"
1 100 "HR" "Bill"
2 200 "IT" "Greg"
3 300 "SALES" "Phil"
-- sqlcode starts here
declare
CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;
CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;
OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('REC1.id: '||REC1.ID||' belongs to dept ID : '||REC2.DEPTNO||' DEPT :'||REC2.DEPTNAME);
END LOOP;
CLOSE CUR2;
end loop;
close CUR1;
end;
