%ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using
%ROWTYPE are treated like those declared using a datatype name. You can use the
%ROWTYPE attribute in variable declarations as a datatype specifier.
Fields in a record and corresponding columns in a row have the same names and datatypes. However, fields in a
%ROWTYPE record do not inherit constraints, such as the
NULL column or check constraint, or default values. For more information, see "Using the %ROWTYPE Attribute".
%rowtype attribute ::=
An explicit cursor previously declared within the current scope.
A PL/SQL strongly typed cursor variable, previously declared within the current scope.
A database table or view that must be accessible when the declaration is elaborated.
Declaring variables as the type table_name
%ROWTYPE is a convenient way to transfer data between database tables and PL/SQL. You create a single variable rather than a separate variable for each column. You do not need to know the name of every column. You refer to the columns using their real names instead of made-up variable names. If columns are later added to or dropped from the table, your code can keep working without changes.
To reference a field in the record, use dot notation (
record_name.field_name). You can read or write one field at a time this way.
There are two ways to assign values to all fields in a record at once:
First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor.
You can assign a list of column values to a record by using the
FETCH statement. The column names must appear in the order in which they were declared. Select-items fetched from a cursor associated with
%ROWTYPE must have simple names or, if they are expressions, must have aliases.
For examples, see the following: