はじめに
Oracleでは、クエリ内でコレクションのテーブル表現TABLE()を使用してコレクションタイプの変数をテーブルの様に扱うことができます。PostgreSQLでのUNNESTにラテラル句を追加した様な非常に便利な機能ですが、11gまではTABLE()で扱えるコレクションはオブジェクトタイプのみでありレコードタイプでは使用不可でした。しかし12cから制限がすこしだけ緩和されパッケージのグローバル領域で宣言されたレコードコレクションでもTABLE()が使えるようになりました。
レコードタイプとオブジェクトタイプの違い
で、レコートタイプとオブジェクトタイプってなにがちがうのか？
レコードタイプもオブジェクトタイプもマルチカラムの行を保存できる型という意味では似た様な機能です。しかし、実のところかなり違います。レコードタイプは値を保存するだけしかできませんが、オブジェクトタイプはメンバ関数の定義やタイプの継承などもでき明らかに高機能です。ざっくりいうと、レコードタイプはC言語での構造体、オブジェクトタイプはC++でのクラスだといえます。また、当然のことながらMULTISET EXCEPTの様なマップメンバ関数を必須とする演算子はオブジェクトタイプしか使用できません。
さらにもう一つの大きな違いですが、レコードタイプはPL/SQLの機能であるのに対してオブジェクトタイプはスキーマオブジェクトです。つまりレコードタイプはPL/SQLコンテキスト内でのみ使用可ですが、オブジェクトタイプはPL/SQLコンテキストおよびSQLコンテキスト両方で使用できます。
これらは単純化すると以下の様なコーディング上の違いとして現れます。
DECLARE
TYPE test_rec IS RECORD (n NUMBER, s VARCHAR2(10)); -- PL/SQLブロック内での宣言
r test_rec;
BEGIN
r.n := 1; -- PL/SQLでの代入（初期化不要）
r := test_rec(1, 'test'); -- コンストラクタでの初期化・代入『18c以降でのみ可能』
SELECT 1, 'test' INTO r FROM DUAL; -- SQLクエリでの代入（タイプ名使用不可）
END;
/
上記レコードタイプのコンストラクタの使用は18cでの新機能です。
PL/SQL修飾式
CREATE type test_obj IS OBJECT (n NUMBER, s VARCHAR2(10)); -- スキーマオブジェクトとして作成
/
DECLARE
r test_obj;
BEGIN
r := test_obj(1, 'test'); -- PL/SQLでの初期化・代入
r.n := 2; -- 初期化後個別代入可
SELECT test_obj(1, 'test') INTO r FROM DUAL; -- SQLクエリでの代入（タイプ名使用可）
END;
/
では低機能なレコードタイプを使うメリットは何かと言う話になりますが、PL/SQL内で宣言するためPL/SQLアトリビートが使えることですね。具体的にいうと%TYPEと%ROWTYPE。テーブルのカラムとその型をいちいち列挙しなくても＜テーブル名＞%ROWTYPEで済んでしまうのは魅力です。テーブルやカラムの変更に対してコードの変更が最小限で済みます。オブジェクトタイプはSQLコンテキストで作成するためそれらが使えません。
少し脱線しますが、PL/SQLを取り扱うときこのコンテキストの違いを意識しておくのは割と重要です。PL/SQLプログラムはPL/SQLコンテキストで実行されますが、PL/SQLブロック内でSQLクエリを発行するとSQLコンテキストに切り替わります。そしてSQLコンテキストからPL/SQLコンテキストは参照できないためSQLクエリからPL/SQL内で定義したものは基本見えません。
たとえば、以下は簡単な例ですがfunc関数をPL/SQLブロック内で定義されたものでありSQLコンテキストからは見えないためクエリで使用するとエラーになります。クエリで使用するにはスキーマオブジェクトにするか、パッケージのグローバル関数にする必要があります。
DECLARE
n NUMBER;
FUNCTION func(n NUMBER) RETURN NUMBER IS BEGIN RETURN n * n; END;
BEGIN
n := func(2); -- 問題なし
SELECT func(2) INTO n FROM DUAL; -- エラー！！
END;
/
ついでに言うとこのコンテキスト・スイッチ動作は、多少のオーバーヘッドを伴います。したがって高速なクエリでもPL/SQLでループさせると、ループ内でPL/SQLとSQLのコンテキスト・スイッチが頻繁に起こりパフォーマンスの低下を引き起こすことが多々あります。ひとつの対策としてPL/SQLにはFORALLコマンドが用意されておりコンテキスト・スイッチを一度だけで済ますループ（あくまでもループです）が使えますが、お世辞にも使い勝手がよいとはいえないものです。
コレクション
コレクションは、簡単に言うと配列のようなものです。単一変数や先程のレコートタイプ、オブジェクトタイプなどをベースに作ります。レコードコレクションはPL/SQLブロック内で宣言します。レコードタイプはPL/SQLコンテキスト専用ですからね。オブジェクトコレクションや単一変数のコレクションは、PL/SQLブロック内で宣言するかスキーマオブジェクトを作成します。PL/SQLブロック内で宣言するとSQLクエリ上で型名が使用できなくなりますが、連想配列(結合配列)(Associative Index)が使用可能になります。配列の添字を自由に選べるあれですね。
さらにいうとコレクションにはVARRAYとTABLEの二種類があるのですが、まぁVARRAYは宣言時に上限指定しないといけないので、一般的にはTABLEで問題ないと思います（密配列がどうしても必要な場合を除いて）。
DECLARE
TYPE test_rec IS RECORD (n NUMBER, s VARCHAR2(10)); -- レコードタイプの宣言
TYPE test_rec_tbl IS TABLE OF test_rec; -- コレクションの宣言
rt test_rec_tbl;
BEGIN
-- PL/SQL式で初期化・拡張・代入
rt := test_rec_tbl();
rt.EXTEND;
rt(1).n := 1;
-- コンストラクションでコレクションに代入『18c以降可』
rt := test_rec_tbl(test_rec(1, 'test'), test_rec(2, 'test2'));
-- テーブルからBULK COLLECTでコレクションに代入
SELECT n, s BULK COLLECT INTO rt
FROM (SELECT 1 n, 'test' s FROM DUAL
UNION
SELECT 2, 'test2' FROM DUAL);
END;
/
CREATE TYPE test_obj IS OBJECT (n NUMBER, s VARCHAR2(10)); -- オブジェクトタイプ作成
/
CREATE TYPE test_obj_tbl IS TABLE OF test_obj; -- コレクションタイプ作成
/
DECLARE
rt test_obj_tbl;
BEGIN
-- PL/SQL式でコレクションに代入
rt := test_obj_tbl(test_obj(1, 'test'), test_obj(2, 'test2'));
-- コレクションを作ってSQLで代入
SELECT test_obj_tbl(test_obj(1, 'test'), test_obj(2, 'test2')) INTO rt FROM DUAL;
-- テーブルからBULK COLLECTでコレクションに代入
SELECT test_obj(n, s) BULK COLLECT INTO rt
FROM (SELECT 1 n, 'test' s FROM DUAL
UNION
SELECT 2, 'test2' FROM DUAL);
-- テーブルからコレクションを作って代入
SELECT CAST(COLLECT(test_obj(n, s)) AS test_obj_tbl) INTO rt
FROM (SELECT 1 n, 'test' s FROM DUAL
UNION
SELECT 2, 'test2' FROM DUAL);
END;
/
ここではコレクションのベースになるタイプが二種類（SQLの型を直接使用する場合を含めれば３種類）あり、またコレクションにも何種類かあるため理解しずらいものがあります。マニュアルよんでもいまいち分かりづらいんですが、以下の表の様な感じです。
| コレクションの種類 | PL/SQL宣言 | スキーマオブジェクト |
|---|---|---|
| VARRAY型 | レコードタイプ オブジェクトタイプ |
オブジェクトタイプ |
| TABLE型 (ネストテーブル) |
レコードタイプ オブジェクトタイプ |
オブジェクトタイプ |
| TABLE型 + INDEX （連想配列） |
レコードタイプ オブジェクトタイプ |
不可 |
コレクションのテーブル表現
コレクションのテーブル表現『TABLE()』はコレクション型のデータをテーブルとして扱うことができる超便利機能です。コレクションの変数、コレクションを返すファンクション(a.k.a. テーブル関数)、コレクションを返すカーサ(SQL)などを引数にとることができます。
-- コレクションを直接渡す例
> SELECT * FROM TABLE(test_obj_tbl(test_obj(1, 'test')));
-- コレクションを返すファンクションを渡す例
> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
-- コレクションを返すカーサを渡す例
> SELECT * FROM TABLE(SELECT COLLECT(n) FROM test_tbl);
テーブル表現を使用すると、コレクションに保存しているデータに対して直接クエリを実行することができます。つまりループする必要がなくなるのですね。例えばDMLで使用するとFORループどころかFORALLループすら必要がなくなり、パフォーマンスが向上します。
MERGE INTO test_tbl
USING (SELECT n, s FROM TABLE(rt))
ON (id = n)
WHEN MATCHED THEN UPDATE SET name = s;
もう一つのテーブル表現の特徴は、ラテラル入力を受け付けることです。いちばん有名なのがDBMS_XPLANを使用した例ですね。他にも、まぁ需要があるかはどうかは別としてテーブル表現のラテラルとカーサの引数渡しを用いると11gでも12cから実装されたLATERAL句と似たようなことができたりもします。
-- テーブル関数へのラテラルの例
SELECT *
FROM v$session s,
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.sql_child_number, 'LAST'))
WHERE s.sid = &SID;
-- カーサへのラテラルの例
SELECT table_name, column_value
FROM user_tables t,
TABLE(SELECT COLLECT(bytes) FROM user_segments
WHERE segment_name = t.table_name);
また以前投稿した「[Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法]
(https://qiita.com/tlokweng/items/183ca87c32ee0e3caa09])」でもテーブル表現のラテラル機能を目一杯利用しています。
レコードコレクションでのテーブル表現の使用
やっと本題です(^^)。
11gまではコレクションのテーブル表現はスキーマオブジェクトのコレクションに対してのみ利用可能でした。つまりレコードタイプでは使用できなかったわけです。しかし12cからPL/SQLブロック内で宣言されたコレクションでも一部使用可能となりました。ただし、パッケージのグローバル宣言領域で宣言されたコレクションという制限がつくので、ファンクションやプロシジャのローカル宣言領域で宣言されたコレクションは相変わらず対象外です。
簡単な例で試してみます。レコードタイプの特徴である%ROWTYPEを使用するためにまず適当なテーブルを作っておきます。
CREATE TABLE test_tbl (n NUMBER, s VARCHAR2(10));
INSERT INTO test_tbl VALUES (1, 'one');
INSERT INTO test_tbl VALUES (2, 'two');
INSERT INTO test_tbl VALUES (3, 'three');
COMMIT;
次にパッケージを作成します。グローバル宣言部で%ROWTYPEを使用してテストテーブルのカラムに沿ったレコードタイプのコレクション型を宣言しています。ファンクションtest_funcでは、テーブルからデータをコレクションに読み込み、テーブル表現を使用してカラムnの合計を計算しています。
CREATE OR REPLACE PACKAGE test_pkg
IS
TYPE test_rec_col IS TABLE OF test_tbl%ROWTYPE;
FUNCTION test_func RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg
IS
FUNCTION test_func RETURN NUMBER
IS
rt test_rec_col;
n number := 0;
BEGIN
SELECT n, s BULK COLLECT INTO rt FROM test_tbl;
SELECT SUM(n) INTO n FROM TABLE(rt); -- TABLE()使用
RETURN n;
END;
END;
/
SELECT test_pkg.test_func FROM DUAL;
TEST_FUNC
----------
6
問題なく実行できました。
ちなみに11gで上記のパッケージを作成するとコンパイルエラーになります。
PL/SQL: SQL Statement ignored
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
PLS-00642: local collection types not allowed in SQL statements
繰り返しになりますが、レコードコレクションを選択するアドバンテージは以下の様なものです（ほかにもあるかもしれませんが）。これらにメリットを感じないのならオブジェクトタイプの方が使い勝手がよいでしょう。
- %TYPE、%ROWTYPEアトリビュートが使える
- PL/SQL内宣言のコレクションなので連想配列がそのまま使える
- パッケージ外のスキーマオブジェクトの作成不要である
レコードコレクションへの置換
最後に実際の例としてオブジェクトコレクションを使ったPL/SQLコードをレコードコレクションを使ったパッケージに置換してみます。まずはオブジェクトタイプのコレクションをつかった迷路を作成するPL/SQLコード。「単一SQLクエリで穴掘り法や壁伸ばし法を使って迷路をちゃんと作ってみる」からの引用です。
CREATE OR REPLACE TYPE maze_cell_typ AS OBJECT (
x NUMBER,
y NUMBER,
c VARCHAR2(1),
id NUMBER);
/
CREATE OR REPLACE TYPE maze_typ AS TABLE OF maze_cell_typ;
/
SET SERVEROUT ON
DECLARE
p_size_x number := 41;
p_size_y number := 21;
cells maze_typ := maze_typ();
roadcells maze_typ := maze_typ();
scell maze_cell_typ;
ncell maze_cell_typ;
bcell maze_cell_typ;
BEGIN
IF p_size_x < 5 OR mod(p_size_x, 2) = 0 OR
p_size_y < 5 OR mod(p_size_y, 2) = 0
THEN
RAISE_APPLICATION_ERROR(-20000, 'The length and width must be odd numbers of 5 or larger.');
RETURN;
END IF;
SELECT maze_cell_typ(MOD(LEVEL, p_size_x) + 1, CEIL(LEVEL / p_size_x), '#', LEVEL)
BULK COLLECT INTO cells
FROM DUAL CONNECT BY LEVEL <= p_size_x * p_size_y;
SELECT v INTO scell
FROM (SELECT VALUE(t) v,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) rn
FROM TABLE(cells) t
WHERE MOD(x, 2) = 0 and MOD(y, 2) = 0)
WHERE rn = 1;
roadcells.EXTEND(cells.count);
roadcells(scell.id) := scell;
cells(scell.id).c := ' ';
LOOP
BEGIN
SELECT v INTO scell
FROM (SELECT VALUE(t) v,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) rn
FROM TABLE(roadcells) t
WHERE id IS NOT NULL)
WHERE rn = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; -- 迷路完成
END;
LOOP
BEGIN
SELECT v, value(p) INTO ncell, bcell
FROM (SELECT value(t) v,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE(1, 1000)) rn
FROM TABLE(cells) t
WHERE t.c = '#' AND
(t.x, t.y) IN ((scell.x + 2, scell.y), (scell.x - 2, scell.y), -- 二つ隣のセル
(scell.x, scell.y + 2), (scell.x, scell.y - 2))
) n,
TABLE(cells) p
WHERE rn = 1 AND
p.x = scell.x + (n.v.x - scell.x)/2 AND -- 移動する方向の一つ隣のセル
p.y = scell.y + (n.v.y - scell.y)/2;
EXCEPTION WHEN NO_DATA_FOUND THEN
roadcells.delete(scell.id); -- 分岐可能セルリストから削除
EXIT;
END;
cells(ncell.id).c := ' ';
cells(bcell.id).c := ' ';
roadcells(ncell.id) := ncell;
scell := ncell;
END LOOP;
END LOOP;
FOR i IN (SELECT y, LISTAGG (c) WITHIN GROUP (ORDER BY x) l
FROM TABLE(cells)
GROUP BY y
ORDER BY y)
LOOP
DBMS_OUTPUT.PUT_LINE(i.l);
END LOOP;
END;
/
以上をパッケージに置き換えてレコードコレクションで書き換えます。ついでに連想配列も使用してみます。
CREATE OR REPLACE PACKAGE maze_pkg
AS
TYPE maze_cell_typ2 IS RECORD (x NUMBER, y NUMBER, c VARCHAR2(1), id NUMBER); -- レコード型
TYPE maze_typ2 IS TABLE OF maze_cell_typ2 INDEX BY PLS_INTEGER; -- 連想配列
PROCEDURE create_maze(
p_size_x NUMBER,
p_size_y NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY maze_pkg
AS
PROCEDURE create_maze(
p_size_x NUMBER,
p_size_y NUMBER)
IS
cells maze_typ2;
roadcells maze_typ2;
scell maze_cell_typ2;
ncell maze_cell_typ2;
bcell maze_cell_typ2;
BEGIN
IF p_size_x < 5 OR mod(p_size_x, 2) = 0 OR
p_size_y < 5 OR mod(p_size_y, 2) = 0
THEN
RAISE_APPLICATION_ERROR(-20000, 'The length and width must be odd numbers of 5 or larger.');
RETURN;
END IF;
SELECT MOD(LEVEL, p_size_x) + 1, CEIL(LEVEL / p_size_x), '#', LEVEL
BULK COLLECT INTO cells
FROM DUAL CONNECT BY LEVEL <= p_size_x * p_size_y;
SELECT x, y, c, id INTO scell
FROM (SELECT t.*,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE(1, 10000)) rn
FROM TABLE(cells) t
WHERE MOD(x, 2) = 0 and MOD(y, 2) = 0)
WHERE rn = 1;
roadcells(scell.id) := scell;
cells(scell.id).c := ' ';
LOOP
BEGIN
SELECT x, y, c, id INTO scell
FROM (SELECT t.*,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE(1, 10000)) rn
FROM TABLE(roadcells) t)
WHERE rn = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN EXIT;
END;
LOOP
BEGIN
SELECT x, y, c, id
INTO ncell
FROM (SELECT t.*,
ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE(1, 1000)) rn
FROM TABLE(cells) t
WHERE t.c = '#' AND
(t.x, t.y) IN ((scell.x + 2, scell.y), (scell.x - 2, scell.y),
(scell.x, scell.y + 2), (scell.x, scell.y - 2))
) n
WHERE rn = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
roadcells.DELETE(scell.id);
EXIT;
END;
SELECT x, y, c, id INTO bcell
FROM TABLE(cells) p
WHERE p.x = scell.x + (ncell.x - scell.x)/2 and
p.y = scell.y + (ncell.y - scell.y)/2;
cells(ncell.id).c := ' ';
cells(bcell.id).c := ' ';
roadcells(ncell.id) := ncell;
scell := ncell;
END LOOP;
END LOOP;
FOR i IN (SELECT y, LISTAGG (c) WITHIN GROUP (ORDER BY x) l
FROM TABLE(cells)
GROUP BY y
ORDER BY y)
LOOP
DBMS_OUTPUT.PUT_LINE(i.l);
END LOOP;
END;
END;
/
実行結果
おわりに
OracleでもPostgreSQLの様な宣言不要の配列型が使えるようになるといいんですけどね。そのうち実装されるかなぁ？