Chapter 19 Records | Oracle PL[s]SQL by Example

Create the following script. Create an index-by table with the element type of a user -defined record. This record should contain first name, last name , and the total number of courses that a particular instructor teaches. Display the records of the index-by table on the screen.

A1:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON DECLARE CURSOR instructor_cur IS SELECT first_name, last_name, COUNT(UNIQUE s.course_no) courses FROM instructor i LEFT OUTER JOIN section s ON (s.instructor_id = i.instructor_id) GROUP BY first_name, last_name; TYPE rec_type IS RECORD (first_name INSTRUCTOR.FIRST_NAME%TYPE, last_name INSTRUCTOR.LAST_NAME%TYPE, courses_taught NUMBER); TYPE instructor_type IS TABLE OF REC_TYPE INDEX BY BINARY_INTEGER; instructor_tab instructor_type; v_counter INTEGER := 0; BEGIN FOR instructor_rec IN instructor_cur LOOP v_counter := v_counter + 1; -- Populate index-by table of records instructor_tab(v_counter).first_name := instructor_rec.first_name; instructor_tab(v_counter).last_name := instructor_rec.last_name; instructor_tab(v_counter).courses_taught := instructor_rec.courses; DBMS_OUTPUT.PUT_LINE ('Instructor, ' instructor_tab(v_counter).first_name' ' instructor_tab(v_counter).last_name', teaches ' instructor_tab(v_counter).courses_taught' courses.'); END LOOP; END;

Consider the SELECT statement used in this script. This SELECT statement returns the instructor's name and total number of courses that he or she teaches. The statement is using an outer join so that if a particular instructor is not teaching any courses, he or she will be included in the results of the SELECT statement. Note that the SELECT statement uses ANSI 1999 SQL standard.

You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL.

In this script, you define a cursor against the INSTRUCTOR and SECTION tables that is used to populate the index-by table of records, instructor_tab . Each row of this table is a user-defined record of three elements. You populate the index-by table via the cursor FOR loop. Consider the notation used to reference each record element of the index-by table:

  instructor_tab(v_counter).first_name   instructor_tab(v_counter).last_name   instructor_tab(v_counter).courses_taught

To reference each row of the index-by table, you use the counter variable. However, because each row of this table is a record, you must also reference individual fields of the underlying record. When run, this script produces the following output:

  Instructor, Anita Morris, teaches 10 courses.   Instructor, Charles Lowry, teaches 9 courses.   Instructor, Fernand Hanks, teaches 9 courses.   Instructor, Gary Pertez, teaches 10 courses.   Instructor, Marilyn Frantzen, teaches 9 courses.   Instructor, Nina Schorin, teaches 10 courses.   Instructor, Rick Chow, teaches 1 courses.   Instructor, Todd Smythe, teaches 10 courses.   Instructor, Tom Wojick, teaches 9 courses.   PL/SQL procedure successfully completed.

Modify the script created in project (1). Instead of using an index-by table, use a nested table.

A2:

Answer: Your script should look similar to the following. All changes are highlighted in bold.

 SET SERVEROUTPUT ON DECLARE CURSOR instructor_cur IS SELECT first_name, last_name, COUNT(UNIQUE s.course_no) courses FROM instructor i LEFT OUTER JOIN section s ON (s.instructor_id = i.instructor_id) GROUP BY first_name, last_name; TYPE rec_type IS RECORD (first_name INSTRUCTOR.FIRST_NAME%TYPE, last_name INSTRUCTOR.LAST_NAME%TYPE, courses_taught NUMBER);  TYPE instructor_type IS TABLE OF REC_TYPE;   instructor_tab instructor_type := instructor_type();  v_counter INTEGER := 0; BEGIN FOR instructor_rec IN instructor_cur LOOP v_counter := v_counter + 1;  instructor_tab.EXTEND;  -- Populate index-by table of records instructor_tab(v_counter).first_name := instructor_rec.first_name; instructor_tab(v_counter).last_name := instructor_rec.last_name; instructor_tab(v_counter).courses_taught := instructor_rec.courses; DBMS_OUTPUT.PUT_LINE ('Instructor, ' instructor_tab(v_counter).first_name' ' instructor_tab(v_counter).last_name', teaches ' instructor_tab(v_counter).courses_taught' courses.'); END LOOP; END;

Notice that the instructor_tab must be initialized and extended before its individual elements can be referenced.

Modify the script created in project (2). Instead of using a nested table, use a varray.

A3:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON DECLARE CURSOR instructor_cur IS SELECT first_name, last_name, COUNT(UNIQUE s.course_no) courses FROM instructor i LEFT OUTER JOIN section s ON (s.instructor_id = i.instructor_id) GROUP BY first_name, last_name; TYPE rec_type IS RECORD (first_name INSTRUCTOR.FIRST_NAME%TYPE, last_name INSTRUCTOR.LAST_NAME%TYPE, courses_taught NUMBER);  TYPE instructor_type IS VARRAY(10) OF REC_TYPE;  instructor_tab instructor_type := instructor_type(); v_counter INTEGER := 0; BEGIN FOR instructor_rec IN instructor_cur LOOP v_counter := v_counter + 1; instructor_tab.EXTEND; -- Populate index-by table of records instructor_tab(v_counter).first_name := instructor_rec.first_name; instructor_tab(v_counter).last_name := instructor_rec.last_name; instructor_tab(v_counter).courses_taught := instructor_rec.courses; DBMS_OUTPUT.PUT_LINE ('Instructor, ' instructor_tab(v_counter).first_name' ' instructor_tab(v_counter).last_name', teaches ' instructor_tab(v_counter).courses_taught' courses.'); END LOOP; END;

This version of the script is almost identical to the previous version. Instead of using a nested table, you are using a varray of 15 elements.

Create the following script. Create a user-defined record with three fields: course_no , description , cost , and prerequisite_rec . The last field, prerequisite_rec , should be a user-defined record with three fields: prereq_no , prereq_desc , and prereq_cost . For any ten courses that have a prerequisite course, populate the user-defined record with all corresponding data and display its information on the screen.

A4:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON DECLARE CURSOR c_cur IS SELECT course_no, description, cost, prerequisite FROM course WHERE prerequisite IS NOT NULL AND rownum <= 10; TYPE prerequisite_type IS RECORD (prereq_no NUMBER, prereq_desc VARCHAR(50), prereq_cost NUMBER); TYPE course_type IS RECORD (course_no NUMBER, description VARCHAR2(50), cost NUMBER, prerequisite_rec PREREQUISITE_TYPE); course_rec COURSE_TYPE; BEGIN FOR c_rec in c_cur LOOP course_rec.course_no := c_rec.course_no; course_rec.description := c_rec.description; course_rec.cost := c_rec.cost; SELECT course_no, description, cost INTO course_rec.prerequisite_rec.prereq_no, course_rec.prerequisite_rec.prereq_desc, course_rec.prerequisite_rec.prereq_cost FROM course WHERE course_no = c_rec.prerequisite; DBMS_OUTPUT.PUT_LINE ('Course: ' course_rec.course_no'  “ ' course_rec.description); DBMS_OUTPUT.PUT_LINE ('Cost: ' course_rec.cost); DBMS_OUTPUT.PUT_LINE ('Prerequisite: ' course_rec.prerequisite_rec. prereq_no'  “ ' course_rec.prerequisite_rec.prereq_desc); DBMS_OUTPUT.PUT_LINE ('Prerequisite Cost: ' course_rec.prerequisite_rec.prereq_cost); DBMS_OUTPUT.PUT_LINE ('========================================'); END LOOP; END;

In the declaration portion of the script, you define a cursor against the COURSE table; two user-defined record types, prerequisite_type and course_type ; and user-defined record, course_rec . It is important to note the order in which the record types are declared. The prerequsite_type must be declared first because one of the course_type elements is of the prerequisite_type .

In the executable portion of the script, you populate course_rec via the cursor FOR loop. First, you assign values to the course_rec.course_no, course_rec.description, and course_rec.cost. Next, you populate the nested record, prerequsite_rec, via the SELECT INTO statement against the COURSE table. Consider the notation used to reference individual elements of the nested record:

 course_rec.prerequisite_rec.prereq_no, course_rec.prerequisite_rec.prereq_desc, course_rec.prerequisite_rec.prereq_cost

You specify the name of the outer record followed by the name of the inner (nested) record followed by the name of the element. Finally, you display record information on the screen.

Note that this script does not contain a NO_DATA_FOUND exception handler even though there is a SELECT INTO statement. Why do you think this is the case?

When run, the script produces the following output:

  Course: 25 - Intro to Programming   Cost: 1195   Prerequisite: 140 - Structured Analysis   Prerequisite Cost: 1195   ========================================   Course: 80 - Structured Programming Techniques   Cost: 1595   Prerequisite: 204 - Intro to SQL   Prerequisite Cost: 1195   ========================================   Course: 100 - Hands-On Windows   Cost: 1195   Prerequisite: 20 - Intro to Computers   Prerequisite Cost: 1195   ========================================   Course: 120 - Intro to Java Programming   Cost: 1195   Prerequisite: 80 - Structured Programming Techniques   Prerequisite Cost: 1595   ========================================   Course: 122 - Intermediate Java Programming   Cost: 1195   Prerequisite: 120 - Intro to Java Programming   Prerequisite Cost: 1195   ========================================   Course: 124 - Advanced Java Programming   Cost: 1195   Prerequisite: 122 - Intermediate Java Programming   Prerequisite Cost: 1195   ========================================   Course: 125 - JDeveloper   Cost: 1195   Prerequisite: 122 - Intermediate Java Programming   Prerequisite Cost: 1195   ========================================   Course: 130 - Intro to Unix   Cost: 1195   Prerequisite: 310 - Operating Systems   Prerequisite Cost: 1195   ========================================   Course: 132 - Basics of Unix Admin   Cost: 1195   Prerequisite: 130 - Intro to Unix   Prerequisite Cost: 1195   ========================================   Course: 134 - Advanced Unix Admin   Cost: 1195   Prerequisite: 132 - Basics of Unix Admin   Prerequisite Cost: 1195   ========================================   PL/SQL procedure successfully completed.