Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

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


Description of rowtype_attribute.gif follows
Description of the illustration rowtype_attribute.gif

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.

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:


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.

   emp_rec   employees%ROWTYPE;
   my_empno  employees.employee_id%TYPE := 100;
      SELECT department_id, department_name, location_id FROM departments;
   dept_rec  c1%ROWTYPE;
   SELECT * INTO emp_rec FROM employees WHERE employee_id = my_empno;
   IF (emp_rec.department_id = 20) AND (emp_rec.salary > 2000) THEN
   END IF;

Related Topics

Constants and Variables, Cursors, Cursor Variables, FETCH Statement