When you abstract something, you generalize it, distancing yourself from the nitty-gritty details and concentrating on the big picture. When you create modules, you abstract the individual actions of the module into a name. The name (and program specification) represents those actions.

When you create a record, you abstract all the different attributes or fields of the subject of that record. You establish a relationship between those different attributes and give that relationship a name by defining a record.

Once you have stored information in records, you can perform operations on whole blocks of data at a time, rather than on each individual attribute. This kind of aggregate operation reinforces the abstraction of the record. Very often, you are not really interested in making changes to individual components of a record but instead to the object that represents all of those different components.

Suppose that in my job I need to work with companies. I don’t really care about whether a company has two lines of address information or three; instead, I want to work at the level of the company itself, making changes to, deleting, or analyzing the status of a company. In all of these cases I am talking about a whole row in the database, not any specific column. The company record hides all that information from me, yet makes it accessible if and when I need it. This orientation brings you closer to viewing your data as a collection of objects, with rules applied to those objects.

Using records also helps you to write cleaner code and less of it. When I use records, I invariably produce programs that have fewer lines of code, are less vulnerable to change, and need fewer comments. Records also cut down on variable sprawl; instead of declaring many individual variables, I declare a single record. This lack of clutter creates aesthetically attractive code that requires fewer resources to maintain.

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

You declare a record type with the TYPE statement. The TYPE statement specifies the name of the new record structure, and the components or fields that make up that record. The general syntax of the record TYPE definition is:

    TYPEtype_name IS RECORD
       (field_name1 datatype1,
                     field_name2 datatype2,
                     ...
                     field_nameN datatypeN
       );

where field_nameN is the name of the Nth field in the record, and datatypeN is the datatype of that Nth field. The datatype of a record’s field can be any of the following:

Here is an example of a record TYPE statement:

    TYPE company_rectype IS RECORD
       (comp# company.company_id%TYPE,
        name  company.name%TYPE);

You can declare a record TYPE in a local declaration section or in a package specification; the latter approach allows you to globally reference that record type in any PL/SQL block compiled in the schema that owns the package or in the PL/SQL blocks of any schema that has EXECUTE privileges on the package.

Once you have created your own customized record types, you can use those types in declarations of specific records. The actual record declarations have the following format:

record_name record_type;

where record_name is the name of the record, and record_type is the name of a record type that you have defined with the TYPE...RECORD statement.

To build a customer sales record, for example, I would first establish a record type called customer_sales_rectype, as follows:

    TYPE customer_sales_rectype IS RECORD
       (customer_id   NUMBER (5),
        customer_name customer.name%TYPE,
        total_sales   NUMBER (15,2)
       );

This is a three-field record structure that contains the primary key and name information for a customer, as well as a calculated, total amount of sales for the customer. I can then use this new record type to declare records with the same structure as this type:

    prev_customer_sales_rec customer_sales_rectype;
    top_customer_rec customer_sales_rectype;

Notice that I do not need the %ROWTYPE attribute, or any other kind of keyword, to denote this as a record declaration. The %ROWTYPE attribute is needed only for table and cursor records.

In addition to specifying the datatype, you can supply default values for individual fields in a record with the DEFAULT or := syntax. You can also apply constraints to the declaration of a record’s fields. You can specify that a field in a record be NOT NULL (in which case you must also assign a default value). Finally, each field name within a record must be unique.

Suppose that I declare the following subtype, a cursor, and an associative array data structure:^[*]

    DECLARE SUBTYPE long_line_type IS VARCHAR2(2000);

    CURSOR company_sales_cur IS
       SELECT name, SUM (order_amount) total_sales
         FROM company c, order o
        WHERE c.company_id = o.company_id;

    TYPE employee_ids_tabletype IS
       TABLE OF employee.employee_id%TYPE
       INDEX BY BINARY_INTEGER;

I can then define the following programmer-defined record in that same declaration section:

As you can see, PL/SQL offers tremendous flexibility in designing your own record structures. Your records can represent tables, views, and SELECT statements in a PL/SQL program. They can also be arbitrarily complex, with fields that are actually records within records or associative arrays.

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:

Several record-level operations are not yet supported:

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 cust_sales_cur%ROWTYPE;

       TYPE customer_sales_rectype IS RECORD
          (customer_id NUMBER(5),
           customer_name customer.name%TYPE,
           total_sales NUMBER(15,2)
          );
       prefererred_cust_rec customer_sales_rectype;
    BEGIN
       -- Assign one record to another.
       cust_sales_roundup_rec := cust_sales_rec;
       prefererred_cust_rec := cust_sales_rec;
    END;

Let’s look at some other examples of record-level operations.

Whenever possible, try to work with records at the aggregate level: the record as a whole, and not individual fields. The resulting code is much easier to write and maintain. There are, of course, many situations in which you need to manipulate individual fields of a record. Let’s take a look at how you would do that.

When you need to access a field within a record (to either read or change its value), you must use dot notation, just as you would when identifying a column from a specific database table. The syntax for such a reference is:

    [schema_name.][package_name.]record_name.field_name

You need to provide a package name only if the record is defined in the specification of a package that is different from the one you are working on at that moment. You need to provide a schema name only if the package is owned by a schema different from that in which you are compiling your code.

Once you have used dot notation to identify a particular field, all the normal rules in PL/SQL apply as to how you can reference and change the value of that field. Let’s take a look at some examples.

The assignment operator (:=) changes the value of a particular field. In the first assignment, total_sales is zeroed out. In the second assignment, a function is called to return a value for the Boolean flag output_generated (it is set to either TRUE or FALSE):

    BEGIN
       top_customer_rec.total_sales := 0;

       report_rec.output_generated :=
          check_report_status (report_rec.report_id);
    END;

In the next example I create a record based on the rain_forest_history table, populate it with values, and then insert a record into that same table:

    DECLARE
       rain_forest_rec rain_forest_history%ROWTYPE;
    BEGIN
       /* Set values for the record */
       rain_forest_rec.country_code  := 1005;
       rain_forest_rec.analysis_date :=
          ADD_MONTHS (TRUNC (SYSDATE), -3);
       rain_forest_rec.size_in_acres := 32;
       rain_forest_rec.species_lost  := 425;

       /* Insert a row in the table using the record values */
       INSERT INTO rain_forest_history
              (country_code, analysis_date, size_in_acres, species_lost)
       VALUES
          (rain_forest_rec.country_code,
           rain_forest_rec.analysis_date,
           rain_forest_rec.size_in_acres,
           rain_forest_rec.species_lost);
       ...
    END;

Notice that because the analysis_date field is of type DATE, I can assign any valid DATE expression to that field. The same goes for the other fields, and this is even true for more complex structures.

By the way, if you are using releases from Oracle9i Database Release 2 onwards, you can also perform a record-level insert, simplifying the above INSERT statement into nothing more than this:

    INSERT INTO rain_forest_history
       (country_code, analysis_date, size_in_acres, species_lost)
    VALUES rain_forest_rec;

Record-level DML (for both inserts and updates) is covered fully in Chapter 14.

Suppose that I have created a nested record structure; that is, one of the fields in my “outer” record is actually another record. In the following example I declare a record TYPE for all the elements of a telephone number (phone_rectype), and then declare a record TYPE that collects all the phone numbers for a person together in a single structure (contact_set_rectype).

    DECLARE
       TYPE phone_rectype IS RECORD
          (intl_prefix   VARCHAR2(2),
           area_code     VARCHAR2(3),
           exchange      VARCHAR2(3),
           phn_number    VARCHAR2(4),
           extension     VARCHAR2(4)
          );

       -- Each field is a nested record...
      TYPE contact_set_rectype IS RECORD
          (day_phone#    phone_rectype,
           eve_phone#    phone_rectype,
           fax_phone#    phone_rectype,
           home_phone#   phone_rectype,
           cell_phone#   phone_rectype
          );
     
       auth_rep_info_rec contact_set_rectype;
    BEGIN

Although I still use the dot notation to refer to a field with nested records, now I might have to refer to a field that is nested several layers deep inside the structure. To do this I must include an extra dot for each nested record structure, as shown in the following assignment, which sets the fax phone number’s area code to the home phone number’s area code:

    auth_rep_info_rec.fax_phone#.area_code :=
       auth_rep_info_rec.home_phone#.area_code;

Finally, here is an example demonstrating references to packaged records (and package-based record TYPEs). Suppose that I want to plan out my summer reading (for all those days I will be lounging about in the sand outside my Caribbean hideaway). I create a package specification as follows:

    CREATE OR REPLACE PACKAGE summer
    IS
       TYPE reading_list_rt IS RECORD (
          favorite_author  VARCHAR2 (100),
          title            VARCHAR2 (100),
          finish_by        DATE);

       must_read reading_list_rt;
       wifes_favorite reading_list_rt;
    END summer;

    CREATE OR REPLACE PACKAGE BODY summer
    IS
    BEGIN  -- Initialization section
       must_read.favorite_author := 'Tepper, Sheri S.';
       must_read.title := 'Gate to Women''s Country';
    END summer;

With this package compiled in the database, I can then construct my reading list as follows:

    DECLARE
       first_book summer.reading_list_rt;
       second_book summer.reading_list_rt;
    BEGIN
       summer.must_read.finish_by := TO_DATE ('01-AUG-2002', 'DD-MON-YYYY'),
       first_book := summer.must_read;

       second_book.favorite_author := 'Morris, Benny';
       second_book.title := 'Righteous Victims';
       second_book.finish_by := TO_DATE ('01-SEP-2002', 'DD-MON-YYYY'),
    END;

I declare two local book records. I then assign a “finish by” date to the packaged must-read book (notice the package.record.field syntax) and assign that packaged record to my first book of the summer record. I then assign values to individual fields for the second book of the summer.

Note that when you work with the UTL_FILE built-in package for file I/O in PL/SQL, you follow these same rules. The UTL_FILE.FILE_TYPE datatype is actually a record TYPE definition. So when you declare a file handle, you are really declaring a record from a package-based TYPE:

    DECLARE
       my_file_id UTL_FILE.FILE_TYPE;