CREATE FUNCTION

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;
/

CREATE FUNCTION

CREATE FUNCTIONの構文

コンパイル・エラー

CREATE FUNCTIONに必要な権限

CREATE FUNCTIONの使用例

関連SQL