Oracle® PL/SQL® Interactive Workbook, Second Edition

Chapter 19 Records

1)

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