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:

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:

1. Declare a cursor.

2. Open it (which conceptually executes the SELECT).

3. Fetch rows as needed.

4. Close the 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.

Assume the SingleLineEdit sle_1 contains the state code for the retrieval:

// 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.

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:

// 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.

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.