CREATE
FUNCTION
文は、スタンドアロン・ファンクションまたはコール仕様を作成するか、または置き換えます。
スタンドアロン・ファンクションは、データベースに格納されるファンクション(単一の値を戻すサブプログラム)です。
注意:
CREATE
FUNCTION
文で作成するスタンドアロン・ファンクションは、PL/SQLブロックまたはパッケージで宣言および定義するファンクションとは異なります。後者の詳細は、「ファンクションの宣言および定義」を参照してください。
コール仕様は、PL/SQLから起動できるようにJavaメソッドまたは第三世代言語(3GL)サブプログラムを宣言します。このようなメソッドまたはサブプログラムは、SQLのCALL
文を使用して起動することもできます。コール仕様は、起動時に起動するJavaメソッドまたは共有ライブラリ内の名前付きファンクションをデータベースに指示します。また、引数および戻り値に対して実行する型変換もデータベースに指示します。
注意:
ストアド・ファンクションは、副作用を制御する特定の規則に従っている場合にのみ、SQL文からコールできます。「サブプログラムの副作用」を参照してください。
ここでのトピック
前提条件
自分のスキーマ内にスタンドアロン・ファンクションを作成するか、または自分のスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE
PROCEDURE
システム権限が必要です。別のユーザーのスキーマ内にスタンドアロン・ファンクションを作成するか、または別のユーザーのスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE
ANY
PROCEDURE
システム権限が必要です。
コール仕様を起動するには、追加の権限が必要になる場合があります。たとえば、Cコール仕様の場合には、Cライブラリに対するEXECUTE
権限が必要です。
CREATE
FUNCTION
文をOracleプリコンパイラ・プログラム内に埋め込むには、キーワードEND-EXEC
の後に特定の言語の埋込みSQL文の終了記号を付けて文を終了する必要があります。
関連項目:
前述の前提条件の詳細は、次のマニュアルを参照してください。
Oracle Database開発ガイド
『Oracle Database Java開発者ガイド』
構文
create_function ::=
plsql_function_source ::=
参照:
invoker_rights_clause ::=
accessible_by_clause ::=
accessor ::=
parallel_enable_clause ::=
streaming_clause ::=
call_spec ::=
java_declaration ::=
c_declaration ::=
external_parameter ::=
property ::=
セマンティクス
OR REPLACE
ファンクションが存在する場合は、ファンクションを再作成し、再コンパイルします。
再定義する前のファンクションに対する権限を付与されていたユーザーは、権限を再付与される必要なく、ファンクションにアクセスできます。
ファンクション索引がファンクションに依存している場合は、データベースによってその索引にDISABLED
のマークが付けられます。
[ EDITIONABLE | NONEDITIONABLE ]
schema
でスキーマ・オブジェクト・タイプFUNCTION
に対してエディションが有効になっている場合に、ファンクションがエディション・オブジェクトまたは非エディション・オブジェクトのどちらになるかを指定します。デフォルト: EDITIONABLE
。エディション・オブジェクトと非エディション・オブジェクトの詳細は、『Oracle Database開発ガイド』を参照してください。
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
などでは制約することができません。
関連項目:
PL/SQLのデータ型の詳細は、「PL/SQLのデータ型」を参照してください
ODCITableDescribe
ファンクションの定義の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
invoker_rights_clause
プロシージャのAUTHID
プロパティを指定します。AUTHID
プロパティの詳細は、「実行者権限および定義者権限(AUTHIDプロパティ)」を参照してください。invoker_rights_clause
はファンクション内で1回のみ使用できます。
accessible_by_clause
ファンクションを起動できる各accessor
(PL/SQLユニット)を指定します。accessor
はaccessible_by_clause
内で複数回使用できますが、accessible_by_clause
はファンクション内で1回しか使用できません。
コンパイラはaccessible_by_clause
の構文はチェックしますが、各accessor
が存在するかどうかはチェックしません。
ファンクションが起動されるたびに、コンパイラはまず起動に対してすべてのデータベース・レベルのセキュリティ・チェックを実行します。いずれかのチェックが失敗すると、起動者がaccessor
の場合も起動は失敗します。
起動に対するすべてのデータベース・レベルのセキュリティ・チェックが成功し、ファンクションにaccessible_by_clause
がなければ、起動は成功します。ファンクションにaccessible_by_clause
が含まれる場合、起動者がaccessor
である場合にのみ起動が成功します。
注意:
accessible_by_clause
はホワイト・リストとも呼ばれます。
TRIGGER [schema.]trigger_name
ファンクションを起動できるトリガーを指定します。schema
を指定する場合、trigger_name
がそのスキーマ内に存在する必要があります。schema
を指定しない場合は、trigger_name
がファンクションを含むスキーマに存在する必要があります。
[ unit_kind ] [schema.]unit_name
ファンクションを起動できるストアドPL/SQLユニットを指定します。
unit_kind
は、FUNCTION
、PACKAGE
、PROCEDURE
またはTYPE
のいずれかです。
unit_name
は、CREATE
FUNCTION
、CREATE
PACKAGE
、CREATE
PROCEDURE
またはCREATE
TYPE
文により作成されるPL/SQLユニットの名前です。unit_kind
を指定した場合、unit_name
はその種類のユニットの名前である必要があります。
schema
を指定する場合、unit_name
がそのスキーマ内に存在する必要があります。schema
を指定しない場合は、unit_name
がファンクションを含むスキーマに存在する必要があります。
DETERMINISTIC
ファンクションがそのパラメータの同じ値でコールされたときに常に同じ結果値を戻すことを示します。DETERMINISTIC
はファンクション内で1回のみ使用できます。
このキーワードは、ファンクション索引の式に指定するか、あるいはREFRESH
FAST
またはENABLE
QUERY
REWRITE
とマークされたマテリアライズド・ビューの問合せからファンクションを起動する場合に指定する必要があります。データベースでは、このようなコンテキストの1つで決定的ファンクションが検出されると、ファンクションが再実行されるのではなく、以前に計算した結果の使用が可能なかぎり試行されます。その後、このファンクションのセマンティクスを変更する場合は、依存するすべてのファンクション索引およびマテリアライズド・ビューを手動で再構築する必要があります。
パッケージ変数を使用するファンクション、またはファンクションから戻される結果に影響を与える可能性のある方法でデータベースにアクセスするファンクションを定義する場合は、この句を指定しないでください。データベースでファンクションが再実行されない場合、この句を指定しなかった結果は取得されません。
DETERMINISTIC
句の使用は、次のセマンティック規則によって制御されます。
スキーマレベルのサブプログラムDETERMINISTIC
を宣言できます。
パッケージレベルのサブプログラムDETERMINISTIC
は、パッケージ仕様部では宣言できますが、パッケージ本体では宣言できません。
(別のサブプログラム内またはパッケージ本体内で宣言される)プライベートのサブプログラムDETERMINISTIC
は宣言できません。
DETERMINISTIC
サブプログラムは、コールされるプログラムがDETERMINISTIC
を宣言されているかどうかに関係なく、別のサブプログラムを起動できます。
プログラミングにおいては、次のDETERMINISTIC
カテゴリに属するファンクションを作成するのが適切です。
WHERE
句、ORDER
BY
句またはGROUP
BY
句の中で使用されるファンクション
SQL型のMAP
メソッドまたはORDER
メソッドであるファンクション
結果セットに行を入れるかどうか、またはどこに入れるかを決定するファンクション
注意:
マテリアライズド・ビューの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
ファンクション索引の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
parallel_enable_clause
パラレル問合せ操作のパラレル実行サーバーからファンクションを実行できることを示します。このファンクションでは、パッケージ変数などのセッション状態は使用しないでください(このような変数がパラレル実行サーバー間で共有されているとはかぎらないためです)。parallel_enable_clause
はファンクション内で1回のみ使用できます。
オプションのPARTITION
argument
BY
句は、REF
CURSOR
データ型のファンクションでのみ使用します。この句を使用すると、REF
CURSOR
引数からファンクションへの入力のパーティション化を定義できます。ファンクションへの入力のパーティション化は、ファンクションが問合せのFROM
句内のテーブル・ファンクションとして使用された場合の問合せのパラレル化の方法に影響します。
ANY
データをパラレル実行サーバー間でランダムにパーティション化できることを示します
注意:
テーブル・ファンクションの弱いカーソル変数引数をパーティション化するには、RANGE
、HASH
やVALUE
ではなく、ANY
のみを使用します。
RANGEまたはHASH
ファンクションのREF
CURSOR
引数によって戻される指定済の列にデータをパーティション化します。
オプションのstreaming_clause
を使用すると、パラレル処理を順序付けまたはクラスタ化できます。
ORDER
BY
またはCLUSTER
BY
は、パラレル実行サーバー上の行はローカルに順序付けし、column
リストで指定されたキー値と同じキー値を持つことが必要であることを示します。
expr
は、パーティション化が指定されているテーブル・ファンクション、およびパラレル問合せの実行時に各スレーブの順序付けまたはクラスタ化を指定する列を持つテーブル・ファンクションのREF
CURSOR
パラメータの名前を示します。
VALUE
ダイレクト・キー・パーティション化を指定します。これは、MapReduceワークロードを実行する際に使用するテーブル・ファンクション用です。column
は、データ型がNUMBER
である必要があります。VALUE
は、使用可能なリデューサに行処理を均等に分散します。
列内に使用可能なリデューサよりも多くのリデューサがある場合、PL/SQLでは、モジュロ演算を使用して、列内のリデューサ数を正しい範囲にマップします。
リデューサ数を計算して対応する行を処理する際に、PL/SQLでは、負の値が0(ゼロ)として処理され、正の小数値が最も近い整数に丸められます。
関連項目:
パラレル・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
PIPELINED { IS | USING }
テーブル・ファンクションの結果を反復的に戻すようにデータベースに指示します。テーブル・ファンクションは、コレクション型(ネストした表またはVARRAY)を戻します。問合せのFROM
句のファンクション名の前にTABLE
キーワードを使用して、テーブル・ファンクションを問い合せます。次に例を示します。
SELECT * FROM TABLE(function_name(...))
これによって、ファンクションで行が生成されると、データベースによってその行が戻されます。
キーワードPIPELINED
を単独で指定する場合(PIPELINED
IS
...)は、PL/SQLファンクション本体でPIPE
キーワードを使用する必要があります。このキーワードは、コレクション全体を単一の値として戻すのではなく、コレクションの個々の要素をファンクションから戻すようにデータベースに指示します。
開始、フェッチおよび終了の操作が含まれるインタフェースを事前定義する場合は、PIPELINED
USING
implementation_type
句を指定できます。実装タイプは、ODCITable
インタフェースを実装する必要があり、テーブル・ファンクションの作成時に存在している必要があります。この句は、C++やJavaなどの外部言語で実装されるテーブル・ファンクションに便利です。
ファンクションの戻り型がANYDATASET
である場合は、ファンクションの実装タイプの一部としてdescribeメソッド(ODCITableDescribe
)も定義する必要があります。
注意:
パイプライン・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
RESULT_CACHE
ファンクションの結果をキャッシュします。RESULT_CACHE
はファンクション内で1回のみ使用できます。詳細は、「PL/SQLファンクション結果キャッシュ」を参照してください。
RESULT_CACHEの制限
ネストしたファンクションにRESULT_CACHE
を指定することはできません。
AGGREGATE USING
ファンクションを集計ファンクションまたは行のグループを評価して単一行を戻すファンクションとして指定します。集計ファンクションは、選択リスト、HAVING
句およびORDER
BY
句で指定できます。
問合せでユーザー定義の集計ファンクションを指定すると、分析ファンクション(問合せ結果セットで機能するファンクション)として処理できます。これを行うには、SQL分析ファンクションで使用可能なOVER
analytic_clause
構文を使用します。分析ファンクションの構文およびセマンティクスは、『Oracle Database SQL言語リファレンス』を参照してください。
USING
句には、ファンクションの実装タイプの名前を指定します。この実装タイプは、ODCIAggregate
サブプログラムの実装が含まれているADTにする必要があります。schema
を指定しない場合、実装タイプは自分のスキーマ内に存在するとみなされます。
AGGREGATE USINGの制限
この句を指定する場合、ファンクションの入力引数は1つしか指定できません。
関連項目:
ユーザー定義の集計関数の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
ODCIサブプログラムの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
body
ファンクションの必須の実行部、およびオプションでファンクションの例外処理部。
declare_section
ファンクションのオプションの宣言部。宣言は、ファンクションに対してローカルであり、body
で参照でき、ファンクションが実行を完了すると消滅します。
call_spec
Cプロシージャ名またはJavaメソッド名、パラメータ型および戻り型を、対応するSQLにマップします。java_declaration
では、string
はメソッドのJava実装を示します。c_declaration
のLIBRARY
lib_name
では、「CREATE LIBRARY文」によって作成されたライブラリを識別します。
関連項目:
Javaコール仕様を作成する方法は、『Oracle Database Java開発者ガイド』を参照してください。
Cコール仕様を作成する方法は、『Oracle Database開発ガイド』を参照してください。
EXTERNAL
下位互換性のためにのみサポートされている、Cファンクションの非推奨の宣言方法。LANGUAGE
C
構文を使用することをお薦めします。
例
例14-12 ファンクションの作成: 例
この文は、サンプル表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
次に示す仮定の文は、Cサブプログラムc_get_val
を外部ファンクションとして登録するPL/SQLスタンドアロン・ファンクションget_val
を作成します。(この例ではパラメータを省略しています。)
CREATE FUNCTION get_val
( x_val IN NUMBER,
y_val IN NUMBER,
image IN LONG RAW )
RETURN BINARY_INTEGER AS LANGUAGE C
NAME "c_get_val"
LIBRARY c_utils
PARAMETERS (...);
例14-13 集計ファンクションの作成: 例
次の文は、一連の数値を集計するためのSecondMax
という集権ファンクションを作成します。ADTのSecondMaxImpl
サブプログラムに、ODCIAggregate
サブプログラムの実装が含まれていることを想定しています。
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
関連項目:
SecondMaxImpl
の型および型本体の実装の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
このような集計ファンクションは、サンプル表hr.employees
を問い合せる次の文のような問合せで使用します。
SELECT SecondMax(salary) "SecondMax", department_id FROM employees GROUP BY department_id HAVING SecondMax(salary) > 9000 ORDER BY "SecondMax", department_id; SecondMax DEPARTMENT_ID --------- ------------- 9450 100 13670.74 50 14175 80 18742.5 90
例14-14 ファンクションでのパッケージ・プロシージャ: 例
この文は、DBMS_LOB.GETLENGTH
プロシージャを使用してCLOB
列の長さを戻すファンクションを作成します。
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
関連トピック
この章:
他の章:
PL/SQLブロック内でのファンクションの作成の詳細は、「ファンクションの宣言および定義」を参照してください
関連項目:
CALL
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SQL文からコールされるユーザー定義ファンクションの制限の詳細は、『Oracle Database開発ガイド』を参照してください。
コール仕様の詳細は、『Oracle Database開発ガイド』を参照してください。