%ROWTYPE Attribute

The %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 NOT NULL column or check constraint, or default values. For more information, see "Using the %ROWTYPE Attribute".

Syntax

%rowtype attribute ::=

Description of the illustration rowtype_attribute.gif

Keyword and Parameter Description

cursor_name

An explicit cursor previously declared within the current scope.

cursor_variable_name

A PL/SQL strongly typed cursor variable, previously declared within the current scope.

table_name

A database table or view that must be accessible when the declaration is elaborated.

Usage Notes

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

Examples

For examples, see the following:

Example 1-6, "Using %ROWTYPE with an Explicit Cursor"
Example 2-8, "Using %ROWTYPE With Table Rows"
Example 2-9, "Using the %ROWTYPE Attribute"
Example 2-10, "Assigning Values to a Record With a %ROWTYPE Declaration"
Example 3-11, "Using SUBTYPE With %TYPE and %ROWTYPE"
Example 5-7, "Specifying Collection Element Types with %TYPE and %ROWTYPE"
Example 5-20, "Assigning Values to VARRAYs with Complex Datatypes"
Example 5-42, "Declaring and Initializing Record Types"
Example 6-24, "Cursor Variable Returning a %ROWTYPE Variable"
Example 6-25, "Using the %ROWTYPE Attribute to Provide the Datatype"
Example 13-1, "Declaring and Assigning Values to Variables"