Record Types PL SQL

Record Types and Record Compatibility

As we have discussed, PL/SQL supports three types of records: table-based, cursor-based, and programmer-defined. A record is defined by its name, its type, and its structure. Two records can have the same structure but be of a different type. PL/SQL places restrictions on certain operations between different record types. This section explains these restrictions based on the records declared below: 

The table structure against which all the different types of records will be declared: 

CREATE TABLE cust_sales_roundup

(customer_id NUMBER (5),

customer_name VARCHAR2 (100),

total_sales NUMBER (15,2));

• The table-based record:

cust_sales_roundup_rec cust_sales_roundup%ROWTYPE;

• The cursor-based record:

CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup;

cust_sales_rec cust_sales_cur%ROWTYPE;

• The programmer-defined record:

TYPE customer_sales_rectype 

IS 

RECORD

   (customer_id NUMBER (5),

    customer_name customer.name%TYPE,

    total_sales NUMBER (15,2));

top_customer_rec  customer_sales_rectype;

A "manual" record: a collection of individual variables which the programmer can treat as a group by always making changes to and referring to all variables together:

V_customer_id NUMBER (5);

v_customer_name customer_name%TYPE;

V_total_sales NUMBER (15, 2);

All three PL/SQL records defined above (cust_sales_roundup_rec, cust_sales_rec, and top_customer_rec) and the "MANUAL" record have exactly the same structure. Each, however, is of a different type. Records of different types are incompatible with each other at the record level. As a result, you can't perform certain kinds of operations between them.

 Assignment Restrictions

Using the previously defined records, the following sections describe the various restrictions you will encounter due to incompatible record types.

 Manual records

You cannot assign a manual record to a real record of any type, and vice versa. If you want to assign individual variables to a record, or assign values in fields to individual variables, you must execute a separate assignment for each field in the record:

top_customer_rec.customer_id:= v_customer_id;

top_customer_rec.customer_name:= v_customer_name;

top_customer_rec.total_sales:= v_total_sales;

 Records of the same type

You can perform aggregate assignments only between records of the same type and same source. All of the aggregate assignments you saw in previous examples were valid because both the source and target records in the assignment were based on the same table, cursor, or TYPE statement.

The two assignments below are invalid and will fail because the record types do not match: 

cust_sales_roundup_rec:= top_customer_rec; /* Incompatible! */

cust_sales_rec:= cust_sales_roundup_rec; /* Incompatible! */

Even when both records in an aggregate assignment are the same type and same structure, the assignment can fail. Your assignment must, in addition, conform to these rules:

·         Both cursor-based records in an aggregate assignment must be based on the same cursor.

·         Both table-based records in an aggregate assignment must be based on the same table.

·         Both programmer-defined records in an aggregate assignment must be based on the same TYPE...RECORD statement.

  Setting records to NULL

In earlier versions of Oracle (7.2 and below), the following assignment would cause an error:

comp_sales_rec:= NULL;

NULL was treated as a scalar value, and would not be applied to each of the record's fields.

In Oracle 7.3 and above, the assignment of NULL to a record is allowed, and will set each of the fields back to the default value of NULL.

9.6.2 Record Initialization

When you declare a scalar variable (a variable with a scalar or non composite datatype) you can provide a default or initial value for that variable. In the following example, I declare the total_sales variable and initialize it to zero using both the DEFAULT syntax and the assignment operator:

total_sales NUMBER (15,2) := 0;

As you might expect based on the aggregate assignment discussed above, you can initialize a table or cursor record at the time of declaration only with another record of the same type and source.

If you want to initialize a record at the time of its declaration, you must use a compatible record to the right of the assignment operator (:=) or DEFAULT phrase. 

The following two examples show such initializations: 

Declare a local record with the same type and structure as the parameter and then set the default value of that local record to the incoming record:

PROCEDURE compare_companies

       (Prev_company_rec IN company%ROWTYPE)

IS

   curr_company_rec company%ROWTYPE: = prev_company_rec;

BEGIN

   ...

END;

Create a new record type and record. Then create a second record type using the first record type as its single column. Initialize this new record with the previously-defined record:

 DECLARE

   TYPE first_rectype IS RECORD (var1 VARCHAR2 (100));

   first_rec first_rectype;

   TYPE second_rectype IS RECORD

      (nested_rec first_rectype:= first_rec);

BEGIN

   ...

END;