ORACLE MULTISET + object type + nested table » Syntax kurz erklärt

ORACLE MULTISET + object type + nested table » Pfiffige Datenbankabfrage » via View Datensätze lesen, Relation auflösen und per Funktion String verketten.



CTPM » Business • Health • Travel • Move - Stefan Müller CTPM » Business • Health • Travel • Move - XING CV - Stefan Müller

Autor: Stefan Müller CEO & Senior Consultant

CTPM » Business • Health • Travel • Move

Köln • Berlin • Hamburg • Lindlar im Bergischen Land



1. ORACLE MULTISET



1.1 Beispiel

MULTISET – OBJECT TYPE – NESTED TABLE (BEISPIEL / EXAMPLE)

drop table ctpm_demo_1;
drop table ctpm_demo_2;
drop type ctpm_type;

— TABLE-Definition

CREATE TABLE ctpm_demo_1
(
ID NUMBER(12),
FELD1 NUMBER(2),
FELD2 VARCHAR2(4000),
FELD3 DATE
);

INSERT INTO tpm_demo_1 VALUES (1,1,'demo1',SYSDATE);

— TABLE-Definition

CREATE TABLE ctpm_demo_2
(
ID NUMBER(12),
FELD1 NUMBER(2),
FELD2 VARCHAR2(4000),
FELD3 DATE
);

INSERT INTO ctpm_demo_2 VALUES (1,1,'Text123',SYSDATE);
INSERT INTO ctpm_demo_2 VALUES (1,1,'4567890',SYSDATE);

— Die beiden Demotables dienen als Basis für dieses Beispiel.

— OBJECT-Definition

CREATE OR REPLACE TYPE ctpm_object AS OBJECT
(
ID NUMBER(12),
FELD1 NUMBER(2),
FELD2 VARCHAR2(4000),
FELD3 DATE
);

— Wenn die vollständige Datensatzstruktur einer bestehenden Tabelle nachgebildet werden soll, empfiehlt es sich eine 1:1 Kopie der Vorlage als Objekt zu erstellen.

— TYPE-Definition

CREATE OR REPLACE TYPE ctpm_type AS TABLE OF ctpm_object;

— Damit eine Nested Table aufgenommen werden kann, ist die Erstellung eines weiteren TYPE notwendig. Diesmal vom TYP: AS TABLE OF.

— MULTISET in VIEW

CREATE OR REPLACE VIEW v_ctpm AS
SELECT ctd1.*
,CAST(MULTISET(SELECT ctd2.*
FROM ctpm_demo_2 ctd2
WHERE ctd2.id = ctd1.id
) AS ctpm_type ) AS allvalue
FROM ctpm_demo_1 ctd1;

— Bei DML-Anweisungen (SELECT) in Prozeduren, Funktionen oder auch in VIEWs kann MULTISET eingesetzt werden. MULTISET bettet einen weiteren SELECT-Befehl ein und bildet somit wie in unserem Beispiel dargestellt, in einem Feld eine weitere ganze TABLE ab.

— FUNCTION, um NESTED TABLE aufzulösen

CREATE OR REPLACE FUNCTION get_multiset_as_clob (in_multiset IN ctpm_type)
RETURN clob AS
v_ctpm_clob CLOB;
BEGIN
FOR i IN in_multiset.FIRST..in_multiset.LAST LOOP
v_ctpm_clob:= v_ctpm_clob ||TO_CHAR(in_multiset(i).feld2);
END LOOP;
RETURN v_ctpm_clob;
END;

— Mittels einer solchen Funktion kann eine eingebettete Tabelle zur Ausgabe genutzt werden.

— EINSATZ

SELECT vc.*
,get_multiset_as_clob(vc.allvalue)
FROM v_ctpm vc;

— Mittels dieser Datenbankabfrage, unter Verwendung der zuvor erstellten View, wird zum einem jeder Datensatz der Tabelle CTPM_DEMO_1, sowie der in Relation (CTPM_DEMO_1.ID = CTPM_DEMO_2.ID) stehenden Datensätze der Tabelle: CTPM_DEMO_2 gelesen. Die Tabelle CTPM_DEMO_2 liefert nur die Spalteninhalte FELD2 und diese werden zuvor mittels der Funktion konkateniert (Stringverkettung).


 

 ctpm.events 📅 
 Anzeige 

Oracle ist eingetragenes Warenzeichen der Oracle Corporation.


Datenbank & Internet

IT-Seminare, Kurse, Workshops oder Stammtisch


Ihre Fragen und Anmerkungen sind jederzeit willkommen.

(Link)

Schreibe einen Kommentar

%d Bloggern gefällt das: