PL/SQL Language Elements, 43 of 52

%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. 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".

Syntax

Text description of rowtype_attribute.gif follows

Text description of the illustration rowtype_attribute.gif

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL strongly (not weakly) typed cursor variable previously declared within the current scope.

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

Usage Notes

The %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:

IF emp_rec.deptno = 20 THEN ...

You can assign the value of an expression to a specific field, as follows:

emp_rec.sal := average * 1.15;

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 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

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 c1 cursor.

DECLARE
   emp_rec   emp%ROWTYPE;
   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
   dept_rec  c1%ROWTYPE;

In the next example, you select a row from the emp table into a %ROWTYPE record:

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;