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

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

第25回 「ストアド・ファンクションで複雑な問い合わせを簡単に」

2012.06.18

前回、ストアド・ファンクションをSQLからコールするための条件を確認しましたが、実際のところ、どんな場合にSQLからストアド・ファンクションをコールすることがあるでしょうか?

一言でいうと、複雑な問い合わせ(SELECT)を簡単にするために、そのSELECT文からストアド・ファンクションをコールする場合、といっていいと思います。

DML(INSERT, UPDATE, DELETE)と違って、SELECT文は大変複雑な文となることが多く、なかなか要件に合うSELECT文を書くことが難しい場合があります。
そんなとき、ストアド・ファンクションを使えば、場合により、SELECT文を簡単に書くことができるのです。

例えば、売上表の売上金額をなにかの「区分」ごとに集計するケースを考えてみます。
売上表には「区分」という列はなく、その区分は1件1件の売上の内容ごとにさまざまな要因をもとに決まるものであると思ってください。
そういった場合、その区分ごとの集計をSELECT文で行うことは難しく、明示カーソル処理などを使ってプログラム的に集計する方法が考えられます。
しかし、売上の内容を元に、区分を判定し、区分を返すストアド・ファンクションを作成しておけば、SELECT文だけで、区分ごとの売上集計は簡単な問い合わせで可能となります。

具体例をご覧ください。
例えば以下のような売上表があります。

SQL> SELECT * FROM 売上表;

  売上番号   売上日       顧客名            製品名    事業所           金額
----------------------------------------------------------------------
       1   2012-06-01 さくら商事         テレビ     銀座          1234000
       2   2012-06-02 さくら商事         テレビ     恵比寿       2342340
       3   2012-06-03 つばき産業         テレビ     銀座          1234340
       4   2012-06-04 つばき産業         テレビ     恵比寿       1234000
       5   2012-06-05 さくら商事         ラジオ     銀座          1234000
       6   2012-06-05 さくら商事         ラジオ     恵比寿       2342340
       7   2012-06-06 その他商事         ラジオ     銀座          1234340
       8   2012-06-07 つばき産業         ラジオ     恵比寿       1234000

8行が選択されました。

ここで、売上区分を以下のように決めたとします。(内容に意味はありません)
・銀座事業所
さくら商事への売上⇒売上区分「A」
つばき産業への売上⇒売上区分「B」
それ以外への売上 ⇒売上区分「C」
・恵比寿事業所
さくら商事への売上⇒売上区分「B」
つばき産業への売上⇒売上区分「A」
それ以外 への売上⇒売上区分「C」

要はさまざまな要因で売上区分が決まる例です。
そしてその区分を決定付ける条件が変動する可能性があるので、表の列として固定的に定義できないとします。

そのような状況では、売上区分を返すファンクションを定義しておけば、売上区分ごとの集計が大変簡単になります。

実際にやってみましょう。上記の条件で売上区分を返すファンクションをFUNC_売上区分という名前で作成します。

SQL> CREATE OR REPLACE FUNCTION FUNC_売上区分
  2  ( P_事業所 IN 売上表.事業所%TYPE,
  3    P_顧客名 IN 売上表.顧客名%TYPE)
  4  RETURN VARCHAR2
  5  IS
  6  BEGIN
  7    IF P_事業所 = '銀座' THEN
  8       IF P_顧客名 = 'さくら商事' THEN
  9          RETURN 'A';
10       ELSIF P_顧客名 = 'つばき産業' THEN
11          RETURN 'B';
12       ELSE
13          RETURN 'C';
14       END IF;
15    ELSIF P_事業所 = '恵比寿' THEN
16            IF P_顧客名 = 'さくら商事' THEN
17                RETURN 'B';
18             ELSIF P_顧客名 = 'つばき産業' THEN
19                RETURN 'A';
20             ELSE
21                RETURN 'C';
22             END IF;
23     ELSE
24         RETURN 'C';
25     END IF;
26  END;
27  /

ファンクションが作成されました。

そしてこのストアド・ファンクションを使えば売上区分ごとの集計が簡単にできます。

SQL> SELECT FUNC_売上区分(事業所,顧客名), SUM(金額) FROM 売上表
2 GROUP BY FUNC_売上区分(事業所,顧客名);

FUNC_売上区分(事業所,顧客名)    SUM(金額)
----------------------------------------
A                                 4936000
B                                 5919020
C                                 1234340

どうでしょう!
ストアド・ファンクションを使うことにより大変簡単な問い合わせとすることができました。

最後に注意点をひとつ。
ストアド・ファンクションを使うことで確かに問い合わせを簡単にすることができますが、問い合わせのパフォーマンスまでよくなるわけではありません。
問い合わせの複雑さをファンクションの中に隠しているだけであり、見かけ上簡単な問い合わせになっても、複雑な処理をしていることには変わりません。

それでは今回はここまでです。また次回、ご期待ください!

先頭へ戻る