Sample example to show FOR UPDATE CURSOR
-----------------------------------------------------------------------------
-
DECLARE
-
CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL;
-
MYREC EMPREC%ROWTYPE;
-
NUM NUMBER(4);
-
BEGIN
-
OPEN EMPREC;
-
LOOP
-
FETCH EMPREC INTO MYREC;
-
EXIT WHEN EMPREC%NOTFOUND;
-
NUM:=EMPREC%ROWCOUNT;
-
UPDATE EMP SET SAL=4000 WHERE CURRENT OF EMPREC;
-
END LOOP;
-
CLOSE EMPREC;
-
DBMS_OUTPUT.PUT_LINE(NUM ||' RECORDS UPDATED');
-
END;
-
Sample example to show PARAMETARISED CURSOR
-----------------------------------------------------------------------------------
-
DECLARE
-
RR EMP%ROWTYPE;
-
CURSOR C1 (MDEPTNO NUMBER,MJOB VARCHAR) IS
-
SELECT * FROM EMP WHERE DEPTNO=MDEPTNO AND JOB=MJOB;
-
BEGIN
-
OPEN C1(20,'CLERK');
-
LOOP
-
FETCH C1 INTO RR;
-
EXIT WHEN C1%NOTFOUND;
-
DBMS_OUTPUT.PUT_LINE(RR.ENAME);
-
END LOOP;
-
CLOSE C1;
-
END;
-
parameter can be passed to a cursor
so the cursor partially acts like a IN mode procedure.
PARAMETER is passed at the time of OPENing the cursor.
SAMPLE PROGRAM SO SHOW USE OF CURSOR WITHIN CURSOR
================================================== ==
-
declare
-
cursor c1 is select deptno from dept order by deptno;
-
cursor c2 (mm dept.deptno%type) is select ename from emp where deptno=mm;
-
begin
-
for mrec in c1 loop
-
for mrec1 in c2(mrec.deptno) loop
-
dbms_output.put_line(mrec1.ename);
-
end loop;
-
end loop;
-
end;
-
Also Check
PL/SQL-CURSOR - 4 
