Record Definition

Records are composite variables that can store data values of different types, similar to a struct type in C, C++, or Java. For more information, see "Understanding PL/SQL Records".

In PL/SQL records are useful for holding data from table rows, or certain columns from table rows. For ease of maintenance, you can declare variables as table%ROWTYPE or cursor%ROWTYPE instead of creating new record types.

Syntax

record type definition ::=

Description of the illustration record_type_definition.gif

record field declaration ::=

Description of the illustration record_field_declaration.gif

record type declaration ::=

Description of the illustration record_type_declaration.gif

Keyword and Parameter Description

datatype

A datatype specifier. For the syntax of datatype, see "Constant and Variable Declaration".

expression

A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expression Definition". When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.

field_name

A field in a user-defined record.

NOT NULL

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.

record_name

A user-defined record.

type_name

A user-defined record type that was defined using the datatype specifier RECORD.

:= | DEFAULT

Initializes fields to default values.

Usage Notes

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package.

A record can be initialized in its declaration. You can use the %TYPE attribute to specify the datatype of a field. You can add the NOT NULL constraint to any field declaration to prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized. To reference individual fields in a record, you use dot notation. For example, to reference the dname field in the dept_rec record, you would use dept_rec.dname.

Instead of assigning values separately to each field in a record, you can assign values to all fields at once:

You can assign one user-defined record to another if they have the same datatype. (Having fields that match exactly is not enough.) You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have compatible datatypes.
You can use the SELECT or FETCH statement to fetch column values into a record. The columns in the select-list must appear in the same order as the fields in your record.

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 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 spec. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, use the following syntax to reference fields in the record:

function_name(parameter_list).field_name

To reference nested fields, use this syntax:

function_name(parameter_list).field_name.nested_field_name

If the function takes no parameters, code an empty parameter list. The syntax follows:

function_name().field_name

Examples

For examples, see the following:

Example 1-16, "Declaring a Record Type"
Example 5-8, "VARRAY of Records"
Example 5-20, "Assigning Values to VARRAYs with Complex Datatypes"
Example 5-21, "Assigning Values to Tables with Complex Datatypes"
Example 5-41, "Declaring and Initializing a Simple Record Type"
Example 5-42, "Declaring and Initializing Record Types"
Example 5-44, "Returning a Record from a Function"
Example 5-45, "Using a Record as Parameter to a Procedure"
Example 5-46, "Declaring a Nested Record"
Example 5-47, "Assigning Default Values to a Record"
Example 5-50, "Inserting a PL/SQL Record Using %ROWTYPE"
Example 5-51, "Updating a Row Using a Record"
Example 5-52, "Using the RETURNING Clause with a Record"
Example 5-53, "Using BULK COLLECT With a SELECT INTO Statement"
Example 6-26, "Cursor Variable Returning a Record Type"
Example 9-3, "Creating the emp_admin Package"