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

Was ist Sammlung?

Eine Sammlung ist eine geordnete Gruppe von Elementen bestimmter Datentypen. Es kann eine Sammlung einfacher Datentypen oder COM seinplex Datentyp (wie benutzerdefinierte oder Datensatztypen).

In der Sammlung wird jedes Element durch einen Begriff namens identifiziert "Index." Jedem Element in der Sammlung wird ein eindeutiger Index zugewiesen. Die Daten in dieser Sammlung können durch Verweis auf diesen eindeutigen Index manipuliert oder abgerufen werden.

Sammlungen sind äußerst nützlich, wenn große Datenmengen desselben Typs verarbeitet oder manipuliert werden müssen. Sammlungen können als Ganzes mit der Option „BULK“ in Oracle gefüllt und bearbeitet werden.

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

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

Zu jedem Zeitpunkt können Daten in der Sammlung durch drei Begriffe bezeichnet werden: Sammlungsname, Index, Feld-/Spaltenname als „ ( ). “. Im folgenden Abschnitt erfahren Sie mehr über diese oben genannten Sammlungskategorien.

Varrays

Varray ist eine Sammlungsmethode, 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. Following sind die Attribute von Varrays.

  • Die Obergrenze für die Größe ist festgelegt
  • Wird nacheinander beginnend mit dem Index „1“ ausgefüllt.
  • Dieser Sammlungstyp ist immer dicht, dh wir können keine Array-Elemente löschen. Varray kann als Ganzes gelöscht oder am Ende gekürzt werden.
  • Da es von Natur aus immer dicht ist, weist es eine sehr geringe Flexibilität auf.
  • Die Verwendung ist sinnvoller, wenn die Array-Größe bekannt ist und ähnliche Aktivitäten für alle Array-Elemente ausgeführt werden.
  • Der Index und die Reihenfolge bleiben immer stabil, dh der Index und die Anzahl der Sammlung sind immer gleich.
  • Sie müssen initialisiert werden, bevor sie in Programmen verwendet werden können. Jeder Vorgang (mit Ausnahme des EXISTS-Vorgangs) für eine nicht initialisierte Sammlung löst einen Fehler aus.
  • Es kann als Datenbankobjekt erstellt werden, das in der gesamten Datenbank sichtbar ist, oder innerhalb des Unterprogramms, das nur in diesem Unterprogramm verwendet werden kann.

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

Index 1 2 3 4 5 6 7
Wert Xyz dfv Hrsg Cxs Vbc Nhu Qwe

Syntax für VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
  • In der obigen Syntax wird Typname als VARRAY des Typs „DATA_TYPE“ für die angegebene Größenbeschränkung deklariert. Der Datentyp kann entweder „simple“ oder „com“ seinplex Art.

Verschachtelte Tabellen

Eine verschachtelte Tabelle ist eine Sammlung, in der die Größe des Arrays nicht festgelegt ist. Es hat den numerischen Indextyp. Nachfolgend finden Sie weitere Beschreibungen zum Typ verschachtelter Tabellen.

  • Für die verschachtelte Tabelle gibt es keine obere Größenbeschränkung.
  • Da die obere Größenbeschränkung nicht festgelegt ist, muss der Sammlungsspeicher jedes Mal erweitert werden, bevor wir ihn verwenden. Wir können die Sammlung mit dem Schlüsselwort „EXTEND“ erweitern.
  • Wird nacheinander beginnend mit dem Index „1“ ausgefüllt.
  • Dieser Sammlungstyp kann beides sein dicht und spärlichDas heißt, wir können die Sammlung als dicht besetzen erstellen und das einzelne Array-Element auch nach dem Zufallsprinzip löschen, wodurch es dünn besetzt 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 Reihenfolge sind nicht stabil, dh der Index und die Anzahl der Array-Elemente können variieren.
  • Sie müssen initialisiert werden, bevor sie in Programmen verwendet werden können. Jeder Vorgang (mit Ausnahme des EXISTS-Vorgangs) für die nicht initialisierte Sammlung löst einen Fehler aus.
  • Es kann als Datenbankobjekt erstellt werden, das in der gesamten Datenbank sichtbar ist, oder innerhalb des Unterprogramms, das nur in diesem Unterprogramm verwendet werden kann.

Die folgende Abbildung erläutert schematisch die Speicherzuweisung von Nested Table (dicht und dünn besetzt). 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 Hrsg Cxs Vbc Nhu Qwe
Wert (spärlich) Qwe Asd afg Asd Wer

Syntax für verschachtelte Tabelle:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
  • In der obigen Syntax wird Typname als verschachtelte Tabellensammlung vom Typ „DATA_TYPE“ deklariert. Der Datentyp kann entweder „simple“ oder „com“ seinplex Art.

Index nach Tabelle

Index-by-Table ist eine Sammlung, in der die Array-Größe nicht festgelegt ist. Im Gegensatz zu den anderen Sammlungstypen kann in der Index-by-Table-Sammlung der Index vom Benutzer definiert werden. Following sind die Attribute von Index-by-Table.

  • Der Index kann eine Ganzzahl oder Zeichenfolgen sein. Zum Zeitpunkt der Erstellung der Sammlung sollte der Indextyp angegeben werden.
  • Diese Sammlungen werden nicht sequentiell gespeichert.
  • Sie sind von Natur aus immer spärlich.
  • Die Arraygröße ist nicht festgelegt.
  • Sie können nicht in der Datenbankspalte gespeichert werden. Sie müssen in jedem Programm in dieser bestimmten Sitzung erstellt und verwendet werden.
  • Sie bieten mehr Flexibilität bei der Beibehaltung des Index.
  • Die Indizes können auch eine negative Indexfolge haben.
  • Sie eignen sich eher für relativ kleinere Sammelwerte, bei denen die Sammlung initialisiert und innerhalb derselben Unterprogramme verwendet werden kann.
  • Sie müssen vor der Verwendung nicht initialisiert werden.
  • Es kann nicht als Datenbankobjekt erstellt werden. Es kann nur innerhalb des Unterprogramms erstellt werden und kann nur in diesem Unterprogramm verwendet werden.
  • BULK COLLECT kann in diesem Sammlungstyp nicht verwendet werden, da der Index explizit für jeden Datensatz in der Sammlung angegeben werden sollte.

Die folgende Abbildung erläutert schematisch die Speicherzuordnung von Nested Table (sparse). Der schwarz gefärbte Elementraum bezeichnet das leere Element in einer Sammlung, dh spärlich.

Tiefgestellt (varchar) ZUERST SECOND DRITTE VIERTE FÜNFTE Sechstes SIEBTE
Wert (spärlich) Qwe Asd afg Asd Wer

Syntax für Index-by-Table

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
  • In der obigen Syntax wird Typname als Index-by-Table-Sammlung vom Typ „DATA_TYPE“ deklariert. Der Datentyp kann entweder „simple“ oder „com“ seinplex Typ. Die Subsciprt/Index-Variable wird vom Typ VARCHAR2 mit einer maximalen Größe von 10 angegeben.

Konstruktor- und Initialisierungskonzept in Sammlungen

Konstruktoren sind die vom Oracle bereitgestellten integrierten Funktionen, die denselben Namen wie das Objekt oder die Sammlungen haben. Sie werden zuerst ausgeführt, wenn auf ein Objekt oder eine Sammlung zum ersten Mal in einer Sitzung verwiesen wird. Nachfolgend finden Sie die wichtigen details des Konstruktors im Sammlungskontext:

  • Bei Sammlungen sollten diese Konstruktoren explizit aufgerufen werden, um sie zu initialisieren.
  • Sowohl Varray- als auch Nested-Tabellen müssen über diese Konstruktoren initialisiert werden, bevor sie in das Programm verwiesen werden.
  • Der Konstruktor erweitert implizit die Speicherzuweisung für eine Sammlung (außer Varray), daher kann der Konstruktor die Variablen auch den Sammlungen zuweisen.
  • Das Zuweisen von Werten zur Sammlung über Konstruktoren führt niemals dazu, dass die Sammlung dünn besetzt wird.

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. Die folgendenwing Die Tabelle enthält die verschiedenen Funktionen und ihre Beschreibung.

Versandart Beschreibung SYNTAX
EXISTIERT (n) Diese Methode gibt boolesche Ergebnisse zurück. Es wird „TRUE“ zurückgegeben, wenn das nth Das Element existiert in dieser Sammlung, andernfalls wird FALSE zurückgegeben. In einer nicht initialisierten Sammlung können nur EXISTS-Funktionen verwendet werden .EXISTS(element_position)
ANZAHL Gibt die Gesamtzahl der in einer Sammlung vorhandenen Elemente an .ZÄHLEN
LIMIT Es gibt die maximale Größe der Sammlung zurück. Für Varray wird die definierte feste Größe zurückgegeben. Für verschachtelte Tabellen und Index-by-Table wird NULL ausgegeben .GRENZE
ZUERST Gibt den Wert der ersten Indexvariablen (Index) der Sammlungen zurück .ERSTE
LAST Gibt den Wert der letzten Indexvariablen (Index) der Sammlungen zurück .ZULETZT
VOR (n) Gibt die Indexvariable in einer Sammlung von n voranth Element. Wenn kein vorangehender Indexwert vorhanden ist, wird NULL zurückgegeben .PRIOR(n)
WEITER (n) Gibt die erfolgreiche Indexvariable in einer Sammlung von n zurückth Element. Wenn kein erfolgreicher Indexwert vorhanden ist, wird NULL zurückgegeben .NEXT(n)
ERWEITERN Erweitert am Ende ein Element in einer Sammlung .VERLÄNGERN
ERWEITERN (n) Erweitert n Elemente am Ende einer Sammlung .EXTEND(n)
ERWEITERN (n,i) Erweitert n Kopien des ith Element am Ende der Sammlung .EXTEND(n,i)
TRIMMEN Entfernt ein Element vom Ende der Sammlung .TRIMMEN
TRIM (n) Entfernt n Elemente vom Ende der Sammlung .TRIM (n)
LÖSCHEN Löscht alle Elemente aus der Sammlung. Macht die Sammlung leer .LÖSCHEN
LÖSCHEN (n) Löscht das n-te Element aus der Sammlung. Wenn, dannth Element NULL ist, wird dies nichts bewirken .DELETE(n)
LÖSCHEN (m,n) Löscht das Element im Bereich mth bis nth in der Sammlung .DELETE(m,n)

Beispiel 1: Datensatztyp auf Unterprogrammebene

In diesem Beispiel werden wir sehen, wie man die Sammlung mit ' füllt.MASSENSAMMELN' und wie man auf die Sammlungsdaten verweist.

Datensatztyp auf Unterprogrammebene

DECLARE
TYPE 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(); 
BEGIN
INSERT 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_rec
FROM emp;
dbms_output.put_line (‘Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_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-Erklärung:

  • Codezeile 2-8: Aufnahmetyp „emp_det“ wird mit den Spalten „empf_no“, „em_name“, „salary“ und „manager“ vom Datentyp NUMBER, VARCHAR2, NUMBER, NUMBER deklariert.
  • Codezeile 9: Erstellen der Sammlung „emp_det_tbl“ des Datensatztypelements „emp_det“
  • Codezeile 10: Deklarieren Sie die Variable „guru99_emp_rec“ als Typ „emp_det_tbl“ und initialisieren Sie sie mit einem 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 Füllen der Sammlungsvariablen 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: Setzen Sie die „FOR“-Schleife, um alle Datensätze in der Sammlung einzeln zu drucken. Als untere und obere Grenze wird die Erhebungsmethode FIRST und LAST verwendet Schleife.

Output: Wie Sie im obigen Screenshot sehen können, erhalten Sie beim Ausführen des obigen Codes Folgendeswing Ausgabe

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------