Explain nested table collections in PL/SQL

How it works

A nested table (TYPE ... IS TABLE OF element_type) is a dense-then-sparse collection that, unlike an associative array, can be declared as a real schema-level TYPE and stored as a database column, or used purely inside PL/SQL. It must be initialized with a constructor before elements are added by index (BULK COLLECT INTO can also populate it directly). Deleting a middle element leaves a gap — the collection becomes sparse — which is why EXISTS is used to check whether a given index is actually populated.

DECLARE
  TYPE t_name_list IS TABLE OF VARCHAR2(50);
  v_names t_name_list := t_name_list('King', 'Kochhar', 'De Haan');
BEGIN
  v_names.DELETE(2); -- leaves index 2 empty; the table is now sparse
  FOR v_i IN v_names.FIRST..v_names.LAST LOOP
    IF v_names.EXISTS(v_i) THEN
      DBMS_OUTPUT.PUT_LINE(v_names(v_i));
    END IF;
  END LOOP;
END;
/

Related Questions