コレクション

コレクションはストアドプロシージャの複合変数であり、同じ型の複数の要素を順序付きで格納できる1次元配列のようなものです。コレクション全体をサブルーチンのパラメータとして渡すことができます。

コレクションの内部コンポーネントは要素と呼ばれ、各要素にはコレクション内での位置を示す一意のインデックスが付いています。コレクションの要素にアクセスするには、インデックス指定方式を使用します：コレクション名(インデックス)。

コレクションメソッドは組み込みのストアドプロシージャであり、コレクションの情報を返すか、コレクションに対する操作を実行することができます。" . "を使用してコレクションを呼び出し、形式は以下のとおりです： コレクション名 . メソッド名。例えば、コレクション名 .COUNT メソッドは、そのコレクションの要素数を返すために使用されます。

集合タイプとその違い

OceanBaseデータベースのPLは、以下の3種類の集合タイプをサポートしています：

• 関連配列（インデックステーブルとも呼ばれる）

• 変動配列(Varrays)

• ネスト表

それぞれの類似点と相違点は以下の表のとおりです。

• 要素数

  要素数を指定した場合、それが集合内の要素の最大数となります。要素数を指定しない場合、集合内の最大要素数はインデックスタイプの上限となります。

• 密集または疎

  密集集合とは、要素間に隙間がないことを指します。つまり、最初の要素と最後の要素の間にあるすべての要素が定義され、値を持ちます（ただし、要素にNOT NULL制約がある場合を除き、その値はNULLとなり得ます）。疎集合とは、要素間に隙間があることを指します。

• 未初期化状態

  要素を一切含まない空集合が存在します。空集合に要素を追加するには、EXTENDメソッドを呼び出します。Null集合（または原子的Null集合とも呼ばれる）は存在しません。Null集合を有効な集合に変更するには、空集合に初期化する必要があります。つまり、空に設定するか、非NULL値を割り当てる必要があります。EXTENDメソッドを使用してNull集合を初期化することはサポートされていません。

• 定義方法

  PLブロック内で定義された集合タイプはローカル型です。これはブロック内でのみ使用可能であり、集合はブロックが独立またはプログラムブロック内にある場合にのみデータベースに格納されます。

  パッケージヘッダー内で定義された集合タイプは公開型です。パッケージ名（package_name.type_name）を限定することで外部からの参照を実現できます。集合はパッケージが削除されるまで常にデータベースに格納されます。

  スキーマレベルで定義された集合タイプは独立型です。CREATE TYPEステートメントを使用して集合を作成し、DROP TYPEステートメントを使用して削除できます。

集合変数への代入

集合変数への代入は、以下の方法で行えます：

• コンストラクタを呼び出して集合を作成し、それを集合変数に代入します。

• 代入文を使用して、既存の集合変数の値を代入します。

• OUTまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で代入します。

集合変数のスカラーエレメントに値を割り当てるには、collection_variable_name(index)を使用してそのエレメントを参照し、値を割り当てます。

集合変数に代入する際には、以下の点に注意してください：

• 代入が可能なのは、両方の集合変数が同じ要素タイプを持つ場合のみです。

• 配列やネスト表の変数には、NULL値や同じデータ型の空集合を代入できます。どちらの代入方法でも、変数は空値となります。

• ネスト表の変数には、SQL MULTISET演算子やSQL SET関数の呼び出し結果を代入できます。

多次元コレクション

OceanBaseデータベースでは、コレクションを使用して多次元コレクションを構築できます。つまり、多次元コレクションの各要素はコレクションです。例えば、多次元コレクションは二次元配列（つまり配列の配列）になります。

以下の例は、可変配列を含む多次元配列です。

obclient> DECLARE
      -- 最大容量が3で、要素型がINTの可変配列type_var1を定義します。
   TYPE type_var1 IS VARRAY(3) OF INT;

      -- 最大容量が5で、要素型がtype_var1の可変配列type_var2を定義します。
   TYPE type_var2 IS VARRAY(5) OF type_var1;

      -- type_var2型の可変配列変数varを定義します。
   var type_var2 := type_var2(type_var1(1,2,3), type_var1(4,5,6), type_var1(7,8,9));

 BEGIN
   FOR i IN 1..3 LOOP
      FOR j IN 1..2 LOOP
        DBMS_OUTPUT.PUT(VAR(i)(j) || '    ');
      END LOOP;
     DBMS_OUTPUT.PUT_LINE('');
   END LOOP;

END;/
Query OK, 0 rows affected

セットの比較

あるセット変数が別の変数より小さいかどうかを判断するには、まずその文脈における「小さい」という意味を定義し、TRUE または FALSE を返す関数を作成する必要があります。

連結配列変数は NULL 値と比較できず、連結配列変数同士も比較できません。また、2つのセット変数を関係演算子で比較することもできません。この制限は暗黙的な比較にも適用されます。例えば、セット変数は DISTINCT、GROUP BY、または ORDER BY 句には使用できません。

セットの比較には主に以下の3つの典型的なケースがあります：

• 可変配列とネスト表変数の NULL 値との比較

  NULL 値と比較する場合は、IS [NOT] NULL 演算子を使用してください。関係演算子の等しい（=）や不等号（<>、!=、〜=、^=）は使用できません。

  例：

  obclient> DECLARE
    TYPE players IS VARRAY(5) OF VARCHAR2(20);   --可変配列型を定義
    names PLAYERS := players('Charles', 'Carl', 'James');        --可変配列変数を定義
    TYPE register IS TABLE OF VARCHAR2(20);        -- ネスト表型を定義
    team REGISTER;    -- ネスト表変数を定義
  
  BEGIN
    IF names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Names IS NULL');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Names IS NOT NULL');
    END IF;
  
    IF team IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('Team IS NOT NULL');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Team IS NULL');
    END IF;
  END;
  /
  Query OK, 0 rows affected
  
  Names IS NOT NULL
  Team IS NULL
  

• ネスト表の相違性の比較

  2つのネスト表変数が等しいとは、両者が同じ要素集合（任意の順序で）を持つ場合に限ります。2つのネスト表変数が同じネスト表型を持ち、かつそのネスト表型がレコード型の要素を含まない場合、関係演算子の等しい（=）や不等号（< >、!=、〜=、^=）を使用して比較できます。

  例：

  obclient> DECLARE
    TYPE players IS TABLE OF VARCHAR2(30);   --要素型は集合型ではない
    name_list1 PLAYERS := players('Andrew', 'Barton', 'Conrad', 'Dick','Edward');
    name_list2 PLAYERS := players('Dick', 'Edward', 'Andrew', 'Conrad','Barton');
    name_list3 PLAYERS := players('John', 'Mary', 'Alberto', 'Juanita');
  
  BEGIN
    IF name_list1 = name_list2 THEN
      DBMS_OUTPUT.PUT_LINE('name_list1 = name_list2');
    END IF;
  
    IF name_list2 != name_list3 THEN
      DBMS_OUTPUT.PUT_LINE('name_list2 != name_list3');
    END IF;
  END;
  /
  Query OK, 0 rows affected
  
  name_list1 = name_list2
  name_list2 != name_list3
  

• ネスト表とSQLセット式の比較

  SQLセット式を使用してネスト表変数を比較し、特定の属性をテストできます。例：

  obclient> DECLARE
    TYPE nested_ty IS TABLE OF NUMBER;
    t1 nested_ty := nested_ty(6,7,9);
    t2 nested_ty := nested_ty(8,7,6);
    t3 nested_ty := nested_ty(7,8,6,8);
    t4 nested_ty := nested_ty(6,7,8);
    t5 nested_ty := nested_ty(6,7,8,6,7);
  
    PROCEDURE obtest (
      result BOOLEAN := NULL,
      quantity NUMBER := NULL
    ) IS
    BEGIN
      IF result IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE (
          CASE result
             WHEN TRUE THEN 'True'
             WHEN FALSE THEN 'False'
          END
        );
      END IF;
      IF quantity IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE(quantity);
      END IF;
    END;
  BEGIN
    obtest(result => (t4 IN (t1,t2,t3,t5)));        -- 条件
    obtest(result => (t4 SUBMULTISET OF t3));      -- 条件
    obtest(result => (t4 NOT SUBMULTISET OF t1));  -- 条件
    obtest(result => (4 MEMBER OF t4));             -- 条件
    obtest(result => (t1 IS A SET));                -- 条件
    obtest(result => (t1 IS NOT A SET));            -- 条件
    obtest(result => (t5 IS EMPTY));                -- 条件
    obtest(quantity => (CARDINALITY(t5)));         -- 関数
    obtest(quantity => (CARDINALITY(SET(t5))));    -- 2つの関数
  END;
  /
  Query OK, 0 rows affected
  
  True
  True
  True
  False
  True
  False
  False
  5
  3