PL/SQL collections - Associative Arrays, Nested Tables & VARRAYs.
Here's a simple example of each of the three types of PL/SQL collections: Associative Arrays, Nested Tables, and VARRAYs.
1. Associative Array Example (Index-By Table)
An associative array uses an index (can be integer or string) to access its elements. It's perfect for key-value pairs.
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; -- Associative Array Declaration
employees emp_table;
BEGIN
-- Assign values to the associative array
employees(101) := 'John Doe';
employees(102) := 'Jane Smith';
employees(103) := 'Sam Wilson';
-- Retrieve and print values
DBMS_OUTPUT.PUT_LINE('Employee 101: ' || employees(101));
DBMS_OUTPUT.PUT_LINE('Employee 102: ' || employees(102));
DBMS_OUTPUT.PUT_LINE('Employee 103: ' || employees(103));
END;
/
Output:
Employee 101: John Doe
Employee 102: Jane Smith
Employee 103: Sam Wilson
2. Nested Table Example
A nested table is an unordered collection of elements that can be extended dynamically. It's suitable for large sets of data.
DECLARE
TYPE salary_table IS TABLE OF NUMBER; -- Nested Table Declaration
employee_salaries salary_table := salary_table(); -- Initialize the nested table
BEGIN
-- Adding elements to the nested table
employee_salaries.EXTEND(3); -- Reserve space for 3 elements
employee_salaries(1) := 50000;
employee_salaries(2) := 60000;
employee_salaries(3) := 70000;
-- Print the salaries
FOR i IN 1..employee_salaries.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Salary for Employee ' || i || ': ' || employee_salaries(i));
END LOOP;
-- Remove an element from the nested table
employee_salaries.DELETE(2); -- Delete salary of Employee 2
-- Check if element exists after deletion
IF employee_salaries.EXISTS(2) THEN
DBMS_OUTPUT.PUT_LINE('Employee 2 Salary still exists.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee 2 Salary deleted.');
END IF;
END;
/
Output:
Salary for Employee 1: 50000
Salary for Employee 2: 60000
Salary for Employee 3: 70000
Employee 2 Salary deleted.
3. VARRAY Example (Variable-Size Array)
A VARRAY has a fixed maximum size and holds elements in order. It's useful when you know the upper limit of your data size.
DECLARE
TYPE employee_varray IS VARRAY(5) OF VARCHAR2(50); -- VARRAY with max size of 5
employee_names employee_varray := employee_varray('John', 'Jane'); -- Initialize with 2 values
BEGIN
-- Add more elements to the VARRAY
employee_names.EXTEND(1); -- Extend by 1 element
employee_names(3) := 'Sam';
-- Print all the names in the VARRAY
FOR i IN 1..employee_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employee_names(i));
END LOOP;
END;
/
Output:
Employee 1: John
Employee 2: Jane
Employee 3: Sam
Key Takeaways:
Associative Arrays are key-value pairs and can be sparse (have gaps between indexes).
Nested Tables are unordered collections that can grow dynamically and may contain gaps.
VARRAYs are ordered collections with a fixed maximum size and do not allow gaps between elements.
Each type is suitable for different scenarios, depending on your need for fixed-size vs dynamic data, ordered vs unordered elements, and whether you require gaps in your dataset.
Top comments (0)