Oracle Database: %TYPE and %ROWTYPE Explained

%TYPE is used to define a variable’s data type based on the column’s datatype in a table. For example, if a column’s datatype is VARCHAR2(30), the %TYPE variable will also be VARCHAR2(30).

Define as: vEmployeeName Employee.Name%TYPE;

Example

SET SERVEROUTPUT ON
DECLARE
vEmployeeName Employee.Name%TYPE;
BEGIN
SELECT Name INTO vEmployeeName
FROM Employee
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(vEmployeeName);
END;
/

%ROWTYPE: This is used to create a record that matches the column types of a specific table. It functions like an array that includes all the columns of the table along with their data types, represented by variable names corresponding to the table’s column names.

Define as:rEmployee Employee%ROWTYPE;

Example

SET SERVEROUTPUT ON
DECLARE
rEmployee Employee%ROWTYPE;
BEGIN
rEmployee.Name := 'RAM';
rEmployee.sex := 'MALE';
DBMS_OUTPUT.PUT_LINE(rEmployee.Name);
DBMS_OUTPUT.PUT_LINE(rEmployee.Age);
END;
/