Die Arbeit mit Arrays ist für einen APEX oder PL/SQL Entwickler alltäglich - sie werden immer wieder gebraucht. Und doch erscheint der Umgang mit Arrays in der Oracle-Datenbank oft rätselhaft.

Es gibt drei Array-Typen in der Oracle-Datenbank: VARRAYs, Nested Tables und PL/SQL Assoziative Arrays. Dieser Tipp gibt einen Einblick in die Arbeit mit Arrays in der Oracle Datenbank und stellt vor, was geht und was nicht. Dabei wird auf SQL und PL/SQL fokussiert - in Application Express lässt sich das hier vorgestellte natürlich beliebig nutzen: Beispiele sind Berichts-SQL oder PL/SQL in einem onSubmit-Prozess. In diesem ersten Teil des Tipps stellen wir VARRAYs und Nested Tables, die Array-Varianten, die sowohl in SQL als auch in PL/SQL bereitstehen, vor. Somit können diese Arrays auch als Datentypen für Tabellenspalten oder in SQL-Abfragen bzw. in DML-Anweisungen genutzt werden. Die nur in PL/SQL verfügbaren assoziativen Arrays werden im zweiten Teil behandelt.

VARRAY und SQL: SELECT, CREATE TYPE, CREATE TABLE

Ein VARRAY (Varying Array) ist am ehesten mit den Arrays in Java oder C vergleichbar. Bei Definition wird ein oberes Limit festgelegt, welches bis zu 2147483647 (2³¹-1) Elementen reichen kann.

create type t_stringarray as varray(2000) of varchar2(200)
/

Nach Absetzen dieses Kommandos enthält das Datenbankschema einen neuen benutzerdefinierten Datentypen - der in SQL und PL/SQL so verwendet werden kann, wie alle anderen. So kann eine Tabelle mit einer Spalte dieses Typs angelegt und Werte eingefügt werden.

create table tab_mydata (
  id         number(10),
  namelist   t_stringarray
)
/

insert into tab_mydata values(
  1, 
  t_stringarray('APEX','HTMLDB','Application Express', 'Flow Builder')
)
/

Auch mehrdimensionale Arrays sind möglich ...

create type t_array_1d as varray(100000) of number;
/

create type t_array_2d as varray(100000) of t_array_1d;
/

create table tab_matrix (
  id          number(10),
  matrix_xy   t_array_2d
)
/

insert into tab_matrix values (
  1, t_array_2d(t_array_1d(1,2,3), t_array_1d(4,5,6), t_array_1d(7,8,9))
)

Natürlich können die Werte auch wieder ausgelesen werden. Interessant ist hier die Fähigkeit der SQL-Engine, Arrays mit der Funktion TABLE() als Tabellen aufzufassen. Das folgende Beispiel zeigt das:

select * from tab_mydata
/

        ID NAMELIST
---------- ----------------------------------------
         1 T_STRINGARRAY('APEX', 'HTMLDB', 'Applica
           tion Express', 'Flow Builder')

1 Zeile ausgewählt.

select t.id, x.column_value from tab_mydata t, table(namelist) x
/

        ID COLUMN_VALUE
---------- --------------------
         1 APEX
         1 HTMLDB
         1 Application Express
         1 Flow Builder

4 Zeilen ausgewählt.

Die Funktion TABLE() fasst ein Array also wie eine Tabelle auf, die per Join in die Abfrage eingebunden wird - das funktioniert auch für mehrdimensionale Arrays.

select t.id, x.column_value from tab_matrix t, table(t.matrix_xy) x
/

        ID COLUMN_VALUE
---------- ------------------------------
         1 T_ARRAY_1D(1, 2, 3)
         1 T_ARRAY_1D(4, 5, 6)
         1 T_ARRAY_1D(7, 8, 9)

select t.id, y.column_value from tab_matrix t, table(t.matrix_xy) x, table(x.column_value) y
/


        ID COLUMN_VALUE
---------- ------------
         1            1
         1            2
         1            3
         1            4
         1            5
         1            6
         1            7
         1            8
         1            9

Das Gegenstück zu TABLE() ist die Funktion COLLECT() - diese fasst einzelne Tabellenzeilen zu einem Array zusammen.

select cast(collect(ename) as t_stringarray) from emp 
/

insert into tab_mydata (
  select 2, cast(collect(ename) as t_stringarray) from emp
)
/

Interessant ist die Frage, wie das Array nun konkret gespeichert wird; immerhin wird hier - in einer Tabellenspalte - eine ganze Liste abgelegt. Ein Blick in USER_TABLES zeigt schnell, dass keine zweite Tabelle angelegt wurde. Sehr aufschlußreich ist dagegen die Dictionary View USER_LOBS.

select table_name, column_name, segment_name, in_row 
from user_lobs where table_name='TAB_MYDATA'
/

TABLE_NAME      COLUMN_NAME     SEGMENT_NAME                   IN_ROW
--------------- --------------- ------------------------------ ------
TAB_MYDATA      NAMELIST        SYS_LOB0000323692C00002$$      YES

Das VARRAY wird also intern als Large Object - und damit als Ganzes abgelegt. Benötigt das VARRAY weniger als 4000 Byte, so wird es, zusammen mit den anderen Spalteninhalten, "in row" gespeichert. Größere VARRAYs werden in das separate LOB-Segment ausgelagert.

Daraus folgt, dass sich solche VARRAYs vor allem dann eignen, wenn eine Liste bei Zugriffen normalerweise als Ganzes gelesen und dann weiterbearbeitet wird. Mit nur einem einzigen Zugriff hat man eine auch größere Werteliste komplett im Zugriff - im Vergleich zu einer "normalen" 1-n Tabellenbeziehung ist das kompakter und effizienter. Ein Beispiel dafür ist der Oracle-Datentyp für Geodaten (SDO_GEOMETRY), welcher die Koordinatenliste eines Linienzugs oder Polygons als VARRAY speichert. Grund ist, dass diese stets als Ganzes gelesen wird - niemand braucht nur einen Teil der Koordinaten eines Polygons.

Nested Tables und SQL: SELECT, CREATE TYPE, CREATE TABLE

Im Vergleich zu einem VARRAY ist eine Nested Table weniger ein Array als - wie der Name schon sagt - eine Tabelle. Ein Datentyp für eine Nested Table wird wie folgt erzeugt.

create type t_stringtable as table of varchar2(200)
/

Wiederum kann dieser neue benutzerdefinierte Datentyp in SQL und PL/SQL verwendet werden - verwendet man ihn allerdings für eine Tabellenspalte, so erlebt man zunächst eine Überraschung.

create table tab_mydata (
  id         number(10),
  namelist   t_stringtable
)
/

FEHLER in Zeile 3:
ORA-22913: Tabellenname für Nested Table-Spalte oder -Attribut muss angegeben werden

Da eine Nested Table keinerlei oberes Limit für die Anzahl an Einträgen hat, kommt die Art der Speicherung als LOB (wie bei einem VARRAY) nicht in Frage. Die Inhalte des VARRAY müssen in eine separate Tabelle abgelegt werden - und für diese Tabelle muss ein Name angegeben werden.

create table tab_mydata (
  id         number(10),
  namelist   t_stringtable
)
nested table namelist store as ntab_namelist
/

Die SQL-Funktionen TABLE() und COLLECT() funktionieren analog zu VARRAYs - und auch Nested Tables können mehrdimensional sein; es braucht dann eine Tabelle für jede Hierarchieebene.

insert into tab_mydata values(
  1, 
  t_stringtable('APEX','HTMLDB','Application Express', 'Flow Builder')
)
/

select * from tab_mydata
/

        ID NAMELIST
---------- ----------------------------------------
         1 T_STRINGTABLE('APEX', 'HTMLDB', 'Applica
           tion Express', 'Flow Builder')

1 Zeile ausgewählt.

select t.id, x.column_value from tab_mydata t, table(namelist) x
/

        ID COLUMN_VALUE
---------- --------------------
         1 APEX
         1 HTMLDB
         1 Application Express
         1 Flow Builder

4 Zeilen ausgewählt.

Wiederum ist die Frage nach der konkreten Speicherung interessant - doch nun ist die Situation eine andere: Schließlich haben wir den Namen NTAB_NAMELIST beim CREATE TABLE-Kommando explizit angegeben. Es lohnt sich also, sich diese Tabelle etwas näher anzusehen ...

SQL> select table_name, parent_table_name, parent_table_column 
     from user_nested_tables;

TABLE_NAME      PARENT_TABLE_NAME              PARENT_TABLE_COLUMN
--------------- ------------------------------ ------------------------------
NTAB_NAMELIST   TAB_MYDATA                     NAMELIST

SQL> desc ntab_namelist

 Name                                Null?    Typ
 ----------------------------------- -------- ------------------------
 COLUMN_VALUE                                 VARCHAR2(200)

SQL> select * from NTAB_NAMELIST;

FEHLER in Zeile 1:
ORA-22812: Speichertabelle der Spalten einer Nested Table nicht
referenzierbar

Das Vorhandensein der Nested Table und die Verknüpfung zu ihrer Parent Table lassen sich anhand des Data Dictionary sehr gut nachvollziehen - direkten Zugriff auf die Nested Table erlaubt Oracle dagegen nicht. Der Grund dafür ist, dass diese Tabellen in der Anwedung eben nicht direkt verwendet werden sollen - der Zugriff soll - wie oben gezeigt - über die Parent-Tabelle und die SQL-Funktion TABLE() erfolgen.

Eine Nested Table eignet sich im Vergleich zu einem VARRAY daher sehr gut, wenn bei Zugriffen nicht zwingend die ganze Liste gelesen werden soll. Dieser Umstand sei an der folgenden Abfrage deutlich gemacht (Auch diese Abfrage funktioniert sowohl mit Nested Tables als auch mit VARRAYs).

select t.id, x.column_value from tab_mydata t, table(namelist) x
where x.column_value like 'H%'

        ID COLUMN_VALUE
---------- ------------------------------
         1 HTMLDB

Hier wird also nur ein Teil der Liste benötigt - ist diese als VARRAY gespeichert, so wird dennoch das ganze Array (LOB-Segment) gelesen und danach werden die gewüschten Zeilen selektiert. Man kann sich schon denken, dass die Nested Table (da die Inhalte in einer separaten Tabelle abgelegt werden) Möglichkeiten zur Optimierung bietet ...

create index ntx_namelist on ntab_namelist (column_value)
/

Index erstellt.

Die Datenbank ist nun in der Lage, den Index zu nutzen - und so tatsächlich nur die Teile der Liste zu lesen, die tatsächlich angefordert sind. Der Fairness halber muss man aber auch zugeben, dass man nun auch über ein "normales" relationales Modell nachdenken könnte.

Nested Tables und VARRAYs in PL/SQL

Auch in PL/SQL lassen sich VARRAYs und Nested Tables verwenden ...

declare
  l_stringarray t_stringarray;
begin
  -- Array-Variable mit zwei Werten initialisieren 
  l_stringarray := t_stringarray('APEX', 'HTMLDB');
  dbms_output.put_line('Size of array: '||l_stringarray.count);
  -- Array um einen Wert erweitern
  l_stringarray.extend(1);
  dbms_output.put_line('Size of array: '||l_stringarray.count);
  l_stringarray(3) := 'Flow Builder';

  dbms_output.put_line('Now printing the array ...');
  for i in 1..l_stringarray.count loop
    dbms_output.put_line('Position '||i||': '||l_stringarray(i));
  end loop;
end;
/

Size of array: 2
Size of array: 3
Now printing the array ...
Position 1: APEX
Position 2: HTMLDB
Position 3: Flow Builder

Ein Array wird, analog zu objektorientierten Programmiersprachen, zunächst initialisiert. Hier kann schon eine Werteliste übergeben werden. Wird die Werteliste weggelassen, so wird das Array mit genau einem Wert initialisiert - und dieser Wert wird mit SQL NULL belegt. Bevor zusätzliche Einträge mit l_stringarray(x) := '...' hinzugefügt werden können, muss das Array mit der Funktion EXTEND erweitert werden. Der Parameter von EXTEND gibt an, für wieviele neue Einträge Platz geschaffen werden soll. In obigem Beispiel hat das VARRAY eine Größe von drei Werten; mit der Funktion COUNT lässt sich das feststellen. Die Oracle-Dokumentation enthält in der PL/SQL Language Reference Übersicht über die PL/SQL-Funktionen für Arrays.

In einer PL/SQL Prozedur, Funktion oder einem anonymen Block kann ein Array-Type auch "lokal" definiert werden. Der Code dazu sieht wie folgt aus:

declare
  type t_plsqlarray is varray(100) of varchar2(300);
  l_stringarray t_plsqlarray;
begin
  -- Array-Variable mit zwei Werten initialisieren 
  l_stringarray := t_plsqlarray('APEX', 'HTMLDB');
:

Der Type t_plsqlarray ist innerhalb des anonymen Blocks definiert und nur in diesem gültig. Für Tabellenspalten oder außerhalb des anonymen Blocks kann er nicht genutzt werden. Das gilt analog für Stored Procedures und Stored Functions. Ein in einer Package-Spezifikation definierter Array-Typ kann, wie eine globale Package-Variable, in anderen PL/SQL-Objekten, nicht jedoch für Tabellendefinitionen genutzt werden.

Speziell für Nested Tables bietet PL/SQL noch "Multiset" Funktionen an, mit denen SQL-Ähnliche Operationen wie UNION, INTERSECT oder DISTINCT auf PL/SQL Nested Tables durchgeführt werden können. Mit Variablen vom Typ VARRAY funktionieren diese Funktionen dagegen nicht. Das folgende Beispiel führt zwei in PL/SQL definierte Nested Table-Objekte zusammen und entfernt Duplikate.

declare
  type t_plsqltable is table of varchar2(100);
  l_array1 t_plsqltable;
  l_array2 t_plsqltable;

  procedure print_array(p_array t_plsqltable) is
  begin
    dbms_output.put_line('*** Beginn ***');
    for i in 1..p_array.count loop
      dbms_output.put_line (i||'. '||p_array(i));
    end loop;
    dbms_output.put_line('*** Ende ***');
  end print_array;
begin
  l_array1 := t_plsqltable('APEX', 'Application Express');
  l_array2 := t_plsqltable('HTML DB','Application Express','Flow Builder');
  print_array(l_array1 multiset union distinct l_array2);
end;
/
sho err

Der Beispielcode produziert diese Ausgabe:

*** Beginn ***
1. APEX
2. Application Express
3. HTML DB
4. Flow Builder
*** Ende ***

Fazit und weitere Informationen

VARRAYs und Nested Tables erlauben es, Arrays im SQL-Kontext zu nutzen. Es wird möglich, Arrays in einzelnen Tabellenspalten abzulegen und mit SQL auf diese Werte zuzugreifen. Insbesondere, wenn Wertelisten stets als Ganzes gelesen und geschrieben werden sollen, kann das VARRAY eine Alternative zu einer separaten Tabelle sein. VARRAYs und Nested Tables lassen sich auch in PL/SQL nutzen. PL/SQL Entwickler können jedoch auch mit assoziativen Arrays arbeiten - diese werden im nächsten Community Tipp näher betrachtet. Mehr Informationen zu VARRAYs und Nested Tables finden Sie ...

• PL/SQL Language Reference: PL/SQL Collections and Records
  http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CIHIHBIA
• SQL Language Reference: VARRAY column properties of the CREATE TABLE command
  http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#i2143624
• SQL Language Reference: COLLECT Function
  http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions033.htm#i1271564

Zurück zur Community-Seite