1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What is Ref cursor?

Discussion in 'SQL PL/SQL' started by nsramu, Dec 14, 2010.

  1. nsramu

    nsramu Active Member

    Likes Received:
    Trophy Points:
    REF CURSOR Overview

    A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable.

    EnterpriseDB currently supports both strongly and weakly typed REF CURSOR's.

    A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable.

    REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs.
    14.8.2. Declaring a Cursor Variable

    SPL supports the declaration of a cursor variable using both the SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.

    Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types. For the rest of this chapter our examples will primarily be making use of the SYS_REFCURSOR cursors. All you need to change in the examples to make them work for user defined REF CURSOR's is the declaration section.

    Note: Strongly-typed REF CURSOR's require the result set to conform to a declared number and order of fields with compatible data types and can also optionally return a result set.

    Declaring a SYS_REFCURSOR Cursor Variable

    The following is the syntax for declaring a SYS_REFCURSOR cursor variable:

    Code (SQL):
    name is an identifier assigned to the cursor variable.

    The following is an example of a SYS_REFCURSOR variable declaration.

    Code (SQL):
        emp_refcur      SYS_REFCURSOR;

    Declaring a User Defined REF CURSOR Type Variable

    You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:

    1. Create a referenced cursor TYPE
    2. Declare the actual cursor variable based on that TYPE

    The syntax for creating a user defined REF CURSOR type is as follows:

    Code (SQL):
    TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
    The following is an example of a cursor variable declaration.

    Code (SQL):
        TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
        my_rec emp_cur_type;
  2. anjan19

    anjan19 Guest

    Hi nsramu,

    It is very helpful...