【PL/SQL】コレクションを実戦活用する完全ガイド｜5ユースケース・MULTISET・TABLE()・lookup40倍速・落とし穴

-- ✅ スキーマ型：複数所で使う・DB列に格納・SQL参照する場合
CREATE OR REPLACE TYPE t_id_array AS TABLE OF NUMBER;
/

-- DB列として保存可能
CREATE TABLE projects(
  id NUMBER,
  name VARCHAR2(100),
  member_ids t_id_array
) NESTED TABLE member_ids STORE AS member_ids_tab;

-- SQLからTABLE()で参照可能
DECLARE
  v_ids t_id_array := t_id_array(101, 102, 103);
BEGIN
  FOR rec IN (
    SELECT * FROM employees
     WHERE employee_id IN (SELECT * FROM TABLE(v_ids))
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.last_name);
  END LOOP;
END;
/

-- ✅ PL/SQL局所型：1パッケージ内で完結する場合
CREATE OR REPLACE PACKAGE pkg_emp_util AS
  -- パッケージSPECで公開すれば他パッケージからも使える
  TYPE t_emp_id_tab IS TABLE OF NUMBER;
  FUNCTION find_managers RETURN t_emp_id_tab;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp_util AS
  FUNCTION find_managers RETURN t_emp_id_tab IS
    -- BODY内だけで使うなら更にローカルに宣言してもOK
    TYPE t_local_tab IS TABLE OF NUMBER;
    v_result t_emp_id_tab := t_emp_id_tab();
  BEGIN
    SELECT manager_id BULK COLLECT INTO v_result
      FROM employees WHERE manager_id IS NOT NULL;
    RETURN v_result;
  END;
END;
/

CREATE OR REPLACE PACKAGE pkg_country_cache AS
  FUNCTION get_name(p_code VARCHAR2) RETURN VARCHAR2;
  PROCEDURE refresh;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_country_cache AS
  -- VARCHAR2キーの連想配列でキャッシュ
  TYPE t_country_map IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(2);
  g_cache t_country_map;

  PROCEDURE refresh AS
  BEGIN
    g_cache.DELETE;
    FOR rec IN (SELECT code, name FROM countries) LOOP
      g_cache(rec.code) := rec.name;
    END LOOP;
  END;

  FUNCTION get_name(p_code VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN g_cache(p_code);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
  END;
BEGIN
  -- 初期化セクション：パッケージ初参照時に1回ロード
  refresh;
END;
/

-- 利用例：1万件のループでも追加SQLゼロ
DECLARE
  v_name VARCHAR2(100);
BEGIN
  FOR rec IN (SELECT customer_id, country_code FROM customers) LOOP
    v_name := pkg_country_cache.get_name(rec.country_code);
    -- ここで国名を使った処理
  END LOOP;
END;
/

-- スキーマ型を準備
CREATE OR REPLACE TYPE t_id_array AS TABLE OF NUMBER;
/

-- IN句の代替として使う
DECLARE
  v_ids t_id_array := t_id_array();
BEGIN
  -- 動的にIDを追加
  v_ids.EXTEND(3);
  v_ids(1) := 100;
  v_ids(2) := 200;
  v_ids(3) := 300;

  -- TABLE()でSQL参照
  FOR rec IN (
    SELECT * FROM customers
     WHERE customer_id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids))
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.customer_name);
  END LOOP;
END;
/

-- アプリ（Java/Python）から配列としてバインドし
-- ストアドプロシージャに渡す形式も可能（OracleArray）

-- スキーマ型のオブジェクト型と配列
CREATE OR REPLACE TYPE t_emp_record AS OBJECT(
  emp_id   NUMBER,
  emp_name VARCHAR2(100),
  salary   NUMBER
);
/
CREATE OR REPLACE TYPE t_emp_array AS TABLE OF t_emp_record;
/

-- 関数で配列を返す
CREATE OR REPLACE FUNCTION find_high_earners(p_min NUMBER)
  RETURN t_emp_array AS
  v_result t_emp_array;
BEGIN
  SELECT t_emp_record(employee_id, last_name, salary)
    BULK COLLECT INTO v_result
    FROM employees
   WHERE salary >= p_min;
  RETURN v_result;
END;
/

-- 利用例：SQLから直接呼び出してテーブルライクに使う
SELECT *
  FROM TABLE(find_high_earners(50000))
 ORDER BY salary DESC;

DECLARE
  -- スキーマ型 t_id_array を再利用
  v_a   t_id_array := t_id_array(1, 2, 3, 4, 5);
  v_b   t_id_array := t_id_array(3, 4, 5, 6, 7);

  v_only_a t_id_array;   -- aにのみある
  v_common t_id_array;   -- 両方にある
  v_union  t_id_array;   -- 和集合
BEGIN
  v_only_a := v_a MULTISET EXCEPT v_b;       -- (1, 2)
  v_common := v_a MULTISET INTERSECT v_b;    -- (3, 4, 5)
  v_union  := v_a MULTISET UNION DISTINCT v_b;  -- (1, 2, 3, 4, 5, 6, 7)

  DBMS_OUTPUT.PUT_LINE('only_a count: ' || v_only_a.COUNT);
  DBMS_OUTPUT.PUT_LINE('common count: ' || v_common.COUNT);
  DBMS_OUTPUT.PUT_LINE('union count:  ' || v_union.COUNT);

  -- TABLE()関数でSQLから扱える
  -- SELECT COLUMN_VALUE FROM TABLE(v_only_a);
END;
/

-- 実用例: 2つのスナップショットの差分を検出
-- 「昨日の在庫」と「今日の在庫」を集合演算で比較してdiffを抽出

-- 注文ヘッダ＋明細をまとめて返すAPI
CREATE OR REPLACE TYPE t_item_record AS OBJECT(
  product_cd VARCHAR2(20),
  qty        NUMBER,
  price      NUMBER
);
/
CREATE OR REPLACE TYPE t_item_array AS TABLE OF t_item_record;
/

CREATE OR REPLACE TYPE t_order_record AS OBJECT(
  order_id    NUMBER,
  customer_id NUMBER,
  items       t_item_array
);
/

CREATE OR REPLACE FUNCTION get_order_full(p_order_id NUMBER)
  RETURN t_order_record AS
  v_order   t_order_record := t_order_record(NULL, NULL, t_item_array());
BEGIN
  SELECT order_id, customer_id INTO v_order.order_id, v_order.customer_id
    FROM orders WHERE order_id = p_order_id;

  SELECT t_item_record(product_cd, qty, price)
    BULK COLLECT INTO v_order.items
    FROM order_items WHERE order_id = p_order_id;

  RETURN v_order;
END;
/

-- 利用例（12c以降のJSON対応）
DECLARE
  v_order t_order_record := get_order_full(100);
  v_json  CLOB;
BEGIN
  SELECT JSON_OBJECT(
    'order_id'    VALUE v_order.order_id,
    'customer_id' VALUE v_order.customer_id,
    'items'       VALUE (
      SELECT JSON_ARRAYAGG(JSON_OBJECT(
        'product_cd' VALUE product_cd,
        'qty'        VALUE qty,
        'price'      VALUE price
      ))
      FROM TABLE(v_order.items)
    )
  ) INTO v_json FROM dual;
  DBMS_OUTPUT.PUT_LINE(v_json);
END;
/

DECLARE
  TYPE t_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  v_arr t_tab;
  v_idx PLS_INTEGER;
BEGIN
  v_arr(1) := 'A';
  v_arr(2) := 'B';
  v_arr(3) := 'C';
  v_arr(4) := 'D';

  v_arr.DELETE(2);   -- 2番目を削除（スパース化）

  -- ❌ 密配列前提のループ → 例外発生
  -- FOR i IN 1..v_arr.COUNT LOOP
  --   DBMS_OUTPUT.PUT_LINE(v_arr(i));
  -- END LOOP;
  -- ↑ COUNT=3だがv_arr(3)はOK・v_arr(2)はNO_DATA_FOUND発生

  -- ✅ FIRST/NEXT走査でスパース対応
  v_idx := v_arr.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_arr(v_idx));
    v_idx := v_arr.NEXT(v_idx);
  END LOOP;

  -- ✅ EXISTSで存在確認してから参照
  FOR i IN 1..v_arr.LAST LOOP
    IF v_arr.EXISTS(i) THEN
      DBMS_OUTPUT.PUT_LINE(i || ': ' || v_arr(i));
    END IF;
  END LOOP;
END;
/