Embedded SQL

DB2 SQL CURSOR : SERIAL CURSOR


SERIAL CURSOR :

  1. A serial cursor is one defined without the SCROLL keyword.
  2. A serial cursor can only more in forward direction.
  3. For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor.
  4. To use a serial cursor again, you must first close the cursor and then re-issue the OPEN statement.
  5. When the cursor is opened, it is positioned before the first row in the result table.
  6. On first FETCH first record of result table will be read and cursor will be positioned between 1st and 2nd record of result table.

Example


Lets say we have a table CUSTOMER with following data.

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 CURSOR FOR
    (SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')

    This will define a cursor with name @Customer. Absence of SCROLL keyword makes it a SERIAL 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

    Now you are ready to read result table using CURSOR.

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

    This code will read all three columns of 1st row from result table and populate corresponding host variables(OR SQL variable in case of SQL/PL).

    After this FETCH, CURSOR will move between 1st and 2nd row(After 1st row and before 2nd row). To read 2nd row you have to run FETCH again means one FETCH statement for each row to read. Because of this FETCH statement is used inside some loop statement.

    After no more record is available to read FECTH will return SQLCODE = 100.

    Here is example of FETCH inside RPG DoW loop(Do-while)

    
       Exec SQL FETCH @Customer 
            into :CustomerId,:CustomerName,:CustomerCountry;
       DoW SQLCODE = 0;
         // some code to process date read from Cursor. 
         Exec SQL FETCH @Customer 
              into :CustomerId,:CustomerName,:CustomerCountry; 
       EndDo;
    
    
  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.