Skip to content

VD Computer Tuition Surat | Learning Platform

PL/SQL Collections | Associative Arrays, Nested Tables & VARRAY

PL/SQL Collections (Arrays) 📦📦¶

Prerequisites: PL/SQL Basics, Cursors

Mentor's Note: A variable holds one value. A record holds one row. A Collection is a "Bag of Rows" held in the computer's memory. It allows you to process thousands of records at lightning speed without talking to the database disk every time! 💡

🌟 The Scenario: The Delivery Crate 🚛¶

Variable: One apple. 🍎
Collection: A Crate of 100 apples. 📦
The Speed: It is much faster to carry one crate into the kitchen than to walk back and forth 100 times for each individual apple.

💻 1. Associative Arrays (Index-by Tables)¶

The most common type. They act like "Key-Value" pairs (like a Dictionary in Python or an Object in JS).

DECLARE
   -- 1. Define the type (Key is VARCHAR2 or PLS_INTEGER)
   TYPE t_capitals IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);

   -- 2. Create the variable
   v_list t_capitals;
BEGIN
   -- 3. Assign values
   v_list('India') := 'New Delhi';
   v_list('Gujarat') := 'Gandhinagar';

   DBMS_OUTPUT.PUT_LINE('Capital: ' || v_list('India'));
END;

💻 2. Nested Tables 🪜¶

Can be stored in database columns! They are like a list that can grow dynamically.

DECLARE
   TYPE t_names IS TABLE OF VARCHAR2(50);
   v_students t_names := t_names('Arjun', 'Priya'); -- Initialize
BEGIN
   v_students.EXTEND; -- Add a new empty slot
   v_students(3) := 'Sneha';
END;

💻 3. VARRAY (Fixed-Size Arrays) 📏¶

Best for small, fixed lists (like days of the week).

DECLARE
   TYPE t_days IS VARRAY(7) OF VARCHAR2(10);
   v_week t_days := t_days('Mon', 'Tue', 'Wed');
BEGIN
   -- Max size is 7. You cannot extend beyond that.
   NULL;
END;

🏗️ Architect's Note: Bulk Collect & Performance 🛡️¶

The real power of collections is BULK COLLECT. - The Secret: Instead of fetching rows one by one in a loop, you say "Fetch all 1,000 rows into this Collection at once." - Benefit: This reduces "Context Switching" between the PL/SQL and SQL engines. It can make your code 10x to 100x faster! ⚡

📊 Summary Table¶

Type	Size	Key Type	Database Storage?
Associative Array	Dynamic	String/Int	No (Memory only)
Nested Table	Dynamic	Integer	Yes ✅
VARRAY	Fixed	Integer	Yes ✅

📈 Learning Path¶