Du hast zwei Collections mit Jobdaten – und willst wissen, welche Jobs in beiden vorkommen, welche nur in einer, und wie du Duplikate loswirst. Klingt nach viel Arbeit? Nicht mit dem MULTISET Operator.
Was ist der MULTISET Operator?
Der MULTISET Operator gehört zu Oracles Collection-Funktionen und erlaubt dir, zwei Collections vom gleichen Typ miteinander zu verknüpfen – ähnlich wie Mengenoperationen in SQL, nur direkt in PL/SQL auf Variablen. Du kannst Collections vereinen, schneiden oder voneinander abziehen – ohne eine einzige Zeile SQL auf eine Tabelle loszulassen.
Kurz gesagt: der MULTISET Operator ist das Set-Kalkül für PL/SQL Collections – UNION, INTERSECT und EXCEPT direkt auf deinen Variablen.
Die Ausgangsdaten
Für das Beispiel nutzen wir das HR-Schema und schauen uns Jobs an, die mit „S“ beginnen – einmal aus der aktuellen Job-Tabelle, einmal aus der Job-Historie.
| # | JOB | JOB-Beschreibung | JOB_HISTORY |
|---|---|---|---|
| 1 | SA_MAN | Sales Manager | SA_MAN |
| 2 | SA_REP | Sales Representative | SA_REP |
| 3 | SH_CLERK | Shipping Clerk | |
| 4 | ST_CLERK | Stock Clerk | ST_CLERK |
| 5 | ST_CLERK | ||
| 6 | ST_MAN | Stock Manager | |
| 7 | ST_TRAINEE | Stock Trainee |
ORACLE Schema – HR
MULTISET UNION – alles zusammenwerfen
Zuerst laden wir beide Listen per BULK COLLECT in Collections und führen sie mit MULTISET UNION zusammen:
DECLARE
TYPE t_job IS TABLE OF VARCHAR2(2000);
v_job t_job;
v_job_his t_job;
BEGIN
-- Jobs der aktuellen Mitarbeiter holen
SELECT job_id
BULK COLLECT INTO v_job
FROM jobs
WHERE job_id LIKE 'S%';
-- Jobs aus der Historie holen
SELECT job_id
BULK COLLECT INTO v_job_his
FROM job_history
WHERE job_id LIKE 'S%';
-- Zusammenfuehren mit UNION
v_job := v_job MULTISET UNION v_job_his;
FOR i IN v_job.FIRST .. v_job.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_job(i));
END LOOP;
END;
/
Das Ergebnis enthält alle Einträge aus beiden Collections – aber Achtung: Oracle arbeitet standardmäßig mit UNION ALL, das heißt Duplikate bleiben drin:
- SA_MAN
- SA_REP
- SH_CLERK
- ST_CLERK
- ST_MAN
- ST_TRAINEE
- SA_MAN (Duplikat!)
- SA_REP (Duplikat!)
- ST_CLERK (Duplikat!)
- ST_CLERK (Duplikat!)
MULTISET UNION DISTINCT – Duplikate raus
Ein einziges Schlüsselwort löst das Problem:
-- Nur diese Zeile aendern: v_job := v_job MULTISET UNION DISTINCT v_job_his;
Ergebnis – sauber, keine Duplikate:
- SA_MAN
- SA_REP
- SH_CLERK
- ST_CLERK
- ST_MAN
- ST_TRAINEE
MULTISET INTERSECT – nur die Schnittmenge
Du willst nur die Jobs, die sowohl in der aktuellen Liste als auch in der Historie vorkommen? Kein Problem:
-- Nur Jobs die in BEIDEN Collections vorkommen v_job := v_job MULTISET INTERSECT DISTINCT v_job_his;
Ergebnis: SA_MAN, SA_REP, ST_CLERK – also genau die Jobs, die Mitarbeiter aktuell innehaben und früher schon hatten.
MULTISET EXCEPT – was fehlt in der Historie?
Und wenn du wissen willst, welche aktuellen Jobs nie in der Historie auftauchen:
-- Jobs die in v_job sind, aber NICHT in v_job_his v_job := v_job MULTISET EXCEPT DISTINCT v_job_his;
Ergebnis: SH_CLERK, ST_MAN, ST_TRAINEE – Jobs, für die es keine historischen Einträge gibt.
Übersicht: alle drei Operatoren
| Operator | Was er macht | Mit DISTINCT |
|---|---|---|
| MULTISET UNION | Alle Elemente beider Collections | Ohne Duplikate |
| MULTISET INTERSECT | Nur gemeinsame Elemente | Ohne Duplikate |
| MULTISET EXCEPT | Elemente nur in der ersten Collection | Ohne Duplikate |
Was du beachten solltest
⚠ Gleicher Typ: Beide Collections müssen vom exakt gleichen TYPE sein – sonst gibt es einen Kompilierungsfehler.
💡 Tipp: MULTISET funktioniert auch mit komplexen Objekttypen, nicht nur mit simplen VARCHAR2-Listen. Sobald dein TYPE ein Oracle-Objekt ist, kannst du trotzdem alle drei Operatoren nutzen – Oracle vergleicht dann attributweise.
Und noch etwas: MULTISET UNION ohne DISTINCT ist nicht dasselbe wie ein einfaches Zusammenkopieren – Oracle behandelt das Ergebnis weiterhin als echte Collection mit allen Rechten, also inklusive weiterer MULTISET-Operationen darauf.
Fazit
Der MULTISET Operator ist einer dieser PL/SQL-Schätze, die du entweder kennst und gezielt einsetzt – oder um die du einen riesigen Umweg mit temporären Tabellen und verschachtelten Cursorn baust. Wer Collections in PL/SQL nutzt, sollte den MULTISET Operator kennen: es macht den Code kürzer, lesbarer und schneller.
Gerade in Szenarien wo du Datenmengen im Speicher vergleichen oder zusammenführen musst, ohne jedes Mal die Datenbank zu bemühen, ist MULTISET das richtige Werkzeug.