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. |
|
لطفا منتظر باشید ...
