|PL/SQL User's Guide and Reference
Part Number A89856-01
PL/SQL Language Elements, 43 of 52
%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 %ROWTYPE".
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL strongly (not weakly) typed cursor variable previously declared within the current scope.
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
%ROWTYPE attribute lets you declare records structured like a row of data in a database table. To reference a field in the record, you use dot notation. For example, you might reference the
deptno field as follows:
You can assign the value of an expression to a specific field, as follows:
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. Second, 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.
In the example below, you use
%ROWTYPE to declare two records. The first record stores a row selected from the
emp table. The second record stores a row fetched from the
In the next example, you select a row from the
emp table into a
DECLARE emp_rec emp%ROWTYPE; ... BEGIN SELECT * INTO emp_rec FROM emp WHERE empno = my_empno; IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN ... END IF; END;
Constants and Variables, Cursors, Cursor Variables, FETCH Statement