Explain associative arrays (index-by tables) in PL/SQL

How it works

An associative array (TYPE ... IS TABLE OF element_type INDEX BY key_type) is a key-value structure, unbounded and sparse — indexed by either a PLS_INTEGER/BINARY_INTEGER or a VARCHAR2 (as a lookup-by-string map). Unlike nested tables and VARRAYs, it exists only in PL/SQL memory — never as a database column type — and doesn't need a constructor before use.

DECLARE
  TYPE t_salary_by_name IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
  v_salaries t_salary_by_name;
BEGIN
  v_salaries('King')    := 24000;
  v_salaries('Kochhar') := 17000;

  DBMS_OUTPUT.PUT_LINE(v_salaries('King'));

  IF v_salaries.EXISTS('Hunold') THEN
    DBMS_OUTPUT.PUT_LINE('Found');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not in the array');
  END IF;
END;
/

Related Questions