【Oracle】PL/SQL コレクション型完全ガイド｜連想配列・VARRAY・ネスト表の違いと使い方・メソッド・例外処理まで解説

DECLARE
    -- PLS_INTEGER キーの連想配列
    TYPE t_salary_tbl IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salary t_salary_tbl;
BEGIN
    -- キーで直接代入（EXTEND 不要）
    v_salary(101) := 50000;
    v_salary(102) := 60000;
    v_salary(103) := 45000;

    -- キーで参照
    DBMS_OUTPUT.PUT_LINE('101: ' || v_salary(101));

    -- 全要素をループ（FIRST / NEXT でスパースなキーも安全に走査）
    DECLARE
        v_key PLS_INTEGER := v_salary.FIRST;
    BEGIN
        WHILE v_key IS NOT NULL LOOP
            DBMS_OUTPUT.PUT_LINE(v_key || ' => ' || v_salary(v_key));
            v_key := v_salary.NEXT(v_key);
        END LOOP;
    END;
END;
/

DECLARE
    -- 文字列キーの連想配列（ハッシュマップ的な使い方）
    TYPE t_dept_map IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);
    v_dept_name t_dept_map;
BEGIN
    v_dept_name('10') := 'Administration';
    v_dept_name('20') := 'Marketing';
    v_dept_name('50') := 'Shipping';

    -- キー存在確認
    IF v_dept_name.EXISTS('20') THEN
        DBMS_OUTPUT.PUT_LINE('部門20: ' || v_dept_name('20'));
    END IF;

    -- 要素数
    DBMS_OUTPUT.PUT_LINE('件数: ' || v_dept_name.COUNT);
END;
/

DECLARE
    -- 最大5要素の VARRAY
    TYPE t_tag_array IS VARRAY(5) OF VARCHAR2(50);
    v_tags t_tag_array;
BEGIN
    -- コンストラクタで初期化（初期値を設定できる）
    v_tags := t_tag_array('Oracle', 'SQL', 'PL/SQL');

    -- 要素を追加（EXTEND で拡張してから代入）
    v_tags.EXTEND;               -- 1要素拡張
    v_tags(4) := 'Performance';
    v_tags.EXTEND;
    v_tags(5) := 'Tuning';

    -- 最大数を超えると SUBSCRIPT_BEYOND_COUNT エラー
    -- v_tags.EXTEND; -- ← 6要素目は ORA-06532

    -- 全要素をループ
    FOR i IN 1 .. v_tags.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(i || ': ' || v_tags(i));
    END LOOP;
END;
/

DECLARE
    -- ネスト表の型宣言（上限なし）
    TYPE t_emp_id_tbl   IS TABLE OF employees.employee_id%TYPE;
    TYPE t_last_name_tbl IS TABLE OF employees.last_name%TYPE;

    v_ids   t_emp_id_tbl;
    v_names t_last_name_tbl;
BEGIN
    -- BULK COLLECT で一括取得（コンテキストスイッチを最小化）
    SELECT employee_id, last_name
    BULK COLLECT INTO v_ids, v_names
    FROM employees
    WHERE department_id = 50
    ORDER BY employee_id;

    DBMS_OUTPUT.PUT_LINE('取得件数: ' || v_ids.COUNT);

    -- インデックスでアクセス
    FOR i IN 1 .. v_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': ' || v_names(i));
    END LOOP;
END;
/

DECLARE
    TYPE t_str_tbl IS TABLE OF VARCHAR2(100);
    v_list t_str_tbl;
BEGIN
    -- コンストラクタで空の初期化（初期化しないと COLLECTION_IS_NULL エラー）
    v_list := t_str_tbl();

    -- 要素を1件ずつ追加
    v_list.EXTEND;
    v_list(v_list.LAST) := 'item A';
    v_list.EXTEND;
    v_list(v_list.LAST) := 'item B';
    v_list.EXTEND;
    v_list(v_list.LAST) := 'item C';

    DBMS_OUTPUT.PUT_LINE('件数: ' || v_list.COUNT);

    -- 特定要素を削除（DELETE はスパースなコレクションにする）
    v_list.DELETE(2);   -- インデックス2を削除
    DBMS_OUTPUT.PUT_LINE('DELETE後の件数: ' || v_list.COUNT);

    -- TRIM は末尾から削除（稠密なまま）
    -- v_list.TRIM;    -- 末尾1件削除
    -- v_list.TRIM(2); -- 末尾2件削除
END;
/

DECLARE
    TYPE t_num_tbl IS TABLE OF NUMBER;
    v_list t_num_tbl;   -- 未初期化
BEGIN
    -- COLLECTION_IS_NULL: 初期化なしでアクセス
    -- v_list.EXTEND;  -- ← ORA-06531: 参照がネストした表またはVARRAYが初期化されていません

    -- 正しい初期化
    v_list := t_num_tbl(10, 20, 30);

    -- SUBSCRIPT_BEYOND_COUNT: COUNT を超えてアクセス
    -- DBMS_OUTPUT.PUT_LINE(v_list(5));  -- ← ORA-06533

    -- EXISTS で事前確認
    IF v_list.EXISTS(5) THEN
        DBMS_OUTPUT.PUT_LINE(v_list(5));
    ELSE
        DBMS_OUTPUT.PUT_LINE('インデックス5は存在しません');
    END IF;

    -- DELETE後にスパースなコレクションを安全にループ
    v_list.DELETE(2);  -- インデックス2を削除 → 1, (2削除済み), 3
    DECLARE
        i PLS_INTEGER := v_list.FIRST;
    BEGIN
        WHILE i IS NOT NULL LOOP
            DBMS_OUTPUT.PUT_LINE(i || ' => ' || v_list(i));
            i := v_list.NEXT(i);
        END LOOP;
    END;
END;
/

-- まずスキーマレベルの型を作成（PL/SQLブロック内の型は TABLE() に使えない）
CREATE OR REPLACE TYPE t_number_list IS TABLE OF NUMBER;
/

-- PL/SQL でコレクションを作成して SQL に渡す
DECLARE
    v_dept_ids t_number_list := t_number_list(10, 20, 50, 80);
BEGIN
    -- TABLE() を使って IN 句の代わりに使う
    FOR rec IN (
        SELECT e.employee_id, e.last_name, e.department_id
        FROM employees e
        WHERE e.department_id IN (SELECT COLUMN_VALUE FROM TABLE(v_dept_ids))
        ORDER BY e.department_id, e.employee_id
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.department_id || ': ' || rec.last_name);
    END LOOP;
END;
/

-- スキーマレベルのネスト表型を使って SQL でクエリ
-- ※ PL/SQL ブロック内の型では使用不可

-- 作成済みの型を使ってSQLのみで利用
SELECT COLUMN_VALUE AS dept_id
FROM TABLE(t_number_list(10, 20, 50));

-- 別テーブルとの JOIN
SELECT e.last_name, e.salary
FROM employees e
JOIN TABLE(t_number_list(50, 80)) dept_list
  ON e.department_id = dept_list.COLUMN_VALUE
ORDER BY e.salary DESC;