%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 the %ROWTYPE Attribute".

Syntax

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

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

Related Topics

Constants and Variables, Cursors, Cursor Variables, FETCH Statement