In Oracle PL/SQL, %TYPE and %ROWTYPE are attributes used to declare variables based on the data type of database columns or entire table rows. They make programs easier to maintain because variables automatically adapt if the table structure changes.

1. %TYPE Attribute

Definition

%TYPE is used to declare a variable with the same data type as a table column or another variable.

Syntax

variable_name table_name.column_name%TYPE;

Example 1: Using %TYPE

Suppose we have an EMPLOYEE table.

DECLARE
    v_name EMPLOYEE.EMP_NAME%TYPE;
    v_salary EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_NAME, SALARY
    INTO v_name, v_salary
    FROM EMPLOYEE
    WHERE EMP_ID = 101;

    DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || v_salary);
END;
/

Output

Employee Name : Rahul
Salary : 35000

Advantages of %TYPE

• Automatically inherits the column’s data type.
• No need to specify VARCHAR2, NUMBER, DATE, etc.
• If the column data type changes, PL/SQL code doesn’t require modification.
• Reduces programming errors.

2. %ROWTYPE Attribute

Definition

%ROWTYPE declares a record variable that can hold an entire row from a table or cursor.

Each column becomes a field of the record.

Syntax

record_name table_name%ROWTYPE;

Example 2: Using %ROWTYPE

DECLARE
    emp_record EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO emp_record
    FROM EMPLOYEE
    WHERE EMP_ID = 101;

    DBMS_OUTPUT.PUT_LINE('ID : ' || emp_record.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('Name : ' || emp_record.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || emp_record.SALARY);
END;
/

Output

ID : 101
Name : Rahul
Salary : 35000

Example Using INSERT with %ROWTYPE

DECLARE
    emp_rec EMPLOYEE%ROWTYPE;
BEGIN
    emp_rec.EMP_ID := 105;
    emp_rec.EMP_NAME := 'Riya';
    emp_rec.SALARY := 50000;

    INSERT INTO EMPLOYEE
    VALUES emp_rec;

    COMMIT;
END;
/

Example Using UPDATE

DECLARE
    emp_rec EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO emp_rec
    FROM EMPLOYEE
    WHERE EMP_ID = 101;

    emp_rec.SALARY := emp_rec.SALARY + 5000;

    UPDATE EMPLOYEE
    SET SALARY = emp_rec.SALARY
    WHERE EMP_ID = emp_rec.EMP_ID;

    COMMIT;
END;
/

Difference Between %TYPE and %ROWTYPE