説明
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システム変数を使用して、データベースの文字セットと照合順序を指定できます。
ストアドプロシージャの作成方法の詳細な例については、ストアドプロシージャを参照してください。