CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
これらのステートメントはストアドルーチンを生成します。これらを使用するにはCREATE ROUTINE権限を持っていることが必要です。バイナリログが有効化されている場合、CREATE FUNCTIONステートメントにもSUPER権限が項17.4. 「ストアドルーチンとトリガのバイナリログ」で述べた通り、要求されます。MySQLはALTER ROUTINE権限とEXECUTE権限をルーチン生成者に自動的に供与します。
デフォルトで、そのルーチンは初期設定データベースに関連されます。あるデータベース中にルーチンを明確に関連させるには、生成時その名称をdb_name.sp_nameと特定してください。
ルーチン名が組み込まれているSQL機能と同じである場合、ルーチンを定義する時、名称とその後のかっこの間にスペースを使用しなければなりません。 これを怠ると、構文エラーが発生します。これは、後にルーチンを呼び出す場合にも当てはまります。この理由によって、保存されているユーザ自身のルーチンに対してSQLの既存のファンクション名称を再使用しないようお勧めします。
IGNORE_SPACEＳＱＬモードは、ストアドルーチンでなく、組み込まれているファンクションに適用され、IGNORE_SPACEが有効になっているか否かにかかわりなく、ルーチン名の後にスペースを含むことは常に許容されています。
かっこの中に含めたパラメータリストは常に存在していなければいけません。パラメータがない場合、空欄のパラメータリスト()を使用すべきです。
各パラメータには、COLLATE属性は使用できないことを除けば、有効なデータタイプを使用していると宣言できます。
デフォルトで各パラメータはINパラメータです。上記とは別にパラメータの属性を特定する場合、OUT またはINOUTキーワードをパラメータ名の前で使用してください。
注:パラメータにIN、OUTしくはINOUTと特定することはPROCEDUREに対してのみ有効です。(FUNCTIONパラメータは常にINパラメータと見なされます。
INパラメータはプロシージャにある値を渡します。プロシージャはその値を修正しなければならない場合もありますが、プロシージャが返されても、発信側にはその改良を閲覧することができません。OUTパラメータは手順からある値を発信側に返します。プロシージャ内の初期値はNULLで、発信側にプロシージャが返されるとき、その値を閲覧することができます。発信側はINOUTパラメータを初期化することができ、プロシージャはそれを改良することができる上、プロシージャによる変更はプロシージャが返されたとき発信側で閲覧することができます。
各OUTパラメータもしくはINOUTパラメータは、ユーザに特定された変数を渡すことで、プロシージャが返されたときにその値を取得できます。(例については、項 「CALLステートメント構文」を参照してください。)保存された他のプロシージャの中からプロシージャまたはファンクションを呼び出す場合、あなたはルーチンパラメータまたはローカルルーチン変数を、INパラメータまたはINOUTパラメータとして渡すことができます。
FUNCTIONだけに対して、遵守する義務のあるRETURNS節を特定することができます。それは、ファンクションのリターンタイプとファンクション本体には、RETURN valueステートメントが含まれていなければならないことを示します。保存されたプロシージャとファンクションのRETURNステートメントがタイプの異なる値を戻した場合、その値は正しい値に強制的に修正されます。例えば、ファンクションがそのRETURN節の中にENUM値またはSET値を特定しますが、RETURNステートメントが整数を戻す場合、ファンクションから返された値は、SETメンバーのセットに対応するENUMメンバーに対する文字列となります。
routine_bodyは有効なSQLプロシージャステートメントから成り立っています。これをSELECTまたはINSERTのような簡単なステートメントもしくはBEGINやENDを使って書かれた複合ステートメントにすることができます。複合ステートメントの構文については、項 「BEGIN ... END 複合ステートメント構文」を参照してください。複合ステートメントには、宣言、ループ並びにその他の制御構造ステートメントを含むことができます。これらのステートメントに対する構文については、この章の後半部分で説明します。例えば、項 「DECLAREステートメント用構文」並びに項 「フローコントロール・コンストラクト」を参照してください。いくつかのステートメントはストアドルーチン内で使用することはできません（項D.1. 「ストアド ルーチンとトリガの規制」を参照してください）
ルーチンが生成されたとき、MySQLは有効化されていたsql_modeシステム変数設定を保存し、現サーバのSQL モードに関係なく、必ずこの設定でルーチンを実行します。
CREATE FUNCTIONステートメントはUDF(ユーザ定義機能)をサポートするため、旧バージョンのMySQLで使用されています。項25.3. 「Adding New Functions to MySQL」を参照してください。UDFは保存されたファンクションが存在していてもサポートされ続けます。UDFは記憶された外部機能であると見なすことができます。ただし、保存されたファンクションは自身の名称スペースをUDFと共有していることに注意してください。サーバが異なった種類のファンクションに対するリファレンスを解釈する方法を述べた規則については、項 「構文解析と解像度のファンクション名」を参照してください。
プロシージャあるいはファンクションは、それが同じインプットパラメータに対して常に同じ結果をもたらす場合、「決定論的」であるとみなされるが、同じ結果をもたらさない場合には、「非決定論的」であるとみなされます。ルーチンの定義にDETERMINISTICもNOT DETERMINISTIC も附与しない場合、初期設定はNOT DETERMINISTICとなります。
NOW()関数(またはその同義語)またはRAND()を含むルーチンは非決定論的であるが、複製に対して耐性を保持していることがあります。NOW()の場合、バイナリ ログはタイムスタンプを含み、正しく複製します。RAND()はルーチンの中で唯一回起動しただけで、正しく複製します。(ルーチン実行のタイムスタンプと乱数種を、マスタとスレーブが同じインプットとみなすことができます。)
現在、DETERMINISTIC特性は容認されていますが、まだオプチマイザによって使用されていません。ただし、バイナリログが有効化されている場合、この特徴はMySQLがどのルーチン定義を受け入れるかに影響します。項17.4. 「ストアドルーチンとトリガのバイナリログ」 を参照してください。
幾つかの特徴は、ルーチンによるデータ使用の性質に関する情報を提供します。MySQLでは、これらの特性は助言のみです。サーバはルーチンに実行が許されるステートメントの種類を制限するために、それらを使用しません。
CONTAINS SQLはルーチンにはデータを読み書きするステートメントは含まれていないことを示しています。これらの特性が明確に附与されていない場合、これがデフォルトとなります。このようなステートメントの例は、SET @x = 1または DO RELEASE_LOCK('abc')です。これは、データの実行はしても読み書きを行いません。
NO SQLはルーチンにSQLステートメントが含まれていないことを示します。
READS SQL DATAは、ルーチンには(例えば、SELECTのように)データを読み取るが、書き取らないステートメントが含まれていることを示します｡
MODIFIES SQL DATAは、ルーチンには(例えば、INSERTもしくはDELETEのように)データを書き取ることができるステートメントが含まれていることを示します。
SQL SECURITY特徴はルーチンを生成させるユーザあるいはそれを呼び出すユーザの許可を使って、ルーチンが実行されるべきか否かを明示するために使うことができます。そのデフォルトはDEFINERです。この特徴はSQL:2003の新機能です。その生成者や利用者は、ルーチンが属するデータベースにアクセスできる許可を取得していなければなりません。ルーチンを実行することができるEXECUTE権限を持つ必要があります。この権限を持たなければいけないユーザは、SQL SECURITY機能を設定する方法によって、規定者か利用者のいずれかになります。
オプションのDEFINER節はSQL SECURITY DEFINER特徴を有するルーチンに対して、実行中にアクセス権限をチェックする時使用すべきMySQLアカウントを特定します。DEFINER節はMySQL で追加されました。
user 値を附与する場合、それを ' user_name'@' host_name ' フォーマット（GRANT ステートメントに使用したと同じフォーマット）の中にあるMySQLアカウントにすべきです。user_name の値とhost_name の値が両方共必要です。CURRENT_USERをCURRENT_USER()として附与することもできます。DEFINERの初期値はCREATE PROCEDUREもしくはCREATE FUNCTIONもしくはステートメントを実行するユーザです。(これはDEFINER = CURRENT_USERと同じです。)
DEFINER節を特定する場合、SUPER権限を保持していない限り、自分の値を除くいかなるアカウントにも値をセットすることはできません。これらの規則はDEFINER ユーザの法定値を定義します。
SUPER権限を保持していない場合、文字によるか、CURRENT_USERを使って規定されているuser法定値のみがユーザのアカウントとなります。デファイナーを別のアカウントに設定することはできません。
SUPER権限を保持している場合、構文的に規定した有効なアナウントネームを特定することができます。そのアカウントが実在しない場合、警告が生成されます。
架空のDEFINER値を使ってルーチンを生成させることは可能ですが、ルーチンをDEFINER権限を使って実行すると、エラーが発生します。しかし定義者は実行中には存在しません。
ルーチンを起動すると、必然的に USE db_nameが実行(ルーチンの実行が終わると自然に停止)されます。ストアドルーチン内でUSEステートメントを使用することは禁止されています。
サーバはルーチンパラメータのデータタイプまたはファンクションリターン値を以下の通り使用します：これらの規則はDECLAREステートメント(項1. 「DECLARE ローカル変数」)で生成されたルーチン変数にも適用します。
割り当てたデータにミスマッチおよびオーバーフローがないかチェックします。警告の中に変換やオーバーフローの問題が、またストリクトモードにエラーがそれぞれもたらされます。
文字データタイプに対して、宣言文中にCHARACTER SET節がある場合、指定されたキャラクタセットとそのデフォルト照合順序が使用されます。このような節がない場合、ルーチンが生成される時有効であったデータベースキャラクタセットと照合順序が使用されます。(これらはcharacter_set_databaseシステム変数およびcollation_databaseシステム変数の値によって附与されます。)COLLATE属性はサポートされていません。(このコンテキストBINARYはキャラクタセットのバイナリー照合順序を規定するので、これにはBINARYの使用が含まれます。)
パラメータや変数にはスカラー値のみ割り当てることができます。例えば、SET x = (SELECT 1, 2)のようなステートメントは無効です。
COMMENT節はMySQLの拡張に含まれ、これはストアドルーチンの説明に使われます。この情報はSHOW CREATE PROCEDUREステートメントとSHOW CREATE FUNCTIONステートメントによって表示されます。
MySQLはルーチンにCREATEおよびDROPのようなDDLステートメントを含めることを許します。MySQLはストアドプロシージャ (保存されたファンクションではない)にCOMMITのようなSQLトランザクションステートメントを含めることも許容します。保存されたファンクションに明示、黙示、コミットもしくは反論を行うステートメントを含めることは許容されません｡これらのステートメントに対するサポートはSQLの基準によって要求されません。当該基準はこれについて、各DBMSベンダーはこれらを許すか否かを決定することができると述べています。
ストアドルーチンはLOAD DATA INFILEを使用することができません。
結果のセットを返すステートメントを保存されたファンクション内で使うことができません。これには、カラム値を変数に取り込むためにINTOを使わないSELECTステートメント、SHOWステートメント並びにEXPLAINのようなその他のステートメントが含まれています。ファンクションを規定する時、結果セットを戻すことを定義できるステートメントに対して、Not allowed to return a result set from a functionエラーが発生します(ER_SP_NO_RETSET_IN_FUNC)。稼動中にだけ、結果セットを返すことを決めることが出来るステートメントに対して、PROCEDURE %s can't return a result set in the given contextエラーが発生します(ER_SP_BADSELECT)。
以下は、OUTパラメータを使用する簡単なストアドプロシージャの例を示したものです。この例は、プロシージャを定義しながら、mysqlクライアントdelimiterコマンドを使用して、 ステートメントデリミタを; から//に変更するのに使用します。これによって、プロシージャ本体の中で使用された; デリミタが、mysql自身によって解釈されないで、サーバに転送されることが許容されます｡
mysql>delimiter //mysql>CREATE PROCEDURE simpleproc (OUT param1 INT)->BEGIN->SELECT COUNT(*) INTO param1 FROM t;->END;->//Query OK, 0 rows affected (0.00 sec) mysql>delimiter ;mysql>CALL simpleproc(@a);Query OK, 0 rows affected (0.00 sec) mysql>SELECT @a;+------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec)
delimiterコマンドを使用する時、MySQLに対してエスケープキャラクタとなるので、バックスラッシュ (‘\’) キャラクターの使用を避けてください。
パラメータを取り込み、SQL機能を使ってオペレーションを行って結果を返すファンクションの例を次に紹介します。この場合、ファンクションの定義に内部;ステートメントデリミタは含まれていないので、デリミタを使う必要はありません。
mysql>CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)->RETURN CONCAT('Hello, ',s,'!');Query OK, 0 rows affected (0.00 sec) mysql>SELECT hello('world');+----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec)
MySQLとのインターフェースを持つ言語で書かれたプログラムの中からストアドプロシージャを起動する方法ついては、項 「CALLステートメント構文」を参照してください。