Oracle PL / SQL-Sammlungen: Varrays, verschachtelt & Index nach Tabellen

Inhaltsverzeichnis:

Anonim

Was ist Sammlung?

Eine Sammlung ist eine geordnete Gruppe von Elementen bestimmter Datentypen. Es kann sich um eine Sammlung einfacher Datentypen oder komplexer Datentypen handeln (z. B. benutzerdefinierte oder Datensatztypen).

In der Sammlung wird jedes Element durch einen Begriff namens "Index" identifiziert . Jedem Element in der Sammlung ist ein eindeutiger Index zugeordnet. Die Daten in dieser Sammlung können unter Bezugnahme auf diesen eindeutigen Index bearbeitet oder abgerufen werden.

Sammlungen sind am nützlichsten, wenn große Daten desselben Typs verarbeitet oder bearbeitet werden müssen. Sammlungen können mit der Option 'BULK' in Oracle als Ganzes gefüllt und bearbeitet werden.

In diesem Tutorial lernen Sie:

  • Was ist Sammlung?
  • Varrays
  • Verschachtelte Tabellen
  • Index nach Tabelle
  • Konstruktor- und Initialisierungskonzept in Sammlungen
  • Sammelmethoden

Sammlungen werden basierend auf der Struktur, dem Index und dem Speicher wie unten gezeigt klassifiziert.

  • Index-by-Tables (auch als assoziatives Array bekannt)
  • Verschachtelte Tabellen
  • Varrays

Zu jedem Zeitpunkt können Daten in der Sammlung mit drei Begriffen bezeichnet werden: Sammlungsname, Index, Feld- / Spaltenname als " (). ". Weitere Informationen zu diesen oben genannten Sammlungskategorien finden Sie im folgenden Abschnitt.

Varrays

Varray ist eine Erfassungsmethode, bei der die Größe des Arrays festgelegt ist. Die Arraygröße darf ihren festen Wert nicht überschreiten. Der Index des Varray hat einen numerischen Wert. Im Folgenden sind die Attribute von Varrays aufgeführt.

  • Die Größe der oberen Grenze ist festgelegt
  • Nacheinander ausgefüllt, beginnend mit dem Index '1'
  • Dieser Auflistungstyp ist immer dicht, dh wir können keine Array-Elemente löschen. Varray kann als Ganzes gelöscht oder vom Ende abgeschnitten werden.
  • Da es in der Natur immer dicht ist, hat es eine sehr geringe Flexibilität.
  • Es ist besser geeignet, diese zu verwenden, wenn die Arraygröße bekannt ist, und ähnliche Aktivitäten für alle Array-Elemente auszuführen.
  • Der Index und die Sequenz bleiben immer stabil, dh der Index und die Anzahl der Sammlungen sind immer gleich.
  • Sie müssen initialisiert werden, bevor sie in Programmen verwendet werden können. Jede Operation (außer EXISTS-Operation) für eine nicht initialisierte Sammlung löst einen Fehler aus.
  • Es kann als Datenbankobjekt erstellt werden, das in der gesamten Datenbank oder im Unterprogramm sichtbar ist und nur in diesem Unterprogramm verwendet werden kann.

Die folgende Abbildung erläutert die Speicherzuordnung von Varray (dicht) schematisch.

Index 1 2 3 4 5 6 7
Wert Xyz Dfv Sde Cxs Vbc Nhu Qwe

Syntax für VARRAY:

TYPE  IS VARRAY () OF ;
  • In der obigen Syntax wird type_name als VARRAY vom Typ 'DATA_TYPE' für die angegebene Größenbeschränkung deklariert. Der Datentyp kann entweder ein einfacher oder ein komplexer Typ sein.

Verschachtelte Tabellen

Eine verschachtelte Tabelle ist eine Sammlung, in der die Größe des Arrays nicht festgelegt ist. Es hat den numerischen tiefgestellten Typ. Unten finden Sie weitere Beschreibungen zum verschachtelten Tabellentyp.

  • Die verschachtelte Tabelle hat keine obere Größenbeschränkung.
  • Da die obere Größenbeschränkung nicht festgelegt ist, muss der Speicher der Sammlung jedes Mal erweitert werden, bevor wir ihn verwenden. Wir können die Sammlung mit dem Schlüsselwort 'EXTEND' erweitern.
  • Nacheinander beginnend mit dem Index '1' gefüllt.
  • Dieser Sammlungstyp kann sowohl dicht als auch spärlich sein , dh wir können die Sammlung als dicht erstellen und das einzelne Array-Element zufällig löschen, wodurch es spärlich wird.
  • Es bietet mehr Flexibilität beim Löschen des Array-Elements.
  • Es wird in der vom System generierten Datenbanktabelle gespeichert und kann in der Auswahlabfrage zum Abrufen der Werte verwendet werden.
  • Der Index und die Sequenz sind nicht stabil, dh der Index und die Anzahl des Array-Elements können variieren.
  • Sie müssen initialisiert werden, bevor sie in Programmen verwendet werden können. Jede Operation (außer EXISTS-Operation) für die nicht initialisierte Sammlung löst einen Fehler aus.
  • Es kann als Datenbankobjekt erstellt werden, das in der gesamten Datenbank oder im Unterprogramm sichtbar ist und nur in diesem Unterprogramm verwendet werden kann.

In der folgenden Abbildung wird die Speicherzuordnung der verschachtelten Tabelle (dicht und dünn) schematisch erläutert. Der schwarz gefärbte Elementraum bezeichnet das leere Element in einer Sammlung, dh spärlich.

Index 1 2 3 4 5 6 7
Wert (dicht) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Wert (spärlich) Qwe Asd Afg Asd Wir sind

Syntax für verschachtelte Tabelle:

TYPE  IS TABLE OF ;
  • In der obigen Syntax wird type_name als verschachtelte Tabellensammlung vom Typ 'DATA_TYPE' deklariert. Der Datentyp kann entweder ein einfacher oder ein komplexer Typ sein.

Index nach Tabelle

Index-by-Table ist eine Sammlung, in der die Arraygröße nicht festgelegt ist. Im Gegensatz zu den anderen Auflistungstypen kann der Index in der Auflistung "Index für Tabelle" vom Benutzer definiert werden. Im Folgenden sind die Attribute von Index für Tabelle aufgeführt.

  • Der Index kann aus Ganzzahlen oder Zeichenfolgen bestehen. Zum Zeitpunkt der Erstellung der Sammlung sollte der tiefgestellte Typ angegeben werden.
  • Diese Sammlungen werden nicht nacheinander gespeichert.
  • Sie sind immer spärlich in der Natur.
  • Die Arraygröße ist nicht festgelegt.
  • Sie können nicht in der Datenbankspalte gespeichert werden. Sie werden in jedem Programm dieser Sitzung erstellt und verwendet.
  • Sie bieten mehr Flexibilität bei der Pflege des Index.
  • Die Indizes können auch eine negative Indexsequenz haben.
  • Sie eignen sich eher für relativ kleinere kollektive Werte, bei denen die Sammlung initialisiert und in denselben Unterprogrammen verwendet werden kann.
  • Sie müssen nicht initialisiert werden, bevor Sie sie verwenden können.
  • Es kann nicht als Datenbankobjekt erstellt werden. Es kann nur innerhalb des Unterprogramms erstellt werden, das nur in diesem Unterprogramm verwendet werden kann.
  • BULK COLLECT kann in diesem Sammlungstyp nicht verwendet werden, da der Index für jeden Datensatz in der Sammlung explizit angegeben werden sollte.

In der folgenden Abbildung wird die Speicherzuordnung der verschachtelten Tabelle (dünn) schematisch erläutert. Der schwarz gefärbte Elementraum bezeichnet das leere Element in einer Sammlung, dh spärlich.

Index (varchar) ZUERST ZWEITE DRITTE VIERTE FÜNFTE SECHSTE SIEBTE
Wert (spärlich) Qwe Asd Afg Asd Wir sind

Syntax für Index-by-Table

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);
  • In der obigen Syntax wird type_name als indexweise Auflistung vom Typ 'DATA_TYPE' deklariert. Der Datentyp kann entweder ein einfacher oder ein komplexer Typ sein. Die Variable subsciprt / index wird als VARCHAR2-Typ mit einer maximalen Größe von 10 angegeben.

Konstruktor- und Initialisierungskonzept in Sammlungen

Konstruktoren sind die eingebaute Funktion des Orakels, die denselben Namen wie das Objekt oder die Sammlungen hat. Sie werden zuerst ausgeführt, wenn Objekte oder Sammlungen zum ersten Mal in einer Sitzung referenziert werden. Nachfolgend sind die wichtigen Details des Konstruktors im Sammlungskontext aufgeführt:

  • Bei Sammlungen sollten diese Konstruktoren explizit aufgerufen werden, um sie zu initialisieren.
  • Sowohl Varray- als auch verschachtelte Tabellen müssen über diese Konstruktoren initialisiert werden, bevor sie in das Programm aufgenommen werden.
  • Der Konstruktor erweitert implizit die Speicherzuordnung für eine Sammlung (außer Varray), daher kann der Konstruktor die Variablen auch den Sammlungen zuweisen.
  • Durch das Zuweisen von Werten zur Sammlung über Konstruktoren wird die Sammlung niemals spärlich.

Sammelmethoden

Oracle bietet viele Funktionen zum Bearbeiten und Arbeiten mit den Sammlungen. Diese Funktionen sind im Programm sehr nützlich, um die verschiedenen Attribute der Sammlungen zu bestimmen und zu ändern. In der folgenden Tabelle sind die verschiedenen Funktionen und ihre Beschreibung aufgeführt.

Methode Beschreibung SYNTAX
BESTEHT (n) Diese Methode gibt boolesche Ergebnisse zurück. Es wird 'TRUE' zurückgeben, wenn das n- te Element in dieser Sammlung vorhanden ist, andernfalls wird FALSE zurückgegeben. In der nicht initialisierten Sammlung können nur EXISTS-Funktionen verwendet werden .EXISTS (Elementposition)
ANZAHL Gibt die Gesamtzahl der in einer Sammlung vorhandenen Elemente an .COUNT
GRENZE Es gibt die maximale Größe der Sammlung zurück. Für Varray wird die festgelegte feste Größe zurückgegeben. Für verschachtelte Tabellen und indexweise gibt es NULL an .LIMIT
ZUERST Gibt den Wert der ersten Indexvariablen (Index) der Sammlungen zurück .FIRST
LETZTE Gibt den Wert der letzten Indexvariablen (Index) der Sammlungen zurück .LAST
VOR (n) Rückgabe vor Indexvariable in einer Sammlung des n- ten Elements. Wenn es keinen vorangestellten Indexwert gibt, wird NULL zurückgegeben .PRIOR (n)
NÄCHSTER (n) Gibt eine erfolgreiche Indexvariable in einer Sammlung des n- ten Elements zurück. Wenn es keinen Erfolg gibt, wird der Indexwert NULL zurückgegeben .NEXT (n)
ERWEITERN Erweitert am Ende ein Element in einer Sammlung .EXTEND
EXTEND (n) Erweitert n Elemente am Ende einer Sammlung .EXTEND (n)
EXTEND (n, i) Erweitert n Kopien des i- ten Elements am Ende der Sammlung .EXTEND (n, i)
TRIMMEN Entfernt ein Element vom Ende der Sammlung .TRIM
TRIM (n) Entfernt n Elemente vom Ende der Sammlung .TRIM (n)
LÖSCHEN Löscht alle Elemente aus der Sammlung. Macht die Sammlung leer .DELETE
LÖSCHEN (n) Löscht das n-te Element aus der Sammlung. Wenn das n- te Element NULL ist, führt dies zu nichts .DELETE (n)
LÖSCHEN (m, n) Löscht das Element im Bereich m - ten bis n - ten in der Sammlung .DELETE (m, n)

Beispiel 1: Datensatztyp auf Unterprogrammebene

In diesem Beispiel erfahren Sie, wie Sie die Sammlung mit 'BULK COLLECT' füllen und wie Sie auf die Sammlungsdaten verweisen.

DECLARETYPE emp_det IS RECORD(EMP_NO NUMBER,EMP_NAME VARCHAR2(150),MANAGER NUMBER,SALARY NUMBER);TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl();BEGININSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000);INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000);INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000);COMMIT:SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no);dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name);dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary);dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager);dbms_output.put_line('--------------------------------');END LOOP;END;/

Code Erläuterung:

  • Codezeile 2-8 : Der Datensatztyp 'emp_det' wird mit den Spalten emp_no, emp_name, Gehalt und Manager des Datentyps NUMBER, VARCHAR2, NUMBER, NUMBER deklariert.
  • Codezeile 9: Erstellen der Sammlung 'emp_det_tbl' des Datensatztypelements 'emp_det'
  • Codezeile 10: Deklarieren der Variablen 'guru99_emp_rec' als Typ 'emp_det_tbl' und Initialisieren mit dem Nullkonstruktor.
  • Codezeile 12-15: Einfügen der Beispieldaten in die Tabelle 'emp'.
  • Codezeile 16: Festschreiben der Einfügetransaktion.
  • Codezeile 17: Abrufen der Datensätze aus der Tabelle 'emp' und Auffüllen der Auflistungsvariablen als Bulk mit dem Befehl "BULK COLLECT". Jetzt enthält die Variable 'guru99_emp_rec' alle Datensätze, die in der Tabelle 'emp' vorhanden sind.
  • Codezeile 19-26: Festlegen der FOR-Schleife zum Drucken aller Datensätze in der Sammlung nacheinander. Die Erfassungsmethode FIRST und LAST wird als untere und obere Grenze der Schleife verwendet.

Ausgabe : Wie Sie im obigen Screenshot sehen können, erhalten Sie bei Ausführung des obigen Codes die folgende Ausgabe

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------