Oracle で配列型（ARRAY）を試してみました。

　作り方は以下。まず create type で型を作ってから、create table でその型を列の型として指定します。

create type VARCHAR2_ARRAY as varray(5) of varchar2(10)

create table TAB1 (
    NO number(5),
    LIST VARCHAR2_ARRAY
)
;

　insert は "型名（データの列挙）" って感じで書けるようです。

insert into TAB1 values (10, VARCHAR2_ARRAY('AA', 'BB', 'CC', 'DD', 'EE'));
insert into TAB1 values (11, VARCHAR2_ARRAY('A1', 'B1', 'C1', 'D1', 'E1'));
insert into TAB1 values (12, VARCHAR2_ARRAY('A2', 'B2', 'C2'));
insert into TAB1 values (13, VARCHAR2_ARRAY('Z3', 'B2', 'C2'));

　sql*plus 上で select してみるとこんな感じ。
　データは見えるけど命令文みたいでちょっと面白くない感じですね。

SQL> select * from TAB1;

        NO LIST
---------- --------------------------------------------------
        10 VARCHAR2_ARRAY('AA', 'BB', 'CC', 'DD', 'EE')
        11 VARCHAR2_ARRAY('A1', 'B1', 'C1', 'D1', 'E1')
        12 VARCHAR2_ARRAY('A2', 'B2', 'C2')
        13 VARCHAR2_ARRAY('Z3', 'B2', 'C2')

　普通の列みたいに見えないか？と思って調べたところ、TABLE 演算子というのがあり、これを使うと配列を表の行データに分解してくれるようです。（コレクション・ネスト解除、というらしい）

SQL> select
  2     ROWNUM,
  3     COLUMN_VALUE
  4  from
  5     TABLE (
  6             select LIST
  7             from TAB1
  8             where NO=11
  9     )
 10  ;

    ROWNUM COLUMN_VALUE
---------- --------------------
         1 A1
         2 B1
         3 C1
         4 D1
         5 E1

　ただこの TABLE 演算子、複数行の入力ができず、クエリの結果は必ず単一行／単一インスタンスでないとならないので、上記コードの 8 行目の where を外すとエラーになってしまいます。

　なのでたとえば、全行の配列要素（=index）が 2 のものだけ抽出…というと、以下のコードを書く必要があります。

SQL> select *
  2  from
  3      (
  4          select
  5              NO,
  6              row_number() over(partition by NO order by N) as ARRAY_INDEX,  -- [3]
  7              ARRAY_VALUE
  8          from
  9              (
 10                  select  -- [2]
 11                      A.NO,
 12                      ROWNUM as N,
 13                      C.COLUMN_VALUE as ARRAY_VALUE
 14                  from  -- [1]
 15                      TAB1 A,
 16                      TABLE(
 17                          select
 18                              LIST
 19                          from
 20                              TAB1 B
 21                          where
 22                              B.NO = A.NO
 23                      ) C
 24             )
 25     )
 26  where
 27     ARRAY_INDEX = 2  -- [4]
 28  ;

        NO ARRAY_INDEX ARRAY_VALUE
---------- ----------- --------------------
        10           2 BB
        11           2 B1
        12           2 B2
        13           2 B2

　上記のコードがやっていることは以下のようなことです。

1. コレクション・ネスト解除した配列の表と、元の表を結合します。
2. とりあえず、配列の格納順を保存するために rownum を含めて射影します。

   # この rownum は順序を保存しているだけなので配列の要素番号とは異なります。
   # 今回データの場合だと16件ヒットするので、1～16が振られています。

3. NO で分割（Partition）します。

   # partition ではソート列を指定する必要があるので、ここで先ほどの rownum 列を指定します。
   # 求める値は分割後の行番号（row_number）にします。これが配列の要素番号になります。

4. 配列の要素番号を指定し、絞り込みます。

　OLAP 関数使ってまで…って感じでちょっといまいちです。# 誰かほかにいい方法を知っていたら教えてください（汗

　まあ配列型を SQL で扱おうとすること自体がダウト、なのかもしれません。
　PL/SQL で配列の要素を参照する場合は以下のような感じ。

SQL> set serveroutput on
SQL> declare
  2    ar VARCHAR2_ARRAY;
  3  begin
  4    select LIST into ar from TAB1 where NO=11;
  5    dbms_output.put_line(ar(2));
  6  end;
  7  /
B1

PL/SQLプロシージャが正常に完了しました。

　全行の配列要素（=index）が 2 のものだけを表示したい場合は以下。
　ずいぶんとすっきりします。

SQL> set serveroutput on
SQL> declare
  2  begin
  3      for row in (select LIST from TAB1) loop
  4          dbms_output.put_line(row.LIST(2));
  5      end loop;
  6  end;
  7  /
BB
B1
B2
B2

PL/SQLプロシージャが正常に完了しました。