Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 20.2 OPEN-FOR, FETCH, and CLOSE STATEMENTS

The OPEN-FOR, FETCH, and CLOSE statements are used for multi-row queries or cursors. This concept is very similar to static cursor processing that you encountered in Chapter 9. Just as in the case of static cursors, first you associate a cursor variable with a query. Next, you open the cursor variable so that it points to the first row of the result set. Next, you fetch one row at a time from the result set. Finally, when all rows have been processed, you close the cursor (cursor variable).

Opening Cursor

In the case of a dynamic SQL, the OPEN-FOR statement has an optional USING clause that allows you to pass values to the bind arguments at run-time. The general syntax for an OPEN-FOR statement is as follows (the reserved words and phrases surrounded by brackets are optional):

OPEN cursor_variable FOR dynamic_SQL_string
[USING bind_argument1, bind_argument2, ...]

The

cursor_variable is a variable of a weak REF CURSOR type, and

dynamic_SQL_string is a string that contains a multi-row query.

FOR EXAMPLE

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||
'WHERE zip = :1'
USING v_zip;
...

In this code fragment, you defined a weak cursor type, student_cur_type. Next, you defined a cursor variable student_cur based on the REF CURSOR type specified in the previous step. At run-time, the student_cur variable is associated with the SELECT statement that returns the first and last names of students for a given value of zip.

Fetching from Cursor

As mentioned earlier, the FETCH statement returns a single row from the result set into a list of variables defined in a PL/SQL block and moves cursor to the next row. If there are no more rows to fetch, the EXIT WHEN statement evaluates to TRUE, and the control of the execution is passed outside the cursor loop. The general syntax for a FETCH statement is as follows:

FETCH cursor_variable
INTO defined_variable1, defined_variable2, ...
EXIT WHEN

cursor_variable %NOTFOUND;

Adding the previous example, you fetch the student's first and last names into variables specified in the declaration section of the PL/SQL block. Next, you evaluate if there are more records to process via EXIT WHEN statement. As long as there are more records to process, the student's first and last names are displayed on the screen. Once the last row is fetched, the cursor loop terminates. Changes are shown in bold letters.

FOR EXAMPLE

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||
'WHERE zip = :1'
USING v_zip;

LOOP

FETCH student_cur INTO v_first_name, v_last_name;

EXIT WHEN student_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);

DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);

END LOOP;
...

It is important to note that the number of variables listed in the INTO clause must correspond to the number of columns returned by the cursor. Furthermore, the variables in the INTO clause must be type-compatible with the cursor columns.

Closing Cursor

The CLOSE statement disassociates the cursor variable with the multi-row query. As a result, after the CLOSE statement executes, the result set becomes undefined. The general syntax for a CLOSE statement is as follows:

CLOSE cursor_variable

Now consider the completed version of the example shown previously. Changes are shown in bold letters.

FOR EXAMPLE

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||
'WHERE zip = :1'
USING v_zip;
LOOP
FETCH student_cur INTO v_first_name, v_last_name;
EXIT WHEN student_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
END LOOP;

CLOSE student_cur;

EXCEPTION

WHEN OTHERS THEN

IF student_cur%ISOPEN THEN

CLOSE student_cur;

END IF;

DBMS_OUTPUT.PUT_LINE ('ERROR: '||

SUBSTR(SQLERRM, 1, 200));

END;

The IF statement in the exception-handling section evaluates to TRUE if an exception is encountered before the cursor processing is completed. In such case, it is considered a good practice to check if a cursor is still open and close it, so that all resources associated with it are freed before the program terminates.

When run, this example produces the output shown below:

Enter value for sv_zip: 11236

old 5: v_zip VARCHAR2(5) := '&sv_zip';

new 5: v_zip VARCHAR2(5) := '11236';

First Name: Derrick

Last Name: Baltazar

First Name: Michael

Last Name: Lefbowitz

First Name: Bridget

Last Name: Hagel

PL/SQL procedure successfully completed.