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

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

第23回 「ストアド・ファンクションをSQLで使用する」

2012.06.04

前回ストアド・ファンクションの作成構文について説明しましたが、同時にコールの仕方も説明してきました。
各回、順を追って読んでいただければ、特に難しくない内容であったと思います。
ポイントは、ファンクションは何かの値をリターンするものであり、ファンクションコールそれ自体を値として何かの文の中で使用すれば良いのでしたね。
何かの文の中で値としてファンクションを記述すれば良いので、例えば、代入文(:=)の右辺で使用して変数に値を代入したり、

(例)
    変数 := ファンクション(P1);

あるいは、プロシージャの引数として、ファンクションをコールしたり、

(例)
    プロシージャ(ファンクション(P1));

などなど、コールの仕方はさまざまなパターンがあるわけです。

では、文の中で値として記述すれば良いのだとすれば、SQL文の中で値としてファンクションをコールすることもできるわけです。
それが今回のテーマです。

ただし、SQL文の中でファンクションをコールするにはいろいろと条件があり、条件がクリアできていればそのファンクションをSQL文の中で使用できます。
その条件は、SELECT文とDML(UPDATE,DELETE,INSERT)では違います。
その条件は大事なことなのですが、後でまとめて解説しますので、以下の例をご覧ください。
まずは正常に処理できる例です。

例えば、前回、順序表の値を1足し込んだ値を返すファンクション FUNC_順序番号 を作成しましたが、これを用いて注文表へのINSERT処理で、注文番号の値としてこのファンクションを記述したいと思います。

例えば、順序表において、注文IDの番号は現在3番まで発番しています。

SQL> SELECT * FROM 順序 WHERE 発番単位 = '注文ID';

発番単位                   番号
------------------------------
注文ID                        3

ではこの状況で、FUNC_順序番号('注文ID')をコールすれば上記の番号を1加算して4を返しますので、注文表へのINSERT文の注文IDの値としてこのファンクションコールを記述すれば、注文ID 4 の行が登録できるはずです。
やってみましょう。

<<<INSERT文の中でファンクションをコールしている>>>
SQL> INSERT INTO 注文 VALUES (FUNC_順序番号('注文ID'),'ラジオ',1);

1行が作成されました。

これにより、注文ID 4 の注文が注文表に登録されたはずですので
さっそく確認してみます。

SQL> SELECT * FROM 注文 ORDER BY 注文ID;

    注文ID 製品名                   注文数
----------------------------------------
         1 テレビ                        3
         2 ラジオ                        4
         3 テレビ                        2
         4 ラジオ                        1 ←追加された行

注文ID 4 の行が登録されました!

このように注文登録時の注文IDの値をファンクションから取得してきちんと注文登録できました。

それではこのファンクショをSELECT文のSELECT句で使えるでしょうか? 
普通のSQL関数であればSELECT句で使うことができます。

(例) 普通のSQL関数はSELECT文で使用できる
SQL> SELECT TO_DATE('2012-01-01') FROM DUAL;

TO_DATE(
--------
12-01-01

しかし、この FUNC_順序番号ファンクションは、INSERT文では使用できてもSELECT文では使用できないのです。
以下の通りエラーです。

SQL> SELECT FUNC_順序番号('注文ID') FROM DUAL;
SELECT FUNC_順序番号('注文ID') FROM DUAL
*
行1でエラーが発生しました。:
ORA-14551: 問合せの中でDML操作を実行することはできません。
ORA-06512: "SCOTT.FUNC_順序番号", 行9

誤解しないでいただきたいのですが、ユーザが定義したファンクションは常にSELECT文で使用できないわけではなく、一定の条件を満たせば使用できるのです。

では、その条件とは? 
上記のエラーメッセージによって半ばお判りと思いますが、次回詳しく説明します。

今回はここまでです。次回もご期待ください。

先頭へ戻る