The Types of Collections#

Standard SQL works with sets of data on disk. When you transition into PL/SQL, you often need to hold sets of data in memory (PGA) to loop over them, modify them, and insert them elsewhere.

To hold an array of data in memory, PL/SQL uses Collections.

Associative Arrays (INDEX BY)#

Also known as Index-By tables, these act like Hash Maps or Dictionaries in Python/Java. They consist of key-value pairs. The key can be a Number or a String (VARCHAR2).

DECLARE
  -- Define the Type (Value is VARCHAR2, Key is NUMBER)
  TYPE emp_name_map_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  
  -- Instantiate the variable
  v_emps emp_name_map_t;
BEGIN
  -- Assign values (The keys do not have to be sequential)
  v_emps(101) := 'Alice';
  v_emps(999) := 'Bob';
  
  DBMS_OUTPUT.PUT_LINE('Employee 999 is: ' || v_emps(999));
END;
/

Because keys aren't sequential, you cannot use a standard FOR i IN 1..v_emps.COUNT loop to iterate through them. You must use the FIRST and NEXT methods.

Nested Tables#

Nested tables are unbounded, dense arrays (initially sequential, starting at index 1). Unlike Associative Arrays, Nested Tables can be stored physically in database columns, though this is heavily discouraged in relational modeling.

DECLARE
  -- Define the Type (Notice there is no INDEX BY clause)
  TYPE string_list_t IS TABLE OF VARCHAR2(100);
  
  -- Instantiate AND Initialize (Requires a constructor)
  v_list string_list_t := string_list_t('Apple', 'Banana', 'Cherry');
BEGIN
  -- We can append to a nested table
  v_list.EXTEND;
  v_list(4) := 'Date';
  
  -- Because they are dense, we can loop easily
  FOR i IN 1..v_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_list(i));
  END LOOP;
END;
/

Collection Methods (COUNT, FIRST, LAST)#

Oracle provides built-in methods to manipulate and interrogate collections.

• .COUNT: Returns the number of elements.
• .FIRST / .LAST: Returns the first and last index numbers.
• .EXISTS(n): Returns TRUE if index n contains data.
• .EXTEND(n): Appends n null elements to a Nested Table or VARRAY (required before adding new data).
• .DELETE(n): Removes the element at index n. This makes a Nested Table "sparse" (gaps in the index).

Bulk Processing (BULK COLLECT & FORALL)#

Collections are most powerful when paired with Bulk Processing.

Normally, if you SELECT 10,000 rows into a loop, you cause 10,000 Context Switches between the SQL and PL/SQL engines. By using BULK COLLECT INTO a collection, you fetch all 10,000 rows into memory in a single trip.

DECLARE
  -- Create a collection of an entire row structure
  TYPE emp_table_t IS TABLE OF employees%ROWTYPE;
  v_emps emp_table_t;
BEGIN
  -- Fetch in chunks to protect PGA memory
  SELECT * BULK COLLECT INTO v_emps 
  FROM employees 
  WHERE status = 'ACTIVE'
  FETCH FIRST 5000 ROWS ONLY;
  
  -- Use FORALL to insert the entire collection in one trip
  FORALL i IN 1..v_emps.COUNT
    INSERT INTO payroll_staging VALUES v_emps(i);
END;
/

Common Gotchas#

Key Takeaways#