PL/SQL User's Guide and Reference

User-Defined Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, you cannot specify the datatypes of fields in the record or declare fields of your own. The composite datatype RECORD lifts those restrictions.

As you might expect, objects of type RECORD are called records. Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.

Defining RECORD Types

Records must be declared in two steps. First, you define a RECORD type, then declare user-defined records of that type. You can define RECORD types in the declarative part of any block, subprogram, or package using the syntax

TYPE record_type_name IS RECORD (field[, field]...);

where record_type_name is a type specifier used in subsequent declarations of records and field stands for the following syntax:

field_name datatype [[NOT NULL] {:= | DEFAULT} expr]

You can use the attributes %TYPE and %ROWTYPE to specify field types. In the following example, you define a RECORD type named DeptRecTyp:

DECLARE
   TYPE DeptRecTyp IS RECORD (
      deptno NUMBER(2),
      dname  dept.dname%TYPE,
      loc    dept.loc%TYPE);

Notice that the field declarations are like variable declarations. Each field has a unique name and specific datatype.

The next example shows that you can initialize a RECORD type. When you declare a record of type TimeTyp, its three fields assume an initial value of zero.

DECLARE
   TYPE TimeTyp IS RECORD (
      seconds SMALLINT := 0,
      minutes SMALLINT := 0,
      hours   SMALLINT := 0);

You can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized.

Nested Records

PL/SQL lets you define nested records. That is, a record can be the component of another record, as the following example shows:

DECLARE
   TYPE TimeTyp IS RECORD (
      seconds SMALLINT,
      minutes SMALLINT,
      hours   SMALLINT);
   TYPE MeetingTyp IS RECORD (
      day     DATE,
      time    TimeTyp,  -- nested record
      place   VARCHAR2(20),
      purpose VARCHAR2(50));

Function Results

The example below shows that you can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type.

DECLARE
   TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
   ...
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS ...

Declaring Records

Once you define a RECORD type, you can declare records of that type, as the following example shows:

DECLARE
   TYPE EmpRecTyp IS RECORD (
      emp_id    NUMBER(4),
      emp_name  CHAR(10),
      job_title CHAR(9)
      hire_date DATE));
   emp_rec EmpRecTyp;  -- declare user-defined record

The identifier emp_rec represents an entire record.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. An example follows:

CREATE PACKAGE emp_actions AS
   TYPE EmpRecTyp IS RECORD ( 
      emp_id    NUMBER(4),
      last_name CHAR(10),
      job_title CHAR(14), ...);
   ...
   PROCEDURE hire_employee (emp_rec EmpRecTyp);

Referencing Records

To reference individual fields in a record, you use dot notation and the following syntax:

record_name.field_name

For example, you reference the field hire_date in the record emp_rec as follows:

emp_rec.hire_date ...

You can assign the value of an expression to a specific field using the following syntax:

record_name.field_name := expression;

In the next example, you convert an employee name to upper case:

emp_rec.ename := UPPER(emp_rec.ename);

Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, you can assign one record to another if they have the same datatype, as the following example shows:

DECLARE
   TYPE DeptRecTyp IS RECORD(...);
   dept_rec1 DeptRecTyp;
   dept_rec2 DeptRecTyp;
BEGIN
   ...
   dept_rec1 := dept_rec2;  -- assign one record to another

Records that have different datatypes cannot be assigned to each other even if their fields match exactly.

Note: A user-defined record and a %ROWTYPE record always have different datatypes.

Second, you can use the SELECT or FETCH statement to fetch column values into a record, as the example below shows. The column names must appear in the same order as the fields in your record.

DECLARE
   TYPE DeptRecTyp IS RECORD(
      dept_no   NUMBER(2),
      dept_name CHAR(14),
      location  CHAR(13));
   dept_rec DeptRecTyp;
BEGIN
   SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ...

However, you cannot use the INSERT statement to insert user-defined records into a database table. So, the following statement is illegal:

INSERT INTO dept VALUES (dept_rec);  -- illegal

Also, you cannot assign a list of values to a record using an assignment statement. Therefore, the following syntax is illegal:

record_name := (value1, value2, value3, ...);  -- illegal

Finally, records cannot be tested for equality, inequality, or nullity. For instance, the following IF conditions are illegal:

IF dept_rec1 = dept_rec2 THEN ...  -- illegal
IF emp_rec IS NULL THEN ...  -- illegal

Nested Records

The example below shows that you can assign one nested record to another if they have the same datatype. Such assignments are allowed even if the parent records have different datatypes.

DECLARE
   TYPE TimeTyp IS RECORD (minutes SMALLINT, hours SMALLINT);
   TYPE MeetingTyp IS RECORD (
      day     DATE,
      time    TimeTyp,  -- nested record
      room    INTEGER(4),
      subject VARCHAR2(35));
   TYPE PartyTyp IS RECORD (
      day   DATE,
      time  TimeTyp,  -- nested record
      place VARCHAR2(15));
   meeting MeetingTyp;
   seminar MeetingTyp;
   party   PartyTyp;
   ...
BEGIN
   ...
   seminar.time := meeting.time;  -- same parent type
   party.time := meeting.time;  -- different parent types
   ...
END;

Function Results

When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:

function_name(parameters).field_name

For example, the following call to the function nth_highest_sal references the field salary in the user-defined record emp_rec:

DECLARE
   TYPE EmpRecTyp IS RECORD ( 
      emp_id    NUMBER(4),
      job_title CHAR(14),
      salary    REAL);
   middle_sal REAL;
   FUNCTION nth_highest_sal (n INTEGER) RETURN EmpRecTyp IS
      emp_rec EmpRecTyp;
   BEGIN
      ...
      RETURN emp_rec;  -- return user-defined record
   END;
BEGIN
   ...
   middle_sal := nth_highest_sal(10).salary;  -- call function

To reference nested fields in a record returned by a function, you use the following syntax:

function_name(parameters).field_name.nested_field_name

For example, the following call to the function calendar_item references the nested field hours in the user-defined record meeting:

DECLARE
   TYPE TimeTyp IS RECORD (minutes SMALLINT, hours SMALLINT);
   TYPE MeetingTyp IS RECORD (
      day      DATE,
      duration TimeTyp,  -- nested record
      room     INTEGER(4),
      subject  VARCHAR2(35));
   ...
   FUNCTION calendar_item (priority INTEGER) RETURN MeetingTyp IS
      meeting MeetingTyp;
   BEGIN
      ...
      RETURN meeting;  -- return user-defined record
   END;
BEGIN
   ...
   IF calendar_item(3).duration.hours > 2 THEN ...

Restriction Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name().field_name  -- illegal; empty parameter list

You cannot just drop the empty parameter list because the following syntax is also illegal:

function_name.field_name  -- illegal; no parameter list

Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly, as shown in the following example:

DECLARE
   TYPE EmpRecTyp IS RECORD (..., salary REAL);
   emp_rec EmpRecTyp;  -- declare record
   median  REAL;
   FUNCTION median_sal RETURN EmpRecTyp IS ...
BEGIN
   ...
   emp_rec := median_sal;  -- assign function result
   median := emp_rec.salary;  -- reference field

Using Records

The RECORD type lets you collect information about the attributes of something. The information is easy to manipulate because you can refer to the collection as a whole. In the following example, you collect accounting figures from the database tables assets and liabilities, then use ratio analysis to compare the performance of two subsidiary companies:

DECLARE
   TYPE FiguresTyp IS RECORD (cash REAL, notes REAL, ...);
   sub1_figs FiguresTyp;
   sub2_figs FiguresTyp;
   ...
   FUNCTION acid_test (figs FiguresTyp) RETURN REAL IS ...
BEGIN
   SELECT cash, notes, ... INTO sub1_figs FROM assets, liabilities
      WHERE assets.sub = 1 AND liabilities.sub = 1;
   SELECT cash, notes, ... INTO sub2_figs FROM assets, liabilities
      WHERE assets.sub = 2 AND liabilities.sub = 2;
   IF acid_test(sub1_figs) > acid_test(sub2_figs) THEN ...
   ...
END;

Notice how easy it is to pass the collected figures to the function acid_test, which computes a financial ratio.