CREATE FUNCTIONは、ストアド・ファンクションを作成するSQL文である。パッケージに含まれるファンクションと区別するために、スタンドアロン・ファンクションとも呼ばれる。

目次

CREATE FUNCTIONの構文

CREATE [OR REPLACE] FUNCTION [schema.]function
  [(argument [,argument]...)]
  RETURN datatype
  [AUTHID {CURRENT_USER|DEFINER}]
  [PARALLEL_ENABLE]
  [DETERMINISTIC]
{IS|AS}
  [PRAGMA_AUTONOMOUS_TRANSACTION;]
  -- ローカル変数の宣言
BEGIN
  -- 実行ステートメント
[EXCEPTION
  -- 例外ハンドラ]
END [function]
OR REPLACE
既存のファンクションがあれば、置き換える。OR REPLACE句を省略した場合、既に同じ名前のファンクションがあったらエラーとなる。
schema
ファンクションを作成するスキーマの名前を指定する。スキーマ名を省略した場合、自分のスキーマ内にファンクションを作成する。
function
作成するファンクションの名前を指定する。END句のファンクション名は省略できる。
argument

ストアド・ファンクションの引数を次の構文で指定する。

name io datatype

nameには引数の名前を指定する。

datatypeには引数のデータ型を指定する。NUMBERやVARCHAR2(32)のように具体的に指定してもよいが、table.column%TYPEと指定した方がテーブルのカラムとデータ型が必ず一致するので望ましい。

ioには入出力の区分を次の形式で指定する。

I/O 説明
IN 入力パラメータ(デフォルト)
OUT 出力パラメータ
OUT NOCOPY 参照渡しの出力パラメータ
IN OUT 入力および出力パラメータ
IN OUT NOCOPY 参照渡しの入力および出力パラメータ
RETURN datetype

ファンクションの戻り値のデータ型を指定する。NUMBERやVARCHAR2(32)のように具体的に指定してもよいが、table.column%TYPEと指定した方がテーブルのカラムとデータ型が必ず一致するので望ましい。

AUTHID CURRENT_USER
実行者権限ファンクションを作成する。 実行者権限ファンクション とは、ファンクションを実行するユーザーの権限で実行されるファンクションである。また、スキーマ・オブジェクトへの未修飾の参照を実行者のスキーマで解決する。
AUTHID DEFINER
定義者権限ファンクションを作成する(デフォルト)。 定義者権限ファンクション とは、ファンクションが存在するスキーマの所有者の権限で実行されるファンクションである。また、スキーマ・オブジェクトへの未修飾の参照を所有者のスキーマで解決する。
AS
IS
ASとISのどちらを指定してもよい。どちらも違いはない。

コンパイル・エラー

ストアド・プロシージャやストアド・ファンクションを作成するときにコンパイル・エラーがあった場合でも、その詳細は表示されない。コンパイル・エラーの詳細を表示するようにするには、SHOW ERRORSコマンドを実行する。

CREATE FUNCTIONに必要な権限

自分のスキーマ内にファンクションを作るには、CREATE PROCEDUREシステム権限が必要である。

他のユーザのスキーマ内にファンクションを作るには、CREATE ANY PROCEDUREシステム権限が必要である。

CREATE FUNCTIONの使用例

引数なしのファンクションを作成する。

CREATE FUNCTION max_salary RETURN emp.salary%TYPE
AS
  maxsalary emp.salary%TYPE;
BEGIN
  SELECT MAX(salary) INTO maxsalary FROM emp;
  RETURN maxsalary;
END;
/

上記のファンクションを実行者権限ファンクションに置き換える。このファンクションは、empテーブルへアクセスできる権限を持つユーザのみ使用できる。

CREATE OR REPLACE FUNCTION max_salary RETURN emp.salary%TYPE
AUTHID CURRENT_USER IS
  maxsalary emp.salary%TYPE;
BEGIN
  SELECT MAX(salary) INTO maxsalary FROM emp;
  RETURN maxsalary;
END;
/

引数ありのファンクションを作成する。

CREATE FUNCTION employee_name(p_empno IN emp.empno%TYPE) RETURN emp.ename%TYPE
AS
  p_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO p_ename FROM emp WHERE empno = p_empno;
  RETURN p_ename;
END;
/

関連SQL