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.
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.