In Chapter 11, you were briefly introduced to the concept of a record type. You learned that a record is a composite data structure that allows you to combine different yet related data into a logical unit. You also learned that PL/SQL supports three kinds of record types: table based, cursor based, and user defined. In this chapter, you will revisit the table-based and cursor-based record types and learn about the user-defined record type. In addition, you will learn about records that contain collections and other records (called nested records) and collections of records.
A record structure is somewhat similar to a row of a database table. Each data item is stored in a field with its own name and data type. For example, suppose you have various data about a company, such as its name, address, and number of employees. A record containing a field for each of these items allows you to treat a company as a logical unit, thereby making it easier to organize and represent the company’s information.
The %ROWTYPE
attribute enables you to create table-based and cursor-based records. It is similar to the %TYPE
attribute that is used to define scalar variables. Consider the following example of a table-based record.
For Example ch16_1a.sql
DECLARE
course_rec course%ROWTYPE;
BEGIN
SELECT *
INTO course_rec
FROM course
WHERE course_no = 25;
DBMS_OUTPUT.PUT_LINE ('Course No: '||course_rec.course_no);
DBMS_OUTPUT.PUT_LINE ('Course Description: '||course_rec.description);
DBMS_OUTPUT.PUT_LINE ('Prerequisite: '||course_rec.prerequisite);
END;
The course_rec
record has the same structure as a row in the COURSE
table. As a result, there is no need to reference individual record fields when the SELECT INTO
statement populates the course_rec
record. However, a record does not have a value of its own; rather, each individual field holds a value. Therefore, to display record information on the screen, the individual fields are referenced using the dot notation, as shown in the DBMS_OUTPUT.PUT_LINE
statements.
When run, this example produces the following output:
Course No: 25
Course Description: Intro to Programming
Prerequisite: 140
Next, consider an example of a cursor-based record.
For Example ch16_2a.sql
DECLARE
CURSOR student_cur IS
SELECT first_name, last_name, registration_date
FROM student
WHERE rownum <= 4;
student_rec student_cur%ROWTYPE;
BEGIN
OPEN student_cur;
LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Name: '||student_rec.first_name||' '||student_rec.last_name);
DBMS_OUTPUT.PUT_LINE
('Registration Date: '||to_char(student_rec.registration_date, 'MM/DD/YYYY'));
END LOOP;
END;
The student_rec
record has the same structure as the rows returned by the student_cur
cursor. As a result, similar to the previous example, there is no need to reference the individual fields when data is fetched from the cursor to the record.
When run, this example produces the following output:
Name: George Kocka
Registration Date: 02/08/2007
Name: Janet Jung
Registration Date: 02/08/2007
Name: Kathleen Mulroy
Registration Date: 02/08/2007
Name: Joel Brendler
Registration Date: 02/08/2007
Because a cursor-based record is defined based on the rows returned by a select statement of a cursor, its declaration must be proceeded by a cursor declaration. In other words, a cursor-based record is dependent on a particular cursor and cannot be declared prior to its cursor.
Consider a modified version of the previous example. The cursor-based record variable is declared before the cursor (changes are shown in bold). In turn, when run, this example causes a syntax error.
For Example ch16_2b.sql
DECLARE
student_rec student_cur%ROWTYPE;
CURSOR student_cur IS
SELECT first_name, last_name, registration_date
FROM student
WHERE rownum <= 4;
BEGIN
OPEN student_cur;
LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Name: '||student_rec.first_name||' '||student_rec.last_name);
DBMS_OUTPUT.PUT_LINE
('Registration Date: '|| to_char(student_rec.registration_date, 'MM/DD/YYYY'));
END LOOP;
END;
This example produces the following erroneous output:
ORA-06550: line 2, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 16:
PL/SQL: Item ignored
ORA-06550: line 12, column 30:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 12, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 21:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 15, column 7:
PL/SQL: Statement ignored
ORA-06550: line 18, column 40:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 17, column 7:
PL/SQL: Statement ignored
So far, you have seen how to create records based on a table or a cursor. However, you may need to create a record that is not based on any table or any one cursor. For such situations, PL/SQL provides a user-defined record type that allows you to have complete control over the record structure.
The general syntax for creating a user-defined record is shown in Listing 16.1 (the reserved words and phrases surrounded by brackets are optional).
TYPE type_name IS RECORD
(field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]);
record_name TYPE_NAME;
First, a record structure is defined using the TYPE
statement, where type_name is the name of the record type that is used in the second step to declare the actual record. Enclosed in the parentheses are declarations of each record field with its name and data type. You may also specify a NOT NULL
constraint and/or assign a default value. Second, the actual record is declared based on the type specified in the previous step. Consider the following example.
For Example ch16_3a.sql
DECLARE
TYPE time_rec_type IS RECORD
(curr_date DATE,
curr_day VARCHAR2(12),
curr_time VARCHAR2(8) := '00:00:00'),
time_rec TIME_REC_TYPE;
BEGIN
SELECT sysdate
INTO time_rec.curr_date
FROM dual;
time_rec.curr_day := TO_CHAR(time_rec.curr_date, 'DAY'),
time_rec.curr_time := TO_CHAR(time_rec.curr_date, 'HH24:MI:SS'),
DBMS_OUTPUT.PUT_LINE ('Date: '||to_char(time_rec.curr_date, 'MM/DD/YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE ('Day: '||time_rec.curr_day);
DBMS_OUTPUT.PUT_LINE ('Time: '||time_rec.curr_time);
END;
In this example, time_rec_type
is a user-defined record type that contains three fields. The last field, curr_time
, has been initialized to a particular value. Here, time_rec
is a user-defined record based on the time_rec_type
. Unlike in the previous examples, each record field is assigned a value individually. When run, the script produces the following output:
Date: 05/20/2014 10:26:32
Day: TUESDAY
Time: 10:26:32
As mentioned earlier, when declaring a record type, you may specify a NOT NULL
constraint for individual fields. Such fields must be initialized. The following example causes a syntax error because a record field has not been initialized after a NOT NULL
constraint has been defined on it.
For Example ch16_4a.sql
DECLARE
TYPE sample_type IS RECORD
(field1 NUMBER(3),
field2 VARCHAR2(3) NOT NULL);
sample_rec sample_type;
BEGIN
sample_rec.field1 := 10;
sample_rec.field2 := 'ABC';
DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
END;
The preceding example produces this output:
ORA-06550: line 4, column 8:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
Now consider the correct version of this example (modified statements are highlighted in bold).
For Example ch16_4b.sql
DECLARE
TYPE sample_type IS RECORD
(field1 NUMBER(3),
field2 VARCHAR2(3) NOT NULL := 'ABC'), -- initialize a NOT NULL field
sample_rec sample_type;
BEGIN
sample_rec.field1 := 10;
DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
END;
This version of the example produces the following output:
sample_rec.field1 = 10
sample_rec.field2 = ABC
You have seen that a record is defined by its name, structure, and type. Actually, two records may have the same structure yet be of a different type. In such a case, certain restrictions apply to the operations between the different record types. Consider the following example:
For Example ch16_5a.sql
DECLARE
TYPE name_type1 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
TYPE name_type2 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
name_rec1 name_type1;
name_rec2 name_type2;
BEGIN
name_rec1.first_name := 'John';
name_rec1.last_name := 'Smith';
name_rec2 := name_rec1; -- illegal assignment
END;
In this example, both records have the same structure, but each record is of a different type. As a result, these records are not compatible with each other on the record level. In other words, an aggregate assignment statement
name_rec2 := name_rec1; -- illegal assignment
will cause an error:
ORA-06550: line 15, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored
To assign name_rec1
to name_rec2
, you can assign each field of name_rec1
to the corresponding field of name_rec2
, or you can declare name_rec2
so that it has the same data type as name_rec1
(changes are shown in bold).
For Example ch16_5b.sql
DECLARE
TYPE name_type1 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
name_rec1 name_type1;
name_rec2 name_type1;
BEGIN
name_rec1.first_name := 'John';
name_rec1.last_name := 'Smith';
name_rec2 := name_rec1; -- no longer illegal assignment
END;
The assignment restriction just mentioned applies to user-defined records. In other words, you can assign a table-based or cursor-based record to a user-defined record as long as they have the same structure. Consider the following example:
For Example ch16_6a.sql
DECLARE
CURSOR course_cur IS
SELECT *
FROM course
WHERE rownum < 2;
TYPE course_type IS RECORD
(course_no NUMBER(38)
,description VARCHAR2(50)
,cost NUMBER(9,2)
,prerequisite NUMBER(8)
,created_by VARCHAR2(30)
,created_date DATE
,modified_by VARCHAR2(30)
,modified_date DATE);
course_rec1 course%ROWTYPE; -- table-based record
course_rec2 course_cur%ROWTYPE; -- cursor-based record
course_rec3 course_type; -- user-defined record
BEGIN
-- Populate table-based record
SELECT *
INTO course_rec1
FROM course
WHERE course_no = 10;
-- Populate cursor-based record
OPEN course_cur;
LOOP
FETCH course_cur INTO course_rec2;
EXIT WHEN course_cur%NOTFOUND;
END LOOP;
-- Assign COURSE_REC2 to COURSE_REC1 and COURSE_REC3
course_rec1 := course_rec2;
course_rec3 := course_rec2;
DBMS_OUTPUT.PUT_LINE (course_rec1.course_no||' - '||course_rec1.description);
DBMS_OUTPUT.PUT_LINE (course_rec2.course_no||' - '||course_rec2.description);
DBMS_OUTPUT.PUT_LINE (course_rec3.course_no||' - '||course_rec3.description);
END;
In this example, each record is of a different type; however, they are compatible with one another because all of the records have the same structure. As a result, this example does not cause any syntax errors and produces the following output:
10 - Technology Concepts
10 - Technology Concepts
10 - Technology Concepts
As mentioned in the introduction to this chapter, PL/SQL allows you to define nested records—that is, records that contain other records and collections. The record that contains a nested record or collection is called an enclosing record.
Consider the code fragment in Listing 16.2.
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 (highlighted in bold).
Next, consider the complete version of the script based on the declaration of the nested record in Listing 16.2. References to the nested record are shown in bold.
For Example ch16_7a.sql
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. To reference its field name
, which is a record with two fields, you use the syntax shown in Listing 16.3. The parentheses are included in this listing solely for readability purposes.
enclosing_record.(nested_record or nested_collection).field_name
In this case, person_rec
is the enclosing record because it contains the name
record as one of its fields. In other words, the name
record is nested in the person_rec
record.
This example produces the following output:
Name: George Kocka
Street: 24 Beaufield St.
City: Dorchester
State: MA
Zip: 02124
A nested record may also contain a collection as one of its fields. In the following example, given a value of a ZIP code, the names of the students residing in that ZIP code area are displayed on the screen.
For Example ch16_8a.sql
DECLARE
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY PLS_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_index PLS_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_index := v_index + 1;
zip_info_rec.last_name_tab(v_index) := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE
('Names('||v_index||'): '||zip_info_rec.last_name_tab(v_index));
END LOOP;
END;
The declaration section of this example contains declarations of the associative array type, last_name_type
; record type, zip_info_type
; and nested user-defined record, zip_info_rec
. The field, last_name_tab
, of the zip_info_rec
is an associative array that is populated with the help of the cursor, name_cur
. In addition, the declaration portion contains two variables, v_zip
and v_index
. The variable v_zip
is used to store the incoming value of the ZIP code provided at run time. The variable v_index
is used to populate the associative array, last_name_tab
. The executable portion of the script assigns values to the individual record fields, zip
and last_name_tab
. The last_name_tab
is an associative array, which is populated via the cursor FOR
loop.
When the value of 11368 is provided for the ZIP code at run time, this script produces the following output:
ZIP: 11368
Names(1): Lasseter
Names(2): Miller
Names(3): Boyd
Names(4): Griffen
Names(5): Hutheesing
Names(6): Chatman
In Lab 16.2, you saw an example of a nested record in which one of the record fields was defined as an associative array. PL/SQL also gives you the ability to define a collection of records (for example, an associative array where the element type is a cursor-based record). The following example illustrates this usage.
For Example ch16_9a.sql
DECLARE
CURSOR name_cur IS
SELECT first_name, last_name
FROM student
WHERE ROWNUM <= 4;
TYPE name_type IS TABLE OF name_cur%ROWTYPE
INDEX BY PLS_INTEGER;
name_tab name_type;
v_index INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_index := v_index + 1;
name_tab(v_index).first_name := name_rec.first_name;
name_tab(v_index).last_name := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('First Name('||v_index ||'): '||
name_tab(v_index).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_index ||'): '||
name_tab(v_index).last_name);
END LOOP;
END;
The declaration section of this example contains a definition of the name_cur
cursor, which returns the first and last names of four students. In addition, it defines an associative array type. The element type of the associative array is a cursor-based record defined as %ROWTYPE
. In addition, this script defines an associative array variable and the index variable that is used later to reference individual rows of the associative array.
The executable section of the example populates the associative array and displays its records on screen. The notation used in the preceding example to reference individual elements of the array is shown in Listing 16.4.
collection_name(index).record_field_name1
collection_name(index).record_field_name2
...
collection_name(index).record_field_nameN
To reference each row of the array, you use the index variable just as in all of the previous examples that employed collections. However, because each row of this associative array is a record, you must also reference individual fields of the underlying record.
This example produces the following output:
First Name(1): George
Last Name(1): Kocka
First Name(2): Janet
Last Name(2): Jung
First Name(3): Kathleen
Last Name(3): Mulroy
First Name(4): Joel
Last Name(4): Brendler
Next, consider a modified version of the preceding example. In this version, the collection type has been changed from an associative array to a nested table (all changes are shown in bold).
DECLARE
CURSOR name_cur IS
SELECT first_name, last_name
FROM student
WHERE ROWNUM <= 4;
TYPE name_type IS TABLE OF name_cur%ROWTYPE;
name_tab name_type := name_type();
v_index INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_index := v_index + 1;
name_tab.EXTEND;
name_tab(v_index).first_name := name_rec.first_name;
name_tab(v_index).last_name := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('First Name('||v_index||'): '||
name_tab(v_index).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_index||'): '||
name_tab(v_index).last_name);
END LOOP;
END;
The only differences in regard to the previous version of the script are the collection type declaration and methods required for the collection initialization. All references to the record and its individual fields remain unchanged. This version of the script produces the same output as the earlier version:
First Name(1): George
Last Name(1): Kocka
First Name(2): Janet
Last Name(2): Jung
First Name(3): Kathleen
Last Name(3): Mulroy
First Name(4): Joel
Last Name(4): Brendler
So far, you have seen examples where a collection of records was defined on the cursor-based record type. Next, consider an example where a collection of records is defined on the user-defined record type.
For Example ch16_10a.sql
DECLARE
CURSOR enroll_cur IS
SELECT first_name, last_name, COUNT(*) total
FROM student
JOIN enrollment USING (student_id)
GROUP BY first_name, last_name;
TYPE enroll_rec_type IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30),
enrollments INTEGER);
TYPE enroll_array_type IS TABLE OF enroll_rec_type
INDEX BY PLS_INTEGER;
enroll_tab enroll_array_type;
v_index INTEGER := 0;
BEGIN
FOR enroll_rec IN enroll_cur
LOOP
v_index := v_index + 1;
enroll_tab(v_index).first_name := enroll_rec.first_name;
enroll_tab(v_index).last_name := enroll_rec.last_name;
enroll_tab(v_index).enrollments := enroll_rec.total;
IF v_index <= 4
THEN
DBMS_OUTPUT.PUT_LINE('First Name('||v_index||'): '||
enroll_tab(v_index).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_index||'): '||
enroll_tab(v_index).last_name);
DBMS_OUTPUT.PUT_LINE('Enrollments('||v_index||'): '||
enroll_tab(v_index).enrollments);
DBMS_OUTPUT.PUT_LINE ('--------------------'),
END IF;
END LOOP;
END;
The declaration section of the script contains a user-defined record type, enroll_rec_type
, which is subsequently used in the declaration of the associative array type, enroll_array_type
. Finally, the associative array, enroll_tab
, is declared based on the enroll_array_type
.
In the executable portion of the script, the associative array, enroll_tab
, is populated via the cursor FOR
loop and the first four records of the associative array are displayed on the screen.
When run, this script produces the following output:
First Name(1): Judy
Last Name(1): Sethi
Enrollments(1): 1
--------------------
First Name(2): Larry
Last Name(2): Walter
Enrollments(2): 2
--------------------
First Name(3): Winsome
Last Name(3): Laporte
Enrollments(3): 2
--------------------
First Name(4): Hiedi
Last Name(4): Lopez
Enrollments(4): 1
--------------------
In this chapter, you learned about the different types of records supported in PL/SQL and saw how to manipulate individual record elements. You have also learned about record compatibility and explored how it affects your ability to assign or compare records to each other. In addition, you discovered how different record types may be nested inside one another and learned how to define and manipulate a record that contains a collection element. Finally, you learned how to define and handle collections of records.