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

Lab 9.1 Exercises

9.1.1 Make Use of Record Types

Here is an example of a record type in an anonymous PL/SQL block.

FOR EXAMPLE

SET SERVEROUTPUT ON;
DECLARE
vr_zip ZIPCODE%ROWTYPE;
BEGIN
SELECT *
INTO vr_zip
FROM zipcode
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE('City: '||vr_zip.city);
DBMS_OUTPUT.PUT_LINE('State: '||vr_zip.state);
DBMS_OUTPUT.PUT_LINE('Zip: '||vr_zip.zip);
END;

A cursor-based record is based on the list of elements of a predefined cursor.

FOR EXAMPLE

DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student;
vr_student_name c_student_name%ROWTYPE;

In the next Lab you will learn how to process an explicit cursor. Afterward you will address record types within that process.

9.1.2 Process an Explicit Cursor

Opening a Cursor

The next step in controlling an explicit cursor is to open it. When the Open cursor statement is processed, the following four actions will take place automatically:

The syntax for opening a cursor is

OPEN cursor_name;

A pointer into the active set is also established at the cursor open time. The pointer determines which row is the next to be fetched by the cursor. More than one cursor can be open at a time.

Fetching Rows in a Cursor

After the cursor has been declared and opened, you can then retrieve data from the cursor. The process of getting the data from the cursor is referred to as fetching the cursor. There are two methods of fetching a cursor, done with the following command:

FETCH cursor_name INTO PL/SQL variables;

or

FETCH cursor_name INTO PL/SQL record;

When the cursor is fetched, the following occurs:

FOR EXAMPLE

-- ch09_2a.sql
SET SERVEROUTPUT ON
DECLARE
CURSOR c_zip IS
SELECT *
FROM zipcode;
vr_zip c_zip%ROWTYPE;
BEGIN
OPEN c_zip;
LOOP
FETCH c_zip INTO vr_zip;

EXIT WHEN c_zip%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vr_zip.zip||
' '||vr_zip.city||' '||vr_zip.state);
END LOOP;
END;

The lines in italics have not yet been covered but are essential for the code to run correctly. They will be explained later in this chapter.

Closing a Cursor

Once all of the rows in the cursor have been processed (retrieved), the cursor should be closed. This tells the PL/SQL engine that the program is finished with the cursor, and the resources associated with it can be freed. The syntax for closing the cursor is

CLOSE cursor_name;

Once a cursor is closed, it is no longer valid to fetch from it. Likewise, it is not possible to close an already closed cursor (either one will result in an Oracle error).

Next, consider another example.

FOR EXAMPLE

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;

FOR EXAMPLE

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;

A programmer-defined record is based on the record type defined by a programmer. First you declare a record type, and next, you declare a record based on the record type defined in the previous step as follows:

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, you define your own type, instructor_info. This type contains four attributes: instructor's ID, first and last names, and number of sections taught by this instructor. Next, you declare a record based on the type just described. As a result, this record has structure similar to the type, instructor_ info. Consider the following example.

FOR EXAMPLE

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;

9.1.3 Make Use of Cursor Attributes

a)

Now that you know cursor attributes, you can use one of these to exit the loop within the code you developed in the previous example. Are you able to make a fully executable block now? If not, explain why.

Describe what is happening in each phase of example ch09_4a.sql. Use the line numbers to reference the example.

Modify the example to make use of the cursor attributes %FOUND and %ROWCOUNT.

Fetch a cursor that has a data from the student table into a %ROWTYPE. Only select students with a student_id under 110. The columns are the STUDENT_ID, LAST_NAME, FIRST_NAME, and a count of the number of classes they are enrolled in (using the enrollment table). Fetch the cursor with a loop and then output all the columns. You will have to use an alias for the enrollment count.