MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.17 CREATE PROCEDURE ステートメントおよび CREATE FUNCTION ステートメント

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / CREATE PROCEDURE ステートメントおよび CREATE FUNCTION ステートメント

このページは機械翻訳したものです。

13.1.17 CREATE PROCEDURE ステートメントおよび CREATE FUNCTION ステートメント

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

CREATE
    [DEFINER = 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: {
    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

これらのステートメントは、ストアドルーチン (ストアドプロシージャーまたはストアドファンクション) の作成に使用されます。つまり、指定されたルーチンがサーバーに認識されます。デフォルトでは、ストアドルーチンはデフォルトのデータベースに関連付けられます。ルーチンを明示的に特定のデータベースに関連付けるには、そのルーチンの作成時に、その名前を db_name.sp_name として指定します。

CREATE FUNCTION ステートメントはまた、UDF (ユーザー定義関数) をサポートするために MySQL でも使用されます。セクション13.7.4.1「ユーザー定義関数用の CREATE FUNCTION ステートメント」を参照してください。 UDF は、外部のストアドファンクションと見なすことができます。ストアドファンクションは、その名前空間を UDF と共有します。各種の関数への参照をサーバーが解釈する方法を記述したルールについては、セクション9.2.5「関数名の構文解析と解決」を参照してください。

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

CREATE PROCEDURE および CREATE FUNCTION には、CREATE ROUTINE 権限が必要です。 DEFINER 句が存在する場合、セクション25.6「ストアドオブジェクトのアクセス制御」で説明されているように、必要な権限は user の値によって異なります。バイナリロギングが有効になっている場合は、セクション25.7「ストアドプログラムバイナリロギング」で説明されているように、CREATE FUNCTION に SUPER 権限が必要になることがあります。

デフォルトでは、MySQL は、ルーチン作成者に ALTER ROUTINE および EXECUTE 権限を自動的に付与します。この動作は、automatic_sp_privileges システム変数を無効にすることによって変更できます。セクション25.2.2「ストアドルーチンと MySQL 権限」を参照してください。

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

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

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

括弧で囲まれたパラメータリストは、常に存在する必要があります。パラメータが存在しない場合は、() の空のパラメータリストを使用するようにしてください。パラメータ名では大文字と小文字は区別されません。

各パラメータは、デフォルトでは IN パラメータです。それ以外のパラメータを指定するには、パラメータ名の前にキーワード OUT または INOUT を使用します。

注記

IN、OUT、または INOUT としてのパラメータの指定は、PROCEDURE に対してのみ有効です。 FUNCTION の場合、パラメータは常に IN パラメータと見なされます。

IN パラメータは、プロシージャーへの値を渡します。プロシージャーはその値を変更する可能性がありますが、そのプロシージャーから戻ったとき、その変更は呼び出し元に表示されません。 OUT パラメータは、プロシージャーから呼び出し元に値を渡します。その初期値はプロシージャー内では NULL であり、そのプロシージャーから戻ったとき、その値は呼び出し元に表示されます。 INOUT パラメータは呼び出し元によって初期化され、プロシージャーで変更できます。そのプロシージャーから戻ったとき、プロシージャーによって行われた変更はすべて呼び出し元に表示されます。

OUT または INOUT パラメータごとに、プロシージャーを呼び出す CALL ステートメントでユーザー定義変数を渡して、プロシージャーから戻ったときにその値を取得できるようにします。別のストアドプロシージャまたはストアドファンクション内からプロシージャをコールする場合は、ルーチンパラメータまたはローカルルーチン変数を OUT または INOUT パラメータとして渡すこともできます。トリガー内からプロシージャをコールする場合は、NEW.col_name を OUT または INOUT パラメータとして渡すこともできます。

プロシージャパラメータに対する未処理条件の影響の詳細は、セクション13.6.7.8「条件の処理と OUT または INOUT パラメータ」を参照してください。

ルーチン内に準備されたステートメントでルーチンパラメータを参照することはできません。セクション25.8「ストアドプログラムの制約」を参照してください。

次の例は、国コードを指定して、world データベースの city テーブルに表示されるその国の都市の数をカウントする単純なストアドプロシージャを示しています。国コードは IN パラメータを使用して渡され、市区町村数は OUT パラメータを使用して返されます:

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|     248 |
+---------+
1 row in set (0.00 sec)

mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

この例では、プロシージャーの定義中に mysql クライアントの delimiter コマンドを使用して、ステートメント区切り文字を ; から // に変更しています。これにより、プロシージャー本体で使用される ; 区切り文字を、mysql 自体が解釈するのではなく、サーバーに渡すようにすることができます。セクション25.1「ストアドプログラムの定義」を参照してください。

RETURNS 句は、FUNCTION (これには必須です) に対してのみ指定できます。これは関数の戻り型を示すものであり、関数本体には RETURN value ステートメントが含まれている必要があります。 RETURN ステートメントが異なる型の値を返した場合、その値は正しい型に強制的に変更されます。たとえば、ある関数が RETURNS 句で ENUM または SET 値を指定しているが、RETURN ステートメントが整数を返した場合、その関数から返される値は SET メンバーのセットの対応する ENUM メンバーを示す文字列になります。

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

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 属性は、CHARACTER SET 指定の前にある場合に使用できます。

routine_body は、有効な SQL ルーチンステートメントで構成されます。これは SELECT や INSERT などの単純なステートメントでも、BEGIN と END を使用して記述された複合ステートメントでもかまいません。複合ステートメントには、宣言、ループ、およびその他の制御構造ステートメントを含めることができます。これらのステートメントの構文については、セクション13.6「複合ステートメントの構文」で説明されています。実際には、本体が単一の RETURN ステートメントで構成されていないかぎり、ストアドファンクションは複合ステートメントを使用する傾向があります。

MySQL では、ルーチンに CREATE や DROP などの DDL ステートメントを含めることが許可されます。 MySQL ではまた、ストアドプロシージャーに COMMIT などの SQL トランザクションステートメントを含めることも許可されます (ただし、ストアドファンクションには許可されません)。ストアドファンクションには、明示的または暗黙的なコミットまたはロールバックを実行するステートメントを含めることはできません。これらのステートメントのサポートは、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 が実行されます (また、そのルーチンが終了すると元に戻されます)。これにより、そのルーチンには実行中、特定のデフォルトデータベースが割り当てられます。ルーチンのデフォルトデータベース以外のデータベース内のオブジェクトへの参照は、適切なデータベース名で修飾するようにしてください。

ストアドルーチン内では許可されないステートメントの詳細は、セクション25.8「ストアドプログラムの制約」を参照してください。

MySQL インタフェースを備える言語で記述されたプログラム内からのストアドプロシージャーの呼び出しについては、セクション13.2.1「CALL ステートメント」を参照してください。

MySQL は、ルーチンが作成または変更されたときの有効な sql_mode システム変数の設定を格納し、ルーチンが実行を開始したときの現在のサーバー SQL モードには関係なく、常にそのルーチンを強制的にこの設定で実行します。

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

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

LANGUAGE 特性は、そのルーチンが記述されている言語を示します。サーバーはこの特性を無視します。SQL ルーチンのみがサポートされています。

ルーチンは、同じ入力パラメータに対して常に同じ結果を生成する場合は「決定的」と見なされ、それ以外の場合は「非決定的」と見なされます。ルーチン定義で DETERMINISTIC と NOT DETERMINISTIC のどちらも指定されていない場合、デフォルトは NOT DETERMINISTIC になります。関数が決定的であることを宣言するには、明示的に DETERMINISTIC を指定する必要があります。

ルーチンの性質の評価は、作成者の「誠実さ」に基づいています。MySQL は、DETERMINISTIC と宣言されたルーチンに非決定的な結果を生成するステートメントが含まれていないかどうかをチェックしません。ただし、ルーチンの誤った宣言は、その結果やパフォーマンスに影響を与える可能性があります。非決定的なルーチンを DETERMINISTIC として宣言すると、オプティマイザが正しくない実行計画を選択するために、予期しない結果を招くことがあります。決定的なルーチンを NONDETERMINISTIC として宣言すると、使用可能な最適化が使用されなくなるために、パフォーマンスが低下することがあります。

バイナリロギングが有効になっている場合、DETERMINISTIC 特性は、MySQL がどのルーチン定義を受け入れるかに影響を与えます。セクション25.7「ストアドプログラムバイナリロギング」を参照してください。

NOW() 関数 (または、そのシノニム) あるいは RAND() を含むルーチンは非決定的ですが、引き続きレプリケーションに対して安全である可能性があります。 NOW() の場合、バイナリログにはタイムスタンプが含まれ、正しくレプリケートされます。 RAND() もまた、ルーチンの実行中に 1 回だけ呼び出されるかぎり、正しくレプリケートされます。 (ルーチン実行タイムスタンプおよび乱数シードは、ソースとレプリカで同一の暗黙的な入力とみなすことができます。)

いくつかの特性によって、ルーチンによるデータ使用の性質に関する情報が提供されます。 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 または INVOKER のどちらかです。これは、そのルーチンがルーチンの DEFINER 句で指定されたアカウント、またはそのルーチンを呼び出すユーザーのどちらの権限を使用して実行されるかを示します。このアカウントには、そのルーチンが関連付けられているデータベースにアクセスするためのアクセス権が必要です。デフォルト値は DEFINER です。そのルーチンを呼び出すユーザーには、それに対する EXECUTE 権限が必要です。また、そのルーチンが定義者のセキュリティーコンテキストで実行される場合は、DEFINER アカウントにもその権限が必要です。

DEFINER 句は、SQL SECURITY DEFINER 特性を持つルーチンのルーチン実行時にアクセス権限を確認するときに使用される MySQL アカウントを指定します。

DEFINER 句が存在する場合、user 値は'user_name'@'host_name'、CURRENT_USER または CURRENT_USER() として指定された MySQL アカウントである必要があります。許可される user 値は、セクション25.6「ストアドオブジェクトのアクセス制御」で説明されているように、保持する権限によって異なります。ストアドルーチンのセキュリティーに関する追加情報については、そのセクションも参照してください。

DEFINER 句を省略すると、デフォルトの定義者は CREATE PROCEDURE または CREATE FUNCTION ステートメントを実行するユーザーになります。これは、明示的に DEFINER = CURRENT_USER を指定するのと同じです。

SQL SECURITY DEFINER 特性で定義されたストアドルーチンの本体内で、CURRENT_USER 関数はルーチン DEFINER 値を返します。ストアドルーチン内のユーザー監査については、セクション6.2.22「SQL ベースのアカウントアクティビティ監査」を参照してください。

mysql.user システムテーブルにリストされている MySQL アカウントの数を表示する次の手順について考えてみます:

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

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

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

データ型の不一致やオーバーフローがないかどうか割り当てがチェックされます。変換やオーバーフローの問題によって警告が発生するか、または厳密な SQL モードではエラーが発生します。
スカラー値のみを割り当てることができます。たとえば、SET x = (SELECT 1, 2) などのステートメントは無効です。
文字データ型では、CHARACTER SET が宣言に含まれている場合、指定された文字セットとそのデフォルトの照合順序が使用されます。 COLLATE 属性も存在する場合は、デフォルトの照合順序ではなく、その照合順序が使用されます。

CHARACTER SET および COLLATE が存在しない場合は、ルーチンの作成時に有効なデータベース文字セットおよび照合順序が使用されます。サーバーでデータベース文字セットおよび照合順序を使用しないようにするには、文字データパラメータに明示的な CHARACTER SET および COLLATE 属性を指定します。

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

データベース文字セットおよび照合順序は、character_set_database および collation_database システム変数の値で指定されます。詳細は、セクション10.3.3「データベース文字セットおよび照合順序」を参照してください。

PREV HOME UP NEXT