プロシージャは、個別の 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 ];
表 1. 引数
引数 説明
name name は、プロシージャの識別子です。
parameters parameters は、仮パラメーターのリストです。
declarations 宣言は、変数、カーソル、型、またはサブプログラムの宣言です。 サブプログラム宣言が含まれている場合は、他のすべての変数、カーソル、および型宣言の後に宣言する必要があります。
statements statements は SPL プログラム文です (BEGIN-END ブロックには EXCEPTION セクションが含まれる場合があります)。
IMMUTABLE

STABLE

VOLATILE

これらの属性は、プロシージャの動作についてクエリオプティマイザーに通知します。 どちらか一方のみ指定することができます。 VOLATILE がデフォルトの動作です。
  • IMMUTABLE は、プロシージャがデータベースを変更できず、同じ引数値が指定された場合に常に同じ結果に到達することを指定します。 データベースの検索や、引数リストに直接存在しない情報の使用は行いません。 この句を含めると、引数がすべて定数のプロシージャの呼び出しは、すぐにプロシージャの値に置き換えることができます。
  • STABLE は、プロシージャがデータベースを変更できないこと、および単一のテーブルスキャン内で同じ引数値に対して常に同じ結果を返すことを指定しますが、その結果は SQL 文間で変更される可能性があります。 これは、データベースの検索や現在のタイムゾーンなどのパラメーター変数に依存する手順に適しています。
  • VOLATILE は、単一のテーブルスキャン内でもプロシージャの値が変更される可能性があることを指定しているため、最適化を行うことはできません。 副作用がある関数は、その結果がかなり予測可能であっても、呼び出しが最適化されないように、発揮性に分類する必要があることにご注意ください。
DETERMINISTIC DETERMINISTICは、IMMUTABLEの同義語です。 DETERMINISTIC プロシージャはデータベースを変更できず、同じ引数値が指定された場合は常に同じ結果になります。 データベースの検索や、引数リストに直接存在しない情報の使用は行いません。 この句を含めると、引数がすべて定数のプロシージャの呼び出しは、すぐにプロシージャの値に置き換えることができます。
[ NOT ] LEAKPROOF LEAKPROOF には副作用がなく、プロシージャの呼び出しに使用される値に関する情報を表示しません。
CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

  • CALLED ON NULL INPUT は、引数の一部が NULL の場合にプロシージャが正常に呼び出されることを示します。 これがデフォルト値です。 必要に応じて、作成者は NULL 値を確認し、適切に応答する必要があります。
  • RETURNS NULL ON NULL INPUT、または STRICT は、引数のいずれかが NULL の場合は常にプロシージャが NULL を返すことを指定します。 これらの句が指定されている場合には、NULL 引数があるときにプロシージャは実行されません。 NULL の結果は自動的に想定されます。
[ EXTERNAL ] SECURITY DEFINER SECURITY DEFINER は、プロシージャを作成したユーザーの権限で実行することを指定します。 これがデフォルト値です。 キーワード EXTERNAL は、SQL 準拠のために許可されていますが、オプションです。
[ EXTERNAL ] SECURITY INVOKER SECURITY INVOKER は、プロシージャがそれを呼び出すユーザーの権限で実行されることを指定します。 キーワード EXTERNAL は、SQL 準拠のために許可されていますが、オプションです。
AUTHID DEFINER

AUTHID CURRENT_USER

  • AUTHID DEFINER は、[EXTERNAL] SECURITY DEFINER の同義語です。 AUTHID 句が省略されている場合、または AUTHID DEFINER が指定されている場合、プロシージャ所有者の権限を使用して、データベースオブジェクトへのアクセス権限が決定されます。
  • AUTHID CURRENT_USER は、[EXTERNAL] SECURITY INVOKER の同義語です。 AUTHID CURRENT_USER が指定されている場合には、プロシージャを実行している現在のユーザーの権限を使用して、アクセス権限が決定されます。
PARALLEL { UNSAFE | RESTRICTED | SAFE } PARALLEL は、並列順次スキャン (並列モード) の使用を可能にします。 シリアル順次スキャンは複数のワーカーを使用して、照会中にリレーションを並行してスキャンします。
  • UNSAFE に設定すると、プロシージャを並列モードで実行することができません。 このようなプロシージャが存在すると、シリアル実行プランが強制されます。 これは、PARALLEL 句が省略されている場合のデフォルト設定です。
  • RESTRICTED に設定すると、プロシージャは並列モードで実行できますが、実行は並列グループリーダーに制限されます。 特定のリレーションの資格に並列制限があるものがある場合は、そのリレーションは並列処理用に選択されません。
  • SAFE に設定すると、プロシージャは制限なしに並列モードで実行することができます。
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 ではサポートされていません。

デフォルトでは、ストアドプロシージャは SECURITY DEFINERS として作成されますが、PL/pgSQL で作成すると、ストアドプロシージャは SECURITY INVOKERS として作成されます。

次の例は、パラメーターを必要としない簡単なプロシージャを示しています。

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 句に置き換えます。