Oracle PL/SQL Einzel-Tipp ansehen

Associative Array

Tipp 73

DECLARE
-- TYPE typ_name IS TABLE OF datentyp [NOT NULL]
-- INDEX BY {BINARY_INTEGER| VARCHAR(x)};
TYPE numarray IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1) := 'Marco';
    l_data(2) := 'Hans';
    l_data(3) := 'Andrea';
    --l_data(4) := 'Andrea'; =>ORA-22160: Element bei Index [3] nicht vorhanden
    for i in 1 .. l_data.count LOOP
     dbms_output.put_line(l_data(i));
    END loop;
END;
/


DECLARE
-- TYPE typ_name IS TABLE OF datentyp [NOT NULL]
-- INDEX BY {BINARY_INTEGER| VARCHAR(x)};
TYPE numarray IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1) := 'Marco';
    l_data(2) := 'Hans';
    l_data(3) := 'Andrea';
    --l_data(4) := 'Andrea'; =>ORA-22160: Element bei Index [3] nicht vorhanden
    forall i in 1 .. l_data.count
    insert into t SELECT l_data(i) from dual
    WHERE l_data(i) is NOT null;
END;
/
SELECT * FROM t;