Usage of %ROWTYPE in oracle

In Oracle, a ROWTYPE is a composite data type used in PL/SQL to declare a variable that represents a row of a table or row of a cursor. It allows you to easily manipulate and access an entire row of data without declaring each column individually.

---

✅ Usage of %ROWTYPE

🔹 Table %ROWTYPE

Declares a variable that has the same structure as a row in a specific table.

DECLARE
   v_emp employees%ROWTYPE;  -- Assuming 'employees' is a table
BEGIN
   SELECT * INTO v_emp
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp.first_name || ' ' || v_emp.last_name);
END;

• v_emp has all columns of the employees table.
• You can access values like v_emp.salary, v_emp.department_id, etc.

---

🔹 Cursor %ROWTYPE

You can also define a row type based on a cursor's result set:

DECLARE
   CURSOR c_emp IS
      SELECT first_name, last_name FROM employees;
   v_rec c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;
   FETCH c_emp INTO v_rec;
   DBMS_OUTPUT.PUT_LINE('Name: ' || v_rec.first_name || ' ' || v_rec.last_name);
   CLOSE c_emp;
END;

---

✅ Advantages of %ROWTYPE

• Auto-updates when the table structure changes (e.g., new columns),
• Reduces boilerplate code,
• Easier to maintain and understand.

---

❗ Limitations

• Brings in all columns of the table/cursor, which can be less efficient if only a few columns are needed.
• You can’t use %ROWTYPE in SQL directly — only in PL/SQL blocks.

---