9.6 Record Types and Record Compatibility

Chapter 9
Records in PL/SQL

 

---

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.

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.

9.6.1.1 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;