CREATE FUNCTION文

14.7 CREATE FUNCTION文

CREATE FUNCTION文は、スタンドアロン・ファンクションまたはコール仕様を作成するか、または置き換えます。

スタンドアロン・ファンクションは、データベースに格納されるファンクション(単一の値を戻すサブプログラム)です。

ノート:

CREATE FUNCTION文で作成するスタンドアロン・ファンクションは、PL/SQLブロックまたはパッケージで宣言および定義するファンクションとは異なります。詳細は、ファンクションの宣言および定義およびCREATE PACKAGE文を参照してください。

コール仕様は、PL/SQLから起動できるようにJavaメソッドまたはCファンクションを宣言します。このようなメソッドまたはサブプログラムは、SQLのCALL文を使用して起動することもできます。コール仕様は、起動時に起動するJavaメソッドまたは共有ライブラリ内の名前付きファンクションをデータベースに指示します。また、引数および戻り値に対して実行する型変換もデータベースに指示します。

ノート:

ストアド・ファンクションは、副作用を制御する特定の規則に従っている場合にのみ、SQL文からコールできます。「サブプログラムの副作用」を参照してください。

ここでのトピック

前提条件

自分のスキーマ内にスタンドアロン・ファンクションを作成するか、または自分のスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE PROCEDUREシステム権限が必要です。

別のユーザーのスキーマ内にスタンドアロン・ファンクションを作成するか、または別のユーザーのスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE ANY PROCEDUREシステム権限が必要です。

コール仕様を起動するには、追加の権限が必要になる場合があります。たとえば、Cコール仕様の場合には、Cライブラリに対するEXECUTE権限が必要です。

CREATE FUNCTION文をOracleプリコンパイラ・プログラム内に埋め込むには、キーワードEND-EXECの後に特定の言語の埋込みSQL文の終了記号を付けて文を終了する必要があります。

関連項目:

前述の前提条件の詳細は、次のマニュアルを参照してください。

構文

create_function ::=

図create_function.epsの説明

plsql_function_source ::=

図plsql_function_source.epsの説明

( sharing_clause ::= , invoker_rights_clause ::= , accessible_by_clause ::= , default_collation_clause ::= , deterministic_clause ::= , parallel_enable_clause ::= , result_cache_clause ::= , aggregate_clause ::= , pipelined_clause ::=, sql_macro_clause ::= , body ::= , call_spec ::= , datatype ::= , declare_section ::= , parameter_declaration ::= )

セマンティクス

create_function

OR REPLACE

ファンクションが存在する場合は、ファンクションを再作成し、再コンパイルします。

再定義する前のファンクションに対する権限を付与されていたユーザーは、権限を再付与される必要なく、ファンクションにアクセスできます。

ファンクション索引がファンクションに依存している場合は、データベースによってその索引にDISABLEDのマークが付けられます。

[ EDITIONABLE | NONEDITIONABLE ]

schemaでスキーマ・オブジェクト・タイプFUNCTIONに対してエディションが有効になっている場合に、ファンクションがエディション・オブジェクトまたは非エディション・オブジェクトのどちらになるかを指定します。デフォルト: EDITIONABLE。エディション・オブジェクトと非エディション・オブジェクトの詳細は、『Oracle Database開発ガイド』を参照してください。

plsql_function_source

schema

ファンクションが含まれているスキーマの名前。デフォルト: 自分のスキーマ。

function_name

作成するファンクションの名前。

ノート:

SQL*Moduleによって生成されたスタブを使用してストアド・サブプログラムを起動する場合、ストアド・サブプログラム名は、起動側ホストの3GL言語(AdaやCなど)の有効な識別子である必要もあります。

RETURN datatype

datatypeには、ファンクションの戻り値のデータ型を指定します。戻り値は、PL/SQLでサポートされているすべてのデータ型にすることができます。

ノート:

Oracle SQLでは、BOOLEANパラメータまたは戻り値を持つファンクションの起動はサポートされていません。したがって、ユーザー定義ファンクションを起動するSQL文の場合は、数値(0または1)または文字列(TRUEまたはFALSE)を戻すように設計する必要があります。

データ型で長さ、精度または位取りを指定することはできません。戻り値の長さ、精度または位取りは、ファンクションがコールされた環境からデータベースによって導出されます。

戻り型がANYDATASETであり、問合せのFROM句でファンクションを使用する場合は、PIPELINED句を指定し、ファンクションの実装タイプの一部としてdescribeメソッド(ODCITableDescribe)を定義する必要もあります。

このデータ型は、NOT NULLなどでは制約することができません。

body

ファンクションの必須の実行部、およびオプションでファンクションの例外処理部。

declare_section

ファンクションのオプションの宣言部。宣言は、ファンクションに対してローカルであり、bodyで参照でき、ファンクションが実行を完了すると消滅します。

例

例14-14 ファンクションの作成

この文は、サンプル表oe.ordersにファンクションget_balを作成します。

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total 
      INTO acc_bal 
      FROM orders 
      WHERE customer_id = acc_no; 
      RETURN(acc_bal); 
    END;
/

get_balファンクションは、指定した口座の残高を戻します。

このファンクションを起動する場合は、引数acc_no(残高を確認する口座の番号)を指定する必要があります。acc_noのデータ型はNUMBERです。

このファンクションは口座の残高を戻します。CREATE FUNCTION文にRETURN句を指定すると、戻り値のデータ型がNUMBERになります。

このファンクションは、SELECT文を使用して、引数acc_noで識別される行のbalance列をorders表で選択します。次に、RETURN文を使用して、ファンクションがコールされた環境にこの値を戻します。

前述の例で作成したファンクションは、SQL文で使用できます。たとえば:

SELECT get_bal(165) FROM DUAL; 

GET_BAL(165)
------------
        2519

例14-15 集計ファンクションの作成

次の文は、一連の数値を集計するためのSecondMaxという集権ファンクションを作成します。ADTのSecondMaxImplサブプログラムに、ODCIAggregateサブプログラムの実装が含まれていることを想定しています。

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
    PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;