🔧 Associative arrays in PLSQL

In PL/SQL, associative arrays (also known as index-by tables) are a type of collection that allows you to store key-value pairs. Unlike regular arrays, which are indexed by sequential integers, associative arrays can be indexed by strings or integers. This makes them flexible for scenarios where you need to use a non-sequential key or a more meaningful identifier.

Associative Array (Index-By Table) Syntax in PL/SQL

An associative array (also called an index-by table) is declared as a type in PL/SQL, and you can use either integer or string keys. Here’s the basic syntax:

TYPE type_name IS TABLE OF element_type INDEX BY index_type

;

type_name: The name you want to give to the associative array type.

element_type: The type of elements that the array will hold (e.g., VARCHAR2, NUMBER, DATE, etc.).

index_type: The data type of the index (either PLS_INTEGER, BINARY_INTEGER, or VARCHAR2).

Here’s how associative arrays work in PL/SQL:

Declaring an Associative Array

DECLARE
  TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;  -- Indexed by integer
  TYPE emp_table_by_name IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50); -- Indexed by string

  emp_names emp_table;
  emp_jobs emp_table_by_name;
BEGIN
  -- Assigning values using integer keys
  emp_names(1) := 'John Doe';
  emp_names(2) := 'Jane Smith';

  -- Assigning values using string keys
  emp_jobs('John Doe') := 'Manager';
  emp_jobs('Jane Smith') := 'Developer';

  -- Accessing values
  DBMS_OUTPUT.PUT_LINE('Employee 1: ' || emp_names(1));  -- Output: John Doe
  DBMS_OUTPUT.PUT_LINE('Employee Job: ' || emp_jobs('Jane Smith')); -- Output: Developer
END;

Explanation:

1. Declaration: Two types of associative arrays are declared: emp_table, which uses integers as keys, and emp_table_by_name, which uses strings as keys.

2. Assignment: Values are assigned to the arrays using the appropriate keys.

3. Access: You can retrieve values using the keys.

Operations on Associative Arrays

Check Existence (EXISTS):

IF emp_jobs.EXISTS('John Doe') THEN
DBMS_OUTPUT.PUT_LINE('John Doe is an employee.');
END IF;

Delete Elements (DELETE):

emp_names.DELETE(1); -- Deletes the entry at index 1
emp_jobs.DELETE('Jane Smith'); -- Deletes the entry with the key 'Jane Smith'
emp_names.DELETE; -- Deletes all entries

Count Elements (COUNT):

DBMS_OUTPUT.PUT_LINE('Total employees: ' || emp_names.COUNT);

First and Last Elements (FIRST, LAST):

DBMS_OUTPUT.PUT_LINE('First Key: ' || emp_names.FIRST); -- Gets the first key
DBMS_OUTPUT.PUT_LINE('Last Key: ' || emp_names.LAST); -- Gets the last key

Example Use Case

Imagine you want to store employee names and their corresponding job titles. Using associative arrays indexed by employee names (strings) makes it easy to look up an employee’s job title directly:

DECLARE
TYPE emp_jobs IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
jobs emp_jobs;
BEGIN
-- Adding employees and their job titles
jobs('John Doe') := 'Manager';
jobs('Alice Johnson') := 'Analyst';
jobs('Bob Brown') := 'Developer';

-- Retrieve and display job title
IF jobs.EXISTS('Alice Johnson') THEN
DBMS_OUTPUT.PUT_LINE('Alice Johnson is a ' || jobs('Alice Johnson'));
END IF;
END;

Key Points

Dynamic Size: Associative arrays do not have a predefined limit on the number of elements, allowing you to add elements dynamically.

Flexible Indexing: You can use strings or integers as keys, providing more flexibility than traditional arrays.

Efficient Lookups: Retrieving data using the key is efficient, making associative arrays a good choice for mapping and lookup operations.

Associative arrays are powerful tools in PL/SQL for handling collections of data, especially when you need quick lookups and non-sequential indexing.