Table of Contents Previous Next


4 Stored Procedure Language : 4.8 Static Cursors

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result set one row at a time. This allows the creation of SPL program logic that retrieves a row from the result set, does some processing on the data in that row, and then retrieves the next row and repeats the process.
Cursors are most often used in the context of a FOR or WHILE loop. A conditional test should be included in the SPL logic that detects when the end of the result set has been reached so the program can exit the loop.
CURSOR name IS query;
name is an identifier that will be used to reference the cursor and its result set later in the program. query is a SQL SELECT command that determines the result set retrievable by the cursor.
Note: An extension of this syntax allows the use of parameters. This is discussed in more detail in Section 4.8.8.
OPEN name;
name is the identifier of a cursor that has been previously declared in the declaration section of the SPL program. The OPEN statement must not be executed on a cursor that has already been, and still is open.
The following shows an OPEN statement with its corresponding cursor declaration.
FETCH name INTO { record | variable [, variable_2 ]... };
name is the identifier of a previously opened cursor. record is the identifier of a previously defined record (for example, using table%ROWTYPE). variable, variable_2... are SPL variables that will receive the field data from the fetched row. The fields in record or variable, variable_2... must match in number and order, the fields returned in the SELECT list of the query given in the cursor declaration. The data types of the fields in the SELECT list must match, or be implicitly convertible to the data types of the fields in record or the data types of variable, variable_2...
Note: There is a variation of FETCH INTO using the BULK COLLECT clause that can return multiple rows at a time into a collection. See Section 4.12.4.2 for more information on using the BULK COLLECT clause with the FETCH INTO statement.
The following shows the FETCH statement.
Instead of explicitly declaring the data type of a target variable, %TYPE can be used instead. In this way, if the data type of the database column is changed, the target variable declaration in the SPL program does not have to be changed. %TYPE will automatically pick up the new data type of the specified column.
If all the columns in a table are retrieved in the order defined in the table, %ROWTYPE can be used to define a record into which the FETCH statement will place the retrieved data. Each field within the record can then be accessed using dot notation.
CLOSE name;
name is the identifier of a cursor that is currently open. Once a cursor is closed, it must not be closed again. However, once the cursor is closed, the OPEN statement can be issued again on the closed cursor and the query result set will be rebuilt after which the FETCH statement can then be used to retrieve the rows of the new result set.

4 Stored Procedure Language : 4.8 Static Cursors

Table of Contents Previous Next