Retrieval of multi row queries in embedded SQL: DECLARE CURSOR, OPEN, CLOSE, FETCH statement

Cursor: A cursor is used to track the current position of the program in retrieval of multi row queries in embedded SQL. It behaves more like a file in host languages like C. Cursors are used for processing multiple sets of query results in parallel.

DECLARE CURSOR statement: DECLARE CURSOR statement is used for declaring a cursor. It is used to associate a cursor name with a query. A cursor name is a valid SQL identifier. DECLARE CURSOR statement is usually placed before OPEN statement for the cursor.

OPEN statement: OPEN statement is used to start processing the SQL query by opening the cursor. In occasion of an error OPEN statement will generate a negative SQLCODE value.

CLOSE statement: OPEN statement can be closed by using the CLOSE statement and also it is closed automatically at the end of the transaction. A cursor can be opened again after it is closed.

FETCH statement: It is used to fetch the next row of query results. It is used to move the cursor to the next available row. After the cursor is moved to the new row, the new row becomes the current row of the cursor. When all rows have been fetched and when there’s no row remaining to be fetched then FETCH statement returns a NOT FOUND warning.

For Example:

exec sql include sqlca;
exec sql begin declare section;
char emplname[30];
float emplsale;
float emplavgsale;
short emplsale_ind;
exec sql end declare section;
exec sql declare emplcur cursor for 1
select name, sale, avgsale from employees
where avgsale > sale
order by name;
whenever sqlerror goto err;
whenever not found goto do;
exec sql open emplcur; 2
for (;;) {
exec sql fetch emplcur 3
into :emplname, :emplsale, :emplsale_ind, emplavgsale;
printf(“Name: %s\n”, emplname);
if (emplsale_ind < 0)
printf(“Sale is NULL\n”);
printf(“Average sale: %f\n”, emplavgsale);
printf(“Sale: %f\n”, emplsale);
printf(“SQL error: %ld\n”, sqlca.sqlcode);
exec sql close emplcur; 4

Leave a Reply