Oracle %ROWTYPE attribute

In Oracle PL/SQL, the %ROWTYPE attribute provides a record type representing a string in the Oracle database table (or view).

A record may store an entire string of data selected from the table, or be extracted from a cursor or a strictly typed cursor variable.

Syntax for declaring a variable with the %ROWTYPE attribute in Oracle PL/SQL

v_rec table_name%ROWTYPE

Parameters and arguments of the attribute

• v_rec – name of a variable which is assigned a record value
• table_name – name of the database table

Note:

• Columns in a row and corresponding fields in a record have the same names and data types.
• Fields in the %ROWTYPE record do not inherit the NOT NULL column limit.

Consider the Oracle example to understand how to apply %ROWTYPE in Oracle PL/SQL.
For example:

DECLARE
-- %ROWTYPE can include all columns in a table ...
emp_rec employees%ROWTYPE;
-- ... or a subset of columns based on the cursor.
CURSOR c1 IS
SELECT department_id, department_name FROM departments;
dept_rec c1%ROWTYPE;
-- You can even create %ROWTYPE with columns from several tables.
CURSOR c2 IS
SELECT employee_id, email, employees.manager_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
join_rec c2%ROWTYPE;
BEGIN
-- We know that EMP_REC can contain a row from an EMPLOYEES table.
SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
-- We can refer to EMP_REC fields using column names.
-- from the EMPLOYEES table.
IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
emp_rec.salary := emp_rec.salary * 1.15;
END IF;
END;

In this Oracle PL/SQL example, we declared the emp_rec variable based on the employees table entry. We also declared the join_rec variable based on the record of the c2 cursor created from the fields of the employees and departments tables.

An example with cumulative assignment

Consider an example that shows how to assign values to all fields in one record simultaneously. You can assign one record to another if their ads belong to the same table or cursor. For example, the following assignment is allowed:

DECLARE
dept_rec1%ROWTYPE;
dept_rec2 departments%ROWTYPE;
CURSOR c1 IS SELECT department_id, location_id FROM departments;
dept_rec3 c1%ROWTYPE;
BEGIN
dept_rec1 := dept_rec2; -- acceptable
-- dept_rec2 refers to the table, dept_rec3 refers to the cursor
-- dept_rec2 := dept_rec3; -- not allowed
END;

In this Oracle PL/SQL example, we declared the variables dept_rec1, dept_rec2 based on a table entry. We also declared the c1 cursor containing the department_id, location_id fields of the departments table.

Since the departments table is represented in the database by the fields department_id, department_name, manager_id, location_id, the assignment of dept_rec2 := dept_rec3 is unacceptable.

The following example shows how to assign a list of column values to a record using the SELECT instruction.

DECLARE
dept_rec departments%ROWTYPE;
BEGIN
SELECT * INTO dept_rec FROM departments
WHERE department_id = 30 and ROWNUM < 2;
END;

In this example, the dept_rec variable is assigned a record value from the departments table with department_id equal to 30.

Oracle pl sql tutorial – %TYPE and %ROWTYPE attributes