15.7 CREATE FUNCTION文
CREATE
FUNCTION
文は、スタンドアロン・ファンクションまたはコール仕様を作成するか、または置き換えます。
スタンドアロン・ファンクションは、データベースに格納されるファンクション(単一の値を戻すサブプログラム)です。
ノート:
CREATE
FUNCTION
文で作成するスタンドアロン・ファンクションは、PL/SQLブロックまたはパッケージで宣言および定義するファンクションとは異なります。詳細は、ファンクションの宣言および定義およびCREATE PACKAGE文を参照してください。
コール仕様は、PL/SQLから呼び出せるように、Javaメソッド、C関数またはJavaScriptファンクションを宣言します。このようなメソッドまたはサブプログラムは、SQLのCALL
文を使用して起動することもできます。コール仕様は、呼出し時に呼び出すJavaScriptファンクション、Javaメソッド、または共有ライブラリを使用する名前付きファンクションをデータベースに指示します。また、引数および戻り値に対して実行する型変換もデータベースに指示します。
ノート:
ストアド・ファンクションは、副作用を制御する特定の規則に従っている場合にのみ、SQL文からコールできます。「サブプログラムの副作用」を参照してください。
ここでのトピック
前提条件
自分のスキーマ内にスタンドアロン・ファンクションを作成するか、または自分のスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE
PROCEDURE
システム権限が必要です。
別のユーザーのスキーマ内にスタンドアロン・ファンクションを作成するか、または別のユーザーのスキーマ内のスタンドアロン・ファンクションを置き換えるには、CREATE
ANY
PROCEDURE
システム権限が必要です。
コール仕様を起動するには、追加の権限が必要になる場合があります。たとえば、Cコール仕様の場合には、Cライブラリに対するEXECUTE
権限が必要です。
CREATE
FUNCTION
文をOracleプリコンパイラ・プログラム内に埋め込むには、キーワードEND-EXEC
の後に特定の言語の埋込みSQL文の終了記号を付けて文を終了する必要があります。
関連項目:
前述の前提条件の詳細は、次のマニュアルを参照してください。
構文
create_function ::=
plsql_function_source ::=
( sharing_clause ::=、invoker_rights_clause ::=、accessible_by_clause ::=、default_collation_clause ::=、deterministic_clause ::=、shard_enable_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開発ガイド』を参照してください。
IF NOT EXISTS
ファンクションがまだ存在しない場合は作成します。同じ名前のファンクションが存在する場合、文はエラーなしで無視され、元のファンクション本体は変更されません。
IF NOT EXISTS
は、OR REPLACE
と組み合せて使用することはできません。
plsql_function_source
schema
ファンクションが含まれているスキーマの名前。デフォルト: 自分のスキーマ。
function_name
作成するファンクションの名前。
ノート:
SQL*Moduleによって生成されたスタブを使用してストアド・サブプログラムを起動する場合、ストアド・サブプログラム名は、起動側ホストの3GL言語(AdaやCなど)の有効な識別子である必要もあります。
RETURN datatype
datatype
には、ファンクションの戻り値のデータ型を指定します。戻り値は、PL/SQLでサポートされているすべてのデータ型にすることができます。
データ型で長さ、精度または位取りを指定することはできません。戻り値の長さ、精度または位取りは、ファンクションがコールされた環境からデータベースによって導出されます。
戻り型がANYDATASET
であり、問合せのFROM
句でファンクションを使用する場合は、PIPELINED
句を指定し、ファンクションの実装タイプの一部としてdescribeメソッド(ODCITableDescribe
)を定義する必要もあります。
このデータ型は、NOT
NULL
などでは制約することができません。
body
ファンクションの必須の実行部、およびオプションでファンクションの例外処理部。
declare_section
ファンクションのオプションの宣言部。宣言は、ファンクションに対してローカルであり、bodyで参照でき、ファンクションが実行を完了すると消滅します。
call_spec
Cプロシージャ、Javaメソッド名、またはJavaScriptファンクション名、パラメータ・タイプおよび戻り型をSQLの対応する部分にマップするコール仕様への参照。
例
例15-14 ファンクションの作成
この文は、サンプル表oe.orders
にファンクションget_bal
を作成します。
CREATE FUNCTION IF NOT EXISTS 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
文を使用して、ファンクションがコールされた環境にこの値を戻します。
オプションのIF NOT EXISTS
句は、文が冪等であることを確認するために使用されます。結果として生成される出力メッセージ(この場合は、「ファンクションが作成されました。」
)は、ファンクションが作成されるか文が無視されるかに関係なく同じです。
前述の例で作成したファンクションは、SQL文で使用できます。たとえば:
SELECT get_bal(165) FROM DUAL; GET_BAL(165) ------------ 2519
例15-15 集計ファンクションの作成
次の文は、一連の数値を集計するための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
---------- -------------
13500 80
17000 90
例15-16 ファンクションでのパッケージ・プロシージャ
この文は、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開発ガイド』を参照してください。
-
ODCITableDescribe
ファンクションの定義の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。