コレクション

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

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

コレクションメソッドは組み込みのストアドプロシージャであり、コレクションの情報を返すか、コレクションに操作を加えることができます。" . " を使用してコレクションを呼び出し、形式は次のとおりです： コレクション名 . メソッド名。例えば、コレクション名 .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 値、または同一データ型の空コレクションを代入できます。いずれの方法で代入しても、変数は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))));    -- 二つの関数
  END;
  /
  Query OK, 0 rows affected
  
  True
  True
  True
  False
  True
  False
  False
  5
  3