Lab 19.2 Nested Records
Lab Objective
After this Lab, you will be able to:
As mentioned in the introduction to this chapter, PL/SQL allows you to define nested records. These are records that contain other records and collections. The record that contains a nested record or collection is called an enclosing record.
Consider the following code fragment.
FOR EXAMPLE
DECLARE
TYPE name_type IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
TYPE person_type IS
(name name_type,
street VARCHAR2(50),
city VARCHAR2(25),
state VARCHAR2(2),
zip VARCHAR2(5));
person_rec person_type;
This code fragment contains two user-defined record types. The second user-defined record type, person_type, is a nested record type because its field name is a record of the name_type type.
Next, consider the complete version of the preceding example.
FOR EXAMPLE
DECLARE
TYPE name_type IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
TYPE person_type IS RECORD
(name name_type,
street VARCHAR2(50),
city VARCHAR2(25),
state VARCHAR2(2),
zip VARCHAR2(5));
person_rec person_type;
BEGIN
SELECT first_name, last_name, street_address, city,
state, zip
INTO person_rec.name.first_name,
person_rec.name.last_name, person_rec.street,
person_rec.city, person_rec.state, person_rec.zip
FROM student
JOIN zipcode USING (zip)
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE ('Name: '||
person_rec.name.first_name||' '||
person_rec.name.last_name);
DBMS_OUTPUT.PUT_LINE ('Street: '||
person_rec.street);
DBMS_OUTPUT.PUT_LINE ('City: '||
person_rec.city);
DBMS_OUTPUT.PUT_LINE ('State: '||
person_rec.state);
DBMS_OUTPUT.PUT_LINE ('Zip: '||
person_rec.zip);
END;
In this example, the person_rec record is a user-defined nested record. As a result, in order to reference its field name that is a record with two fields, the following syntax is used:
enclosing_record.(nested_record or
nested_collection).field_name
In this case, the person_rec is enclosing record because it contains the name record as one of its fields while the name record is nested in the person_rec record.
This example produces the following output:
Name: James E. Norman
Street: PO Box 809 Curran Hwy
City: North Adams
State: MA
Zip: 01247
PL/SQL procedure successfully completed.
Lab 19.2 Exercises
19.2.1 Use Nested Records
In this exercise, you will learn more about nested records.
Create the following PL/SQL script:
-- ch19_3a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE zip_info_type IS RECORD
(zip VARCHAR2(5),
last_name_tab last_name_type);
CURSOR name_cur (p_zip VARCHAR2) IS
SELECT last_name
FROM student
WHERE zip = p_zip;
zip_info_rec zip_info_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_counter INTEGER := 0;
BEGIN
zip_info_rec.zip := v_zip;
FOR name_rec IN name_cur (v_zip) LOOP
v_counter := v_counter + 1;
zip_info_rec.last_name_tab(v_counter) :=
name_rec.last_name;
END LOOP;
END;
Answer the following questions:
| a) |
Explain the script ch19_3a.sql. | | b) |
Modify the script so that zip_info_rec data is displayed on the screen. Make sure that a value of the zipcode is displayed only once. Provide the value of '11368' when running the script. | | c) |
Modify the script created in the previous exercise (ch19_3b.sql). Instead of providing a value for a zipcode at runtime, populate via the cursor FOR loop. The SELECT statement associated with the new cursor should return zipcodes that have more than one student in them. |
Lab 19.2 Exercise Answers
This section gives you some suggested answers to the questions in Lab 19.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.
19.2.1 Answers| a) | Explain the script ch19_3a.sql. | A1:
| Answer: The declaration portion of the script contains index-by table type, last_name_type, record type, zip_info_type, and nested-user-defined record, zip_info_rec, declarations. The field, last_name_tab, of the zip_info_rec is an index-by table that is populated with the help of the cursor, NAME_CUR. In addition, the declaration portion also contains two variables, v_zip and v_counter. The variable v_zip is used to store incoming value of the zipcode provided at runtime. The variable v_counter is used to populate the index-by table, last_name_tab. The executable portion of the script assigns values to the individual record fields, zip and last_name_tab. As mentioned previously, the last_name_tab is an index-by table, and it is populated via cursor FOR loop. | | b) | Modify the script so that zip_info_rec data is displayed on the screen. Make sure that a value of the zipcode is displayed only once. Provide the value of '11368' when running the script. | A2:
| Answer: Your script should look similar to the following script. Changes are shown in bold letters.
-- ch19_3b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE zip_info_type IS RECORD
(zip VARCHAR2(5),
last_name_tab last_name_type);
CURSOR name_cur (p_zip VARCHAR2) IS
SELECT last_name
FROM student
WHERE zip = p_zip;
zip_info_rec zip_info_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_counter INTEGER := 0;
BEGIN
zip_info_rec.zip := v_zip;
DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);
FOR name_rec IN name_cur (v_zip) LOOP
v_counter := v_counter + 1;
zip_info_rec.last_name_tab(v_counter) :=
name_rec.last_name;
DBMS_OUTPUT.PUT_LINE ('Names('||v_counter||'): '||
zip_info_rec.last_name_tab(v_counter));
END LOOP;
END;
In order to display the value of the zipcode only once, the DBMS_OUTPUT. PUT_LINE statement
DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);
is placed outside the loop. When run, this script produces the following output:
Enter value for sv_zip: 11368
old 15: v_zip VARCHAR2(5) := '&sv_zip';
new 15: v_zip VARCHAR2(5) := '11368';
Zip: 11368
Names(1): Lasseter
Names(2): Miller
Names(3): Boyd
Names(4): Griffen
Names(5): Hutheesing
Names(6): Chatman
PL/SQL procedure successfully completed.
| | c) | Modify the script created in the previous exercise (ch19_3b.sql). Instead of providing a value for a zipcode at runtime, populate via the cursor FOR loop. The SELECT statement associated with the new cursor should return zipcodes that have more than one student in them. | A3:
| Answer: Your script should look similar to the following script. Changes are shown in bold letters.
-- ch19_3c.sql, version 3.0
SET SERVEROUTPUT ON SIZE 20000
DECLARE
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE zip_info_type IS RECORD
(zip VARCHAR2(5),
last_name_tab last_name_type);
CURSOR zip_cur IS
SELECT zip, COUNT(*)
FROM student
GROUP BY zip
HAVING COUNT(*) > 1;
CURSOR name_cur (p_zip VARCHAR2) IS
SELECT last_name
FROM student
WHERE zip = p_zip;
zip_info_rec zip_info_type;
v_counter INTEGER;
BEGIN
FOR zip_rec IN zip_cur LOOP
zip_info_rec.zip := zip_rec.zip;
DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);
v_counter := 0;
FOR name_rec IN name_cur (zip_info_rec.zip) LOOP
v_counter := v_counter + 1;
zip_info_rec.last_name_tab(v_counter) :=
name_rec.last_name;
DBMS_OUTPUT.PUT_LINE ('Names('||v_counter||'): '||
zip_info_rec.last_name_tab(v_counter));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('----------');
END LOOP;
END;
In the preceding script, you declared a new cursor called zip_cur. This cursor returns zipcodes that have more than one student in them. Next, in the body of the script, you use nested cursors to populate the last_name_tab index-by table for each value of zipcode. First, the outer cursor FOR loop populates the zip field of the zip_info_rec and displays its value on the screen. Then it passes the zip field as a parameter to the inner cursor FOR loop that populates last_name_tab table with last names of corresponding students. Consider the partial output of the preceding example:
Zip: 06820
Names(1): Scrittorale
Names(2): Padel
Names(3): Kiraly
--------------------
Zip: 06830
Names(1): Dennis
Names(2): Meshaj
Names(3): Dalvi
--------------------
Zip: 06880
Names(1): Miller
Names(2): Cheevens
--------------------
Zip: 06903
Names(1): Segall
Names(2): Annina
--------------------
Zip: 07003
Names(1): Wicelinski
Names(2): Intal
--------------------
Zip: 07010
Names(1): Lopez
Names(2): Mulroy
Names(3): Velasco
Names(4): Kelly
Names(5): Tucker
Names(6): Mithane
--------------------
…
PL/SQL procedure successfully completed.
|
Lab 19.2 Self-Review Questions
In order to test your progress, you should be able to answer the following questions.
Answers appear in Appendix A, Section 19.2.
| 1) |
A record is called a nested record if it contains
_____ other records. _____ collections. _____ all of the above _____ none of the above
| | 2) |
When creating a nested record, you are allowed to nest only a single record or a single collection.
_____ True _____ False
| | 3) |
When creating a nested record, you must initialize
_____ all of the fields of the enclosing record. _____ at least one of the fields of the enclosing record. _____ a field of the enclosing record only if there is a NOT NULL constraint defined in it.
| | 4) |
It is illegal to declare a record field as an index-by table.
_____ True _____ False
| | 5) |
It is illegal to declare a record field as a varray.
_____ True _____ False
|
|
No comments:
Post a Comment