Oracle PL/SQL by Example, Fifth Edition (2015)

---

For Example ch11_2a.sql

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student
WHERE rownum <= 5;
vr_student_name c_student_name%ROWTYPE;
BEGIN
OPEN c_student_name;
LOOP
FETCH c_student_name INTO vr_student_name;
EXIT WHEN c_student_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student name: '||
vr_student_name.first_name
||' '||vr_student_name.last_name);
END LOOP;
CLOSE c_student_name;
END;

In this example, a cursor is declared that returns five student names. Next, a cursor-based record is declared. In the body of the program, an explicit cursor is processed via the cursor loop. In the body of the loop, each record is returned by the cursor to the cursor-based record,vr_student_name. Next the content of the cursor is displayed on the screen. When run, the example produces the following output:

Student name: Austin V. Cadet
Student name: Frank M. Orent
Student name: Yvonne Winnicki
Student name: Mike Madej
Student name: Paula Valentine
PL/SQL procedure successfully completed.

Consider the same example with single modification. Notice that the DBMS_OUTPUT.PUT_LINE statement has been moved outside the loop.

For Example ch11_2b.sql

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student
WHERE rownum <= 5;
vr_student_name c_student_name%ROWTYPE;
BEGIN
OPEN c_student_name;
LOOP
FETCH c_student_name INTO vr_student_name;
EXIT WHEN c_student_name%NOTFOUND;
END LOOP;
CLOSE c_student_name;
DBMS_OUTPUT.PUT_LINE('Student name: '||
vr_student_name.first_name||' '
||vr_student_name.last_name);
END;

The DBMS_OUTPUT.PUT_LINE has been moved outside the loop. First the loop will process the five student records. The values for each record will be placed in the record vr_student_name, but each time the loop iterates it will replace the value in the record with a new value. When the five iterations of the loop are finished, it will exit because of the EXIT WHEN condition, leaving the vr_student_name record with the last value that was in the cursor. This is the only value that will be displayed via the DBMS_OUTPUT.PUT_LINE, which comes after the loop is closed.

Making Use of a User-Defined Record

A user-defined record is based on the record type defined by a programmer. To make use of such a record, first you declare a record type, and then you declare a record variable based on the record type.

For Example

type type_name IS RECORD
(field_name 1 DATATYPE 1,
field_name 2 DATATYPE 2,
...
field_name N DATATYPE N);
record_name TYPE_NAME%ROWTYPE;

Consider the following code fragment:

For Example

SET SERVEROUTPUT ON;
DECLARE
-- declare user-defined type
TYPE instructor_info IS RECORD
(instructor_id instructor.instructor_id%TYPE,
first_name instructor.first_name%TYPE,
last_name instructor.last_name%TYPE,
sections NUMBER(1));
-- declare a record based on the type defined above
rv_instructor instructor_info;

In this code fragment, a type instructor_info is defined. This type contains four attributes: the instructor’s ID, first name, and last name, and the number of sections taught by this instructor. Next, a record based on the type just described is declared. As a result, this record has structure similar to the type, instructor_info.

Consider the following example:

For Example ch11_2c.sql

SET SERVEROUTPUT ON;
DECLARE
TYPE instructor_info IS RECORD
(first_name instructor.first_name%TYPE,
last_name instructor.last_name%TYPE,
sections NUMBER);
rv_instructor instructor_info;
BEGIN
SELECT RTRIM(i.first_name),
RTRIM(i.last_name), COUNT(*)
INTO rv_instructor
FROM instructor i, section s
WHERE i.instructor_id = s.instructor_id
AND i.instructor_id = 102
GROUP BY i.first_name, i.last_name;
DBMS_OUTPUT.PUT_LINE('Instructor, '||
rv_instructor.first_name||
' '||rv_instructor.last_name||
', teaches '||rv_instructor.sections||
' section(s)');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('There is no such instructor');
END;

In this example, a record called vr_instructor is declared. This record is based on the type defined previously. In the body of the PL/SQL block, this record is initialized with the help of the SELECT INTO statement, and then the values in the cursor are displayed on the screen. Note that the columns of the SELECT INTO statement are listed in the same order that the attributes are defined in instructor_info type. There is no need to use dot notation for this record initialization.

When run, this example produces the following output:

Instructor, Tom Wojick, teaches 9 section(s)
PL/SQL procedure successfully completed.

Making Use of Cursor Attributes

Table 11.1 lists the attributes of a cursor, which are used to determine the result of a cursor operation when fetched or opened.

Table 11.1 Explicit Cursor Attributes

You can make use of the attribute %NOTFOUND to close a loop. It would also be a wise idea to add an exception clause to the end of the block to close the cursor if it is still open. If you add the following statements to the end of the PL/SQL block, it will be complete:

EXIT WHEN c_student%NOTFOUND;
END LOOP;
CLOSE c_student;
EXCEPTION
WHEN OTHERS
THEN
IF c_student%ISOPEN
THEN
CLOSE c_student;
END IF;
END;

Cursor attributes can be used with implicit cursors by using the prefix “SQL”—for example, SQL%ROWCOUNT.

If you use the SELECT INTO syntax in your PL/SQL block, you will be creating an implicit cursor. You can then use these attributes on the implicit cursor.

For Example ch11_3a.sql

SET SERVEROUTPUT ON;
DECLARE
v_city zipcode.city%type;
BEGIN
SELECT city
Into v_city
from zipcode
Where zip = 07002;
IF SQL%ROWCOUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE(v_city ||' has a '||
'ZIP code of 07002');
ELSIF SQL%ROWCOUNT = 0
THEN
DBMS_OUTPUT.PUT_LINE('The ZIP code 07002 is '||
' not in the database');
ELSE
DBMS_OUTPUT.PUT_LINE('Stop harassing me');
END IF;
END;

The PL/SQL block ch11_3a would display the following output:

Bayonne has a ZIP code of 07002
PL/SQL procedure successfully completed.

The declaration section declares a variable, v_city, anchored to the data type of the city item in the zipcode table. The SELECT statement causes an implicit cursor to be opened, fetched, and then closed. The IF clause makes use of the attribute %ROWCOUNT to determine whether the implicit cursor has a row count of 1. If it does have a row count of 1, then the first DBMS_OUTPUT line will be displayed. Notice that this example does not handle a situation where the row count is greater than 1. Since the zipcode table’s primary key is the ZIP code, this could happen.

If you rerun this block after changing 07002 to 99999, you will get the following result:

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

A SELECT statement in a PL/SQL block that does not return any rows will raise a “no data found” exception. Because there is no exception handler, the preceding error would be displayed.

You may have expected the second and third conditions of the IF statement to capture the instance of a %ROWCOUNT equal to 0. Now that you understand a SELECT statement that returns no rows will raise a WHEN NO_DATA_FOUND exception, it would be a good idea to handle this situation by adding a WHEN NO_DATA_FOUND exception to the existing block. You can add a %ROWCOUNT in the exception, either to display the row count in a DBMS_OUTPUT statement or to create an IF statement that handles the various possibilities.

Putting It All Together

Here is an example of the complete cycle of declaring, opening, fetching, and closing a cursor, including use of cursor attributes.

For Example ch11_4.sql

1> DECLARE
2> v_sid student.student_id%TYPE;
3> CURSOR c_student IS
4> SELECT student_id
5> FROM student
6> WHERE student_id < 110;
7> BEGIN
8> OPEN c_student;
9> LOOP
10> FETCH c_student INTO v_sid;
11> EXIT WHEN c_student%NOTFOUND;
12> DBMS_OUTPUT.PUT_LINE('STUDENT ID : '||v_sid);
13> END LOOP;
14> CLOSE c_student;
15> EXCEPTION
16> WHEN OTHERS
17> THEN
18> IF c_student%ISOPEN
19> THEN
20> CLOSE c_student;
21> END IF;
22> END;

This example illustrates a complete cursor fetch loop, in which multiple rows of data are returned from the query. The cursor is declared in the declaration section of the block (lines 1–6) just like other identifiers. In the executable section of the block (lines 7–15), a cursor is opened using the OPEN (line 8) statement. Because the cursor returns multiple rows, a loop is used to assign returned data to the variables with a FETCH statement (line 10). Because the loop statement has no other means of termination, an exit condition must be specified. In this case, one of the attributes for the cursor is %NOTFOUND (line 11). The cursor is then closed to free the memory allocation (line 14). Additionally, if the exception handler is called, a check is made to see if the cursor is open (line 18) or closed (line 20).

This example is now modified to make use of the cursor attributes %FOUND and %ROWCOUNT.

For Example ch11_5.sql

SET SERVEROUTPUT ON
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id
FROM student
WHERE student_id < 110;
BEGIN
OPEN c_student;
LOOP
FETCH c_student INTO v_sid;
IF c_student%FOUND THEN
DBMS_OUTPUT.PUT_LINE
('Just FETCHED row '
||TO_CHAR(c_student%ROWCOUNT)||
' Student ID: '||v_sid);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c_student;
EXCEPTION
WHEN OTHERS
THEN
IF c_student%ISOPEN
THEN
CLOSE c_student;
END IF;
END;

In this script, there has been a modification to the loop structure. Instead of relying on an exit condition, an IF statement is used. The IF statement makes use of the cursor attribute %FOUND, which returns TRUE when a row is “found” in the cursor and FALSE when it is not found. The attribute %ROWCOUNT then returns a number, which is the current row number of the cursor.

The next example demonstrates how to fetch a cursor that has taken data from the student table into the cursor variable %ROWTYPE. The cursor selects only those students with a student_id less than 110. The columns are the STUDENT_ID, LAST_NAME, FIRST_NAME, and a count of the number of classes the student is enrolled in. The cursor is fetched with a loop and then all of the columns are output.

For Example ch11_6.sql

SET SERVEROUTPUT ON
DECLARE
CURSOR c_student_enroll IS
SELECT s.student_id, first_name, last_name,
COUNT(*) enroll,
(CASE
WHEN count(*) = 1 Then ' class.'
WHEN count(*) is null then
' no classes.'
ELSE ' classes.'
END) class
FROM student s, enrollment e
WHERE s.student_id = e.student_id
AND s.student_id <110
GROUP BY s.student_id, first_name, last_name;
r_student_enroll c_student_enroll%ROWTYPE;
BEGIN
OPEN c_student_enroll;
LOOP
FETCH c_student_enroll INTO r_student_enroll;
EXIT WHEN c_student_enroll%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student INFO: ID '||
r_student_enroll.student_id||' is '||
r_student_enroll.first_name|| ' ' ||
r_student_enroll.last_name||
' is enrolled in '||r_student_enroll.enroll||
r_student_enroll.class);
END LOOP;
CLOSE c_student_enroll;
EXCEPTION
WHEN OTHERS
THEN
IF c_student_enroll %ISOPEN
THEN
CLOSE c_student_enroll;
END IF;
END;

In the declaration section, a cursor c_student_enroll is defined as well as a record, which is the type of a row of the cursor. The cursor loop structure makes use of an exit condition with the %NOTFOUND cursor attribute. When there are no more rows, the %NOTFOUND attribute will be TRUE and will cause the loop to exit. While the cursor is open and the loop is processing, it will fetch a row of the cursor in a record, one at a time. The DBMS output will cause each row to be displayed to the screen. Finally, the cursor is closed. An exception clause will also close the cursor if any error is raised.