In unseren Kursen stöhnen die Teilnehmer häufig über Oracles eigene Arrays, die man auch Collection nennt.
Der Vorwurf lautet:
Aber eigentlich sind sie doch 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) := 'Jendrik';
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) := 'Jendrik';
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:='Jendrik';
l_data(0).nachname:='Hägerbäumer';
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:='Hägerbäumer';
END;
/
Es kann auch ein VARCHAR2 Wert als Index 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;
/