PL/SQL User's Guide and Reference

Records

Description

Records are objects of type RECORD. Records have uniquely named fields that can store data values of different types. For more information, see "User-Defined Records"

To create records, you must take two steps. First, you define a RECORD type, then declare user-defined records of that type.

Syntax

record_type_definition ::=

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

record_declaration ::=

record_name record_type_name;

field_declaration ::=

field_name    
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | local_field_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression]

Keyword and Parameter Description

record_type_name

This identifies a user-defined type specifier, which is used in subsequent declarations of records. For naming conventions, see "Identifiers"

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

local_field_name

This identifies a field previously declared in the same user-defined record definition.

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

record_name

This identifies a user-defined record previously declared within the current scope.

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints. For more information, see "Datatypes"

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

%TYPE

This attribute provides the datatype of a field, record, PL/SQL table, database column, or variable.

NOT NULL

This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.

:= | DEFAULT

This operator or keyword allows you to initialize fields to default values.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

. When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.

Usage Notes

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, a record can be initialized in its declaration, as the following example shows:

DECLARE
   TYPE TimeTyp IS RECORD(
      second SMALLINT := 0,
      minute SMALLINT := 0,
      hour   SMALLINT := 0);

The next example shows that you can use the %TYPE attribute to specify a field datatype. It also shows that you can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field.

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

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the dname field in the dept_rec record as follows:

dept_rec.dname := 'PURCHASING';

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, PL/SQL lets you assign one record to another if they have the same datatype. Note, however, that even if their fields match exactly, a user-defined record and a %ROWTYPE record have different types. Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. Just make sure the column names appear in the same order as the fields in your record.

You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:

DECLARE
   TYPE TimeTyp IS RECORD(
      minute SMALLINT,
      hour   SMALLINT);
   TYPE MeetingTyp IS RECORD(
      day     DATE,
      time    TimeTyp,     -- nested record
      place   CHAR(20),
      purpose CHAR(50));

   TYPE PartyTyp IS RECORD(
      day  DATE,
      time TimeTyp,        -- nested record
      loc  CHAR(15));
   meeting MeetingTyp;
   seminar MeetingTyp;
   party   PartyTyp;

The next example shows that you can assign one nested record to another if they have the same datatype:

seminar.time := meeting.time;

Such assignments are allowed even if the containing records have different datatypes.

User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you exit the application or end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.

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

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

function_name(parameters).field_name.nested_field_name

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.

Example

In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:

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