|PL/SQL User's Guide and Reference
Part Number A89856-01
PL/SQL Language Elements, 39 of 52
Records are items of type
RECORD. Records have uniquely named fields that can store data values of different types. Thus, a record lets you treat related but dissimilar data as a logical unit. For more information, see "What Is a Record?".
This is a datatype specifier. For the syntax of
datatype, see "Constants and Variables".
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.
This identifies a field in a user-defined record.
This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as
NULL raises the predefined exception
VALUE_ERROR. The constraint
NULL must be followed by an initialization clause.
This identifies a user-defined record.
This identifies a user-defined record type that was defined using the datatype specifier
This operator or keyword lets you initialize fields to default values.
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:
The next example shows that you can use the
%TYPE attribute to specify the datatype of a field. It also shows that you can add the
NULL constraint to any field declaration and thereby prevent the assigning of nulls to that field. Fields declared as
NULL must be initialized.
DECLARE TYPE DeptRecTyp IS RECORD( deptno NUMBER(2) NOT NULL := 99, 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 field
dname in the record
dept_rec as follows:
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
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( minutes SMALLINT, hours SMALLINT); TYPE MeetingTyp IS RECORD( day DATE, time_of TimeTyp, -- nested record place VARCHAR2(20), purpose VARCHAR2(50)); TYPE PartyTyp IS RECORD( day DATE, time_of TimeTyp, -- nested record place VARCHAR2(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:
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 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:
To reference nested fields, use this syntax:
If the function takes no parameters, code an empty parameter list. The syntax follows:
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 VARCHAR2(14), loc VARCHAR2(13)); dept_rec DeptRecTyp; ... BEGIN SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE deptno = 20;
Collections, Functions, Procedures, Packages