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).