This thread contains some useful tips/samples regarding some advance concepts in cursors.
FEW MORE EXAMPLES
===================
-
declare
-
er emp%rowtype;
-
cursor c1 is select * from emp;
-
begin
-
open c1;
-
loop
-
fetch c1 into er;
-
exit when c1%notfound;
-
if er.job='SALESMAN' then
-
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||er.sal*1.10);
-
-
elsif er.job='CLERK' then
-
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||er.sal*1.08);
-
-
else
-
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||'No ince req.');
-
end if;
-
-
end loop;
-
close c1;
-
end;
-
NOTE:--PLEASE TRY THE ABOVE CODE IN SCOTT SCHEMA
EXAMPLE #1
--------------------
-
begin
-
--no need to open and close.
-
for dr in (select * from dept) loop
-
dbms_output.put_line(dr.deptno||' '||dr.dname||' '||dr.loc);
-
end loop;
-
end;
-
EXAMPLE #2
----------------------
-
declare
-
--cursor is declared
-
cursor c1 is select * from dept;
-
-- a cur type variable is declared
-
dr c1%rowtype;
-
begin
-
-- open the cursor
-
open c1;
-
--fetch into the target variable
-
fetch c1 into dr;
-
-- check for existance of more dat in the cursor
-
while (c1%found=TRUE) loop
-
fetch c1 into dr;
-
dbms_output.put_line(dr.deptno||' '||dr.dname||' '||dr.loc);
-
--end the loop
-
end loop;
-
--close the cursor.
-
close c1;
-
end;
-
SAMPLE PROGRAM TO SHOW USE OF RECORD TYPE IN CURSOR
-----------------------------------------------------------------------------------------------------------
-
declare
-
TYPE MYTYPE IS RECORD
-
(
-
MENAME VARCHAR2(10),
-
MMGR NUMBER(4)
-
);
-
mm mytype;
-
CURSOR C1 IS
-
select ename,mgr from emp where deptno=20 and job='CLERK';
-
begin
-
open C1;
-
LOOP
-
FETCH C1 INTO mm.mename,mm.mmgr;
-
EXIT WHEN C1%NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE(mm.mename || ' '|| mm.mmgr);
-
END LOOP;
-
CLOSE C1;
-
END;
-
Also Check
PL/SQL-CURSOR - 5 
