Cursors

Discussion in 'Oracle' started by Sanskruti, May 1, 2007.

  1. Sanskruti

    Sanskruti New Member

    Joined:
    Jan 7, 2007
    Messages:
    108
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Consultant
    Location:
    Mumbai, India
    PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows. You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown :

    CURSOR c_line_item IS

    To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor.

    The following is an example of cursor control:
    Code:
    OPEN a_item;
    
      ...
    
       FETCH a_item
    
        INTO b_item;
    
      ...
    
      (retrieved row processing)
    
      ...
    
    CLOSE a_item;
    
    The code opens the cursor a_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes.

    Explicit Cursor Attributes



    There are four attributes associated with PL/SQL cursors.
    • %NOTFOUND
    • %FOUND
    • %ROWCOUNT
    • %ISOPEN
    All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. Attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE but still evaluates to NULL if the cursor FETCH returns no data.

    You can use %ROWCOUNT to determine how many rows have been selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.

    The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.

    Cursor Parameters



    You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:

    Code:
    CURSOR a_item (order_num IN NUMBER) IS
      SELECT tot_Quantity, recipient_num
      FROM b_item
      WHERE order_num = g_order_num;
    The parameter mode is always IN for cursor parameters, but the data type can be any valid data type. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.

    Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:

    Code:
    CURSOR a_item
      (order_num INTEGER DEFAULT 100,
        line_num INTEGER DEFAULT 1) IS ...
    By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.

    Cursor Packages



    A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure.
     
    aug.1987 likes this.
  2. rekha11

    rekha11 New Member

    Joined:
    Jan 31, 2008
    Messages:
    13
    Likes Received:
    3
    Trophy Points:
    0
    There are four attributes associated with PL/SQL cursors.
    %NOTFOUND
    %FOUND
    %ROWCOUNT
    %ISOPEN
    A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area.
     
    aug.1987 likes this.
  3. imrantechi

    imrantechi New Member

    Joined:
    Feb 12, 2008
    Messages:
    116
    Likes Received:
    4
    Trophy Points:
    0
    can you please provide certain examples of cursors
     
  4. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
    examples please
     
  5. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
    i m new cannot understand without examples ,please start with simple to tough ones
     
  6. harminder

    harminder New Member

    Joined:
    Jul 24, 2008
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Cursors in PL/SQL

    Static cursors
    Dynamic cursors
    Cursors with parameters
    for update
    Ref cursors
    explicit cursors.
    ref cursor from record type.
     
  7. kidas

    kidas Super Moderator

    Joined:
    Nov 25, 2008
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.club-oracle.com
    Some Additional Info:

    Explicit cursor attributes

    There are four cursor attributes used in Oracle:

    1. cursor_name%Found
    2. cursor_name%NOTFOUND
    3. cursor_name%ROWCOUNT
    4. cursor_name%ISOPEN

    Implicit cursor attributes

    Implicit Cursors are prefixed by the word SQL:

    1. SQL%Found
    2. SQL%NOTFOUND
    3. SQL%ROWCOUNT
    4. SQL%ISOPEN
    5. SQL%BULK_ROWCOUNT

    Cheers! :D
     
  8. Night club

    Night club New Member

    Joined:
    Oct 22, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    CURSOR c_line_item IS

    To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor.

    The following is an example of cursor control:
    Code: SQL

    It is the major opition.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice