Oracle PL/SQL provides a special data type called the %TYPE data type. The %TYPE data type allows you to declare a variable that is associated with a column in a database table.
The %TYPE attribute is a powerful feature designed to enhance the readability, maintainability, and flexibility of code by associating variables with database columns or other variables. .
The Oracle PL/SQL %TYPE attribute allow you to declare a constant, variable, or parameter to be of the same data type as previously declared variable, record, nested table, or database column.
To use the PL/SQL %TYPE data type, you first need to declare a variable. You can then use the variable in your PL/SQL code just like any other variable.
The %TYPE attribute can be used with variables, records, nested tables, and database columns.
Syntax:
identifier Table.column_name%TYPE;
Table and Column Association example
Here is an example of how to declare a variable using the PL/SQL %TYPE attribute:
DECLARE
v_name employee.lastname%TYPE;
v_dep number;
v_min_dep v_dep%TYPE:=31;
BEGIN
select lastname
into v_name
from EMPLOYEE
where DEPARTMENTID=v_min_dep;
DBMS_OUTPUT.PUT_LINE('v_name: '||v_name);
END;
Record Fields example
%TYPE can also be applied to record variables, providing a concise way to define records based on the structure of database tables or other record variables.
DECLARE
TYPE emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.first_name%TYPE
);
v_employee emp_rec;
BEGIN
-- Code logic using v_employee
END;
In summary, the %TYPE attribute in PL/SQL offers several advantages, including data type consistency, adaptability to schema changes, improved code readability, reduced maintenance effort, and enhanced code portability. Its usage is a best practice in PL/SQL development for creating robust and maintainable code.
Oracle PL/SQL %TYPE Attribute is very useful when you want to write code that is independent of the database column’s data type. It is also helpful in reducing coding errors.
