By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,382 Members | 2,032 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

PL/SQL-CURSOR - 3

debasisdas
Expert 5K+
P: 8,127
Sample example to show FOR UPDATE CURSOR
-----------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL;
  3. MYREC EMPREC%ROWTYPE;
  4. NUM NUMBER(4);
  5. BEGIN
  6. OPEN EMPREC;
  7. LOOP
  8. FETCH EMPREC INTO MYREC;
  9. EXIT WHEN EMPREC%NOTFOUND;
  10. NUM:=EMPREC%ROWCOUNT;
  11. UPDATE EMP SET SAL=4000 WHERE CURRENT OF EMPREC;
  12. END LOOP;
  13. CLOSE EMPREC;
  14. DBMS_OUTPUT.PUT_LINE(NUM ||'   RECORDS UPDATED');
  15. END;
  16.  
Sample example to show PARAMETARISED CURSOR
-----------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. RR EMP%ROWTYPE;
  3. CURSOR C1 (MDEPTNO NUMBER,MJOB VARCHAR) IS 
  4. SELECT * FROM EMP WHERE DEPTNO=MDEPTNO AND JOB=MJOB;
  5. BEGIN
  6. OPEN C1(20,'CLERK');
  7. LOOP
  8. FETCH C1 INTO RR;
  9. EXIT WHEN C1%NOTFOUND;
  10. DBMS_OUTPUT.PUT_LINE(RR.ENAME);
  11. END LOOP;
  12. CLOSE C1;
  13. END;
  14.  
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
================================================== ==
Expand|Select|Wrap|Line Numbers
  1. declare
  2. cursor c1 is select deptno from dept order by deptno;
  3. cursor c2 (mm dept.deptno%type) is select ename from emp where deptno=mm;
  4. begin
  5. for mrec in c1 loop
  6. for mrec1 in c2(mrec.deptno) loop
  7. dbms_output.put_line(mrec1.ename);
  8. end loop;
  9. end loop;
  10. end;
  11.  

Also Check PL/SQL-CURSOR - 4
May 28 '07 #1
Share this Article
Share on Google+