ファンクション
概要
ファンクション(Function、関数)は与えられたパラメータで計算かロジックを行い、戻り値を返す処理である。
文法
CREATE FUNCTION STATEMENT ::=
CREATE [OR REPLACE] FUNCTION function_name (parameter_list) RETURN return_description [IS|AS]
BEGIN
…
END;
サンプル
create function test return msg varchar is begin return 'Hello!' end;
詳細説明
- ファンクションは戻り値を1つ戻すPL/SQLブロックであり、RETURN句とRETURN文を含める必要がある
- 仮パラメータおよびRETURN句に指定するデータ型には、サイズ指定ができない
- ファンクションの作成途中でコンパイルエラーが発生した場合にも、ソースコードはデータディクショナリに格納される
- ファンクションの仮パラメータに指定するパラメータモードは、基本的にはINパラメータを使用する。OUTおよびIN OUTパラメータは使用しないようにする
- ファンクションを作成する目的は、パラメータを受け取り単一の値を戻すことにあるため、複数の値を戻したいのであれば、プロシージャの作成を考える
ファンクションのコール
SQL*Plus環境でファンクションを実行する場合には、VARIABLEコマンドで、ファンクションからの戻り値を受け取るためのホスト変数を宣言し、ファンクションのコール時に使用する。ファンクションのコールには、EXCUTEコマンドを使用するが、ホスト変数の使用には注意してほしい。ホスト変数はコロン(:)を付けて私用する必要がある。
例:ファンクション「get_bonus」をSQL*Plus環境からコールする方法
SQL> VARIABLE BONUS VARCHAR2(20) SQL> EXECUTE :BONUS := get_bonus(201) PL/SQLプロシージャが正常に完了しました SQL> PRINT BONUS BONUS ------------------------------ KEVINの賞与:15000
SQL機能として、あらかじめ提供されている関数(組込み関数)と同様に、SQL文中からファンクションをコールすることができる。これには、SQL文では行えないような複雑な計算処理が可能になるなどの利点がある。
例:SQL文中からファンクションをコールする方法
SQL> CREATE OR REPLACE FUNCTION tax_func 2 (p_value IN NUMBER) 3 RETURN NUMBER 4 IS 5 BEGIN 6 RETURN p_value * 1.05; 7 END tax_func; 8 / ファンクションが作成されました SQL> SELECT S_NAME, S_TANKA, TAX_FUNC(S_TANKA) 2 FROM SHOHIN; S_NAME S_TANKA TAX_FUNC(S_TANKA) ------------------------------------------ NOTE 120 126 BOOK 1500 1575
ファンクションをコールする場所
- SELECTの選択リスト
- WHERE句およびHAVING句の条件
- CONNECT BY句、START WITH句、OREDER BY句、GROUP BY句
- INSERT文のVALUE句
- UPDATE文のSET句
SQL文からコールされるファンクションの制限
- ファンクションはストアドファンクションである必要がある
- ファンクションはINパラメータのみを受け取る必要があり、実パラメータは位置表示方で指定する必要がある
- ファンクションからは有効なSQLデータ型を戻す必要がある。PL/SQL固有のデータ型(BOOLEAN、RECORDなど)は戻り値として使用できない。また、これはファンクションの仮パラメータに対しても同様
- ファンクションはCREATE文やALTER文のCHECK制約内からコールすること、および、列のDEFAULT値の指定に使用することはできない
- ファンクションがSELECT文またはパラレル化DML文からコールされる場合は、データベース票を変更することはできない
- ファンクションは前の制限を満たさないサブプログラムをコールすることはできない
よく使われる関数
NVL
SYSDATE
TRUNC
ROUND
AVG
COUNT
MAX
MIN
SUBSTR
SUBSTRB
LEN
LENB
MONTHS_BETWEEN
ADD_MONTHS
TO_CHAR
LPAD
RPAD
LTRIM
RTRIM
SUBSTR