ファンクション (Function、関数)

ファンクション

概要

ファンクション(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固有のデータ型(BOOLEANRECORDなど)は戻り値として使用できない。また、これはファンクションの仮パラメータに対しても同様
  • ファンクションは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

See Also

PROCEDURE
http://tahiti.oracle.com