e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第77回 「テーブル・ファンクションとカーソル変数 ~問い合わせ結果の変換~」

2013.11.18

こんにちは。インストラクターの蓑島です。

いつのまにかもう冬ですね。
天気予報をみると雪のマークが見えるようになりました。1年は短いものです。

さて、前回、前々回とテーブル・ファンクションについて説明してきました。
テーブルファンクションは表のように問い合わせ可能な行の集合を返すファンクションでしたね。
表のようにSELECT文のFROM句に記述して問い合わせが可能です。

このテーブルファンクションでは、よくある使用例として、パラメータにカーソル変数をもつケースがあります。 それは具体的には、他の問い合わせ(SELECT文)の結果をテーブル・ファンクションで何らかの形に変換するというものです。
テーブル・ファンクションで他の問い合わせの結果をカーソル変数で参照しながら、その値をもとに何かのプログラム処理を行い、その結果をテーブルファンクションから PIPE ROW処理で結果を返すわけです。

例えば、説明のために極端に簡単な例ですが、以下のような問い合わせ(SELECT * FROM TEST02)があるとします。

1
2
3
4
5
6
SELECT * FROM TEST02;
 
A          B          C
---------- ---------- ----------
1          EMP        7934
2          DEPT       10

2行あるTEST02という表への問い合わせです。
ここで、B列が'EMP'の場合のC列の値は、EMP表(社員表)の主キーのEMPNO列(社員番号)の値だとします。そして、B列が'DEPT'の場合は、C列の値はDEPT表(部門表)の主キーのDEPTNO列(部門番号)だとします。

そんな時に、上の表の問い合わせをもとに、テーブルファンクションで変換すると、D列が追加されて、そこにもとの行がEMPであれば、社員名がセットされ、DEPTであれば部門名がセットされるとします。
以下のような例です。

上の問い合わせ結果をもとに、テーブル・ファンクションで変換された問い合わせ結果例

1
2
3
4
A          B          C          D                  -- もとの問い合わせにD列が追加されている
---------- ---------- ---------- ----------
1          EMP        7934       MILLER             -- MILLERは社員番号7934の社員名
2          DEPT       10         ACCOUNTING         -- ACCOUNTINGは部門番号10の部門名

ではこのような問い合わせの変換を行うテーブル・ファンクションをF2という名前でPAC2というパッケージに作成してみます。
実際に試される方は、デモ用のユーザーでSCOTTが存在していればそれを使ってください。また、上記のTEST02表は、簡単な表なのでご自分で作成して試してみてください。

まず、PAC2パッケージの仕様部の作成です。なるべく簡単にしています。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE PACKAGE PAC2
IS
/*************************************************/
/*  テーブルファンクションの行のレコード型       */
/*************************************************/
    TYPE REC_TYPE IS RECORD
    (  A  VARCHAR2(20),
       B  VARCHAR2(20),
       C  VARCHAR2(20),
       D  VARCHAR2(20));
/*************************************************/
/*  そのレコード型のコレクション                 */
/*************************************************/
    TYPE REC_TAB_TYPE IS TABLE OF REC_TYPE;
/**************************************************/
/*  そのコレクションを返すテーブルファンクション  */
/**************************************************/
    FUNCTION F2 (P_CURSOR IN SYS_REFCURSOR)
       RETURN REC_TAB_TYPE PIPELINED;
END PAC2;
/
 
パッケージが作成されました。

まずパッケージ仕様部を作成しました。
前々回(第75回)でこの辺を説明していますが、ここで目新しいのは、18行目でファンクションF2のパラメータP_CURSOR の型がSYS_REFCURSORとある点です。
これがカーソル変数の型です。

以前、バックナンバー第46回で、カーソル変数の定義は2段階であり、以下の構文であるとご紹介しました。

① TYPE 型名 IS REF CURSOR;   
(※IS REF CURSOR は固定)
② カーソル変数名 上記①の型名;

SYS_REFCURSOR型はこの2段階を1回にまとめたものです。したがって、より簡便にカーソル変数を定義できます。

ファンクションF2のパラメータがカーソル変数であるということは、ファンクションF2は他の問い合わせからの行をそのまま入力できるということです。

では次にパッケージの本体です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE PACKAGE BODY PAC2
IS
/**************************************************/
/*  テーブルファンクション F2の定義               */
/**************************************************/
    FUNCTION F2 (P_CURSOR IN SYS_REFCURSOR)
       RETURN REC_TAB_TYPE PIPELINED
    IS
       -- テーブルファンクションの行レコード
       REC  REC_TYPE;
       -- カーソルの行のA列、B列、C列の変数
       V_A   VARCHAR2(20);
       V_B   VARCHAR2(20);
       V_C   VARCHAR2(20);
       -- 追加したD列の変数
       V_D   VARCHAR2(20);
    BEGIN
       LOOP                                          -- 以下の処理を繰り返す
          FETCH P_CURSOR INTO V_A, V_B, V_C;         -- カーソル変数から1行フェッチ
          EXIT WHEN P_CURSOR%NOTFOUND;               -- フェッチして行がなければ繰り返しを終える
          -- フェッチした各列の値をテーブル・ファンクションの行レコードにセット
          REC.A := V_A;
          REC.B := V_B;
          REC.C := V_C;
          -- D列の値は、場合によりEMP表やDEPT表から取得
          IF V_B = 'EMP'  THEN
             SELECT ENAME INTO V_D FROM EMP WHERE EMPNO = V_C;
          ELSE
             SELECT DNAME INTO V_D FROM DEPT WHERE DEPTNO = V_C;
          END IF;
          REC.D := V_D;
          PIPE ROW(REC);                             -- 行レコードをパイプを通して返す
       END LOOP;
       RETURN ;                                 -- 上記の繰り返し処理が終わったらファンクションを終える
    END F2;
END PAC2;
/
 
パッケージ本体が作成されました。

これでパッケージ本体ができました。本体ではテーブル・ファンクションの完全な定義を行います。

注目していただきたいのは、19行目なのですが、カーソル変数P_CURSORに対してOPEN処理をしないでFETCHしている点です。
バックナンバー第46回でも触れましたが、カーソル変数は明示カーソルと同じように、OPENしてFETCH可能となります。しかし、この場合はすでに呼び出し元でOPENされているのでOPEN処理は不要です。同様に、CLOSE処理もテーブルファンクションが終了すれば呼び出し元で自動的に行いますので、ここでは不要です。

カーソル変数の終了判定は、普通どおり、カーソル変数%NOTFOUNDで行います。(20行目)
フェッチする行が無かったとき、ループ処理を終えているわけです。

22行~24行目で、フェッチしたA列~C列の値をテーブルファンクションの行レコードのA列~C列にセットしています。

D列については、B列が'EMP'の時はEMP表から取得し、'DEPT'の場合はDEPT表から取得しているわけです。(26行~30行目)

そうしてA列からD列まですべてセットされた段階でその行レコードをPIPE ROW処理で返しています。(32行目)

ここでの処理はできるだけ簡略に書いていますので、例外処理等は記述していません。

これでテーブルファンクションが完成しました。
では実際にどのように使用するのか説明をします。以下の例をご覧ください。
結果もご覧ください。D列が追加されていますね。

1
2
3
4
5
6
SELECT * FROM TABLE (PAC2.F2( CURSOR ( SELECT * FROM TEST02)));
 
A          B          C          D
---------- ---------- ---------- --------------------
1          EMP        7934       MILLER
2          DEPT       10         ACCOUNTING

ここで注目していただきたいのは、PAC2.F2にパラメータとして、「CURSOR(SELECT * FROM TEST02)」と記述している点です。
これが、PAC2.F2に問い合わせ「SELECT * FROM TEST02」の行を渡しているということです。

構文的に書けば以下のようになります。

SELECT ~ FROM TABLE(テーブルファンクション( CURSOR ( SELECT文) ) );

このような形で、CURSOR式で指定した問い合わせになんらかの変換をした結果を返すことでできるわけです。

このように、TEST02表のC列の値が、場合により社員番号だったり部門番号だったりしますので、その名前であるD列を含む結果をSELECT文だけで返そうとすると、今回のようなテーブル・ファンクションを使わないとかなり難しいと思います。
(このように行により意味の異なる列が存在することは、データベース設計的には良くないことですが)

今回は元の問い合わせの1行に対して1行の結果を生成する内容ですが、元が1行で複数行を生成してもかまわないし、逆に元が複数行で1行を返してもかまいません。

また、テーブル・ファンクション(正確にはパイプライン・テーブル・ファンクション)は、生成した行をコレクションとしてためておいて最後にそのコレクション全体を返すのではなく、PIPE ROW処理により、1行が生成されるたびに返します。したがって、データを中間的にステージング(ためること)していません。
また、テーブル・ファンクションの変換結果をさらに他のテーブルファンクションで変換することもできます。
つまりテーブル・ファンクションは連鎖して使用できます。
このようなことから大量データを段階的に変換する処理(例 OLTPデータからDWHデータへの変換)でよいパフォーマンスを期待できます。
大量データ変換のパフォーマンス向上を目的とする方は パラレル処理とOracle Streamsも関連しますので詳細はマニュアルをご覧ください。

このように、ただのSELECT文だけでは難しい問い合わせの変換や、大量データの変換などに、カーソル変数をパラメータにもつテーブル・ファンクションを使えば大きな効果を期待できます。

いかがでしたか?それでは今回はここまでにします。また、次回ご期待ください。

先頭へ戻る