%TYPE and %ROWTYPE Attributes in PLSQL

In PL/SQL, both %TYPE and %ROWTYPE are attributes that allow you to define variables that inherit the data type of a column or the structure of a table (or cursor). The key difference between them is the scope and purpose of their usage.

---

1. %TYPE

The %TYPE attribute is used to declare a variable that inherits the data type of a specific column, field, or variable.

• It is useful when you want to ensure that the variable you declare has the same data type as a particular column or another variable, making your code more maintainable and less prone to errors if the column's data type changes.

Example of %TYPE:

DECLARE
  -- Declare a variable with the same data type as employees.employee_id
  v_emp_id employees.employee_id%TYPE;
  -- Declare a variable with the same data type as employees.first_name
  v_emp_name employees.first_name%TYPE;
BEGIN
  -- Assign values to the variables
  v_emp_id := 101;
  v_emp_name := 'John';

  -- Output the values
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;
/

In this example:

• v_emp_idwill inherit the data type of the employee_id column in the employees table.
• v_emp_namewill inherit the data type of the first_name column in the employees table.

---

2. %ROWTYPE

The %ROWTYPE attribute is used to declare a record that can hold an entire row of data from a table or a cursor.

• It inherits the structure (i.e., all the columns and their corresponding data types) of the table or cursor.
• It is useful when you want to work with an entire row of data and avoid declaring each column separately.

Example of %ROWTYPE:

DECLARE
  -- Declare a variable that can hold an entire row from the employees table
  v_employee employees%ROWTYPE;
BEGIN
  -- Select an employee's row into the record variable
  SELECT *
  INTO v_employee
  FROM employees
  WHERE employee_id = 101;

  -- Output the values of the record
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee.employee_id);
  DBMS_OUTPUT.PUT_LINE('First Name: ' || v_employee.first_name);
  DBMS_OUTPUT.PUT_LINE('Last Name: ' || v_employee.last_name);
END;
/

In this example, v_employee is a variable that can hold all the columns of a row from the employees table.

---

Summary:

Use %TYPE when you need to declare a variable with the same data type as a specific column or another variable.

Use %ROWTYPE when you need to work with an entire row of a table or cursor, without needing to declare each column individually.