Arrays in SQL-Anweisungen

Eine Reihe ist eine geordnete Menge von Elementen eines einzelnen integrierten Datentyps. Ein Array kann mit einem benutzerdefinierten Array-Typ verknüpft sein oder das Ergebnis einer SQL-Operation sein, die einen Array-Wert ohne verknüpften benutzerdefinierten Array-Typ zurückgibt.

Arrays können gewöhnliche Arrays und assoziative Arrays sein.

Gewöhnliche Arrays haben eine benutzerdefinierte Obergrenze. Auf Elemente im Array kann über ihren Indexwert zugegriffen und dieser geändert werden. Array-Elemente werden in SQL-Anweisungen durch Verwendung einer auf 1 basierenden Indizierung referenziert, z. B. MYARRAY[1], MYARRAY[2] usw.

Assoziative Arrays haben keine Obergrenze. Assoziative Arrays enthalten eine geordnete Menge von null oder mehr Elementen, wobei jedes Element im Array nach einem zugehörigen Indexwert geordnet ist und über diesen referenziert werden kann. Der Datentyp der Indexwerte kann eine Ganzzahl oder eine Zeichenkette sein, aber alle Indexwerte für das Array haben denselben Datentyp.

Arrays können nur in den folgenden Kontexten verwendet werden:

Parameter für SQL-Funktionen
RETURN-Datentypen von SQL-Funktionen
Parameter für SQL-Prozeduren
SQL-Variablen, die in SQL-Funktionen deklariert sind
SQL-Variablen, die in SQL-Prozeduren deklariert sind

Sie können ein Array erstellen, indem Sie einen Array-Typ erstellen und dann eine Array-Variable dieses Typs definieren. Beispiel:

-- CREATE ORDINARY ARRAY TYPE INTARRAY
CREATE TYPE INTARRAY AS INTEGER ARRAY[100];
-- IN AN SQL PROCEDURE, DEFINE ARRAY INTA OF THE INTARRAY TYPE
DECLARE INTA INTARRAY;
-- CREATE ASSOCIATIVE ARRAY TYPE CHARARRAY
CREATE TYPE CHARARRAY  AS CHAR(10) ARRAY[VARCHAR(10)];
-- IN AN SQL PROCEDURE, DEFINE ARRAY CHARA OF THE CHARARRAY TYPE
DECLARE CHARA CHARARRAY;

Sie können den Inhalt einer Spalte nicht direkt in ein Array abrufen. Sie müssen die ARRAY_AGG-Funktion verwenden, um ein Array zu erstellen, das das Zwischenergebnis einer SELECT-Anweisung ist, und dann den Inhalt dieses Arrays in eine SQL-Array-Variable oder einen SQL-Array-Parameter abrufen. Beispiel:

-- INTB IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE INTARRAY.
-- COL2 IS AN INTEGER COLUMN.
-- ARRAY_AGG RETRIEVES THE VALUES FROM COL2, AND PUTS THEM INTO AN ARRAY.
SELECT ARRAY_AGG(COL2) INTO INTB FROM TABLE1;

Sie können Daten aus einem Array abrufen, indem Sie die UNNEST-Spezifikation verwenden, um Array-Elemente einer Zwischenergebnistabelle zuzuweisen. Beispiel:

-- IDS AND NAMES ARE ARRAYS OF TYPE INTARRAY.
INSERT INTO PERSONS(ID, NAME) 
  (SELECT T.I, T.N FROM UNNEST(IDS, NAMES) AS T(I, N));

Um Arrays zu bevölkern, verwenden Sie Array-Konstruktoren.

Diese Anweisung füllt beispielsweise ein gewöhnliches Array:

SET CHARA = ARRAY['1','2','3','4','5','6'];

Diese Anweisungen füllen beispielsweise ein assoziatives Array, das Element für Element gefüllt werden muss:

SET CANADACAPITALS['Alberta'] = 'Edmonton';
SET CANADACAPITALS['Manitoba'] = 'Winnipeg';
SET CANADACAPITALS['Ontario'] = 'Toronto';
SET CANADACAPITALS['Nova Scotia'] = 'Halifax';

Für die Bearbeitung von Arrays stehen eine Reihe von integrierten Funktionen zur Verfügung. Dies sind:

ARRAY_DELETE: Löscht Elemente aus einem Array.
ARRAY_FIRST: Gibt den kleinsten Array-Indexwert eines Arrays zurück.
ARRAY_LAST: Gibt den maximalen Array-Indexwert eines Arrays zurück.
ARRAY_NEXT: Gibt den nächsthöheren Array-Indexwert relativ zu einem angegebenen Array-Indexwert zurück.
ARRAY_PRIOR: Gibt den nächstkleineren Array-Indexwert relativ zu einem angegebenen Array-Indexwert zurück.
CARDINALITY: Gibt die Anzahl der Elemente in einem Array zurück.
MAX_KARDINALITÄT: Gibt die maximale Anzahl von Elementen zurück, die ein Array enthalten kann.
TRIM_ARRAY: Löscht Elemente vom Ende eines gewöhnlichen Arrays.