Ref Cursor Vs Normal cursor

[Sameer]

One of the advantage of REF CURSOR is, you can use defined CURSOR type variable to open a new cursor after closing the previous. So by saving multiple CURSOR definations in DECLARATION. Using REF CURSOR you can also define multiple cursor variables, if you need to open multiple cursors at a time

F.ex:

DECLARE

      TYPE cCurType is REF CURSOR;
      cMyFirstCursor cCurType;
      cMySecondCursor cCurType;

      
BEGIN
     OPEN cMyFirstCursor FOR 
       SELECT id, name 
       FROM table_x;
     LOOP
       -- do processing
     END LOOP;
     CLOSE cMyFirstCursor;

     OPEN cMyFirstCursor FOR 
       SELECT id, name 
       FROM table_y;
     LOOP
        -- do processing

        OPEN cMySecondCursor FOR 
          SELECT id, name 
          FROM table_y1;
          LOOP 
            -- do processing for second cursor
          END LOOP;
          CLOSE cMySecondCursor;

     END LOOP;
     CLOSE cMyFirstCursor;

     OPEN cMyFirstCursor FOR 
       SELECT id, name 
       FROM table_z;
     LOOP
        -- do processing
     END LOOP;
     CLOSE cMyFirstCursor;
END;

Where as one of the normal CURSOR declaration would be

DECLARE
      CURSOR c1 IS 
         SELECT id, name 
         FROM table_x;
      
      CURSOR c2 IS 
         SELECT id, name 
         FROM table_y;
      
      CURSOR c3 IS 
         SELECT id, name 
         FROM table_z;
      
BEGIN
     OPEN c1;
     LOOP
        -- do processing
     END LOOP;
     CLOSE c1;

     OPEN c2;
     LOOP
        -- do processing
     END LOOP;
     CLOSE c2;

     OPEN c3;
     LOOP
        -- do processing       
     END LOOP;
     CLOSE c3;

END;

HTH

Sameer