%ROWTYPE evaluates to the type attribute of cursors and database table rows. The following covers database table row.
A declared record structure can contain a component for each column in a database table. The syntax is:
structure_name [schema.]table_name%ROWTYPE;
Example
A cursor that selects columns from a single table can select into a record declared like:
type rec_type is record
( emp_id emp.emp_id%TYPE,
... emp...%TYPE,
name emp.emp_name%TYPE );
rec rec_type; -- record declaration
CURSOR c1 is SELECT * FROM EMP WHERE ...
LOOP
...
FETCH INTO rec; -- fetch into the record
...
END LOOP;
As an alternative we can remove the record type definition and declare a record whose type is derived from EMP:
type rec_type is record ( THIS DEFINITION IS NO
emp_id emp.emp_id%TYPE, LONGER NEEDED FOR THIS
... emp...%TYPE, PROGRAM.
name emp.emp_name%TYPE );
CURSOR c1 is * FROM EMP WHERE ...
rec EMP%ROWTYPE; -- record declaration that uses EMP.
-- There is no change to any other code.
-- We just changed record declarations.
LOOP
...
FETCH INTO rec; -- fetch into the record
...
END LOOP;
There three ways to declare a composite record type structure in PL/SQL:
Records
%ROWTYPE of a cursor
%ROWTYPE of a table
The following questions will help you decide which way to declare a composite record type structure in PL/SQL.
Questions to ask:
Does the SELECT query return columns from more than one table?
If YES - more than one table:
RECORD cursor%TYPE table%ROWTYPE
can do can do won't do
If NO - just one table:
RECORD cursor%TYPE table%ROWTYPE
can do can do can do
A declared record structure can contain a component for each column in a database table. The syntax is:
structure_name [schema.]table_name%ROWTYPE;
Example
A cursor that selects columns from a single table can select into a record declared like:
type rec_type is record
( emp_id emp.emp_id%TYPE,
... emp...%TYPE,
name emp.emp_name%TYPE );
rec rec_type; -- record declaration
CURSOR c1 is SELECT * FROM EMP WHERE ...
LOOP
...
FETCH INTO rec; -- fetch into the record
...
END LOOP;
As an alternative we can remove the record type definition and declare a record whose type is derived from EMP:
type rec_type is record ( THIS DEFINITION IS NO
emp_id emp.emp_id%TYPE, LONGER NEEDED FOR THIS
... emp...%TYPE, PROGRAM.
name emp.emp_name%TYPE );
CURSOR c1 is * FROM EMP WHERE ...
rec EMP%ROWTYPE; -- record declaration that uses EMP.
-- There is no change to any other code.
-- We just changed record declarations.
LOOP
...
FETCH INTO rec; -- fetch into the record
...
END LOOP;
There three ways to declare a composite record type structure in PL/SQL:
Records
%ROWTYPE of a cursor
%ROWTYPE of a table
The following questions will help you decide which way to declare a composite record type structure in PL/SQL.
Questions to ask:
Does the SELECT query return columns from more than one table?
If YES - more than one table:
RECORD cursor%TYPE table%ROWTYPE
can do can do won't do
If NO - just one table:
RECORD cursor%TYPE table%ROWTYPE
can do can do can do