What are %TYPE and %ROWTYPE in Oracle?

In Oracle, both %TYPE and %ROWTYPE attributes are used to define variables in PL/SQL as it is defined within the database.

• %TYPE attribute provides the data type of a variable or a table column to that variable.
• %ROWTYPE attribute provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages to use %TYPE and %ROWTYPE to define variables are:

• You need not know about variable's data type when defining your variables.
• If the database definition of a column in a table changes, the data type of a variable changes accordingly.

Example 1

DECLARE

   empName  employee.Name%TYPE;
  
BEGIN

   SELECT name INTO empName FROM employee WHERE id = 123;
  
   --//........
  
END;

Example 2

DECLARE

  empRow  employee%ROWTYPE;

BEGIN

  SELECT * INTO empRow FROM employee WHERE id = 123;

  --/.....

END;