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 RecordsThe %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.
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 RecordsSo 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 CompatibilityYou 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. |