Embedded SQL

DB2 SQL CURSOR : BASIC


SQL CURSORS

Result Set of a SELECT Statement which returns more than one row is easy to view on SQL interface like STRSQL. But How we can handle this result set pragmatically in Embedded SQL or SQL/PL code? Answer is SQL CURSOR.

CURSOR provides a simple way to access Result Set returned by SELECT Statement. You can think of CURSOR as a pointer to one row in a Result Set containing multiple rows. So at a time cursor can reference only one row but It can move to other rows of Result Set as required.

DB2 SQL supports 2 types of CURSORS
  1. SERIAL CURSOR
  2. SCROLL-ABLE CURSOR
To use CURSOR there are 4 control statements
  1. DECLARE

    DECLARE Statement define a CURSOR for a given select statement. There is not limit on number of CURSORs you can define in one program but name of each CURSOR must be unique with in program scope.

    The DECLARE CURSOR statement basic syntax

    DECLARE cursor-name CURSOR FOR select-statement

    The DECLARE CURSOR statement complete syntax

    
                              .--ASENSITIVE----------------.    .--NO SCROLL--.
    >>-DECLARE--cursor-name---+----------------------------+----+-------------+---->
                              +--INSENSITIVE---------------+    '--SCROLL-----'
                              |             .--DYNAMIC--.  |
                              '--SENSITIVE--+-----------+--'
     
                 .--WITHOUT HOLD--.    .-WITHOUT RETURN----------------.
    >--CURSOR----+----------------+----+-------------------------------+----------->
                 '--WITH HOLD-----'    |              .--TO CALLER--.  |
                                       '-WITH RETURN--+-------------+--'
                                                      '--TO CLIENT--'
     
    >----FOR--+-select-statement-+--------------+------------------------------+-><
              '-statement-name---'              '-FOR UDPATE---+------------+--'
                                                               '-OF columns-' 
    
    Option Values Details
    Sensitivity ASENSITIVE [DEFAULT]SQL decide whether CURSOR should be
    SENSITIVE or INSENSITIVE. This is default for readonly cursors.
    Updateable CURSORS are SENSITIVE by default.
    SENSITIVE After OPEN CURSOR, if there is any changes (inserts, updates, or deletes) made to the associated tables, CURSOR will be updated automatically.
    INSENSITIVE After OPEN CURSOR there will be no impact on CURSOR for any change in associated tables. INSENSITIVE CURSORS can not be updateable.
    Scrolling NO SCROLL [DEFAULT] This will be a SERIAL CURSOR
    SCROLL This will be a SCROLLABLE CURSOR
    Hold WITHOUT HOLD [DEFAULT] Will talk about this in CURSOR and COMMITMENT CONTROL section.
    WITH HOLD
    Return WITHOUT RETURN [DEFAULT] This CURSOR can not be returned as result set.
    WITH RETURN TO CALLER : This CURSOR will be returned as result set to caller procedure just 1 level up. For example let say
    PGM1 --calls--> PGM2 --calls--> PGM3.
    In PGM there is a CURSOR "WITH RETURN TO CALLER" then result set will be returned to PGM2 (PGM1 will not get access to this result set)
    TO CLIENT : This CURSOR will be returned as result set to an external client like DotNet application. For example
    "DotNet app"--calls-->StroedProc1 --calls--> StroedProc2
    In StroedProc2 there is a CURSOR "WITH RETURN TO CLIENT" then result set will be returned to "DotNet app" (StroedProc1 will not get any access to this result set)
    Updateable FOR UPDATE This will make cursor updateable. If FOR UPDATE is not used CURSOR will be readonly. We will discuss about updateable cursor in detail in coming sections.
  2. OPEN

    When you OPEN the CURSOR, System actually run CURSOR's SELECT statement to build the result set.

    OPEN cursor-name
  3. FETCH

    FETCH statement gets data from CURSOR and populate give host variables(or SQL variable in SQL/PL) and move CURSOR to next record of result set. Behavior of FETCH is little different for SERIAL and SCROLL-ABLE CURSORS(We will discuss these in more details in their respective sections)

    At most basic level FETCH looks like this

    FETCH FROM cursor-name INTO :host-variable1,:host-variable2
  4. CLOSE

    CLOSE statement close the CURSOR and free the resources being used by CURSOR. After CLOSE you can not use FETCH statement but you can OPEN CURSOR again and after that you can use FETCH.

    CLOSE cursor-name

IBM i developer.

View Comments
There are currently no comments.