Lab 19.1 Records


Lab Objectives

After this Lab, you will be able to:

Use Table-Based and Cursor-Based Records

Use User -Defined 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.  
graphics/error_icon.gif

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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net