Embedded SQL

DB2 SQL CURSOR : SCROLLABLE CURSOR


  1. A scrollable cursor is one defined with the SCROLL keyword.
  2. A scrollable cursor can move in both forward and backward directions.
  3. For a scrollable cursor, each row of the result table can be fetched multiple times per OPEN of the cursor.
  4. When the cursor is opened, it is positioned before the first row in the result table.

Example

We are using same CUSTOMER table as used in Serial Cursor

CUS_ID CUS_NAME CUS_COUNTRY
1 ROHIT INDIA
2 ANDY USA
3 SUMIT INDIA
4 RAM INDIA
5 Emily USA

Now we want to read only customers from INDIA.

  1. DECLARE CURSOR
    DECLARE @Customer SCROLL CURSOR FOR
    (SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')

    This will define a cursor with name @Customer. SCROLL keyword makes it a SCROLLABLE CURSOR.

  2. OPEN CURSOR
    OPEN @Customer

    OPEN statement will execute CURSOR’s SELECT-statement “SELECT * FROM CUSTOMER Where CUS_COUNTRY = ‘INDIA'” and create a result table. like this

    CUS_ID CUS_NAME CUS_COUNTRY
    1 RHOIT INDIA
    3 SUMIT INDIA
    4 RAM INDIA

    Now cursor @Customer will act as pointer to this result table which is pointed before the first record of result table.

  3. FETCH CURSOR

    FETCH statement for SCROLLABLE CURSOR has one extra parameter than SERIAL CURSOR to define direction options for CURSOR.

    FETCH [direction-option] From cursor-name into :host-var1,:host-var2

    direction-option details

    Direction option Detail
    NEXT Positions the cursor on the next row and read that row.
    This is the default if no position is specified.
    PRIOR Positions the cursor on the previous row and read that row
    FIRST Positions the cursor on the first row and read that row
    LAST Positions the cursor on the last row and read that row
    BEFORE Positions the cursor before the first row.
    [No data is read in this case]
    AFTER Positions the cursor after the last row.
    [No data is read in this case]
    CURRENT Read the current row but does not change the cursor position.
    RELATIVE n Evaluates (a host variable or integer) n in relationship to
    the cursor’s current position.
    For example,
    if n is -1,
    the cursor is positioned on the previous row of the result table.
    If n is +3,
    the cursor is positioned three rows after the current row.
    ABSOLUTE n If n is ZERO : CURSOR will be positioned before the
    FIRST row of result table.If n is positive : CURSOR will be positioned on nth
    record of result table from TOP.
    n = 3 : CURSOR will be positioned on 3rd
    record of result table from TOP

    If n is negative : CURSOR will be positioned on (last+nth+1)
    record of result table from BOTTOM.
    Let say result table has total 10 record.So
    n= -1 : CURSOR will be positioned on LAST Record
    [(10-1+1) = 10]

    n= -3 : CURSOR will be positioned on 8th Record
    [(10-3+1) = 8]

    Read LAST record of result table.

    FETCH LAST From @Customer 
    into :CustomerId,:CustomerName,:CustomerCountry
    

    Read 2nd record of result table

    FETCH ABSOLUTE 2 From @Customer 
    into :CustomerId,:CustomerName,:CustomerCountry
    

    Read previous record of result table

    FETCH PRIOR From @Customer 
    into :CustomerId,:CustomerName,:CustomerCountry
    
  4. CLOSE CURSOR

    After FETCH is complete CURSOR should release all resource it is using including memory for result table. To do this CURSOR should be closed usinf CLOSE statement.

    CLOSE @Customer

    To read the CURSOR after closing , CURSOR should be reopened.After reopen CURSOR will point before the 1st record again.


IBM i developer.

View Comments
There are currently no comments.