DB2 SQL CURSOR : SERIAL CURSOR
SERIAL CURSOR :
- A serial cursor is one defined without the SCROLL keyword.
- A serial cursor can only more in forward direction.
- For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor.
- To use a serial cursor again, you must first close the cursor and then re-issue the OPEN statement.
- When the cursor is opened, it is positioned before the first row in the result table.
- On first FETCH first record of result table will be read and cursor will be positioned between 1st and 2nd record of result table.
Lets say we have a table CUSTOMER with following data.
Now we want to read only customers from INDIA.
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.
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.
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;
CLOSE CURSORAfter 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.
To read the CURSOR after closing , CURSOR should be reopened.After reopen CURSOR will point before the 1st record again.