LoginSignup
6
6

More than 3 years have passed since last update.

@szly

PL/SQLで配列を扱う(要素数0をうまく扱う)

Last updated at Posted at 2020-02-16

最近会社で聞かれて答えたことをまとめてみました。

PL/SQLで配列を扱うとき、FIRST～LASTでループするような処理を作っていたら、配列の要素数が0のときにエラーになってしまった。どうすればよいか分からない、と聞かれました。

用語

配列と呼んでいるものは、Oracleの公式用語としてはコレクションです。

コレクション・メソッド

コレクションの先頭データを取得するFIRST、コレクションの末尾データを取得するLASTなどは、コレクション・メソッドと呼ばれます。この用語を知っていないと 公式ドキュメント(11g) を検索することも容易ではありません。

コレクションメソッドを使って、0件でもうまく動くような、コレクションを一巡する処理を作成してみます。

そもそもコレクションには3種類ある

PL/SQLには、コレクションと呼べるものが3種類あります。

  • 結合配列
  • ネストした表
  • 可変長配列(VARRAY)

分類の詳細と特性については、SHIFT the Oracle さんのサイト を見ていただくのが分かりやすいと思います。

サンプルコードの解説

動作を理解するためのサンプルコードを末尾に置いてあります。このサンプルコードの動作は、以前作成した Oracle Database 11g XE を用いて動作確認しています。

T_ARRAY1が結合配列、T_ARRAY2がネストした表、T_ARRAY3が可変長配列(VARRAY)を表すデータ型です。これらのデータ型で宣言した変数が、V_ARRAY1～V_ARRAY3です。

このサンプルコードは、コレクションを一巡する処理に焦点を当てています。そのため、コレクションに値を入れる部分は、さほど凝ったことをしていません。EXTENDなどを使用していない、という意味です。

処理をみると分かりますが、コレクションの情報を出力する PRC_OUTPUT は3つとも同じコードです。引数の型のみが異なります。

言い換えるとこうなります。

コレクションは、宣言、初期化、代入、要素数の拡大の方法が、3種類それぞれ微妙に違います。しかし、コレクションを一巡して参照する処理は、どれも同じ書き方で書くことができます。

ループで回す場合、以下のどちらかの方法を使うことになります。

  • FIRST ～ LAST でループして EXISTS で存在するもののみを処理する
  • FIRST+NEXT で飛び飛びに処理する

前者は添え字が数字の場合に使うことができます。Javaに例えると、ArrayListをインデックス番号で辿るような方法です。

後者は添え字の種類を問わず使うことができます。Javaに例えると、LinkedListをリンク経由で辿るような方法です

なお、FIRST ～ LAST でループする場合、注意する点があります。

COUNT=0の場合、FIRST=LAST=NULLとなります。この場合、FIRST ～ LAST でループしようとすると、エラーになってしまいます(ORA-06502)。そのため、ループを COUNT>0 のIFで包む必要があります。

逆順に処理するときは、ループの REVERSE や、コレクションメソッドの PRIOR を使えばできます。でも、使うことがあるかは微妙です。

結合配列

結合配列は、他の言語で言うところの連想配列です。そのため、添え字は連続しているとは限りません。さらに、添え字は文字の可能性もあります。

飛び飛びの度合いが強いなら、FIRST+NEXTでループするのが効率的です。

結合配列では、"未初期化"＝"要素数0"です。未初期化の場合、COUNTメソッドは0を返します。

"ネストした表" や "可変長配列" では、NULLを代入することで再度、未初期化の状態にできます。しかし、結合配列には、NULLを代入できません(ORA-06550)。再初期化するには、未初期化の変数を用いて代入するという方法があります。

サンプルコードでは V_EMPTY が未初期化のままとなっている変数です。これをV_ARRAY1に代入することで、再度、未初期化状態に戻す処理としています。

ネストした表

ネストした表は、1オリジンのコレクションです。

単純に作成するうえでは密なコレクションです。しかし、DELETEメソッドを用いて途中の要素を削除することができます。このようなことをすると疎なコレクションになります。

大量にDELETEして、よほど飛び飛びにならない限り、FIRST～LASTでループするのが効率的かつ分かりやすいでしょう。

ネストした表は、"未初期化"≠"要素数0"です。未初期化の場合、COUNTメソッドもエラーとなります(ORA-06531)。
必ず初期化してから使うようにすることで、COUNTのエラーを避けられます。
Effective Java 項目43「nullではなく空配列か空コレクションを返す」に似ているかもしれません。
初期化さえしておけば、結合配列の場合と同様に扱うことができます。

NULLを代入することで再度、未初期化の状態にできます。
しかし、先にも書いたように、NULLを代入するよりは要素数0のコレクションとするほうが使い勝手が良いでしょう。

可変長配列(VARRAY)

可変長配列は、1オリジンで密なコレクションです。

密なコレクションなので、FIRST～LASTでループするのが効率的かつ分かりやすいでしょう。

下のサンプルコードでは、処理を揃えるためにFIRST～LASTループ部分にEXISTSを入れてあります。しかし、密なコレクションなので、EXISTSはなくても動きます。

可変長配列は、"未初期化"≠"要素数0"です。未初期化の場合、COUNTメソッドもエラーとなります(ORA-06531)。このあたりの特性は、ネストした表に似ています。

DELETEメソッドを用いて一部の要素を削除することはできません(ORA-06550)。DELETEメソッドを引数なしで使用し、要素を全削除することは可能です。全削除後は、要素数0となります。

NULLを代入することで再度、未初期化の状態にできます。ここも、ネストした表に似ています。

サンプルコード

DECLARE
    TYPE        T_ARRAY1    IS  TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;     -- 結合配列
    TYPE        T_ARRAY2    IS  TABLE OF VARCHAR2(100);                             -- ネストされた表
    TYPE        T_ARRAY3    IS  VARRAY(10) OF VARCHAR2(100);                        -- 可変長配列
    V_IDX       NUMBER;

    -- 結合配列
    PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY1 ) IS
    BEGIN
        -- COUNT/FIRST/LASTを表示
        BEGIN
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
                RETURN;
        END;
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
        -- FIRST/LAST/EXISTSを用いてループ
        --      添え字が数字ならこの方法でもできる
        --      NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
        IF V_ARRAY.COUNT > 0 THEN
            FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
                IF V_ARRAY.EXISTS ( V_IDX ) THEN
                    DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
                END IF;
            END LOOP;
        END IF;
        -- FIRST/NEXTを用いてループ
        --      添え字が文字でも扱える
        V_IDX := V_ARRAY.FIRST;
        LOOP
            EXIT WHEN V_IDX IS NULL;
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
            V_IDX := V_ARRAY.NEXT(V_IDX);
        END LOOP;
    END;

    -- ネストした表
    PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY2 ) IS
    BEGIN
        -- COUNT/FIRST/LASTを表示
        BEGIN
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
                RETURN;
        END;
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
        -- FIRST/LAST/EXISTSを用いてループ
        --      添え字が数字ならこの方法でもできる
        --      NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
        IF V_ARRAY.COUNT > 0 THEN
            FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
                IF V_ARRAY.EXISTS ( V_IDX ) THEN
                    DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
                END IF;
            END LOOP;
        END IF;
        -- FIRST/NEXTを用いてループ
        --      添え字が文字でも扱える
        V_IDX := V_ARRAY.FIRST;
        LOOP
            EXIT WHEN V_IDX IS NULL;
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
            V_IDX := V_ARRAY.NEXT(V_IDX);
        END LOOP;
    END;

    -- 可変長配列
    PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY3 ) IS
    BEGIN
        -- COUNT/FIRST/LASTを表示
        BEGIN
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
                RETURN;
        END;
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
        DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
        -- FIRST/LAST/EXISTSを用いてループ
        --      添え字が数字ならこの方法でもできる
        --      NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
        IF V_ARRAY.COUNT > 0 THEN
            FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
                IF V_ARRAY.EXISTS ( V_IDX ) THEN
                    DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
                END IF;
            END LOOP;
        END IF;
        -- FIRST/NEXTを用いてループ
        --      添え字が文字でも扱える
        V_IDX := V_ARRAY.FIRST;
        LOOP
            EXIT WHEN V_IDX IS NULL;
            DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
            V_IDX := V_ARRAY.NEXT(V_IDX);
        END LOOP;
    END;

BEGIN

    -- 結合配列
    DECLARE
        V_ARRAY     T_ARRAY1;
        V_EMPTY     T_ARRAY1;
    BEGIN
        DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
        DBMS_OUTPUT.PUT_LINE ( '■■■結合配列' );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■代入' );
        V_ARRAY(1) := '111';
        V_ARRAY(3) := '333';
        V_ARRAY(5) := '555';
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
        V_ARRAY := V_EMPTY;
        PRC_OUTPUT ( V_ARRAY );
        --
    END;

    -- ネストした表
    DECLARE
        V_ARRAY     T_ARRAY2;
    BEGIN
        DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
        DBMS_OUTPUT.PUT_LINE ( '■■■ネストした表' );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
        V_ARRAY := NULL;
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(空)' );
        V_ARRAY := T_ARRAY2();
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み' );
        V_ARRAY := T_ARRAY2( '111', '333', '555' );
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(途中DELETE)' );
        V_ARRAY := T_ARRAY2( '111', '333', '555' );
        V_ARRAY.DELETE(2);
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(全DELETE)' );
        V_ARRAY := T_ARRAY2( '111', '333', '555' );
        V_ARRAY.DELETE;
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
        V_ARRAY := T_ARRAY2( '111', '333', '555' );
        V_ARRAY := NULL;
        PRC_OUTPUT ( V_ARRAY );
        --
    END;

    -- 可変長配列(VARRAY)
    DECLARE
        V_ARRAY     T_ARRAY3;
    BEGIN
        DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
        DBMS_OUTPUT.PUT_LINE ( '■■■可変長配列(VARRAY)' );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
        V_ARRAY := NULL;
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(空)' );
        V_ARRAY := T_ARRAY3();
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み' );
        V_ARRAY := T_ARRAY3( '111', '333', '555' );
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(全DELETE)' );
        V_ARRAY := T_ARRAY3( '111', '333', '555' );
        V_ARRAY.DELETE;
        PRC_OUTPUT ( V_ARRAY );
        --
        DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
        V_ARRAY := T_ARRAY3( '111', '333', '555' );
        V_ARRAY := NULL;
        PRC_OUTPUT ( V_ARRAY );
        --
    END;

END;

実行結果

■■■■■■■■■■■■■■■■■■■■
■■■結合配列
■■■■■未初期化
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■代入
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [5]
V_ARRAY(1) = [111]
V_ARRAY(3) = [333]
V_ARRAY(5) = [555]
V_ARRAY(1) = [111]
V_ARRAY(3) = [333]
V_ARRAY(5) = [555]
■■■■■再度、未初期化
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■■■■■■■■■■■■■■■■
■■■ネストした表
■■■■■未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■初期化済み(空)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■初期化済み
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
■■■■■初期化済み(途中DELETE)
V_ARRAY.COUNT = [2]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(3) = [555]
■■■■■初期化済み(全DELETE)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■再度、未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■■■■■■■■■■■■■■■■
■■■可変長配列(VARRAY)
■■■■■未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■初期化済み(空)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■初期化済み
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
■■■■■初期化済み(全DELETE)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■再度、未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
6
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
6