Go to Qiita Advent Calendar 2024 Top
LoginSignup
14
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

sql oracleAdvent Calendar 2018Day 7
@tlokweng

12cからレコード型のコレクションでもテーブル表現が使えるぞ (Oracle)

Last updated at Posted at 2019-01-16

はじめに

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のコレクションおよびレコード

コレクションの種類 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ループすら必要がなくなり、パフォーマンスが向上します。

DMLでテーブル表現の使用例
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の合計を計算しています。

テストパッケージ(12c以降)
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;
/

以上をパッケージに置き換えてレコードコレクションで書き換えます。ついでに連想配列も使用してみます。

レコードコレクション使用バージョン(12c以降)
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;
/

実行結果
image.png

おわりに

OracleでもPostgreSQLの様な宣言不要の配列型が使えるようになるといいんですけどね。そのうち実装されるかなぁ？

14
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
14
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?