%ROWTYPE in Oracle PLSQL with Example

In this Post, We are going to explore %ROWTYPE in Oracle PL/SQL.

%ROWTYPE in Oracle PL/SQL: A Simplified Guide

In Oracle PLSQL, the %ROWTYPE attribute is a powerful tool that simplifies your code by allowing you to easily work with entire rows of data from a table.

What is %ROWTYPE?

Imagine a table in your database. Each row in that table represents a record with multiple columns (like name, age, address, etc.).

%ROWTYPE lets you create a variable that can hold the complete structure of a single row from that table.

Why use %ROWTYPE?

• Reduced Code: Instead of declaring individual variables for each column, you declare a single variable to hold the entire row. This significantly reduces code redundancy

• Improved Readability: Code becomes more concise and easier to understand, as you're dealing with rows as a whole.

• Flexibility: You can easily modify the structure of the variable if the table's structure changes, as %ROWTYPE automatically adapts.

Advantages of %ROWTYPE

• Efficiency: Reduces coding time and effort.

• Maintainability: Easier to modify code when table structures change

• Readability: Improves code clarity and understandability.

Syntax of %ROWTYPE

variable_name table_name%ROWTYPE;

Example of %ROWTYPE

Let's say you have a table named "EMPLOYEES" with columns like "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY". Here's how you can use %ROWTYPE:

DECLARE
  v_employee EMPLOYEES%ROWTYPE; 
BEGIN
  -- Fetch a row from the EMPLOYEES table
  SELECT * 
  INTO v_employee 
  FROM EMPLOYEES 
  WHERE EMPLOYEE_ID = 100;
 -- Access individual columns within the row
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee.FIRST_NAME || ' ' || v_employee.LAST_NAME);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_employee.SALARY); 
END;
/

In this example:

1. v_employee EMPLOYEES%ROWTYPE; declares a variable named v_employee that can hold a complete row from the "EMPLOYEES" table.

2. The SELECT ... INTO statement fetches a row from the table and stores it in the v_employee variable.

3. You can then access individual columns within the row using dot notation (e.g., v_employee.FIRST_NAME).

That's all for today, this simplified explanation of %ROWTYPE in Oracle PLSQL is helpful. If you like the post then share your view in the comment box.

You could also watch %rowtype in oracle plsql with example click here to watch