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 |
SELECT emp_name, emp_salary<br> INTO :emp_name_var, :emp_salary_var<br> 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.
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 |
// Declare cursor emp_curs for employee table<br>// retrieval.<br>DECLARE emp_curs CURSOR FOR<br> SELECT emp_id, emp_name FROM EMPLOYEE<br> WHERE emp_state = :sle_1.text; |
|
1 |
// For UPDATE WHERE CURRENT OF cursor_name and<br>// DELETE WHERE CURRENT OF cursor_name to work<br>// correctly in Oracle 7, include the FOR UPDATE<br>// clause in the SELECT statement. |
|
1 |
// Declare local variables for retrieval.<br>string    emp_id_var<br>string    emp_name_var |
|
1 |
// Execute the SELECT statement with<br>// the current value of sle_1.text.<br>OPEN emp_curs; |
|
1 |
// At this point, if there are no errors,<br>// 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 |
// Get the first row from the result set.<br>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.