|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
%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. Fields in a record and corresponding columns in a row have the same names and datatypes.
You can use the
%ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using
%ROWTYPE are treated like those declared using a datatype name. For more information, see "Using the %ROWTYPE Attribute".
Keyword and Parameter Description
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.
The following example uses
%ROWTYPE to declare two records. The first record stores an entire row selected from a table. The second record stores a row fetched from the
c1 cursor, which queries a subset of the columns from the table. The example retrieves a single row from the table and stores it in the record, then checks the values of some table columns.
DECLARE emp_rec employees%ROWTYPE; my_empno employees.employee_id%TYPE := 100; CURSOR c1 IS SELECT department_id, department_name, location_id FROM departments; dept_rec c1%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM employees WHERE employee_id = my_empno; IF (emp_rec.department_id = 20) AND (emp_rec.salary > 2000) THEN NULL; END IF; END; /