Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 19.1 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 datatype. For example, suppose you have various data about a company, such as 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, thus making it easier to organize and represent company's information.

Table-Based and Cursor-Based Records

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

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 from 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,

note that 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, 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 
PL/SQL procedure successfully completed. 

As mentioned previously, a record does not have a value of its own. For this reason, you cannot test records for nullity, equality, or inequality. In other words, the statements IF course_rec IS NULL THEN … IF course_rec1 = course_rec2 THEN … are illegal and will cause syntax errors.

Next, consider an example of a cursor-based record.

FOR EXAMPLE

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: '||
student_rec.registration_date);
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 individual fields when data is fetched from the cursor to the record.

When run, this example produces the following output:

Name: Fred Crocitto 
Registration Date: 22-JAN-99 
Name: J. Landry 
Registration Date: 22-JAN-99 
Name: Laetia Enison 
Registration Date: 22-JAN-99 
Name: Angel Moskowitz 
Registration Date: 22-JAN-99 
PL/SQL procedure successfully completed. 

Note that 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, and as a result, when run, this example causes a syntax error.

FOR EXAMPLE

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: '||
student_rec.registration_date);
END LOOP;
END;
student_rec student_cur%ROWTYPE; 
* 
ERROR at line 2: 
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 10: 
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 17, column 52: 
PLS-00320: the declaration of the type of this expression is
 incomplete or malformed 
ORA-06550: line 17, column 7: 
PL/SQL: Statement ignored 

User-Defined Records

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 as follows (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 datatype. 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

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: '||time_rec.curr_date);
DBMS_OUTPUT.PUT_LINE ('Day: '||time_rec.curr_day);
DBMS_OUTPUT.PUT_LINE ('Time: '||time_rec.curr_time);
END;

In this example, the time_rec_type is a user-defined record type that contains three fields. Notice that the last field, curr_time, has been initialized to a particular value. The time_rec is a user-defined record based on the time_rec_type. Notice that, different from the previous examples, each record field is assigned a value individually. When run, the script produces the following output:

Date: 30-MAR-02 
Day: SATURDAY 
Time: 18:12:59 
PL/SQL procedure successfully completed. 

As mentioned earlier, when declaring a record type you may specify a NOT NULL constraint for individual fields. It is important to note that such fields must be initialized. Consider an example that causes a syntax error because a record field has not been initialized after a NOT NULL constraint has been defined on it.

FOR EXAMPLE

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;
field2 VARCHAR2(3) NOT NULL); 
* 
ERROR at line 4: 
ORA-06550: line 4, column 8: 
PLS-00218: a variable declared NOT NULL must have an 
initialization assignment 

Next, consider the correct version of the preceding example and its output.

FOR EXAMPLE

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;
sample_rec.field1 = 10 
sample_rec.field2 = ABC 
PL/SQL procedure successfully completed. 

Record Compatibility

You have seen that a record is defined by its name, structure, and type. However, it is important to realize that two records may have the same structure yet be of a different type. As a result, there are certain restrictions that apply to the operations between different record types. Consider the following example.

FOR EXAMPLE

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; however, 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 will cause an error as follows:

name_rec2 := name_rec1; -- illegal assignment 
* 
ERROR at line 15: 
ORA-06550: line 15, column 17: 
PLS-00382: expression is of wrong type 
ORA-06550: line 15, column 4: 
PL/SQL: Statement ignored 

In order 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 datatype as name_rec1, as follows:

FOR EXAMPLE

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;

It is important to note that the assignment restriction just mentioned applies to the user-defined records. In other words,

you can assign a table-based or a cursor-based record to a user-defined record as long as they have the same structure . Consider the following example.

FOR EXAMPLE

DECLARE
CURSOR course_cur IS
SELECT *
FROM course
WHERE rownum <= 4;
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;
course_rec1 := course_rec2;
course_rec3 := course_rec2;
END;

In this example, each record is a different type; however, they are compatible with each other because all records have the same structure. As a result, this example does not cause any syntax errors.