CREATE
FUNCTION
文は、スタンドアロン・ストアド・ファンクションまたはコール仕様を作成するか、または置き換えます。
スタンドアロン・ストアド・ファンクションは、データベースに格納されるファンクション(単一の値を戻すサブプログラム)です。
注意: CREATE FUNCTION 文で作成するスタンドアロン・ストアド・ファンクションは、PL/SQLブロックまたはパッケージで宣言および定義するファンクションとは異なります。 後者の詳細は、「ファンクション宣言と定義」を参照してください。 |
コール仕様は、PL/SQLからコールできるようにJavaメソッドまたは第三世代言語(3GL)ルーチンを宣言します。 このようなメソッドまたはルーチンは、SQLのCALL
文を使用してコールすることもできます。 コール仕様は、コール時に起動するJavaメソッドまたは共有ライブラリ内の名前付きファンクションをデータベースに指示します。 また、引数および戻り値に対して実行する型変換もデータベースに指示します。
前提条件
自身のスキーマ内にスタンドアロン・ストアド・ファンクションを作成するか、または自身のスキーマ内のスタンドアロン・ストアド・ファンクションを置き換えるには、CREATE
PROCEDURE
システム権限が必要です。 別のユーザーのスキーマ内にスタンドアロン・ストアド・ファンクションを作成するか、または別のユーザーのスキーマ内のスタンドアロン・ストアド・ファンクションを置き換えるには、CREATE
ANY
PROCEDURE
システム権限が必要です。
コール仕様を起動するには、追加の権限が必要になる場合があります。たとえば、Cコール仕様の場合には、Cライブラリに対するEXECUTE
権限が必要です。
CREATE
FUNCTION
文をOracleプリコンパイラ・プログラム内に埋め込むには、キーワードEND-EXEC
の後に特定の言語の埋込みSQL文の終了記号を付けて文を終了する必要があります。
参照: 前述の前提条件の詳細は、次のマニュアルを参照してください。
|
構文
create_function ::=
(parameter_declaration ::=、datatype ::=、result_cache_clause ::=、declare_section ::=、body ::=)
streaming_clause ::=
call_spec ::=
Java_declaration ::=
C_declaration ::=
キーワードとパラメータの説明
OR
REPLACE
を指定すると、ファンクションがすでに存在する場合に再作成できます。 この句を使用すると、既存のファンクションに以前に付与されたオブジェクト権限を削除、再作成および再付与しなくても、そのファンクションの定義を変更できます。 ファンクションを再定義すると、そのファンクションはデータベースによって再コンパイルされます。
再定義されたファンクションに対する権限を以前に付与されているユーザーは、権限を再付与される必要なく、ファンクションにアクセスできます。
ファンクション索引がファンクションに依存している場合は、データベースによってその索引にDISABLED
のマークが付けられます。
schema
ファンクションを含めるスキーマを指定します。 schema
を省略すると、データベースによって現行のスキーマにファンクションが作成されます。
function_name
作成するファンクションの名前を指定します。
datatype
には、ファンクションの戻り値のデータ型を指定します。 戻り値は、PL/SQLでサポートされているすべてのデータ型にすることができます。
注意: Oracle SQLでは、ブール値のパラメータまたは戻り値を持つファンクションのコールはサポートされていません。 したがって、ユーザー定義ファンクションがSQL文からコールされる場合は、数値(0または1)または文字列(TRUE またはFALSE )を戻すように設計する必要があります。 |
データ型で長さ、精度または位取りを指定することはできません。 戻り値の長さ、精度または位取りは、ファンクションがコールされた環境からデータベースによって導出されます。
戻り型がANYDATASET
であり、問合せのFROM
句でファンクションを使用する場合は、PIPELINED
句を指定し、ファンクションの実装タイプの一部としてdescribeメソッド(ODCITableDescribe
)を定義する必要もあります。
このデータ型は、NOT
NULL
などでは制約することができません。
参照:
|
メンバー・ファンクションのAUTHID
プロパティおよびオブジェクト型のプロシージャを指定します。 AUTHID
プロパティの詳細は、「実行者権限または定義者権限の使用(AUTHID句)」を参照してください。
CURRENT_USER
の権限でファンクションを実行する場合は、CURRENT_USER
を指定します。 この句は実行者権限ファンクションを作成します。
また、この句は、問合せ、DML操作および動的SQL文の外部名がCURRENT_USER
のスキーマで解決されるように指定します。 その他すべての文に含まれる外部名は、このファンクションが存在するスキーマで解決されます。
ファンクションが存在するスキーマの所有者の権限でファンクションを実行し、ファンクションが存在するスキーマでその外部名を解決する場合は、DEFINER
を指定します。 これがデフォルトです。これによって、定義者権限ファンクションが作成されます。
参照:
|
ファンクションがそのパラメータの同じ値でコールされたときに常に同じ結果値を戻すことを示すには、DETERMINISTIC
を指定します。
このキーワードは、ファンクション索引の式に指定するか、あるいはREFRESH
FAST
またはENABLE
QUERY
REWRITE
とマークされたマテリアライズド・ビューの問合せからファンクションをコールする場合に指定する必要があります。 データベースでは、このようなコンテキストの1つで決定的ファンクションが検出されると、ファンクションが再実行されるのではなく、以前に計算した結果の使用が可能なかぎり試行されます。 その後、このファンクションのセマンティクスを変更する場合は、依存するすべてのファンクション索引およびマテリアライズド・ビューを手動で再構築する必要があります。
パッケージ変数を使用するファンクション、またはファンクションから戻される結果に影響を与える可能性のある方法でデータベースにアクセスするファンクションを定義する場合は、この句を指定しないでください。 データベースでファンクションが再実行されない場合、この句を指定しなかった結果は取得されません。
DETERMINISTIC
句の使用は、次のセマンティック規則によって制御されます。
トップレベルのサブプログラムDETERMINISTIC
を宣言できます。
パッケージレベルのサブプログラムDETERMINISTIC
は、パッケージ仕様部では宣言できますが、パッケージ本体では宣言できません。
(別のサブプログラム内またはパッケージ本体内で宣言される)プライベートのサブプログラムDETERMINISTIC
は宣言できません。
DETERMINISTIC
サブプログラムは、コールされるプログラムがDETERMINISTIC
を宣言されているかどうかに関係なく、別のサブプログラムをコールできます。
参照:
|
PARALLEL_ENABLE
は、パラレル問合せ操作のパラレル実行サーバーからファンクションを実行できることを示す最適化ヒントです。 このファンクションでは、パッケージ変数などのセッション状態は使用しないでください。このような変数がパラレル実行サーバー間で共有されているとはかぎらないためです。
オプションのPARTITION
argument
BY
句は、REF
CURSOR
引数型のファンクションでのみ使用します。 この句を使用すると、REF
CURSOR
引数からファンクションへの入力のパーティション化を定義できます。
ファンクションへの入力のパーティション化は、ファンクションが問合せのFROM
句内のテーブル・ファンクションとして使用された場合の問合せのパラレル化の方法に影響します。 ANY
は、データをパラレル実行サーバー間でランダムにパーティション化できることを示します。 また、指定した列リストにRANGE
またはHASH
パーティション化を指定することもできます。
オプションのstreaming_clause
を使用すると、指定した列リストでパラレル処理を順序付けまたはクラスタ化できます。
ORDER
BY
は、パラレル実行サーバー上の行はローカルで順序付けする必要があることを示します。
CLUSTER
BY
は、パラレル実行サーバー上の行に、column_list
で指定されたキー値と同じキー値が必要であることを示します。
expr
は、パーティション化が指定されているテーブル・ファンクション、およびパラレル問合せの実行時に各スレーブの順序付けまたはクラスタ化を指定する列を持つテーブル・ファンクションのREF
CURSOR
パラメータの名前を示します。
これらすべてのオプション句で指定された列は、ファンクションのREF
CURSOR
引数によって戻される列を参照します。
参照: ユーザー定義の集計ファンクションの詳細は、次のマニュアルを参照してください。
|
PIPELINED
を指定すると、テーブル・ファンクションの結果を反復的に戻すようにデータベースに指示できます。 テーブル・ファンクションは、コレクション型(ネストした表またはVARRAY)を戻します。 問合せのFROM
句のファンクション名の前にTABLE
キーワードを使用して、テーブル・ファンクションを問い合せます。 次に例を示します。
SELECT * FROM TABLE(function_name(...))
これによって、ファンクションで行が生成されると、データベースによってその行が戻されます。
キーワードPIPELINED
を単独で指定する場合(PIPELINED
IS
...)は、PL/SQLファンクション本体でPIPE
キーワードを使用する必要があります。 このキーワードは、コレクション全体を単一の値として戻すのではなく、コレクションの個々の要素をファンクションから戻すようにデータベースに指示します。
開始、フェッチおよび終了の操作が含まれるインタフェースを事前定義する場合は、PIPELINED
USING
implementation_type
句を指定できます。 実装タイプは、ODCITable
インタフェースを実装する必要があります。また、テーブル・ファンクションの作成時に存在している必要があります。 この句は、C++やJavaなどの外部言語で実装されるテーブル・ファンクションに便利です。
ファンクションの戻り型がANYDATASET
である場合は、ファンクションの実装タイプの一部としてdescribeメソッド(ODCITableDescribe
)も定義する必要があります。
AGGREGATE
USING
を指定すると、このファンクションを集計ファンクションまたは行のグループを評価して単一行を戻すファンクションとして指定できます。 集計ファンクションは、選択リスト、HAVING
句およびORDER
BY
句で指定できます。
問合せでユーザー定義の集計ファンクションを指定すると、分析ファンクション(問合せ結果セットで機能するファンクション)として処理できます。 これを行うには、組込み分析ファンクションで使用可能なOVER
analytic_clause
構文を使用します。 分析ファンクションの構文およびセマンティクスは、『Oracle Database SQL言語リファレンス』を参照してください。
USING
句には、ファンクションの実装タイプの名前を指定します。 この実装タイプは、ODCIAggregate
ルーチンの実装が含まれているオブジェクト型にする必要があります。 schema
を指定しない場合、実装タイプは自身のスキーマ内に存在するとみなされます。
集計ファンクションの作成の制限 この句を指定する場合、ファンクションに対して指定できる入力引数は1つのみです。
参照: ODCIルーチンの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。 |
body
ファンクションの必須の実行部、およびオプションでファンクションの例外処理部。
declare_section
ファンクションのオプションの宣言部。 宣言は、ファンクションに対してローカルであり、body
で参照でき、ファンクションが実行を完了すると消滅します。
call_spec
call_spec
を使用すると、JavaまたはCのメソッド名、パラメータ型および戻り型を対応するSQLにマップできます。 Java_declaration
では、string
はメソッドのJava実装を示します。
参照:
|
EXTERNAL
以前のリリースでは、EXTERNAL
はCメソッドを宣言する場合の代替方法でした。 この句は非推奨となっており、下位互換性のためにのみサポートされています。 LANGUAGE
C
構文を使用することをお薦めします。
ファンクションの作成: 例 次の文は、サンプルの表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 (...);
集計ファンクションの作成: 例 次の文は、数値を集計するSecondMax
という名前の集計ファンクションを作成します。 オブジェクト型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 --------- ------------- 13500 80 17000 90
ファンクションでのパッケージ・プロシージャの使用: 例 次の文は、DBMS_LOB.GETLENGTH
プロシージャを使用してCLOB
列の長さを戻すファンクションを作成します。
CREATE OR REPLACE FUNCTION text_length(a CLOB) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN DBMS_LOB.GETLENGTH(a); END;
参照: このファンクションを使用してファンクション索引を作成する場合の例は、『Oracle Database SQL言語リファレンス』を参照してください。 |
関連トピック
ファンクション宣言と定義(PL/SQLブロック内でのファンクションの作成の詳細)
参照:
|