PL/SQLには配列やリストを表現できる「コレクション」という仕組みがあり、大量データを効率的に扱うために活用されます。代表的なコレクションには「連想配列」「ネスト表」「VARRAY」の3種類があり、それぞれ特徴と用途が異なります。ここでは基本構文と操作方法、実務での活用例を解説します。
コレクションの種類と特徴
- 連想配列(INDEX BY TABLE):インメモリ専用、主にキー付きの配列として利用。データベース列には直接保存不可。
- ネスト表(NESTED TABLE):テーブル列に格納可能、サイズ無制限。SQL操作とも親和性が高い。
- 可変配列(VARRAY):最大サイズを固定して宣言、順序保証。保存と転送に向いている。
連想配列の基本
キーに数値や文字列を指定でき、動的に拡張可能です。典型的には一時的なデータ保持やループ処理に使います。
DECLARE
TYPE t_numbers IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_arr t_numbers;
BEGIN
v_arr(1) := 100;
v_arr(2) := 200;
DBMS_OUTPUT.PUT_LINE('要素1=' || v_arr(1));
DBMS_OUTPUT.PUT_LINE('要素2=' || v_arr(2));
END;
/
ネスト表の基本
スキーマレベルで型を定義し、テーブル列にも使用できます。バルク操作やSQLとの連携がしやすいのが強みです。
-- 型の定義
CREATE OR REPLACE TYPE t_str_tab AS TABLE OF VARCHAR2(100);
/
-- PL/SQL内で利用
DECLARE
v_names t_str_tab := t_str_tab('Alice','Bob','Charlie');
BEGIN
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
/
テーブルにネスト表型を列として持たせることもできます。
CREATE TABLE projects (
id NUMBER,
members t_str_tab
) NESTED TABLE members STORE AS members_tab;
VARRAYの基本
あらかじめ要素数を制限した配列です。順序付きで格納でき、外部システムとのやり取りに便利です。
CREATE OR REPLACE TYPE t_num_varray AS VARRAY(5) OF NUMBER;
/
DECLARE
v_scores t_num_varray := t_num_varray(90,80,70);
BEGIN
FOR i IN 1..v_scores.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('スコア=' || v_scores(i));
END LOOP;
END;
/
BULK COLLECTとの併用
SELECTの結果をまとめてコレクションに取り込み、FORALLで一括処理することで性能を改善できます。
DECLARE
TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
v_emps t_emp_tab;
BEGIN
SELECT * BULK COLLECT INTO v_emps FROM employees WHERE deptno = 10;
FORALL i IN 1..v_emps.COUNT
INSERT INTO emp_backup VALUES v_emps(i);
END;
/
活用例1:一時リストの保持
複数のIDをコレクションに保持し、IN句の代わりに利用できます。
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
v_ids t_ids := t_ids(101,102,103);
BEGIN
FORALL i IN 1..v_ids.COUNT
DELETE FROM orders WHERE customer_id = v_ids(i);
END;
/
活用例2:関数の戻り値として返す
コレクションは関数の戻り値としても利用できます。アプリケーション側で配列として受け取れるため便利です。
CREATE OR REPLACE FUNCTION get_active_ids RETURN t_str_tab IS
v_res t_str_tab;
BEGIN
SELECT username BULK COLLECT INTO v_res
FROM users WHERE active = 1;
RETURN v_res;
END;
/
注意点とベストプラクティス
- 連想配列はインメモリ専用、永続化したい場合はネスト表やVARRAYを選択する。
- BULK処理で大量データを扱う際はLIMITを併用してメモリを圧迫しないようにする。
- VARRAYは最大サイズを慎重に設計し、過剰に大きな値は避ける。
- スキーマレベル型を利用する場合、依存関係管理に注意する。
まとめ
コレクションはPL/SQLで配列やリストを扱う標準的な仕組みで、データの一時保持からバルク処理、関数の戻り値まで幅広く活用できます。連想配列・ネスト表・VARRAYの特徴を理解し、適材適所で選択すれば、性能と可読性を兼ね備えた堅牢な処理を実現できます。