🔍 SEO & DISCOVERABILITY (Metadata)¶

title: "PL/SQL Records | Grouping Data with %ROWTYPE" description: "Learn how to use PL/SQL records to group related data. Master user-defined records and the powerful %ROWTYPE for whole-row processing." keywords: [plsql records, %rowtype oracle, user-defined records plsql, sql record structure, oracle data grouping, vishnu damwala] tags: [oracle, plsql, data]

🎓 EDUCATIONAL CONTEXT¶

difficulty: Intermediate estimated_time: 20 minutes prerequisites: - PL/SQL Data Types - SELECT INTO

PL/SQL Records 📋¶

Mentor's Note: Imagine you are collecting details about a car. Instead of having separate boxes for v_color, v_model, and v_year, wouldn't it be easier to have one big box labeled v_car that holds all three? That's what a Record does! 💡

🌟 The Scenario: The Identity Card 🪪¶

Imagine an ID card. - It's one physical card. - Inside that card, you have: Name, Birthdate, and Address. - In PL/SQL, the ID card is the Record, and the specific details are the Fields.

💻 1. The %ROWTYPE Record (The Automatic Box) ⚓¶

This is the easiest way to create a record. It creates a box that perfectly matches the structure of a database table.

DECLARE
   -- 📦 Create a record that matches the 'employees' table
   r_emp employees%ROWTYPE;
BEGIN
   SELECT * INTO r_emp 
   FROM employees 
   WHERE employee_id = 100;

   -- 🚀 Accessing fields using dot notation: record.field
   DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.first_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || r_emp.salary);
END;

💻 2. User-Defined Records (The Custom Box)¶

If you only need specific columns from different tables, you can build your own structure.

DECLARE
   -- 1. Define the Blueprint
   TYPE t_emp_summary IS RECORD (
      full_name VARCHAR2(100),
      job_title VARCHAR2(50)
   );

   -- 2. Create the Variable
   v_summary t_emp_summary;
BEGIN
   v_summary.full_name := 'Vishnu Damwala';
   v_summary.job_title := 'Architect';
END;

🏗️ Architect's Note: Record Efficiency 🛡️¶

Records make your code much cleaner and easier to maintain. - The Secret: If you add a new column to your database table, any PL/SQL code using %ROWTYPE will automatically update its internal structure the next time it's compiled. - Tip: When passing data between Procedures or Functions, pass a single Record instead of 10 individual variables. It's much more professional!

🎨 Visual Logic: The Grouping¶

[ Name   ] 
[ Age    ] -> [ EMPLOYEE RECORD ]
[ Salary ] /