%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_id will inherit the data type of the employee_id column in the employees table.

v_emp_name will 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.