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.[ 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:
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:
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);
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.
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.