Using %TYPE and %ROWTYPE Attributes to Declare Identical Datatypes

Using the %TYPE Attribute to Declare Variables

The %TYPE attribute provides the datatype of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column. For example, suppose you want to declare variables as the same datatype as the employee_id and last_name columns in employees table. To declare variables named empid and emplname that have the same datatype as the table columns, use dot notation and the %TYPE attribute. See Example: Using %TYPE With Table Columns in PL/SQL.

DECLARE -- declare variables using %TYPE attribute
   empid    employees.employee_id%TYPE;  -- employee_id datatype is NUMBER(6)
   emplname employees.last_name%TYPE;  -- last_name datatype is VARCHAR2(25)
BEGIN
   empid    := 100301;  -- this is OK because it fits in NUMBER(6)
--   empid  := 3018907;  -- this is too large and will cause an overflow
   emplname := 'Patel'; --  this is OK because it fits in VARCHAR2(25)
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || empid);  -- display data
   DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data
END;
/

Declaring variables with the %TYPE attribute has two advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of columns, such as employee_id or last_name, the datatypes of empid and emplname in Example: Using %TYPE With Table Columns in PL/SQL change accordingly at run time.

Using the %ROWTYPE Attribute to Declare Variables

For easier maintenance of code that interacts with the database, you can use the %ROWTYPE attribute to declare a variable that represents a row in a table. A PL/SQL record is the datatype that stores the same information as a row in a table.

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. For information about records, see "Using Record Types".

Columns in a row and corresponding fields in a record have the same names and datatypes. In Example: Using %ROWTYPE with a PL/SQL Record, you declare a record named emp_rec. Its fields have the same names and datatypes as the columns in the employees table. You use dot notation to reference fields, such as emp_rec.last_name.

In Example: Using %ROWTYPE with a PL/SQL Record, the SELECT statement is used to store row information from the employees table into the emp_rec record. When you run the SELECT INTO statement, the value in the first_name column of the employees table is assigned to the first_name field of emp_rec; the value in the last_name column is assigned to the last_name field of emp_rec; and so on.

DECLARE -- declare variables
-- declare record variable that represents a row fetched from the employees table
   emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
  SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' ' 
                       || emp_rec.last_name); -- display
END;
/

Declaring variables with the %ROWTYPE attribute has several advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of any of the table columns, the datatypes associated with the %ROWTYPE declaration change accordingly at run time.