プロシージャは、個別の SPL プログラム文として発動または呼び出されるスタンドアロンの SPL プログラムです。 プロシージャは、呼び出されるとオプションで入力パラメーターの形式で呼び出し元から値を受け取り、オプションで出力パラメーターの形式で呼び出し元に値を返します。
CREATE PROCEDURE 文は、データベースに格納されるスタンドアロンプロシージャを定義して名前を付けます。
スキーマ名が含まれている場合、プロシージャは指定されたスキーマに作成されます。 それ以外の場合は、現在のスキーマに作成されます。 新しいプロシージャの名前は、同じスキーマ内で同じ入力引数型を持つ既存のプロシージャと一致してはなりません。 ただし、異なる入力引数型のプロシージャは名前を共有することができます。 これはオーバーロードと呼ばれます。 プロシージャのオーバーロードは、Oracle と互換性のある PolarDB データベースの機能です。ストアドスタンドアロンプロシージャのオーバーロードは、Oracle データベースと互換性がありません。
既存のプロシージャの定義を更新するには、CREATE OR REPLACE PROCEDURE を使用します。 この方法でプロシージャの名前または引数の型を変更することはできません (これを試した場合、実際には新しい個別のプロシージャを作成することになります)。 OUT パラメーターを使用する場合には、プロシージャを削除しない限り、OUT パラメーターの型を変更することはできません。
CREATE [OR REPLACE] PROCEDURE name [ (parameters) ]
[
IMMUTABLE
| STABLE
| VOLATILE
| DETERMINISTIC
| [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT
| RETURNS NULL ON NULL INPUT
| STRICT
| [ EXTERNAL ] SECURITY INVOKER
| [ EXTERNAL ] SECURITY DEFINER
| AUTHID DEFINER
| AUTHID CURRENT_USER
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter
{ TO value | = value | FROM CURRENT }
...]
{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declarations ]
BEGIN
statements
END [ name ];
引数 | 説明 |
---|---|
name | name は、プロシージャの識別子です。 |
parameters | parameters は、仮パラメーターのリストです。 |
declarations | 宣言は、変数、カーソル、型、またはサブプログラムの宣言です。 サブプログラム宣言が含まれている場合は、他のすべての変数、カーソル、および型宣言の後に宣言する必要があります。 |
statements | statements は SPL プログラム文です (BEGIN-END ブロックには EXCEPTION セクションが含まれる場合があります)。 |
IMMUTABLE
STABLE VOLATILE |
これらの属性は、プロシージャの動作についてクエリオプティマイザーに通知します。 どちらか一方のみ指定することができます。 VOLATILE がデフォルトの動作です。
|
DETERMINISTIC | DETERMINISTICは、IMMUTABLEの同義語です。 DETERMINISTIC プロシージャはデータベースを変更できず、同じ引数値が指定された場合は常に同じ結果になります。 データベースの検索や、引数リストに直接存在しない情報の使用は行いません。 この句を含めると、引数がすべて定数のプロシージャの呼び出しは、すぐにプロシージャの値に置き換えることができます。 |
[ NOT ] LEAKPROOF | LEAKPROOF には副作用がなく、プロシージャの呼び出しに使用される値に関する情報を表示しません。 |
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT STRICT |
|
[ EXTERNAL ] SECURITY DEFINER | SECURITY DEFINER は、プロシージャを作成したユーザーの権限で実行することを指定します。 これがデフォルト値です。 キーワード EXTERNAL は、SQL 準拠のために許可されていますが、オプションです。 |
[ EXTERNAL ] SECURITY INVOKER | SECURITY INVOKER は、プロシージャがそれを呼び出すユーザーの権限で実行されることを指定します。 キーワード EXTERNAL は、SQL 準拠のために許可されていますが、オプションです。 |
AUTHID DEFINER
AUTHID CURRENT_USER |
|
PARALLEL { UNSAFE | RESTRICTED | SAFE } | PARALLEL は、並列順次スキャン (並列モード) の使用を可能にします。 シリアル順次スキャンは複数のワーカーを使用して、照会中にリレーションを並行してスキャンします。
|
COST execution_cost | execution_cost は、プロシージャの推定実行コストを示す正の数です。 Units: cpu_operator_cost. プロシージャがセットを返す場合、これは返される行あたりのコストです。 値が大きいと、プランナーは必要以上に頻繁に関数を評価しないようにします。 |
ROWS result_rows | result_rows は正の数で、プランナーがプロシージャから返されると予想する行の推定数を示します。 これは、プロシージャがセットを返すように宣言されている場合にのみ許可されます。 デフォルトの想定は 1,000 行です。 |
SET configuration_parameter { TO value | = value | FROMCURRENT } | SET は、指定された設定パラメーターを、プロシージャが入力されたときに指定された値に設定し、プロシージャが終了したときに以前の値に戻します。 SET FROM CURRENT
は、セッションの現在のパラメーター値を、プロシージャが入力されたときに適用される値として保存します。
SET 句がプロシージャにアタッチされている場合、同じ変数に対してプロシージャ内で実行される SET LOCAL 文の影響は、プロシージャに限定されます。 設定パラメーターの以前の値は、プロシージャの終了時に復元されます。 通常の SET 文 (LOCALなし) は、前の SET LOCAL 文の場合と同様に、SET 句をオーバーライドします。このような文の影響は、現在のトランザクションがロールバックされない限り、プロシージャの終了後も持続します。 |
PRAGMA AUTONOMOUS_TRANSACTION | PRAGMA AUTONOMOUS_TRANSACTION は、プロシージャを自律型トランザクションとして設定するディレクティブです。 |
STRICT、LEAKPROOF、PARALLEL、COST、ROWS、および SET キーワードは、Oracle と互換性のある PolarDB データベースに拡張機能を提供していますが、Oracle ではサポートされていません。
例
次の例は、パラメーターを必要としない簡単なプロシージャを示しています。
CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!') ;
END simple_procedure;
プロシージャは、Oracle と互換性のある PolarDB データベースにプロシージャコードを入力することにより、データベースに格納されます。
次の例では、プロシージャ宣言で AUTHID DEFINER および SET 句を使用する方法について説明します。 update_salary プロシージャは、プロシージャの実行中に、プロシージャを定義したロールの権限を、プロシージャを呼び出しているロールに伝達します。
CREATE OR REPLACE PROCEDURE update_salary(id INT、new_salary NUMBER)
SET SEARCH_PATH = 'public' SET WORK_MEM = '1MB'
AUTHID DEFINER IS
BEGIN
UPDATE emp SET salary = new_salary WHERE emp_id = id;
END;
SET 句を含めて、プロシージャの検索パスを public に、ワークメモリを 1 MB に設定します。 他のプロシージャ、関数、およびオブジェクトは、これらの設定の影響を受けます。
この例では、AUTHID DEFINER 句は、プロシージャ内の文の実行が許可されていない可能性があるロールに権限を一時的に付与します。 プロシージャを呼び出すロールに関連付けられた権限を使用するようにサーバーに指示するには、AUTHID DEFINER 句を AUTHID CURRENT_USER 句に置き換えます。