PL/SQL-CURSOR - 3

[debasisdas]

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