Oracle
Retrieval
Retrieval using cursors is conceptually similar to retrieval in the
singleton SELECT. The main difference is that since there can be multiple
rows in a result set, you control when the next row is fetched into the
PowerScript variables.
If you expect only a single row to exist in the employee table with
the specified emp_id, use the singleton SELECT. In a singleton SELECT, you
specify the SELECT statement and destination variables in one concise SQL
statement:
|
1 2 3 |
SELECT emp_name, emp_salary INTO :emp_name_var, :emp_salary_var FROM employee WHERE emp_id = :emp_id_var; |
However, if the SELECT may return multiple rows, you must:
-
Declare a cursor.
-
Open it (which conceptually executes the SELECT).
-
Fetch rows as needed.
-
Close the cursor.
Declaring and opening a
cursor
Declaring a cursor is tightly coupled with the OPEN statement. The
DECLARE specifies the SELECT statement to be executed, and the OPEN
actually executes it.
Declaring a cursor is similar to declaring a variable; a cursor is a
nonexecutable statement just like a variable declaration. The first step
in declaring a cursor is to define how the result set looks. To do this,
you need a SELECT statement, and since you must refer to the result set in
subsequent SQL statements, you must associate the result set with a
logical name.
Note
For UPDATE … WHERE CURRENT OF cursor_name and DELETE … WHERE
CURRENT OF cursor_name statements to execute successfully, the SELECT
statement must contain the FOR UPDATE clause.
Example
Assume the SingleLineEdit sle_1 contains the state code for the
retrieval:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Declare cursor emp_curs for employee table // retrieval. DECLARE emp_curs CURSOR FOR SELECT emp_id, emp_name FROM EMPLOYEE WHERE emp_state = :sle_1.text; // For UPDATE WHERE CURRENT OF cursor_name and // DELETE WHERE CURRENT OF cursor_name to work // correctly in Oracle 7, include the FOR UPDATE // clause in the SELECT statement. // Declare local variables for retrieval. string emp_id_var string emp_name_var // Execute the SELECT statement with // the current value of sle_1.text. OPEN emp_curs; // At this point, if there are no errors, // the cursor is available for further processing. |
Fetching Rows
In the singleton SELECT, you specify variables to hold the values
for the columns within the selected row. The FETCH statement syntax is
similar to the syntax of the singleton SELECT. Values are returned INTO a
specified list of variables.
This example continues the previous example by retrieving some
data:
|
1 2 |
// Get the first row from the result set. FETCH emp_curs INTO :emp_id_var, :emp_name_var; |
If at least one row can be retrieved, this FETCH places the values
of the emp_id and emp_name columns from the first row in the result set
into the PowerScript variables emp_id_var and emp_name_var. FETCH
statements typically occur in a loop that processes several rows from a
result set (one row at a time), but that is not the only way they are
used.
What happens when the result set is exhausted?
FETCH returns +100 (not found) in the SQLCode property within the
referenced transaction object. This is an informational return code; -1
in SQLCode indicates an error.
Closing the cursor
The CLOSE statement terminates processing for the specified cursor.
CLOSE releases resources associated with the cursor, and subsequent
references to that cursor are allowed only if another OPEN is executed.
Although you can have multiple cursors open at the same time, you should
close the cursors as soon as possible for efficiency reasons.