Lab 9.1 Exercises | Oracle PL[s]SQL by Example

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)	What will happen when the preceding example is run in a SQL*Plus session?

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

b)	Explain how the record type vr_student_name is being used in the following example.

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

a)	Write the declarative section of a PL/SQL block that defines a cursor named c_student, based on the student table with the last_name and the first_name concatenated into one item called name and leaving out the created_by and modified_by columns . Then declare a record based on this 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 variables (including bind variables ) in the WHERE clause are examined.
Based on the values of the variables, the active set is determined and the PL/SQL engine executes the query for that cursor. Variables are examined at cursor open time only.
The PL/SQL engine identifies the active set of datathe rows from all involved tables that meet the WHERE clause criteria.
The active set pointer is set to the first row.

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.

b)	Add the necessary lines to the PL/SQL block that you just wrote to open the cursor.

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;

 FETCH cursor_name INTO PL/SQL record;

When the cursor is fetched, the following occurs:

The fetch command is used to retrieve one row at a time from the active set. This is generally done inside a loop. The values of each row in the active set can then be stored into the corresponding variables or PL/SQL record one at a time, performing operations on each one successively.
After each FETCH, the active set pointer is moved forward to the next row. Thus, each fetch will return successive rows of the active set, until the entire set is returned. The last FETCH will not assign values to the output variables; they will still contain their prior values.

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.

In Chapter 3 you learned how to construct a loop. For the PL/SQL block that you have been writing, add a loop. Inside the loop FETCH the cursor into the record. Include a DBMS_OUTPUT line inside the loop so that each time the loop iterates, all the information in the record is displayed in a SQL*Plus session.

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

d)	Continue with the code you have developed by adding a close statement to the cursor. Is your code complete now?

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;

e)	Explain what is occurring in this PL/SQL block. What will be the output from the preceding example?
f)	Next, consider the same example with single modification. Notice that the DBMS_OUTPUT.PUT_LINE statement has been moved outside the loop (shown in bold letters ). Execute this example, and try to explain why this version of the script produces different output.

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;

g)	Explain what is declared in the previous example. Describe what is happening to the record and explain how this results in the output.

9.1.3 Make Use of Cursor Attributes

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

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.

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

If you use a 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

 -- ch09_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 '         'zipcode of 07002');    ELSIF SQL%ROWCOUNT = 0    THEN       DBMS_OUTPUT.PUT_LINE('The zipcode 07002 is '          ' not in the database');    ELSE       DBMS_OUTPUT.PUT_LINE('Stop harassing me');    END IF; END;

Table 9.1. Explicit Cursor Attributes

Cursor Attribute	Syntax	Explanation
%NOTFOUND	cursor_name%NOTFOUND	A Boolean attribute that returns TRUE if the previous FETCH did not return a row, and FALSE if it did.
%FOUND	cursor_name%FOUND	A Boolean attribute that returns TRUE if the previous FETCH returned a row, and FALSE if it did not.
%ROWCOUNT	cursor_name%ROWCOUNT	# of records fetched from a cursor at that point in time.
%ISOPEN	Cursor_name%ISOPEN	A Boolean attribute that returns TRUE if cursor is open, FALSE if it is not.

b)	What will happen if this code is run? Describe what is happening in each phase of the example.
c)	Rerun this block, changing 07002 to 99999. What do you think will happen? Explain.
d)	Now, try running this file. Did it run as you expected? Why or why not? What could be done to improve the way it handles a possible error condition?

9.1.4 Put It All Together

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

 -- ch09_4a.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;

a)	Describe what is happening in each phase of example ch09_4a.sql. Use the line numbers to reference the example.
b)	Modify the example to make use of the cursor attributes %FOUND and %ROWCOUNT.
c)	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.