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

これらのステートメントはストアドルーチンを生成します。デフォルトでは、ルーチンはデフォルトデータベースに関連付けられます。あるデータベース中にルーチンを明確に関連させるには、生成時その名称を db_name.sp_name と特定してください。

CREATE FUNCTION ステートメントはまた、UDF (ユーザー定義関数) をサポートするために MySQL でも使用されます。詳しくはAdding New Functions to MySQLを参照してください。UDF は記憶された外部機能であると見なすことができます。ただし、ストアドファンクションは自身の名前空間を UDF と共有していることに注意してください。サーバーの異なる種類の機能に対するリファレンスの解釈を説明した規則は、項5.2.4. 「関数名の構文解析と解決」 を参照してください。

ストアドプロシージャーを呼び出すには、CALL ステートメントを使用します (項8.2.1. 「CALL 構文」 を参照)。ストアドファンクションを呼び出すには、式でその関数を参照します。その関数は、式の評価中に値を返します。

CREATE PROCEDURE または CREATE FUNCTION ステートメントを実行するには、CREATE ROUTINE 権限を持っている必要があります。デフォルトでは、MySQL は、ルーチン作成者に ALTER ROUTINE および EXECUTE 権限を自動的に与えます。 この動作は、automatic_sp_privileges システム変数を無効にすることによって変更できます。詳しくはStored Routines and MySQL Privilegesを参照してください。Binary Logging of Stored Programs で説明されているように、バイナリログが有効になっている場合は、CREATE FUNCTION ステートメントに SUPER 権限も必要になる可能性があります。

あとで説明するように、DEFINER および SQL SECURITY 節は、ルーチンの実行時にアクセス権限を確認するときに使用されるセキュリティーコンテキストを指定します。

ルーチン名が組み込みの SQL 関数の名前と同じである場合は、ルーチンを定義するとき、またはあとで呼び出すときに名前とそれに続く括弧の間にスペースを使用しないかぎり、構文エラーが発生します。このため、ユーザー独自のストアドルーチンに既存の SQL 関数の名前を使用することは避けてください。

IGNORE_SPACE SQL モードは、ストアドルーチンではなく、組み込み関数に適用されます。IGNORE_SPACE が有効になっているかどうかには関係なく、ストアドルーチン名のあとにスペースを使用することは常に許容されます。

かっこの中に含めたパラメータリストは常に存在していなければいけません。パラメータがない場合、空欄のパラメータリスト () を使用すべきです。パラメータ名の大文字と小文字は区別されません。

デフォルトで各パラメータは IN パラメータです。上記とは別にパラメータの属性を特定する場合、OUT または INOUT キーワードをパラメータ名の前で使用してください。

IN パラメータはプロシージャーにある値を渡します。プロシージャーはその値を修正しなければならない場合もありますが、プロシージャーが返されても、発信側にはその改良を閲覧することができません。OUT パラメータは手順からある値を発信側に返します。プロシージャー内の初期値は NULL で、発信側にプロシージャーが返されるとき、その値を閲覧することができます。発信側は INOUT パラメータを初期化することができ、プロシージャーはそれを改良することができる上、プロシージャーによる変更はプロシージャーが返されたとき発信側で閲覧することができます。

プロシージャーを呼び出す CALL ステートメントでは、OUT または INOUT パラメータごとにユーザー定義変数を渡して、プロシージャーが終了したときにその値を取得できるようにします。保存されたほかのプロシージャーの中からプロシージャーまたはファンクションを呼び出す場合、あなたはルーチンパラメータまたはローカルルーチン変数を、IN パラメータまたは INOUT パラメータとして渡すことができます。

次の例は、OUT パラメータを使用する単純なストアドプロシージャーを示しています。

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)

この例は、プロシージャーを定義しながら、mysql クライアント delimiter コマンドを使用して、ステートメント区切り文字を; から//に変更するのに使用します。これによって、プロシージャー本体の中で使用された; 区切り文字が、mysql 自身によって解釈されないで、サーバーに転送されることが許容されます。Defining Stored Programs を参照してください。

FUNCTION だけに対して、遵守する義務のある RETURNS 節を特定することができます。これは関数の戻り値の型を示すものであり、関数の本体に RETURN value ステートメントが含まれている必要があります。保存されたプロシージャーとファンクションの RETURN ステートメントが型の異なる値を返した場合、その値は正しい値に強制的に修正されます。たとえば、ファンクションがその RETURN 節の中に ENUM 値または SET 値を特定しますが、RETURN ステートメントが整数を返す場合、ファンクションから返された値は、SET メンバーのセットに対応する ENUM メンバーに対する文字列となります。

次の関数例はパラメータを取得し、SQL 関数を使用して操作を実行したあと、結果を返します。この場合、ファンクションの定義に内部;ステートメント区切り文字は含まれていないので、区切り文字を使う必要はありません。

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> 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)

パラメータの型と関数の戻り値の型には、COLLATE 属性は使用できませんが、それ以外の任意の有効なデータ型を使用するように宣言できます。

routine_body は有効な SQL プロシージャーステートメントから成り立っています。これを SELECT または INSERT のような簡単なステートメントまたは BEGIN や END を使って書かれた複合ステートメントにすることができます。複合ステートメントには、宣言、ループ並びにその他の制御構造ステートメントを含むことができます。これらのステートメントの構文は、項8.8. 「MySQL 複合ステートメント構文」 で説明されています。

MySQL はルーチンに CREATE および DROP のような DDL ステートメントを含めることを許します。MySQL はストアドプロシージャー (ストアドファンクションではない) に COMMIT のような SQL トランザクションステートメントを含めることも許容します。ストアドファンクションには、明示的または暗黙的なコミットまたはロールバックを実行するステートメントを含めることはできません。これらのステートメントに対するサポートは SQL の基準によって要求されません。当該基準はこれについて、各 DBMS ベンダーはこれらを許すか否かを決定することができると述べています。

結果セットを返すステートメントは、ストアドプロシージャー内で使用できますが、ストアドファンクション内では使用できません。この禁止には、INTO var_list 節を含まない SELECT ステートメントや、SHOW、EXPLAIN、CHECK TABLE などのほかのステートメントも含まれます。関数の定義時に、結果セットを返すことが判断できるステートメントの場合は、Not allowed to return a result set from a function エラーが発生します (ER_SP_NO_RETSET)。稼働中にだけ、結果セットを返すことを決めることが出来るステートメントに対して、PROCEDURE %s can't return a result set in the given context エラーが発生します (ER_SP_BADSELECT)。

ストアドルーチン内で USE ステートメントを使用することは禁止されています。ルーチンが呼び出されたときに、暗黙の USE db_name が実行されます (その後、ルーチンが終了したときに元に戻ります)。これにより、ルーチンが実行されている間、そのルーチンに特定のデフォルトデータベースが与えられます。ルーチンのデフォルトデータベース以外のデータベース内のオブジェクトへの参照は、適切なデータベース名で修飾する必要があります。

ストアドルーチン内で許可されていないステートメントの詳細については、Restrictions on Stored Routines, Triggers, and Events を参照してください。

MySQL とのインタフェースを持つ言語で書かれたプログラムの中からストアドプロシージャーを起動する方法ついては、項8.2.1. 「CALL 構文」を参照してください。

MySQL は、ルーチンが作成されるときに有効な sql_mode システム変数設定を格納し、ルーチンを呼び出すときに有効なサーバー SQL モードには関係なく、常にこの設定を適用してルーチンを実行します。

呼び出し元の SQL モードからルーチンの SQL モードへの切り替えは、引数を評価し、結果の値をルーチンパラメータに割り当てたあとで実行されます。ルーチンを厳密な SQL モードで定義しても、非厳密モードでそのルーチンを呼び出した場合、ルーチンパラメータへの引数の割り当ては厳密モードで実行されません。ルーチンに渡された式を厳密な SQL モードで割り当てる必要がある場合は、厳密モードを有効にしてルーチンを呼び出すようにしてください。

プロシージャーあるいはファンクションは、それが同じインプットパラメータに対して常に同じ結果をもたらす場合、「決定論的」であるとみなされるが、同じ結果をもたらさない場合には、「非決定論的」であるとみなされます。ルーチンの定義に DETERMINISTIC も NOT DETERMINISTIC も附与しない場合、初期設定は NOT DETERMINISTIC となります。

NOW() 関数 (または、その同義語) または RAND() を含むルーチンは決定的ではありませんが、依然としてレプリケーションに対して安全である可能性があります。NOW() の場合、バイナリログはタイムスタンプを含み、正しく複製されます。また RAND() も、ルーチンの実行中に 1 回だけ呼び出されるかぎり、正しく複製されます。 (ルーチン実行のタイムスタンプと乱数種を、マスタとスレーブが同じインプットとみなすことができます)。

MySQL 5.1.21 より前のバージョンでは、DETERMINISTIC 特性は受け入れられますが、オプティマイザで使用されません。ただし、バイナリログが有効になっている場合、この特性は MySQL がどのルーチン定義を受け入れるかに常に影響を与えます。Binary Logging of Stored Programs を参照してください。

幾つかの特徴は、ルーチンによるデータ使用の性質に関する情報を提供します。MySQL では、これらの特性は助言のみです。サーバーはルーチンに実行が許されるステートメントの種類を制限するために、それらを使用しません。

SQL SECURITY 特徴はルーチンを生成させるユーザーあるいはそれを呼び出すユーザーの許可を使って、ルーチンが実行されるべきか否かを明示するために使うことができます。そのデフォルトは DEFINER です。この特徴は SQL:2003 の新機能です。その作成者や利用者は、ルーチンが属するデータベースにアクセスできる許可を取得していなければなりません。ルーチンを実行することができる EXECUTE 権限を持つ必要があります。この権限を持たなければいけないユーザーは、SQL SECURITY 機能を設定する方法によって、規定者か利用者のいずれかになります。

COMMENT 特性は MySQL の拡張機能であり、ストアドルーチンを説明するために使用できます。この情報は SHOW CREATE PROCEDURE ステートメントと SHOW CREATE FUNCTION ステートメントによって表示されます。

オプションの DEFINER 節は SQL SECURITY DEFINER 特徴を有するルーチンに対して、実行中にアクセス権限を確認する時使用すべき MySQL アカウントを特定します。DEFINER 節は MySQL 5.1.8.で追加されました。

DEFINER 節に対して user 値を指定する場合は、'user_name'@'host_name' の形式 (GRANT ステートメントで使用されるのと同じ形式) の MySQL アカウントである必要があります。user_name の値と host_name の値が両方共要求されます。定義者はまた、CURRENT_USER または CURRENT_USER() として指定することもできます。DEFINER の初期値は CREATE PROCEDURE または CREATE FUNCTION またはステートメントを実行するユーザーです。(これは DEFINER = CURRENT_USER と同じです)。

DEFINER 節を指定する場合は、これらの規則によって正当な DEFINER ユーザー値が決定されます。

SQL SECURITY DEFINER 特性を使用して定義されたストアドルーチン内で、CURRENT_USER は、そのルーチンの DEFINER 値を返します。ストアドルーチン内のユーザー監査については、Auditing MySQL Account Activity を参照してください。

mysql.user テーブルにリストされている MySQL アカウントの数を表示する次のプロシージャーを考えてみます。

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

どのユーザーが定義した場合でも、このプロシージャーには 'admin'@'localhost' の DEFINER アカウントが割り当てられます。どのユーザーから呼び出された場合でも、このプロシージャーは、そのアカウントの権限で実行されます (デフォルトのセキュリティー特性が DEFINER であるため)。'admin'@'localhost' がこのプロシージャーに対する EXECUTE 権限と、mysql.user テーブルに対する SELECT 権限を持っているかどうかに応じて、このプロシージャーは成功または失敗します。

ここで、このプロシージャーが SQL SECURITY INVOKER 特性を使用して定義されているとします。

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

このプロシージャーは、依然として 'admin'@'localhost' の DEFINER を持っていますが、この場合は呼び出し元ユーザーの権限で実行されます。そのため、このプロシージャーは、呼び出し元が必要な権限を持っているかどうかに応じて成功または失敗します。

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