Lab 19.1 Exercises

19.1.1 Use Table-Based and Cursor-Based Records

In this exercise, you will learn more about table-based and cursor-based records.

Create the following PL/SQL script:

 -- ch19_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    zip_rec zipcode%ROWTYPE; BEGIN    SELECT *      INTO zip_rec      FROM zipcode     WHERE rownum < 2; END; 

Answer the following questions:

a)	Explain the script ch19_1a.sql.
b)	Modify the script so that zip_rec data is displayed on the screen.
c)	Modify the script created in the previous exercise (ch19_1b.sql) so that zip_rec is defined as a cursor-based record.
d)	Modify the script created in the previous exercise (ch19_1c.sql). Change the structure of the zip_rec record so that it contains total number of students in a given city, state, and zipcode. Do not include audit columns such as CREATED_BY and CREATED_DATE in the record structure.

19.1.2 Use User -Defined Records

In this exercise, you will learn more about user-defined records.

Create the following PL/SQL script:

 -- ch19_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    CURSOR zip_cur IS       SELECT zip, COUNT(*) students         FROM student       GROUP BY zip;    TYPE zip_info_type IS RECORD       (zip_code VARCHAR2(5),        students INTEGER);    zip_info_rec zip_info_type; BEGIN    FOR zip_rec IN zip_cur LOOP       zip_info_rec.zip_code := zip_rec.zip;       zip_info_rec.students := zip_rec.students;    END LOOP; END; 

Answer the following questions:

a)	Explain the script ch19_2a.sql.
b)	Modify the script so that zip_info_rec data is displayed on the screen only for the first five records returned by the ZIP_CUR cursor.
c)	Modify the script created in the previous exercise (ch19_2b.sql). Change the structure of the zip_info_rec record so that it also contains total number of instructors for a given zipcode. Populate this new record and display its data on the screen for the first five records returned by the ZIP_CUR cursor.