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

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

第24回 「ストアド・ファンクションをSQLで使用するための条件」

2012.06.11

前回、ストアド・ファンクションが、SQL関数のように、SQL文で使用できる(コールできる)ことを見てきました。
しかし、前回のファンクションの場合は、INSERT文で正常に使用できましたが、SELECT文で使用するとエラーになりました。
このように、SQL文からユーザ定義のファンクションをコールする際には、そのファンクション定義にいろいろと条件があるのです。
この条件が今回のテーマです。
詳細は、PL/SQL言語のマニュアルに記載されてますが、そのエッセンスをわかりやすく説明します。

【条件1】
まず、ファンクションのパラメータのデータ型と、RETURNするデータ型は、表の列のデータ型であることが必要です。
つまり、NUMBER、VARCHAR2、DATEなど、ごく普通に表の列のデータ型であることが必要です。
つまり、そのファンクションは特殊なデータ型をやり取りするようなものであってはいけないということです。
例えば、BOOLEAN型やコレクション(簡単に言えば配列)などPL/SQL固有のデータ型の場合はSQLからコールできません。

【条件2】
そのファンクションの中で、COMMIT、ROLLBACK、DDL文(CREATE、DROPなど)を使用していないことが必要です。
これらの文はトランザクションを終了(確定または取り消し)させます。
つまりSQLからコールするファンクションが勝手にトランザクションを確定させたり、取り消したりしては不都合があるので、それを禁止しているわけです。

【条件3】
そのファンクションの中で、SQL文の対象となる表にアクセスしていないこと。
例えばEMP表に対するSQL文でコールするファンクションの中で、EMP表に対してDML(INSERT、UPDATE、DELETE)やSELECTなどを行っていないこと。
(ただし、例外があるのですが、レアケースと思いますのでここでは説明しません)

【条件4】
したがって、一般にSQL文からファンクションをコールするとき、そのSQLの対象外の表に対してアクセス(SELECTやDML)するファンクションであれば、SQL文からコールできます。
しかしその際に、そのファンクションの中でDML文(INSERT、UPDATE、DELETE、MERGE)を使用している場合は、そのファンクションはSELECT文からはコールできません。
仮にこのような時SELECT文からコール可能だとすると、SELECTによりトランザクションが発生することになるので、不都合があるわけです。
DML文からコールするのであれば、もともとトランザクションが発生するので、そのトランザクションの一部としてファンクションで他の表を更新しても矛盾はないわけです。

上記のようなことが、SQLからコールできるファンクションの条件です。

前回、FUNC_順序番号というファンクションを例にとって、INSERT文では使用可能で、SELECT文で使用するとエラーになりましたが、上記の条件に照らし合わせて以下の例で解説します。
なおFUNC_順序番号ファンクションの定義は前回の記事を参照してください。

(例 前回から抜粋)
SQL> INSERT INTO 注文 VALUES (FUNC_順序番号('注文ID'),'ラジオ',1);

1行が作成されました。

(解説)
このFUNC_順序番号ファンクションは「順序表」をDML(UPDATE)して新たな注文IDの番号をRETURNしますが、ファンクション内で「注文表」に対しては何の操作もしていないので、上記の例のように「注文表」に対するDML(INSERT)からコールできるわけです。(条件3)

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

(解説)
FUNC_順序番号ファンクションはDMLを含んでいるので、SELECT文からはコールできないわけです。(条件4)

ということで、今回はSQLからコールできるファンクションの条件を解説しました。
なお、SQLからコールしないファンクションであればもちろんこれらの条件には制限されません。
しかしファンクションをSQLからコール可能なものにしておけば、ファンクションの値を変数に取得しなくても、SQLから直接その値を使えますので便利です。
その他にも、SQLからファンクションをコールするメリットはいろいろとあります。
次回はそういったメリットについて簡単にご紹介したいと思います。

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

先頭へ戻る