PL/SQL Collections (Arrays) 📦📦
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 ✅ |