CREATE PROCEDURE

説明

CREATE PROCEDURE ステートメントは、ストアドプロシージャを作成するために使用されます。

権限要件

CREATE PROCEDURE ステートメントを使用するには、CREATE PROCEDURE 権限が付与されている必要があります。

例：

ユーザーに権限 CREATE ROUTINE を付与すると、ユーザーは PROCEDURE を作成できるようになります。

mysql> GRANT CREATE ROUTINE ON my.* TO mingye;
mysql> CREATE PROCEDURE p1()
    -> BEGIN
    -> SELECT 1 FROM dual;
    -> END;
    -> /

デフォルトでは、PROCEDURE が作成されると、OceanBaseデータベースは自動的に ALTER ROUTINE および EXECUTE 権限をルーチン（ストアドプロシージャおよび関数）の作成者に付与します。DEFINER 句が存在する場合、必要な権限は user の値によって決まります。

構文

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

デフォルトでは、ストアドプロシージャはデフォルトのデータベースに関連付けられています。ストアドプロシージャを指定されたデータベースに関連付けるには、database_name.sp_name で名前を指定します。

ストアドプロシージャを呼び出すには、CALL ステートメントを使用します。詳細については、CALL参照してください。

DEFINER および SQL SECURITY 句は、ルーチン実行時にアクセス権限をチェックするために使用するセキュリティコンテキストを指定します。

IF NOT EXISTS は、既に存在する同名のルーチンの作成を防ぎます。

括弧内の proc_parameter パラメータリストは常に存在する必要があります。パラメータがない場合は、空のパラメータリストを表すために空の括弧 () を使用します。ここでのパラメータ名は大文字と小文字を区別しません。デフォルトでは、各パラメータは IN パラメータです。ストアドプロシージャのパラメータに他の値を指定する場合は、パラメータ名の前にキーワード OUT または INOUT を使用します。

IN パラメータは値をストアドプロシージャに渡します。ストアドプロシージャはその値を変更する可能性がありますが、ストアドプロシージャが返されるとき、呼び出し元は変更内容を確認できません。OUT パラメータは、ストアドプロシージャ内の値を呼び出し元に返します。OUT パラメータのストアドプロシージャ内部の初期値は NULL であり、プロセスが返されると、呼び出し元は OUT パラメータの値を確認できます。INOUT パラメータは呼び出し元によって初期化され、プロセスによって変更され、ストアドプロシージャが返されると、ストアドプロシージャによる変更はすべて呼び出し元に可視されます。

各 OUT または INOUT パラメータについて、CALL ステートメントを使用してユーザー定義変数を渡してプロセスを呼び出し、プロセスが返されるときにその値を取得できるようにします。このプロセスが別のストアドプロシージャまたは関数から呼び出される場合、ルーチンパラメータまたはローカルルーチン変数を OUT または INOUT パラメータとして渡すこともできます。トリガー内からプロセスを呼び出す場合、NEW.column_name を OUT または INOUT パラメータとして渡すこともできます。

パラメータ型は任意の有効なデータ型として宣言でき、その前に CHARACTER SET が付加されている場合は、COLLATE プロパティを使用できます。

routine_body は有効なSQLステートメントで構成されます。SQLステートメントは、SELECT または INSERT のような単純なステートメントである可能性もありますし、BEGIN と END で記述された複合ステートメントである可能性もあります。複合ステートメントには、宣言、ループ、その他の制御構造ステートメントを含めることができます。

ストアドルーチンには、CREATE や DROP のようなDDLステートメントを含めることができます。ストアドプロシージャには、COMMIT のようなSQLトランザクションステートメントを含めることができます。ストアド関数には、明示的または暗黙的なコミットやロールバックを実行するステートメントを含めることはできません。これらのステートメントのサポートはSQL標準では要求されておらず、各DBMSベンダーがこれらのステートメントの使用を許可するかどうかを決定できると規定されています。

結果セットを返すステートメントはストアドプロシージャで使用できますが、ストアド関数では使用できません。これには、INTO var_list 句を持たない SELECT ステートメントやその他のステートメント、および SHOW、EXPLAIN、CHECK TABLE などのステートメントが含まれます。ストアド関数がこれらのステートメントを使用するとエラーが発生します。ストアドプログラムがこれらのステートメントを使用して結果セットを返せない場合もエラーが発生します。

ストアドルーチンで USE ステートメントを使用することは許可されていません。ルーチンを呼び出すとき、USE database_name が暗黙的に実行され（ルーチン終了時に取り消され）、ルーチンの実行にデフォルトのデータベースが指定されます。ルーチンのデフォルトデータベース以外の他のデータベースオブジェクトを参照する場合は、データベース名を使用して限定することができます。

厳密なSQLモードでルーチンを定義しても、非厳密モードで呼び出される場合、ルーチンのパラメータには厳密モードで値が代入されません。厳密なSQLモードでルーチンに渡される式を割り当てる場合は、厳密モードでルーチンを呼び出す必要があります。

COMMENT プロパティは、ストアドルーチンを説明するために使用でき、SHOW CREATE PROCEDURE および SHOW CREATE FUNCTION ステートメントを使用してコメント情報を表示できます。

LANGUAGE プロパティは、ルーチンを記述するために使用される言語を示します。SQLルーチンにのみ適用され、サーバーはこのプロパティを無視します。

ルーチンが常に同じ入力パラメータに対して同じ結果を生成する場合、それは「決定的」と見なされます。そうでない場合は、「非決定的」と見なされます。ルーチン定義に DETERMINISTIC または NOT DETERMINISTIC がどちらも指定されていない場合、デフォルトは NOT DETERMINISTIC です。

ストアドルーチンがデータを使用するプロパティ情報は以下のとおりです：

• CONTAINS SQL は、ストアドルーチンにデータの読み書きを行うステートメントが含まれていないことを示します。これがデフォルトです。例えば、SET@x=1 または DO RELEASE_LOCK('abc') は実行されますが、データの読み取りは行われません。

• NO SQL は、ストアドルーチンにSQLステートメントが含まれていないことを示します。

• READS SQL DATA は、ストアドルーチンにデータの読み取りを行うステートメント（例えば SELECT）が含まれているが、データの書き込みを行うステートメントは含まれていないことを示します。

• MODIFIES SQL DATA は、ストアドルーチンにデータの書き込みを行う可能性のあるステートメント（例えば INSERT または DELETE）が含まれていることを示します。

SQL SECURITY は DEFINER または INVOKER であり、ストアドルーチンが指定されたアカウント（このアカウントにはルーチンに関連付けられたデータベースへのアクセス権限が必要です）の権限で実行されるか、呼び出し元の権限で実行されるかを指定します。デフォルト値は DEFINER です。ストアドルーチンを呼び出すユーザーには、そのルーチンに対する EXECUTE 権限が必要です。

DEFINER 句は、ルーチンの実行時にルーチンのアクセス権限をチェックするために使用されるアカウントを指定します。DEFINER 句が存在する場合、user 値は 'user_name'@'host_name' として指定されたアカウント（例えば 'admin'@'oblocalhost'）または CURRENT_USER() 関数によって取得されたアカウントである必要があります。DEFINER 句が省略されている場合、デフォルトの定義者は CREATE PROCEDURE ステートメントを実行したユーザーです。SQL SECURITY DEFINER プロパティを使用して定義されたストアドルーチンの本体では、CURRENT_USER 関数を使用してストアドルーチンの DEFINER 値を返すことができます。

次の例では、SQL SECURITY INVOKER プロパティが使用されていますが、このプロセスには 'admin'@'localhost' の DEFINER があります。この場合、ストアドプロシージャは呼び出し元の権限で実行され、実行の成功は呼び出し元が EXECUTE 権限と ob.user の SELECT 権限を持っているかどうかによって決まります。

CREATE DEFINER = 'admin'@'oblocalhost' PROCEDURE account_num()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM ob.users;
END;

サーバーは、ルーチンパラメータ、DECLARE で作成されたローカルルーチン変数、または関数の戻り値のデータ型を次のように処理します：

• データ型のマッチング違反やオーバーフローが存在しないかどうかをチェックします。変換とオーバーフローの問題は、厳密なSQLモードでアラートまたはエラーを引き起こします。

• スカラー値のみを指定できます。例えば、SET val=(SELECT 1, 2) ステートメントは無効です。

• 文字データ型について、宣言に CHARACTER SET が含まれている場合は、指定された文字セットとそのデフォルトの照合順序が使用されます。COLLATE プロパティが使用されている場合は、デフォルトの照合順序ではなく、指定された照合順序が使用されます。

  CHARACTER SET と COLLATE が存在しない場合は、ルーチン作成時に適用されたデータベースの文字セットと照合順序が使用されます。サーバーがデータベースの文字セットと照合順序を使用するのを避けるには、パラメータに CHARACTER SET と COLLATE プロパティを明示的に指定してください。

  データベースのデフォルトの文字セットまたは照合順序を変更するには、ストアドルーチンを削除して再作成し、新しいデータベースのデフォルト値を適用する必要があります。

  character_set_database および collation_database システム変数は、データベースの文字セットと照合順序を指定するために使用できます。

ストアドプロシージャの作成方法の詳細については、ストアドプロシージャ参照してください。