Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 9.5 Assigning Values to and from RecordsChapter 9
Records in PL/SQL
Next: 9.7 Nested Records

9.6 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:

CREATE TABLE cust_sales_roundup
      (customer_id NUMBER (5),
       customer_name VARCHAR2 (100),
       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.

9.6.1 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 assignmentwould cause an erroor:

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 noncomposite 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:

Previous: 9.5 Assigning Values to and from RecordsOracle PL/SQL Programming, 2nd EditionNext: 9.7 Nested Records
9.5 Assigning Values to and from RecordsBook Index9.7 Nested Records

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference