Oracle PL/SQL for DBAs

Working with Records

Regardless of how you define a record (based on a table, cursor, or explicit record TYPE statement), you work with the resulting record in the same ways. You can work with the data in a record at the record level, or you can work with individual fields of the record.

Record-level operations

When you work at the record level, you avoid any references to individual fields in the record. Here are the record-level operations currently supported by PL/SQL:

• You can copy the contents of one record to another (as long as they are compatible in structure—that is, have the same number of fields and the same or convertible datatypes).

• You can assign a value of NULL to a record with a simple assignment.

• You can define and pass the record as an argument in a parameter list.

• You can RETURN a record back through the interface of a function.

You can perform record-level operations on any records with compatible structures. In other words, the records must have the same number of fields and the same or convertible datatypes, but they don’t have to be the same type. Suppose that I have created the following table:

    CREATE TABLE cust_sales_roundup (
       customer_id NUMBER (5),
       customer_name VARCHAR2 (100),
       total_sales NUMBER (15,2)
       );

Then the three records defined as follows all have compatible structures, and I can mix and match the data in these records as shown:

 DECLARE cust_sales_roundup_rec cust_sales_roundup%ROWTYPE; CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup; cust_sales_rec ...