Amazon Aurora PostgreSQL および Amazon RDS for PostgreSQL で Oracle PL/SQL 連想配列をエミュレートする

Oracle でソース PL/SQL ブロックを作成する。

次の連想配列を使用するソース PL/SQL ブロックを Oracle で作成します。

DECLARE
    TYPE country_codes IS TABLE OF VARCHAR2(100) INDEX BY pls_integer;
    cc country_codes;
    cc_idx NUMBER := NULL;
BEGIN
    cc(7) := 'India';
    cc(3) := 'UK';
    cc(5) := 'USA';
    cc(0) := 'China';
    cc(-2) := 'Invalid';
    dbms_output.put_line('cc_length:' || cc.COUNT);
    IF (cc.COUNT > 0) THEN
        cc_idx := cc.FIRST;
        FOR i IN 1..cc.COUNT LOOP
            dbms_output.put_line('cc_idx:' || cc_idx || ' country:' || cc(cc_idx));
            cc_idx := cc.next(cc_idx);
        END LOOP;
    END IF;
END;

PL/SQL ブロックを実行する。

Oracle でソース PL/SQL ブロックを実行します。連想配列のインデックス値の間にギャップがある場合、そのギャップにはデータが保存されません。そのため、Oracle ループはインデックス位置を介してのみイテレーションできます。

出力の確認

非連続の間隔で 5 つの要素が配列 (cc) に挿入されました。配列数を次の出力に示します。

cc_length:5
cc_idx:-2 country:Invalid
cc_idx:0 country:China
cc_idx:3 country:UK
cc_idx:5 country:USA
cc_idx:7 country:India

PostgreSQL でターゲット PL/pgSQL ブロックを作成する。

次の連想配列を使用するターゲット PL/pgSQL ブロックを PostgreSQL で作成します。

DO $$
DECLARE
    cc character varying(100)[];
    cc_idx integer := NULL;
BEGIN
    cc[7] := 'India';
    cc[3] := 'UK';
    cc[5] := 'USA';
    cc[0] := 'China';
    cc[-2] := 'Invalid';
    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);
    IF (ARRAY_LENGTH(cc, 1) > 0) THEN
        FOR i IN ARRAY_LOWER(cc, 1)..ARRAY_UPPER(cc, 1)
        LOOP
            RAISE NOTICE 'cc_idx:% country:%', i, cc[i];
        END LOOP;
    END IF;
END;
$$;

PL/pgSQL ブロックを実行する。

ターゲット PL/pgSQL ブロックを PostgreSQL で実行します。連想配列のインデックス値の間にギャップがある場合、そのギャップにはデータが保存されません。そのため、Oracle ループはインデックス位置を介してのみイテレーションできます。

出力の確認

インデックス位置間のギャップには NULL が保存されるため、配列の長さは 5 を超えています。次の出力に示すように、ループは 10 回のイテレーションを完了して、配列内の 5 つの値を取得します。

cc_length:10
cc_idx:-2 country:Invalid
cc_idx:-1 country:<NULL>
cc_idx:0 country:China
cc_idx:1 country:<NULL>
cc_idx:2 country:<NULL>
cc_idx:3 country:UK
cc_idx:4 country:<NULL>
cc_idx:5 country:USA
cc_idx:6 country:<NULL>
cc_idx:7 country:India

配列とユーザー定義型を使用してターゲット PL/pgSQL ブロックを作成する。

パフォーマンスを最適化し、Oracle の機能に合わせるには、インデックス位置と対応するデータの両方を保存するユーザー定義型を作成します。このアプローチでは、インデックスと値との直接的な関連付けを維持することにより、不要なイテレーションを減らします。

DO $$
DECLARE
    cc country_codes[];
    cc_append country_codes := NULL;
    i record;
BEGIN
    cc_append.idx = 7;
    cc_append.val = 'India';
    cc := array_append(cc, cc_append);
    cc_append.idx = 3;
    cc_append.val = 'UK';
    cc := array_append(cc, cc_append);
    cc_append.idx = 5;
    cc_append.val = 'USA';
    cc := array_append(cc, cc_append);
    cc_append.idx = 0;
    cc_append.val = 'China';
    cc := array_append(cc, cc_append);
    cc_append.idx = - 2;
    cc_append.val = 'Invalid';
    cc := array_append(cc, cc_append);
    RAISE NOTICE 'cc_length: %', ARRAY_LENGTH(cc, 1);
    IF (ARRAY_LENGTH(cc, 1) > 0) THEN
        FOR i IN (
            SELECT
                *
            FROM
                unnest(cc)
            ORDER BY
                idx)
                LOOP
                    RAISE NOTICE 'cc_idx:% country:%', i.idx, i.val;
                END LOOP;
    END IF;
END;
$$;

PL/pgSQL ブロックを実行する。

ターゲット PL/pgSQL ブロックを実行します。連想配列のインデックス値の間にギャップがある場合、そのギャップにはデータが保存されません。そのため、Oracle ループはインデックス位置を介してのみイテレーションできます。

出力の確認

次の出力に示すように、ユーザー定義型には、入力されたデータ要素のみが保存されます。そのため、配列の長さは値の数と一致します。その結果、LOOP イテレーションは存在するデータのみを処理するように最適化されるため、空の位置を追跡する必要はありません。

cc_length:5
cc_idx:-2 country:Invalid
cc_idx:0 country:China
cc_idx:3 country:UK
cc_idx:5 country:USA
cc_idx:7 country:India