Auswahl  

 

Oracle
PL/SQL
11.2:12.1, 12.2
PL/SQL, Collection, Arrays
07.11.17
MP
15.06.18
MM

Body

In unseren Kursen stöhnen die Teilnehmer häufig über Oracles eigene Arrays, die man Collection nennt.
Der Vorwurf lautet:

  • zu kompliziert
  • unnötig

Aber eigentlich sind sie ganz schön. Ein paar Beispiele sollen das verdeutlichen:

1. Associative Array vom Typ String wird mit drei Werten gefüllt (und wieder ausgegeben):

SET SERVEROUTOUT ON
DECLARE
TYPE numarray IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1) := 'Marco';
    l_data(2) := 'Matthias';
    l_data(3) := 'Andrea';
    for i in 1 .. l_data.count LOOP
     dbms_output.put_line(l_data(i));
    end loop;
END;
/


Nun schreiben wir die Daten aus dem Array in eine Tabelle:

CREATE TABLE t ( txt VARCHAR2(64));

DECLARE
  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;


Beispiel 2: ASCII Tabelle:

DECLARE
  TYPE numarray IS TABLE OF VARCHAR2(2)
  INDEX BY BINARY_INTEGER;
  l_data  numarray;
BEGIN
  FOR i IN 1 .. 255 LOOP
     l_data(i) := chr(i);
  END LOOP;
  FOR i in 1 .. l_data.count LOOP
     dbms_output.put_line(rpad(i,3,' ')||' '||l_data(i));
  END LOOP;
END;
/


Basis des Arrays ist nun ein RECORD:

DECLARE
  TYPE mytyp IS RECORD (
  vorname  VARCHAR2(100),
  nachname VARCHAR2(100));
  TYPE numarray IS TABLE OF mytyp
  INDEX BY BINARY_INTEGER;
  l_data  numarray;
BEGIN
    l_data(1).vorname:='Marco';
    l_data(1).nachname:='Patzwahl';
    l_data(0).vorname:='Hans';
    l_data(0).nachname:='Wesnitzer';
  for i in 0 .. l_data.count-1 LOOP
    dbms_output.put_line('Name='||l_data(i).vorname||' '||l_data(i).nachname);
  END LOOP;
END;
/

DECLARE
  TYPE mytyp IS RECORD (
  vorname  VARCHAR2(100),
  nachname VARCHAR2(100));
  TYPE numarray IS TABLE OF mytyp
  INDEX BY BINARY_INTEGER;
  l_data  numarray;
  l_rtyp  mytyp;
BEGIN
    l_rtyp.vorname:='Peter';
    l_rtyp.nachname:='Kraus';
    l_data(2):=l_rtyp;
  for i in 0 .. l_data.count-1 LOOP
    dbms_output.put_line('Name='||l_data(i).vorname||' '||l_data(i).nachname);
  END LOOP;
END;
/


DECLARE
  TYPE mytyp IS RECORD (
    owner  VARCHAR2(30),
    name  VARCHAR2(30),
    text  VARCHAR2(4000) );
  TYPE numarray IS TABLE OF mytyp
  INDEX BY BINARY_INTEGER;
  l_data  numarray;
  l_rtyp  mytyp;
BEGIN
   dbms_output.put_line('Zeit:'||systimestamp);
   FOR r in (select owner,name,text,rownum as rn from all_source) LOOP
    l_rtyp.owner:=r.owner;
    l_rtyp.name:=r.name;
    l_rtyp.text:=r.text;
    l_data(r.rn):=l_rtyp;
   END LOOP;
    dbms_output.put_line('Anzahl:'||l_data.count);
    dbms_output.put_line('Zeit:'||systimestamp);
END;
/


Basis des Records ist eine Tabellenstruktur

DECLARE
  TYPE emp_type IS TABLE OF scott.emp%ROWTYPE
  INDEX BY BINARY_INTEGER;
  l_data  emp_type;
BEGIN
    l_data(1).empno:=8000;
    l_data(1).ename:='Patzwahl';
    l_data(5).empno:=8001;
    l_data(5).ename:='Huberl';
END;
/


Ab Version 9.2 kann auch VARCHAR2 als Referenz benutzt werden:

SET SERVEROUTPUT ON
DECLARE
  TYPE country_tab_type IS TABLE OF VARCHAR2(50)
  INDEX BY VARCHAR2(2);
  t_country country_tab_type;
BEGIN
  -- Array fuellen
  t_country('AT') := 'Austria';
  t_country('FR') := 'France';
  t_country('DE') := 'Germany';

  -- Welches Land verbirgt sich hinter dem ISO code "DE"
  DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE'));
END;
/


Beispiel Bulk Cursor mit SAVE EXCEPTIONS:

DROP TABLE scott.objects;
DROP TABLE scott.tables;

CREATE TABLE scott.objects AS
  SELECT object_name,owner FROM all_objects
   WHERE owner='SCOTT' and object_type in ('TABLE','INDEX');
CREATE TABLE scott.tables AS
  SELECT table_name,owner FROM all_tables WHERE owner='SCOTT';
ALTER TABLE scott.objects ADD PRIMARY KEY (owner,object_name);

SET SERVEROUTPUT ON
DECLARE
  TYPE object_tab_type IS TABLE OF scott.objects.object_name%TYPE;
  TYPE table_tab_type IS TABLE OF scott.tables.table_name%TYPE;
  myobjects_tab     object_tab_type;
  mytables_tab    table_tab_type;
  error_count    NUMBER;
BEGIN
  SELECT object_name BULK COLLECT INTO myobjects_tab
    FROM scott.objects;
  SELECT table_name BULK COLLECT INTO mytables_tab
    FROM scott.tables;
  FORALL i IN mytables_tab.first .. mytables_tab.last SAVE EXCEPTIONS
    INSERT INTO scott.objects (object_name,owner)
      VALUES (mytables_tab(i),'SCOTT');
  EXCEPTION when others then
    error_count := SQL%BULK_EXCEPTIONS.COUNT;
    for i in 1.. error_count loop
        dbms_output.put_line('** Oracle Fehler:' ||
        sqlerrm(-SQL%BULK_EXCEPTIONS(I).ERROR_CODE)||':'||mytables_tab(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
    '  #  Position:'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
    end loop;
    COMMIT;
END;
/