Ref Cursor Vs Normal cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Ref Cursor Vs Normal cursor

  1. #1
    Join Date
    Nov 2002
    Location
    India
    Posts
    22

    Unhappy Ref Cursor Vs Normal cursor

    In our application, a oracle SP was written using 'Ref' cursor.Then the same SP will be invoked by a JSP script .
    Is there any advantage of using 'Ref' cursor over the normal cursor?
    Which cursor will give me the optimal performance?
    Can the 'cursor For Loops' improve the performance of the SP?

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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:

    Code:
    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
    Code:
    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
    Last edited by Sameer; 01-17-2003 at 04:25 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width