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.
Using the previously defined records, the following sections describe the various restrictions you will encounter due to incompatible record types.
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;
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:
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.
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 (
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.