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