9.1.1 Different Types of Records

PL/SQL supports three different kinds of records: table-based, cursor-based, and programmer-defined. These different types of records are used in different ways and for different purposes, but all three share the same internal structure: every record is composed of one or more fields. However, the way these fields are defined in the record depend on the record type. Table 9.1 shows this information about each record type.

Figure 9.1 illustrates the way a cursor record adopts the structure of the SELECT statement by using the %ROWTYPE declaration attribute (explained later in this chapter).

Figure 9.1: Mapping of cursor structure to PL/SQL record

9.1.2 Accessing Record-Based Data

You access the fields within a record using dot notation, just as you would identify a column from a database table, in the following format:

<record name>.<field name>

You would reference the first_name column from the employee table as follows:

employee.first_name

You would reference the emp_full_name field in the employee PL/SQL record as:

employee_rec.emp_full_name 

The record or tuple structure of relational database tables has proven to be a very powerful way to represent data in a database, and records in your programs offer similar advantages. The next section describes briefly the reasons you might want to use records. The rest of this chapter show you how to define and use each of the different types of records, and the situations appropriate to each record type.

9.1.3 Benefits of Using Records

The record data structure provides a high-level way of addressing and manipulating program-based data. This approach offers the following benefits:

Data abstraction

Instead of working with individual attributes of an entity or object, you think of and manipulate that entity as a "thing in itself."

Aggregate operations

You can perform operations which apply to all the columns of a record.

Leaner, cleaner code

You can write less code and make what you do write more understandable.

The following sections describe each of these benefits.

9.1.4 Guidelines for Using Records

Use of PL/SQL records can have a dramatic impact on your programs, both in initial development and in ongoing maintenance. To ensure that I personally get the most out of record structures, I have set the following guidelines for my development:

• Create corresponding cursors and records. Whenever I create a cursor in my programs, I also create a corresponding record (except in the case of cursor FOR loops). I always FETCH into a record, rather than into individual variables. In those few instances when it might involve a little extra work over simply fetching into a single variable, I marvel at the elegance of this approach and compliment myself on my commitment to principle.

• Create table-based records. Whenever I need to store table-based data within my programs, I create a new (or use a predefined) table-based record to store that data. I keep my variable use to a minimum and dynamically link my program data structures to my RDBMS data structures with the %ROWTYPE attribute.

• Pass records as parameters. Whenever appropriate, I pass records rather than individual variables as parameters in my procedural interfaces. This way, my procedure calls are less likely to change over time, making my code more stable. There is a downside to this technique, however: if a record is passed as an OUT or IN OUT parameter, its field values are saved by the PL/SQL program in case of the need for a rollback. This can use up memory and consume unnecessary CPU cycles.

9.1.5 Referencing a Record and its Fields

The rules you must follow for referencing a record in its entirety or a particular field in the record are the same for all types of records: table, cursor, and programmer-defined.

A record's structure is similar to that of a database table. Where a table has columns, a record has fields. You reference a table's column by its name in a SQL statement, as in:

SELECT company_id FROM company;

Of course, the fully qualified name of a column is:

<table_name>.<column_name>

This full name is often required in a SQL statement to avoid ambiguity, as is true in the following statement:

SELECT employee.company_id, COUNT(*) total_employees
  FROM company, employee
 WHERE company.company_id = employee.company_id;

If I do not preface the name of company_id with the appropriate table name, the SQL compiler will not know to which table that column belongs. The same is true for a record's fields. You reference a record by its name, and you reference a record's field by its full name using dot notation, as in:

<record_name>.<field_name>

So if I create a record named company_rec, which contains a company_id field, then I would reference this field as follows:

company_rec.company_id

You must always use the fully qualified name of a field when referencing that field. If you don't, PL/SQL will never be able to determine the "default" record for a field, as it does in a SQL statement.

You do not, on the other hand, need to use dot notation when you reference the record as a whole; you simply provide the name of the record. In the following example, I pass a record as a parameter to a procedure:

DECLARE
   TYPE customer_sales_rectype IS RECORD (...);
   customer_rec customer_sales_rectype;
BEGIN
   display_sales_data (customer_rec);
END; 

I didn't make a single dotted reference to any particular field in the customer record. Instead I declared the record type, used it to create the record, used the record type again to define the type for the parameter in the procedure specification, and finally called the procedure, passing it the specific record I declared.

9.1.6 Comparing Two Records

While it is possible to stay at the record level in certain situations, you can't avoid direct references to fields in many other cases. If you want to compare records, for example, you must always do so through comparison of the records' individual fields.

Suppose you want to know if the old company information is the same as the new company information, both being stored in records of the same structure. The following test for equality will not compile:

IF old_company_rec = new_company_rec /-- Illegal syntax!
THEN
   ...
END IF;

even though the structures of the two records are absolutely identical and based on the same record type (in this case, a table record type).

PL/SQL will not automatically compare each individual field in the old company record to the corresponding field in the new company record. Instead, you will have to perform that detailed check yourself, as in:

IF old_company_rec.name = new_company_rec.name AND
   old_company_rec.incorp_date = new_company_rec.incorp_date AND
   old_company_rec.address1 = new_company_rec.address1 AND
THEN
   ... the two records are identical ...
END IF;

Of course, you do not simply examine the value of a particular field when you work with records and their fields. Instead, you will assign values to the record and its fields, from either scalar variables or other records. You can reference a record's field on both sides of the assignment operator. In the following example I change the contents of a record, even though that record was just filled from a cursor:

DECLARE
   CURSOR company_cur IS ...;
   company_rec company_cur%ROWTYPE;
BEGIN
   OPEN company_cur;
   FETCH company_cur INTO company_rec;
   company_rec.name := 'New Name';
END;

There is, in other words, no such thing as a "read-only" PL/SQL record structure.