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

Get Oracle® PL/SQL® Interactive Workbook, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.