Using a cursor

Cursors are appropriate for statements where there is a result set, or where you want to repeat a single basic statement but use different values from a local variable as a parameter (for example, in a loop).

A cursor is a specific, allocated part of the server process memory that keeps track of the statement that is being processed. Memory space is allocated for holding one row of the underlying statement, together with some status information about the current row (in SELECT statements) or the number of rows affected by the statement (in UPDATE, INSERT, and DELETE statements).

In this way, query results are processed one row at a time. The stored procedure logic should take care of the actual handling of the rows, and the positioning of the cursor on the required rows.

There are five basic steps in handling a cursor:

3 fetching on the cursor (for select procedure calls) - getting the results row by row,

prepare_statement ::= EXEC SQL PREPARE
{ cursor_name | CURSORNAME( { string_literal | variable } ) }
sql_statement

For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.

By preparing a cursor, memory space is allocated to accommodate one row of the result set of the statement, and the statement is parsed and optimized.

The cursor name that is given for a statement must be unique within the connection. This means procedures that contain cursors cannot be called recursively (at least not from a statement that comes after a PREPARE CURSOR and before the corresponding DROP CURSOR). When a cursor is prepared, a solidDB server checks that no other cursor of this name is currently open.

Note that statement cursors can also be opened by using the ODBC API. These cursor names need to be different from the names of cursors that are opened from procedures.

EXEC SQL PREPARE sel_tables
  SELECT table_name
  FROM sys_tables
  WHERE table_name LIKE 'SYS%';

This statement prepares the cursor named sel_tables, but does not execute the statement that it contains.

After a statement has been successfully prepared, it can be executed. An execute binds possible input and output variables to the cursor and runs the actual statement.

EXEC SQL EXECUTE cursor_name
[USING (using-variable-list)]
[INTO (into-variable-list)]

For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.

using-variable-list contains a list of procedure variables or parameters separated by a comma. These variables are input parameters for the SQL statement. The SQL input parameters are marked with the standard question mark syntax in the prepare statement. If the SQL statement has no input parameters, the USING specification is ignored.

into-variable-list contains the variables that are used when running a SELECT or CALL statement. The resulting columns of the SELECT or CALL statement are bound to these variables when the statement is executed. The variables are bound starting from the leftmost column listed in the statement. Binding of variables continues to the following column until all variables in the list of variables have been bound.

For example to extend the sequence for the previous example, you must run the following statements:

EXEC SQL PREPARE sel_tables
  SELECT table_name
  FROM sys_tables
  WHERE table_name LIKE 'SYS%'

EXEC SQL EXECUTE sel_tables INTO (tab);

The statement is now executed and the resulting table names are returned into variable tab in the subsequent fetch statements.

When a SELECT or CALL statement has been prepared and executed, it is ready for fetching data from it. Other statements (UPDATE, INSERT, DELETE, DDL) do not require fetching as there will be no result set. Fetching results is done by using the FETCH syntax:

This statement fetches a single row from the cursor to the variables that were bound with INTO keyword when the statement was executed.

To complete the previous example (to get result rows back), run the following statements:

EXEC SQL PREPARE sel_tables
  SELECT table_name
  FROM sys_tables
  WHERE table_name LIKE 'SYS%'
EXEC SQL EXECUTE sel_tables INTO (tab);
EXEC SQL FETCH sel_tables;

After this the variable tab will contain the table name of the first table found that conforms to the WHERE clause.

A subsequent call to fetch the cursor sel_tables will get the next row if the SELECT statement found more than one.

To fetch all table names a loop construct can be used:

Note that after the completion of the loop, the variable tab will contain the last fetched table name.

If you are likely to reuse the cursor and want to improve performance, you should close (rather than drop) the cursor. When you close a cursor, all the memory allocated during the execute phase is released but the cursor is kept in prepared state.

To close a cursor use the following statement syntax:

Dropping a cursor frees all allocated resources. The next time you use the dropped cursor, it must be prepared again.