Oracle Nested Table bevorzugt vor Oracle Global Temporary Table

von Karsten Besserdich

Ich verwende für flüchtige Daten in einer Oracle Procedure / Oracle Function sehr häufig Oracle Nested Tables. Dabei verzichte ich ganz bewußt auf den Einsatz von Oracle Global Temporary Tables (GTT). Die Daten werden dabei permanent im Arbeitsspeicher gehalten und könnten dort direkt nach belieben manipuliert und wieder persistent in die Oracle Datenbank geschrieben werden. Mittels BULK Anweisung werden unnötige Netzwerkroundtrips vermieden. Intern kann eine Oracle Nested Table mittels SELECT Statements abgefragt werden.

In dem folgenden kleinen Beispiel wird mit dynamischen und statischen SQL Statements gearbeitet, um den Einsatz von Oracle Nested Tables zu verdeutlichen.

1. Voraussetzungen

-- 1. Anlegen des ObjectTypes OBJ_PERSON
CREATE OR REPLACE TYPE OBJ_PERSON AS OBJECT
(NACHNAME   VARCHAR2(50)
,VORNAME    VARCHAR2(50)
,GEBURTSTAG DATE
,GESCHLECHT VARCHAR2(50));

-- 2. Anlegen einer NestedTable auf Basis des ObjectTypes OBJ_PERSON
CREATE OR REPLACE TYPE TBL_OBJ_PERSON AS TABLE OF OBJ_PERSON;

-- 3. Für mein Demoscript brauche ich noch eine Tabelle um brauchbare Daten in die Oracle Nested Table zu kopieren
CREATE TABLE T_PERSON
(Namen   VARCHAR2(40)
,Wohnort VARCHAR2(40)
,Vorname VARCHAR2(40)
,eMail   VARCHAR2(40)
,Geburtsdatum DATE
);

-- 4. Testdaten einfügen
INSERT INTO T_PERSON VALUES ('Schmidt','Berlin','Klaus','klpl@testmail.ug',to_date('11.02.1979','dd.mm.yyyy'));
INSERT INTO T_PERSON VALUES ('Müller','Berlin','Thorsten','poi@testmail.ug',to_date('01.05.1980','dd.mm.yyyy'));
INSERT INTO T_PERSON VALUES ('Meier','Stuttgart','Karsten','klpl@testmail.ug',to_date('16.07.1985','dd.mm.yyyy'));
INSERT INTO T_PERSON VALUES ('Besserdich','Karlsruhe','Nico','klpl@testmail.ug',to_date('09.04.1990','dd.mm.yyyy'));
COMMIT;

-- 1. Anlegen des ObjectTypes OBJ_PERSON

CREATE OR REPLACE TYPE OBJ_PERSON AS OBJECT

(NACHNAME VARCHAR2(50)

,VORNAME VARCHAR2(50)

,GEBURTSTAG DATE

,GESCHLECHT VARCHAR2(50));

-- 2. Anlegen einer NestedTable auf Basis des ObjectTypes OBJ_PERSON

CREATE OR REPLACE TYPE TBL_OBJ_PERSON AS TABLE OF OBJ_PERSON;

-- 3. Für mein Demoscript brauche ich noch eine Tabelle um brauchbare Daten in die Oracle Nested Table zu kopieren

CREATE TABLE T_PERSON

(Namen VARCHAR2(40)

,Wohnort VARCHAR2(40)

,Vorname VARCHAR2(40)

,eMail VARCHAR2(40)

,Geburtsdatum DATE

);

-- 4. Testdaten einfügen

INSERT INTO T_PERSON VALUES ('Schmidt','Berlin','Klaus','klpl@testmail.ug',to_date('11.02.1979','dd.mm.yyyy'));

INSERT INTO T_PERSON VALUES ('Müller','Berlin','Thorsten','poi@testmail.ug',to_date('01.05.1980','dd.mm.yyyy'));

INSERT INTO T_PERSON VALUES ('Meier','Stuttgart','Karsten','klpl@testmail.ug',to_date('16.07.1985','dd.mm.yyyy'));

INSERT INTO T_PERSON VALUES ('Besserdich','Karlsruhe','Nico','klpl@testmail.ug',to_date('09.04.1990','dd.mm.yyyy'));

COMMIT;

2. Anonymer PL/Plock mit statischen SQL

DECLARE
 -- Definition einer Variable vom Typ TBL_OBJ_PERSON
 nt_person TBL_OBJ_PERSON;
BEGIN

 -- Selektiere mir alle Einträge aus der Tabelle T_PERSON in die Oracle Nested Table nt_person mittels BULK Operation
 SELECT
    OBJ_PERSON
    (p.namen
    ,p.vorname
    ,p.Geburtsdatum
    ,NULL -- wir haben kein Geschlecht, aber das Object muss genau die gleiche Anzahl an Paramtern haben!
    )
 BULK COLLECT INTO nt_person
 FROM T_PERSON p;

 dbms_output.put_line('Statisch --> Anzahl Datensätze in Oracle Nested Table: ' || nt_person.count());

EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('SQLERRM: ' || sqlerrm);
END;

DECLARE

-- Definition einer Variable vom Typ TBL_OBJ_PERSON

nt_person TBL_OBJ_PERSON;

BEGIN

-- Selektiere mir alle Einträge aus der Tabelle T_PERSON in die Oracle Nested Table nt_person mittels BULK Operation

SELECT

OBJ_PERSON

(p.namen

,p.vorname

,p.Geburtsdatum

,NULL -- wir haben kein Geschlecht, aber das Object muss genau die gleiche Anzahl an Paramtern haben!

)

BULK COLLECT INTO nt_person

FROM T_PERSON p;

dbms_output.put_line('Statisch --> Anzahl Datensätze in Oracle Nested Table: ' || nt_person.count());

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM: ' || sqlerrm);

END;

3. anonymer PL/Plock mit dynamischen SQL

DECLARE
 -- Definition einer Variable vom Typ TBL_OBJ_PERSON
 nt_person TBL_OBJ_PERSON;
 lv_sqlString varchar2(4000);
BEGIN

 -- Zusammensetzen des dynamischen SQL Statements
 lv_sqlString := 'SELECT '
 || '  OBJ_PERSON '
 || '   (p.namen '
 || '   ,p.vorname '
 || '   ,p.Geburtsdatum '
 || '   ,NULL '-- wir haben kein Geschlecht, aber das Object muss genau die gleiche Anzahl an Paramtern haben!
 || '   ) '
 || 'FROM T_PERSON p ';

 EXECUTE IMMEDIATE lv_sqlString
 BULK COLLECT INTO nt_person;   

 dbms_output.put_line('Dynamisch --> Anzahl Datensätze in Oracle Nested Table: ' || nt_person.count());

EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('SQLERRM: ' || sqlerrm);
END;

DECLARE

-- Definition einer Variable vom Typ TBL_OBJ_PERSON

nt_person TBL_OBJ_PERSON;

lv_sqlString varchar2(4000);

BEGIN

-- Zusammensetzen des dynamischen SQL Statements

lv_sqlString := 'SELECT '

|| ' OBJ_PERSON '

|| ' (p.namen '

|| ' ,p.vorname '

|| ' ,p.Geburtsdatum '

|| ' ,NULL '-- wir haben kein Geschlecht, aber das Object muss genau die gleiche Anzahl an Paramtern haben!

|| ' ) '

|| 'FROM T_PERSON p ';

EXECUTE IMMEDIATE lv_sqlString

BULK COLLECT INTO nt_person;

dbms_output.put_line('Dynamisch --> Anzahl Datensätze in Oracle Nested Table: ' || nt_person.count());

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM: ' || sqlerrm);

END;

1 KOMMENTAR

2016

von Ju

Sehr hilfreich. Besten Dank.

SCHREIBEN SIE EINEN KOMMENTAR

Sie sind hier: