Programmer-Defined Records

Now you know how to create a record with the same structure as a table or a cursor. These are certainly very useful constructs in a programming language designed to interface with the Oracle RDBMS. Yet do these kinds of records cover all of our needs for composite data structures?

What if I want to create a record that has nothing to do with either a table or a cursor? What if I want to create a record whose structure is derived from several different tables and views? Should I really have to create a "dummy" cursor just so I can end up with a record of the desired structure? For just these kinds of situations, PL/SQL offers programmer-defined records, declared with the TYPE...RECORD statement.

Programmer-defined records are supported -- but undocumented -- in PL/SQL Release 1.1.

With the programmer-defined record, you have complete control over the number, names, and datatypes of fields in the record.

To declare a programmer-defined record, you must perform two distinct steps: 

1.    Declare or define a record TYPE containing the structure you want in your record. 

2.    Use this record TYPE as the basis for declarations of your own actual records having that structure.

 

Declaring Programmer-Defined Record TYPEs

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

The general syntax of the record TYPE definition is:

TYPE <type_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: 

•  Pre-defined datatype (VARCHAR2, NUMBER, etc.)
• Programmer-defined subtype(PL/SQL Release 2.1 and above)
• Declarations using %TYPE attribute
• Declarations using %ROWTYPE attribute
• PL/SQL record type
• PL/SQL table type

You cannot, on the other hand, declare a field to be an exception or a cursor. (With PL/SQL Release 2.3, you can declare a field to be a cursor variable.)

Here is an example of a record TYPE statement:

TYPE company_rectype 

IS 

RECORD

(comp# company.company_id%TYPE,

 name company.name%TYPE);

 Declaring the Record

 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 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_type, 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 which 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 only needed for table and cursor records.

The customer_sales_rectype identifier is itself a record type, so PL/SQL does not need the attribute to properly declare the record. You simply must make sure that the record type is defined before you try to use it. You can declare it in the same declaration section as the records themselves, in a block which encloses the current block of code, or in a package specification that makes that record type globally accessible.

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.

  

Examples of Programmer-Defined Record Declarations

 

Suppose that I declare the following subtype (an alias for VARCHAR2), a cursor, and a PL/SQL table data structure:

SUBTYPE long_line_type IS VARCHAR2;

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

   INDEX BY BINARY_INTEGER;

I can then declare the following programmer-defined records:

·         A programmer-defined record which is a subset of the company table, plus a PL/SQL table of employees. I use the %TYPE attribute to link the fields in the record directly to the table. I then add a third field which is actually a PL/SQL table of employee ID numbers. 

TYPE company_rectype 

 IS 

RECORD

   (company_id    company.company_id%TYPE,

    company_name company.name%TYPE,

    new_hires_tab employee_ids_tabletype);

·         A mish-mash of a record which demonstrates the different kinds of field declarations in a record, including: the NOT NULL constraint, use of a subtype, the %TYPE attribute, default value specification, a PL/SQL table, and a nested record. These varieties are shown below.

1.  TYPE mishmash_rectype IS RECORD

2.  (emp_number NUMBER(10) NOT NULL,

3.  paragraph_text long_line_type,

4.  company_nm company.name%TYPE,

5.  total_sales company_sales_cur.total_sales%TYPE := 0,

6.  new_hires_tab employee_ids_tabletype,

7.  prefers_nonsmoking_fl BOOLEAN := FALSE,

8.  new_company_rec company_rectype

   );

BEGIN

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