9.4.1 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 attributes

• Declarations using %ROWTYPE attributes

• 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);

9.4.2 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.

9.4.3 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. (PL/SQL tables are described in Chapter 10, PL/SQL Tables .)

  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.

  TYPE mishmash_rectype IS RECORD
     (emp_number NUMBER(10) NOT NULL,
      paragraph_text long_line_type,
      company_nm company.name%TYPE,
      total_sales company_sales_cur.total_sales%TYPE := 0,
      new_hires_tab employee_ids_tabletype,
      prefers_nonsmoking_fl BOOLEAN := FALSE,
      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.