Difference between %TYPE and %ROWTYPE in Oracle
%TYPE: is used to defined the data type of variable as the column name datatype specified for a table. If table column has datatype varchar2(30) then %type variable has same datatype 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: Used to declare a record with the same types as found in the specified table, (it is like an array which defined all columns of a table with their datatype representation used with variable name with column_name of table).
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;
/