ORACLE - Create Records

Create Records

Records are items of type RECORD. Records have uniquely named fields that can store data values of different types.

Syntax

Parameter Description and Keyword

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.

datatype

This is a type specifier. For the syntax of datatype.

record_type_name

This identifies a user-defined type specifier, which is used in subsequent declarations of records.

:= 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. 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,

name dept.name%TYPE,

local dept.local%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, 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.

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

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),

name CHAR(14),

local CHAR(13));

dept_rec DeptRecTyp;

...

BEGIN

SELECT deptno, name, local INTO dept_rec FROM dept

WHERE deptno = 20;