Oracle SQL/PLSQL Section 2.3: Difference between %TYPE and %ROWTYPE

Admin December 24, 2024

Oracle SQL/PLSQL Section 2.3: Difference between %TYPE and %ROWTYPE.

%TYPE: Used to declare a field with the same type as that of a specified table's column.

DECLARE

vEmployeeName Employees.FIRST_NAME%TYPE;

BEGIN

SELECT FIRST_NAME

INTO vEmployeeName

FROM Employees

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, view or cursor (= multiple columns).

DECLARE

rEmployee Employees%ROWTYPE;

BEGIN

rEmployee.FIRST_NAME := 'Matt';

rEmployee.SALARY := 3100;

DBMS_OUTPUT.PUT_LINE(rEmployee.FIRST_NAME);

DBMS_OUTPUT.PUT_LINE(rEmployee.SALARY);

END;