PL/SQL 配列

OracleのPL/SQLの配列について。

概要

Oracleのドキュメントでは、配列関連をコレクション型と呼んでいるようだ。

コレクション型には3つの種類がある。

配列を使うためには、typeで配列型を定義する必要がある。
変数宣言のデータ型に直接「varray(n) of〜」や「table of〜」を書くことは出来ない。

配列の要素を指定するには、変数の後ろに丸括弧を付けて添字を指定する。

VARRAY

VARRAYは可変長配列。
可変長といっても、保持できる要素の最大数は決まっている。（最初に上限を定義する）
添字は1から始まる。

declare
  --Basicの dim EX(10) as String
  type EX is varray(10) of varchar2(8); --最大要素数は10
  v EX;
begin
  v := EX();    --配列の初期化
  v.extend(10); --空要素追加

  v(1) := 'abc';
  v(2) := 'def';
end;

  v := EX('a', 'b', 'c'); --初期値を指定した初期化

VARRAYの変数は実際に保持できる要素数を持っており、それを超える添字は指定できない。

以下の例では、EX()で初期化すると要素数は0個なので、v(1)は要素数の範囲を超えており、エラーになる。

  v := EX(); --要素数が0個
  v(1) := 'abc';
↓
ORA-06533: サブスクリプトがカウントを超えています。 ORA-06512: 行9

  v := EX('a', 'b', 'c'); --要素数が3個
  v(1) := 'aaa';
  v(4) := 'ddd'; --エラー

extendを呼び出すと要素を追加することが出来る。
extendの引数には追加する要素数を指定する。省略した場合は1個だけ追加される。

  v := EX();   --要素数0個
  v.extend(1); --要素数1個
  v(1) := 'abc';

  v := EX('a', 'b', 'c'); --要素数3個
  v.extend(2);            --要素数5個
  v(4) := 'ddd';
  v(5) := 'eee';

要素を追加していって、最初のvarary(N)で指定した上限を超えるとエラーになる。

declare
  type EX is varray(10) of varchar2(8); --最大要素数は10
  v EX;
begin
  v := EX('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'); --要素数10個
  v.extend(); --要素を追加（上限を超える）
end;
↓
ORA-06532: サブスクリプトが有効範囲外です。 ORA-06512: 行7

ネストした表

ネストした表（nested table）は、要するにリストのこと。（JavaのList相当）

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
begin
  v := EX(); --初期化

  v.extend(); --空要素追加
  v(1) := 'abc'; --追加した位置に値を設定
end;

初期化方法

ネストした表の変数は、使用前に初期化する必要がある。[2020-12-10]
定義した配列の型の後ろに丸括弧を付けて、関数（コンストラクター）のように呼び出す。

  v := EX(); --要素数0個で初期化

  v := EX('a', 'b', 'c'); --初期値つきの初期化（要素数3個）

変数宣言時に一緒に初期化する例。

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX := EX(); --初期化
begin
  v.extend(); --空要素追加
  v(1) := 'abc';
end;

添字

ネストした表の要素を扱うには、その変数の後ろに丸括弧で添字を指定する。[2020-12-10]
添字は1から始まる。

  v(1) := 'abc';
  dbms_output.put_line(v(1));

なお、“変数内で保持している範囲”の外の要素を扱うことは出来ない。

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
begin
  v := EX(); --要素数0個で初期化

  v(1) := 'abc'; --要素数が0個なので、v(1)には代入できない
end;
↓
ORA-06533: サブスクリプトがカウントを超えています。 ORA-06512: 行8

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
begin
  v := EX(); --要素数0個で初期化

  dbms_output.put_line(v(1)); --要素数が0個なので、v(1)は参照できない
end;
↓
ORA-06533: サブスクリプトがカウントを超えています。 ORA-06512: 行8

末尾への要素の追加

ネストした表に要素を追加するには、extendを使う。

  v.extend(); --空要素追加
  v(v.last) := 'abc'; --末尾に値をセット

長さや添字の上限・下限の取得

ネストした表の長さ（要素数）や添字の上限下限は以下のようにして取得できる。[2020-12-10]

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
begin
  v := EX(); --初期化
  v.extend(3);

  dbms_output.put_line('count=' || v.count);
  dbms_output.put_line('first=' || v.first);
  dbms_output.put_line('last =' || v.last);
end;

ネストした表が空（要素数＝0）の場合、firstおよびlastはNULLを返す。

deleteによって要素が削除された場合、countも減る。
先頭要素が削除された場合はfirstも変動し、末尾要素が削除された場合はlastも変動する。

巡回

ネストした表の全ての要素を処理するにはfor文が利用できる。[2020-12-10]

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
begin
  v := EX('a', 'b', 'c'); --初期化

  for i in 1..v.count loop
    dbms_output.put_line(v(i));
  end loop;
end;

  v := EX('a', 'b', 'c'); --初期化
  v.delete(2);

  for i in v.first..v.last loop
    if v.exists(i) then
      dbms_output.put_line(v(i));
    end if;
  end loop;

  for i in v.first..v.last loop
    continue when not v.exists(i); --要素が存在しないときはスキップ

    dbms_output.put_line(v(i));
  end loop;

ネストした表の要素数が0個だとfirst, lastはNULLになるので、for文でエラーになる。
for文の前にfirstをNULLチェックしたり、nvl辺りで値を変更したりする必要がある。

ネストした表が密の場合（要素がdeleteされない場合）は、first（先頭の添字）は常に1でlast（末尾の添字）はcountと等しくなるので、「1 .. v.count」の方が楽だろう。

ネストした表が疎の場合（要素がdeleteされる場合）は、first, lastを使わないと正確にならない。
要素が存在しているかどうか（deleteされていないこと）の確認はexistsで行う。
deleteされているのにその要素を参照しようとすると「ORA-01403: データが見つかりません。」が発生するので、existsによる確認は必須。[2021-01-06]

nextやpriorを使うと、要素が存在している添字だけを扱うことが出来る。

nextは次に存在している添字、priorは前に存在している添字を返す。
要素が無い場合はNULLを返す。

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;
  i number;
begin
  v := EX('a', 'b', 'c'); --初期化
  v.delete(2);

  i := v.first;
  while i is not null loop
    dbms_output.put_line(v(i));

    i := v.next(i);
  end loop;
end;

文字列変換

ネストした表は文字列へ暗黙変換されない。[2020-12-10]
（dbms_output.putの引数に渡したり、文字列と結合したりすることは出来ない）

文字列に変換するには地道にループするしかなさそう。

declare
  --Javaの List<String>
  type EX is table of varchar2(8);
  v EX;

  str varchar2(256);
  sep varchar2(2);
  i number;
begin
  v := EX('a', 'b', 'c'); --初期化

  str := '';
  sep := '';
  i := v.first;
  while i is not null loop
    str := str || sep || v(i);

    sep := ', ';
    i := v.next(i);
  end loop;
  dbms_output.put_line(str);
end;

カンマで区切られた文字列を配列に変換するにはdbms_utility.comma_to_tableが便利かもしれない。

comma_to_tableの第1引数にカンマ区切りの文字列を指定する。
第3引数に分割された文字列、第2引数にその個数が返ってくる。

declare
  type EX is table of varchar2(8); --JavaのList<String>
  v EX;

  len binary_integer;
  arr dbms_utility.uncl_array; -- TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER
begin
  dbms_utility.comma_to_table('a, b, c', len, arr);
  dbms_output.put_line(len);

  v := EX(); --初期化
  v.extend(len);

  for i in 1..len loop
    v(i) := arr(i);

    dbms_output.put_line(v(i));
  end loop;
end;

ただ、数字をカンマ区切りにしている文字列に対してはバグがあるようで、正しく分解できないようだ。[2020-12-16]
参考： stackoverflowのcomma separated list invalid near.. with comma_to_table

  dbms_utility.comma_to_table('1, 2, 3', len, arr);
↓
ORA-20001: comma-separated list invalid near 3
ORA-06512: "SYS.DBMS_UTILITY", 行236
ORA-06512: "SYS.DBMS_UTILITY", 行256
ORA-06512: 行8

泥臭く分解するしかないかな？[2020-12-16]

declare
  type EX is table of varchar2(8); --JavaのList<String>
  v EX := EX();

  input varchar2(256) := '1, 2, 3';
  s varchar2(1);
  start_pos int;
begin
  start_pos := 1;
  for i in 1..length(input) + 1 loop
    s := substr(input, i, 1);
    if s = ',' or s is null then
      if i - start_pos >= 1 then
        v.extend();
        v(v.last) := substr(input, start_pos, i - start_pos);
      end if;
      start_pos := i + 1;
    end if;
  end loop;

  for i in 1..v.count loop
    dbms_output.put_line('[' || v(i) || ']');
  end loop;
end;