第17章 ストアドプロシージャとファンクション

目次

17.1. ストアドルーチンとグラントテーブル
17.2. ストアドルーチン構文
17.2.1. CREATE PROCEDUREおよびCREATE FUNCTION 構文
17.2.2. ALTER PROCEDURE および ALTER FUNCTION 構文
17.2.3. DROP PROCEDURE および DROP FUNCTION 構文
17.2.4. CALLステートメント構文
17.2.5. BEGIN ... END 複合ステートメント構文
17.2.6. DECLAREステートメント用構文
17.2.7. ストアドルーチン内の変数
17.2.8. 条件とハンドラ
17.2.9. カーソル
17.2.10. フローコントロール・コンストラクト
17.3. ストアドプロシージャ、ファンクション、トリガ並びにLAST_INSERT_ID()
17.4. ストアドルーチンとトリガのバイナリログ

ストアドルーチン(プロシージャとファンクション)が MySQL 5.1ではサポートされています。ストアドプロシージャはサーバが保存することができるSQLステートメントの組です。これが実行されると、クライアントは各ステートメントを発行し続ける必要がなくなり、代わりにストアドプロシージャを参照します。

MySQL中に保存されているルーチンとその利用に関する一般質問に対する答えは、項A.4. 「MySQL 5.1 FAQ ? Stored Procedures」で見ることができます。更に、ストアドルーチンに関する一般的な質問に対する答えについては、項A.6. 「MySQL 5.1 FAQ ? Stored Routines, Triggers, and Replication」をご参照ください。

ストアドルーチンが特に有用な幾つかの状況:

サーバとクライアント間の通信を減らすことができるので、ストアドルーチンは性能を向上させます。より多くの作業がサーバ側で実行され、クライアント(アプリケーション)側では、より少ない作業が実行されるので、欠点はこれがデータベースサーバ上の負荷を増やすということです。(Web サーバのような)多くのクライアントマシンに対して、1つあるいは少数のデータベースサーバによってメンテナンスされる場合、これを考慮に入れてください。

ストアドルーチンはユーザがデータベースサーバの中にファンクションライブラリーを持つことも許します。内部にこのようなデザインを許容する(例えば、クラスを使うことによって)最近のアプリケーション言語が共有している特徴です。これらのクライアントアプリケーション言語の特徴を使用することは、データベースの使用範囲の外でもプログラマーにとって有益です。

MySQLはストアドルーチンに対して、IBMのDB2にも使われているSQL:2003構文に準じています。

ストアドルーチンのMySQLへの実装は進行途中です。この章で述べたすべての構文はサポートされており、全ての制約や拡張は適切文書化されます。ストアドルーチンの使用に対する制限については、項D.1. 「ストアド ルーチンとトリガの規制」を参照してください。

ストアドルーチンのバイナリ ログ実行については、項17.4. 「ストアドルーチンとトリガのバイナリログ」を参照してください。

再帰的なストアドプロシージャは、デフォルトで無効化されていますが、max_sp_recursion_depthサーバシステム変数をゼロの値に設定することによって、サーバ上で有効化することができます。詳細については、項4.2.3. 「システム変数」 をご参照してください。

保存されたファンクションは再帰的にはなり得ません。項D.1. 「ストアド ルーチンとトリガの規制」 を参照してください。

17.1. ストアドルーチンとグラントテーブル

ストアドルーチンでは、mysqデータベース中にprocテーブルが要求されます。このテーブルはMySQL 5.1をインストールしている最中に生成されます。旧バージョンのMySQLからMySQL 5.1にアップグレードする場合、ユーザのグラントテーブルが更新され、proc テーブルが存在しているか確認してください。項4.5.4. 「mysql_upgrade ? MySQL アップグレードのテーブル チェック」 を参照してください。

サーバはストアドルーチンを生成、変更もしくは撤去するステートメントに対して、mysql.procテーブルを操作します。このテーブルの手動操作のサポートは、サーバに通知されません。

MySQL グラントシステムはストアドルーチンを以下の通り取り扱います。

  • ストアドルーチンを生成するため、CREATE ROUTINE特権が必要です。

  • ストアドルーチンの変更・撤去には、ALTER ROUTINE権限が必要です。必要な場合、この権限はルーチン生成者に自動的に与えられますが、生成者がルーチンを廃止すると、権限も消滅します。

  • ストアドルーチンを実行するため、EXECUTE権限が要求されます。必要な場合、この権限はルーチン生成者に自動的に与えられます。(生成者がルーチンを撤去すると、権限も消滅します)ルーチンのデフォルト設定SQL SECURITYDEFINERです。これは、ルーチンに関連するデータベースにアクセス可能なユーザがルーチンを実行できるようにします。

  • automatic_sp_privilegesシステム変数がゼロである場合、EXECUTEおよびALTER ROUTINE権限は自動的に供与・除去されません。

17.2. ストアドルーチン構文

ストアドルーチンはプロシージャかファンクションのいずれかです。ストアドルーチンはCREATE PROCEDUREおよびCREATE FUNCTIONステートメントを使って作成されます。プロシージャはCALLステートメントを使って起動し、アウトプット変数を使ってのみ値を返すことができます。関数(ファンクション)は(関数名を呼び出す方法を採用している)他の関数のように、ステートメントの内側から呼び出して、スカラー値を返すことができます。ストアドルーチンは他のストアドルーチンを呼び出すことができます。

ストアドプロシージャもしくはファンクションは特定データベースに関連します。これは複数の意味を含んでいます。

  • ルーチンを呼び出すと、必然的に USE db_nameが実行されます(ルーチンの実行が終了すると停止します)。ストアドルーチンの中でUSEステートメントの使用は禁止されています。

  • データベース名を使ってルーチン名を認定することができます。これは現在データベース中に含まれていないルーチンを参照するのに使用することができます。例えば、testデータベースに関連するストアドプロシージャ p またはファンクション f を呼び出すため、CALL test.p()またはtest.f() とするとができます。

  • データベースを撤去すると、それに関連する一切のストアドルーチンも撤去されます。

MySQLは、ストアドプロシージャの中に含まれるレギュラーSELECTステートメントの(カーソルまたはローカル変数なしで)の使用を可能にする非常に有用な拡張機能をサポートしています。このようなクエリーに対する結果セットは、簡単に直接クライアントに送られます。複数のSELECTステートメントは複数の結果セットを生成するので、クライアントは複数の結果セットをサポートしているMySQLクライアント・ライブラリーを使用しなければなりません。これは、クライアントは少なくとも4.1より新しいバージョンのMySQLから取得したクライアント・ライブラリーを使用しなければならないことを意味します。クライアントは接続時、CLIENT_MULTI_RESULTSオプションも特定しなければなりません。Cプログラムに対しては、mysql_real_connect()C API関数を使って実施することができます。項23.2.3.52. 「mysql_real_connect()項23.2.9. 「マルチプルステートメントを実行するC APIハンドリング」 を参照して下さい。

以下のセクションでは、ストアドプロシージャとファンクションを生成、変更、並びに起動を実行するのに使用する構文について説明します。

17.2.1. CREATE PROCEDUREおよびCREATE FUNCTION 構文

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_SPACESQLモードは、ストアドルーチンでなく、組み込まれているファンクションに適用され、IGNORE_SPACEが有効になっているか否かにかかわりなく、ルーチン名の後にスペースを含むことは常に許容されています。

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

各パラメータには、COLLATE属性は使用できないことを除けば、有効なデータタイプを使用していると宣言できます。

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

:パラメータにINOUTしくはINOUTと特定することはPROCEDUREに対してのみ有効です。(FUNCTIONパラメータは常にINパラメータと見なされます。

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

OUTパラメータもしくはINOUTパラメータは、ユーザに特定された変数を渡すことで、プロシージャが返されたときにその値を取得できます。(例については、項17.2.4. 「CALLステートメント構文」を参照してください。)保存された他のプロシージャの中からプロシージャまたはファンクションを呼び出す場合、あなたはルーチンパラメータまたはローカルルーチン変数を、INパラメータまたはINOUTパラメータとして渡すことができます。

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

routine_bodyは有効なSQLプロシージャステートメントから成り立っています。これをSELECTまたはINSERTのような簡単なステートメントもしくはBEGINENDを使って書かれた複合ステートメントにすることができます。複合ステートメントの構文については、項17.2.5. 「BEGIN ... END 複合ステートメント構文」を参照してください。複合ステートメントには、宣言、ループ並びにその他の制御構造ステートメントを含むことができます。これらのステートメントに対する構文については、この章の後半部分で説明します。例えば、項17.2.6. 「DECLAREステートメント用構文」並びに項17.2.10. 「フローコントロール・コンストラクト」を参照してください。いくつかのステートメントはストアドルーチン内で使用することはできません(項D.1. 「ストアド ルーチンとトリガの規制」を参照してください)

ルーチンが生成されたとき、MySQLは有効化されていたsql_modeシステム変数設定を保存し、現サーバのSQL モードに関係なく、必ずこの設定でルーチンを実行します。

CREATE FUNCTIONステートメントはUDF(ユーザ定義機能)をサポートするため、旧バージョンのMySQLで使用されています。項25.3. 「Adding New Functions to MySQL」を参照してください。UDFは保存されたファンクションが存在していてもサポートされ続けます。UDFは記憶された外部機能であると見なすことができます。ただし、保存されたファンクションは自身の名称スペースをUDFと共有していることに注意してください。サーバが異なった種類のファンクションに対するリファレンスを解釈する方法を述べた規則については、項8.2.4. 「構文解析と解像度のファンクション名」を参照してください。

プロシージャあるいはファンクションは、それが同じインプットパラメータに対して常に同じ結果をもたらす場合、「決定論的」であるとみなされるが、同じ結果をもたらさない場合には、「非決定論的」であるとみなされます。ルーチンの定義にDETERMINISTICNOT 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 5.1.8.で追加されました。

user 値を附与する場合、それを ' user_name'@' host_name ' フォーマット(GRANT ステートメントに使用したと同じフォーマット)の中にあるMySQLアカウントにすべきです。user_name の値とhost_name の値が両方共必要です。CURRENT_USERCURRENT_USER()として附与することもできます。DEFINERの初期値はCREATE PROCEDUREもしくはCREATE FUNCTIONもしくはステートメントを実行するユーザです。(これはDEFINER = CURRENT_USERと同じです。)

DEFINER節を特定する場合、SUPER権限を保持していない限り、自分の値を除くいかなるアカウントにも値をセットすることはできません。これらの規則はDEFINER ユーザの法定値を定義します。

  • SUPER権限を保持していない場合、文字によるか、CURRENT_USERを使って規定されているuser法定値のみがユーザのアカウントとなります。デファイナーを別のアカウントに設定することはできません。

  • SUPER権限を保持している場合、構文的に規定した有効なアナウントネームを特定することができます。そのアカウントが実在しない場合、警告が生成されます。

    架空のDEFINER値を使ってルーチンを生成させることは可能ですが、ルーチンをDEFINER権限を使って実行すると、エラーが発生します。しかし定義者は実行中には存在しません。

ルーチンを起動すると、必然的に USE db_nameが実行(ルーチンの実行が終わると自然に停止)されます。ストアドルーチン内でUSEステートメントを使用することは禁止されています。

サーバはルーチンパラメータのデータタイプまたはファンクションリターン値を以下の通り使用します:これらの規則はDECLAREステートメント(項17.2.7.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とのインターフェースを持つ言語で書かれたプログラムの中からストアドプロシージャを起動する方法ついては、項17.2.4. 「CALLステートメント構文」を参照してください。

17.2.2. ALTER PROCEDURE および ALTER FUNCTION 構文

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

このステートメントはストアドプロシージャもしくはファンクションの特徴を変更するのに使用することができます。ルーチンに対して、ALTER ROUTINE 権限を持っていなければなりません。(この権限はルーチン生成者に自動的に供与されます。)バイナリ ログが有効化されている場合、ALTER FUNCTIONステートメントにもSUPER権限が項17.4. 「ストアドルーチンとトリガのバイナリログ」に述べた通り、要求されます。

ALTER PROCEDUREまたはALTER FUNCTIONステートメントに複数の変更を施すことができます。

17.2.3. DROP PROCEDURE および DROP FUNCTION 構文

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

このステートメントはストアドプロシージャもしくはファンクションを撤去するのに使用されます。即ち、特定されたルーチンがサーバから撤去されます。ルーチンに対して、ALTER ROUTINE 権限を持っていなければなりません。(この権限はルーチン生成者に自動的に供与されます。)

IF EXISTS節はMySQLの拡張子です。それは、プロシージャもしくはファンクションが存在しない場合にエラーが発生するのを阻止します。SHOW WARNINGSを使用して閲覧することができる警告が生成されます。

17.2.4. CALLステートメント構文

CALL sp_name([parameter[,...]])
CALL sp_name[()]

CALLステートメントによって、CREATE PROCEDURE.を使用して以前に定義したプロシージャが起動されます。

CALLは、OUTまたは INOUTパラメータであると宣言されているパラメータを使って、値を発信側に返すことができます。またこれはROW_COUNT() ファンクションをコールする事で クライアントプログラムがSQLレベルで取得する事ができ、またmysql_affected_rows() C API 機能をコールする事によって Cから取得する事ができる、影響を受けた行を「返します」。

MySQL 5.1.13では現在、アーギュメントを取り込んでいないストアドプロシージャに、かっことをつけることなく取り出すことができるようになっています。即ち、CALL p()CALL pは等価です。

OUTまたはINOUTパラメータを使って、値をプロシージャから戻すには、パラメータを、ユーザ変数を使って渡し、プロシージャが戻した後、変数の値をチェックします。ユーザが保存された他のプロシージャの中からプロシージャまたはファンクションを呼び出す場合、ユーザはルーチンパラメータまたはローカルルーチン変数を、INパラメータまたはINOUTパラメータとして渡すことができます。INOUTパラメータの場合、それをプロシージャに渡す前に値を初期化してください。以下のプロシージャには、そのプロシージャがサーバの現バージョンにセットするOUT パラメータおよびそのプロシージャがその現在値から1だけ増やすINOUT値が含まれています。

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

プロシージャを呼び出す前に、INOUTパラメータとして渡すべき変数を初期化してください。プロシージャを呼び出した後には、2つの変数はセットまたは改良されています。

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+-----------------+------------+
| @version        | @increment |
+-----------------+------------+
| 5.1.12-beta-log | 11         | 
+-----------------+------------+

CALLSQLステートメントを使用するCプログラムを書き込んで、結果セットを生成させるストアドプロシージャを実行する場合、ユーザはmysql_real_connect()を呼び出す時、CLIENT_MULTI_STATEMENTSをセットすることによって、CLIENT_MULTI_RESULTSフラグを明確または暗黙にセットする要があります。このようなストアドプロシージャはそれぞれ、複数の結果を生成します。プロシージャ内で実行されたステートメントによって返された結果のセット、並びに呼び出しのステータスを示す結果。CALLステートメントの結果を処理するには、mysql_next_result()を呼び出すループを使用してさらに多くの結果があるか特定してください。(例については、項23.2.9. 「マルチプルステートメントを実行するC APIハンドリング」を参照してください。)

MySQLインターフェースを提供する言語で書かれたプログラムに対して、OUTパラメータやINOUTパラメータの結果をCALLステートメントから直接複製するネイティブ方法は存在しません。パラメータ値を取得するには、CALLステートメント中のプロシージャに、ユーザが規定した変数を渡し、その後、SELECTステートメントを実行して、変数値を含む結果セットを生成させてください。以下の例は、2つのOUTパラメータを含むストアドプロシージャ p1に対するテクニック(エラーチェックを除く)を例示したものです。

mysql_query(mysql, "CALL p1(@param1, @param2)");
mysql_query(mysql, "SELECT @param1, @param2");
result = mysql_store_result(mysql);
row = mysql_fetch_row(result);
mysql_free_result(result);

先行するコードを実行した後、row[0]並びにrow[1]に、@param1および@param2の値をそれぞれ含ませます。

INOUTパラメータを扱う為に、ユーザ変数をその値にセットしてプロシージャに渡すCALLの前に、ステートメントを実行してください。

17.2.5. BEGIN ... END 複合ステートメント構文

[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN ... END構文は、ストアドルーチンおよびトリガの中に表示することができる複合ステートメントを書くのに使用します。複合ステートメントには、BEGINなるキーワードとENDなるキーワードによって封じ込める方法を使って、複数のステートメントを含めることができます。statement_listは複数のステートメントのリストを意味します。 statement_list中の各ステートメントは、セミコロン(;)ステートメントデリミタで終了させなければなりません。statement_listはオプションであることに注意してください。これは、空の合成ステートメント (BEGIN END)は有効であることを意味します。

複数のステートメントを使用する場合、クライアントには;ステートメントデリミタを含むステートメント文字列を送れることが要求されます。これは、mysqlコマンド・ライン クライアントの中で、delimiterコマンドを使って扱かわれます。ステートメントの最後のデリミタである ; を変更すると、(例えば //に変更) ; をルーチンボディの中で使用する事ができます。(例については、項17.2.1. 「CREATE PROCEDUREおよびCREATE FUNCTION 構文」を参照してください。)

複合ステートメントにはラベルを貼ることができます。begin_label がなければ、end_label を付与する事はできません。両方が存在する場合、これらは同じでなければなりません。

オプションの[NOT] ATOMIC節はまだサポートされていません。これは、トランザクションのセーブポイントがインストラクションブロックの始めにセットされていなく、この文脈中で使用するBEGIN節は現在のトランザクションに対して効果が無いことを意味します。

17.2.6. DECLAREステートメント用構文

DECLAREステートメントはルーチンに付属する様々なアイテムを定義するのに使用します。

SIGNALステートメント並びにRESIGNALステートメントは現在サポートされていません。

DECLAREBEGIN ...の内部でのみ許容されています。ENDはステートメントを合成し、他のステートメントの前にその始めがこなくてはなりません。

宣言は決まったオーダーを遵守しなければなりません。カーソルは、ハンドラを宣言する前に宣言されなければなりません。また、変数と条件はカーソルかハンドラのいずれかを宣言する前に宣言されなければなりません。

17.2.7. ストアドルーチン内の変数

ユーザはルーチンの中で変数を宣言して使用することができます。

17.2.7.1. DECLARE ローカル変数

DECLARE var_name[,...] type [DEFAULT value]

このステートメントはローカル変数を宣言するのに使用します。DEFAULT節を含ませて、その変数に対するデフォルト値を提供してください。その値は表現として規定することができます。それは定数である必要はありません。DEFAULT節が含まれていない場合、初期値はNULLとなります。

ローカル変数は、データタイプとオーバーフローチェックに関して、ルーチンパラメータと同じように処理されます。項17.2.1. 「CREATE PROCEDUREおよびCREATE FUNCTION 構文」 を参照してください。

ローカル変数の範囲は、それが宣言されている BEGIN ... END ブロックの範囲内です。 変数は、同じ名称を使って変数を宣言するこれらのブロックを除く、宣言ブロック内の入れ子を作っているブロックの中に引用することができます。

17.2.7.2. 変数 SET ステートメント

SET var_name = expr [, var_name = expr] ...

ストアドルーチン中のSETステートメントは一般SETステートメントの拡張されたバージョンです。引用された変数は、ルーチンもしくはグローバル システム変数の内側に宣言されたものにすることができます。

ストアドルーチン中のSETステートメントは、既存のSET構文の一部として施行されます。これは、異なった変数タイプ(ローカルに宣言された変数およびグローバル変数並びにセッション・サーバ変数)を混在させることができるSET a=x, b=y, ...の拡張された構文を容認します。これは、ローカル変数の組み合わせおよびシステム変数に対してだけ意味を持つ幾つかのオプションも認めます。この場合、オプションは認識されますが無視されます。

17.2.7.3. SELECT ... INTO ステートメント

SELECT col_name[,...] INTO var_name[,...] table_expr

このSELECT 構文は選択されたカラムを直接変数の中に保存します。従って、1本の横列のみ取り出すことが許されています。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

ユーザ変数名に対して、大文字小文字を区別されません。項8.4. 「ユーザによって定義された変数」 を参照してください。

重要SQL変数の名称をカラム名称と同じにすべきではありあません。SELECT ... INTO のようなSQLステートメントが、カラムとローカル変数のリファレンスを同名で含んでいると、現在のMySQLはそのリファレンスを変数名だと認識します。例えば、次のステートメントの中では、xnamexname.カラムではなく、変数 カラム対するリファレンスと解釈されます:

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
    
    SELECT xname,id INTO newname,xid 
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

このプロシージャを呼び出すとき、newname変数は、table1.xname カラムに関係なく、値'bob'.を返します。

項D.1. 「ストアド ルーチンとトリガの規制」も参照してください。

17.2.8. 条件とハンドラ

条件によっては、特別な扱いが求められます。これらの条件は、エラー並びにルーチンの内側で行われている一般フロー制御に関連している場合もあります。

17.2.8.1. DECLARE 条件

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

このステートメントは特別な扱いが必要な条件を規定します。それには規定されたエラーの条件を含む名称が関連付けられます。その名称は後にDECLARE HANDLERステートメントの中で使われます。項17.2.8.2. 「DECLARE ハンドラ」 を参照してください。

condition_valueをSQLSTATE値もしくはMySQLエラーコードにすることができます。

17.2.8.2. DECLARE ハンドラ

DECLARE handler_type HANDLER FOR condition_value[,...] statement

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

DECLARE ... HANDLERステートメントは各々が複数の条件で処理することができるハンドラを規定します。もし、これらの条件の1つが起った場合、ステートメントが実行されます。この場合、ステートメントを単純なものにすることができます。 (例えば、 SET var_name = value), もしくは、BEGINEND を使って書いた複合ステートメントにすることができます。(項17.2.5. 「BEGIN ... END 複合ステートメント構文」参照)

CONTINUEハンドラに対して、現ルーチンの実行が、ハンドラステートメントの実行の後に続きます。EXIT ハンドラに関しては、ハンドラが宣言された BEGIN ... END コンパウンドステートメントの中で実行が終了します。(これは、条件が内側にあるブロックの中に発生する場合でも同じです。)UNDO ハンドラタイプのステートメントはまだサポートされていません。

ハンドラがまだ宣言されていない条件がしている場合、デフォルトアクションはEXITとなります。

A condition_valueは以下の値のいずれかにすることができます:

  • SQLSTATE値もしくはMySQLエラーコード。

  • 既に DECLARE ... CONDITIONで指定されている条件名。項17.2.8.1. 「DECLARE 条件」 を参照してください。

  • SQLWARNING01で始まる全てのSQLSTATEコードに対する速記文字です。

  • NOT FOUND02で始まる全てのSQLSTATEコードに対する速記文字です。

  • SQLEXCEPTIONSQLWARNINGまたはNOT FOUNDによって捕らえられなかった全てのSQLSTATEコードの速記文字です。

例:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

その例は、重複キーエラーに対して発生するSQLSTATE 23000を持つハンドラに関連するものです。@x は 3です。 MySQLがプロシージャの最後まで実行されたことを示しています。もしDECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;ラインが存在していなかった場合、 MySQLは(EXIT) のデフォルトパスを、2番目のINSERTPRIMARY KEY制限によって失敗したとき取り、そしてSELECT @x2を返しています。

条件を無視したい場合、ユーザはそれに対して、CONTINUEハンドラと宣言して、それを空のブロックと関連させることができます。例:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

17.2.9. カーソル

ストアドプロシージャとファンクションの内側に単純なカーソルがサポートされています。その構文は埋め込まれているSQLの中のものと同じです。.カーソルは現在、アセンシティブ、読み取り専用、そしてスクロール機能はついていません。アセンシテブはサーバがその結果テーブルの複製を作ることができるか、できないとを意味します。

カーソルは、ハンドラを宣言する前に宣言されなければなりません。また、変数と条件はカーソルかハンドラのいずれかを宣言する前に宣言されなければなりません。

例:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

17.2.9.1. 宣言用カーソル

DECLARE cursor_name CURSOR FOR select_statement

このステートメントはカーソルを宣言します。複数のカーソルを一つのルーチンの中に宣言することができますが、各カーソルは附与されたブロックの中に、ユニークな名称を持っていなければなりません。

SELECTステートメントにINTO節を含めることはできません。

17.2.9.2. カーソルOPENステートメント

OPEN cursor_name

このステートメントは以前に宣言したカーソルを開きます。

17.2.9.3. カーソルFETCHステートメント

FETCH cursor_name INTO var_name [, var_name] ...

このステートメントは、規定されたオープンカーソルを使って、次の行(存在している場合)を取り込んで、ここにカーソルポインタを進めます。

行が得られなくなると、SQLSTATE値02000を使用したNo Data条件が発生します。この条件を検出するため、ハンドラをセットすることができます。例が項17.2.9. 「カーソル」で紹介されています。

17.2.9.4. カーソル CLOSE ステートメント

CLOSE cursor_name

このステートメントは以前開いたカーソルを閉じます。

明らかに閉じていない場合、それは、それが宣言された複合ステートメントの最後で閉じられます。

17.2.10. フローコントロール・コンストラクト

IFCASE, LOOPWHILEREPLACE ITERATEおよびLEAVE コンストラクトは完全に実装されます。

これらのコンストラクトの多くには、以下セクションの文法仕様に示すようなその他のステートメントが含まれています。このようなコンストラクトを入れ子とすることができます。例えば、IFステートメントにはそれ自体がCASEステートメントを含むWHILEループが含まれているかもしれません。

FORループは現在サポートされていません。

17.2.10.1. IF ステートメント

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IFは基本条件コンストラクトを施行します。search_conditionが真の場合、該当するSQLステートメントが実行されます。search_conditionが合致しない場合、ELSE節内のステートメントリストが実行されます。各statement_listは複数のステートメントから成り立っています。

:ここで述べたIFステートメントとは異なるIFファンクションもあります。項11.2. 「制御フロー関数」 を参照してください。

17.2.10.2. CASEステートメント

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

または

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

ストアドルーチンに対するCASEステートメントは複雑な条件コンストラクトを実装します。search_conditionが真の場合、該当するSQLステートメントが実行されます。検索条件が合致しない場合、ELSE節内のステートメントリストが実行されます。各statement_listは複数のステートメントから成り立っています。

:ストアドルーチンの内部で使用する目的でここに示したCASEステートメントの構文は、 (項11.2. 「制御フロー関数」)で説明されたCASE表現とは若干異なっています。CASE ステートメントにELSE NULL節を含めることはできません。これを持たせると、それは、ENDの代わりにEND CASEを使って消去されます。

17.2.10.3. LOOP ステートメント

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOPは単純なループコンストラクトを実装します。これによって、複数のステートメントからなるステートメントリストを繰り返して使用することが可能になります。ステートメントのループ内での実行は、ループが閉じられるまで繰り返されます。これは一般的にLEAVEステートメントを使って達成されます。

LOOPステートメントにはラベルを貼ることができます。 begin_labelも存在していない限り、end_labelを附与することはできません。両方が存在する場合、これらは同じでなければなりません。

17.2.10.4. LEAVE ステートメント

LEAVE label

このステートメントは、ラベルを貼ったフローコントロールコンストラクトを閉じるために使用します。それは、BEGIN ......ENDもしくはループコンストラクト(LOOPREPEATWHILE)の中で使うことができます。

17.2.10.5. ITERATE ステートメント

ITERATE label

ITERATELOOPステートメント、REPEATステートメント並びにWHILEステートメントの中にだけ現れます。ITERATEは「再びループを実行」を意味します。

例:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

17.2.10.6. REPEAT ステートメント

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEATステートメント中のステートメントリストは、search_conditionが真になるまで繰り返されます。このようにして、REPEAT は常に、少なくとも1回入力ループを書き込みます。 statement_list は複数のステートメントから成り立っています。

REPEATステートメントにはラベルを貼ることができます。begin_label がなければ、end_label を与える事はできません。両方が存在する場合、これらは同じでなければなりません。

例:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

17.2.10.7. WHILE ステートメント

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILEステートメント中のステートメントリストは、search_conditionが真になるまで繰り返されます。 statement_listは複数のステートメントから成り立っています。

CHECK WHILEステートメントにはラベルを貼ることができます。 begin_labelも存在していない限り、end_labelを附与することはできません。両方が存在する場合、これらは同じでなければなりません。

例:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END

17.3. ストアドプロシージャ、ファンクション、トリガ並びにLAST_INSERT_ID()

ストアドルーチン(プロシージャまたはファンクション) もしくはトリガの中で、LAST_INSERT_ID()の値は同じ方法で、ステートメントがオブジェクトのこれらの種類の本体の外側で実行させるように変更されます。(項11.10.3. 「情報関数」を参照してください)。ストアドルーチンまたはトリガの以下のステートメントに基づいて現れるLAST_INSERT_ID()の値に与える効果は、ルーチンの種類によって変わります。

  • ストアドプロシージャが、LAST_INSERT_ID()の値を変えるステートメントを実行する場合、変更された値はプロシージャコールに従うステートメントによって現れます。

  • 値を変える保存されたファンクションとトリガの場合、ファンクションまたはトリガが終わる時、値が元に戻るので、以下のステートメントには変更された値は現れません。

17.4. ストアドルーチンとトリガのバイナリログ

バイナリログには、データベースの中身を修正するSQLステートメントに関する情報が含まれていす。この情報は改良を説明する「イベント」の形で記憶されます。バイナリログは2つの重要な目的を持っています。

  • 複製の場合、マスターサーバはそのバイナリログに含まれているエベントを自分のスレーブに送ります。これによって、これらのイベントが実行されて、マスターが行ったと同じデータ変更が行われます。項5.5. 「レプリケーションの実装」 を参照してください。

  • ある種のデータリカバリには、バイナリログの使用が必要です。バックアップファイルが修復された後、バックアップ後に記録されたバイナリログ中のイベントは再実行されます。これらのイベントは、データベースをバックアップの点からデートまで持って行きます。項4.9.2.2. 「バックアップ ファイルでリカバリ」 を参照してください。

このセクションで、MySQL 5.1がストアドルーチン(プロシージャとファンクション)およびトリガの為のバイナリログを取り扱う方法について説明します。ここで、その実装がストアドルーチンの使用上に置かれる現在の条件も説明し、これらの条件が必要とされる理由に関する追加情報を提供します。

一般に、ここで述べた問題はバイナリログがSQLステートメントレベルにおいて起こる時生じます。ユーザが行をベースとするバイナリログを使用する場合、ログには、SQLステートメントを実行した結果として個別の行に施した変更が含まれます。行をベースとするログに関する一般情報については、 項5.1.2. 「レプリケーション フォーマット」 を参照してください。

行をベースとするログを使用する時、ストアドルーチンとトリガの定義がステートメントとして複製されます。ルーチンやトリガを実行する時、行に施した変更は登録されますが、これらを実行するステートメントは登録されません。ストアドプロシージャに対して、これはCALLステートメントは登録されないことを意味します。保存されたファンクションに対して、ファンクション内で行に施した変更は登録されますが、ファンクション起動は登録されません。トリガの場合、トリガが行に対して行った変更は登録されます。スレーブ側では、行の変更のみ現れ、ルーチンまたはトリガ起動は現れません。

特に注記しない限り、ここに示した備考はユーザが既に--log-binオプションを使ってサーバを立ち上げることによって、バイナリログを有効化しているものと見なします。(項4.11.4. 「バイナリ ログ」を参照してください。)バイナリログが有効化されていない場合、複製は不可能なばかりでなく、データリカバリ用に、バイナリログも利用できません。

MySQL5.1は以下の通り総括することができます:これらの条件はストアドプロシージャには適用されず、これらはバイナリログが有効化されない限り、適用されません。

  • 保存されたファンクションを生成もしくは変更するには、ユーザは通常要求されるCREATE ROUTINE権限もしくはALTER ROUTINE権限に加え、SUPER権限を保持していなければなりません。

  • 保存されたファンクションを生成する時、それが決定論的なものであるか、データを改良しないものであるかを宣言しなければなりません。これを怠ると、データリレカバリやデータの複製が安全にできなくなる場合があります。

  • ファンクション生成に対する(SUPER権限を持たなければならず、決定論的か、データを修正しないかの別を宣言しなければならない)前の規制を緩和するには、グローバル log_bin_trust_function_creatorsシステム変数を1に設定します。 デフォルトで、これは0に設定されていますが、ユーザはこのようにして変更することができます。

    mysql> SET GLOBAL log_bin_trust_function_creators = 1;
    

    この変数を、サーバを立ち上げる時--log-bin-trust-function-creators=1オプションを使って設定することもできます。

    バイナリログが有効化されていない場合、log_bin_trust_function_creators は適用されず、ファンクション生成に対してSUPERは要求されません。

トリガは保存されたファンクションと同等であるため、ファンクションに関する前の備考は、以下の場合を除き、トリガには適用されません。CREATE TRIGGERには、オプションの DETERMINISTIC 特徴は含まれていないので、トリガは常に決定論的であると見なします。しかし、この仮定は場合によっては無効です。例えば、UUID()機能は非決定論的です(複製されません)。このような機能のトリガ中での使用に関して、注意すべきです。

CREATE TRIGGERトリガはテーブルを更新することができるので、ユーザがTRIGGER権限(MySQL 5.1.6より前の版ではSUPER)を持っておらず、 log_bin_trust_function_creators が0でる場合、保存されたファンクションに起こるこれらと同等なエラーメッセージが発生します。(スレーブ側では、スレーブはトリガDEFINER属性を使って、どのユーザがトリガ生成者であるか査定します。)

以下のディスカッションで、ログの実装とその意味に関する追加詳細を提供します。このディスカッションは最初のアイテムを除き、ステートメントをベースとするログだけを対象とし、行をベースとするログには適用されません。CREATEステートメントおよびDROPステートメントはログモードと関係なく、ステートメントとして登録されます。

  • サーバはCREATE PROCEDURECREATE FUNCTIONALTER PROCEDURE, ALTER FUNCTIONDROP PROCEDUREおよびDROP FUNCTION ステートメントをバイナリログに書き込みます。

  • 保存されたファンクションの利用は、ファンクションがデータを変更し、これ以外登録しないステートメントの中に起こる場合、SELECTステートメントとして登録されます。これによって、未登録ステートメントの中で保存されたファンクションを使用したことにより、データの変更が複製されない問題が防止されます。例えば、SELECT ステートメントはバイナリログに書き込まれないが、SELECTは変更を施す保存されたファンクションを使用しなければならならない場合があります。これを扱うため、SELECT func_name())ステートメントは、あるファンクションが変更を実行する時、バイナリログに書き込まれます。以下のステートメントがマスターの上で実行されると仮定すると:

    CREATE FUNCTION f1(a INT) RETURNS INT
    BEGIN
      IF (a < 3) THEN 
        INSERT INTO t2 VALUES (a);
      END IF;
    END;
    
    CREATE TABLE t1 (a INT);
    INSERT INTO t1 VALUES (1),(2),(3);
    
    SELECT f1(a) FROM t1;
    

    SELECT ステートメントが実行される時、ファンクションf1() は三回起動されます。これらの起動の内、2回の起動で、行を挿入し、MySQLはそれらの各々に対してSELECTステートメントを登録します。即ち、MySQLは以下のステートメントをバイナリログに書き込みます。

    SELECT f1(1);
    SELECT f1(2);
    

    サーバは、ファンクションがエラーを引き起こすストアドプロシージャを取り出す時、保存されたファンクションの取り出しに対するSELECTステートメントも登録します。この場合、サーバは、SELECT ステートメントを期待エラーコードと一緒にログに書き込みます。スレーブに同じエラーが発生しても、それは期待された結果で、複製は継続されます。さもないと、複製はストップします。

    注:MySQL 5.1.7の前に、DO func_name()として登録されたこれらのSELECT func_name()ステートメントが見えます。SELECTに対する変更は、DOを使用してのエラーコードチェッキングに十分な管理が得られないことが判明した結果行われています。

  • ファンクションによって実行されたステートメントを除く、保存されたファンクションの利用に対する登録には、複製に対するセキュリティー問題が関与します。当該問題は、以下からなる2つの要因によって起こります。

    • ファンクションがマスタサーバとスレーブサーバ上にある異なった実行パスに従うことは可能です。

    • スレーブサーバ上で実行されたステートメントはフル権限を持つスレーブSQLスレッドによって処理されます。

    ユーザはファンクションを生成させるCREATE ROUTINE権限を持っていなければならないが、そのユーザは、フル権限を持つSQLスレッドによって処理されるスレーブ上でのみ実行される危険なステートメントを含むファンクションを書き込むことができます。例えば、マスタサーバとスレーブサーバがそれぞれ1と2のID値を持っている場合、マスタサーバ上のユーザは安全でない関数unsafe_func()を以下の通り生成して取り出すことができる場合があります。

    mysql> delimiter //
    mysql> CREATE FUNCTION unsafe_func () RETURNS INT
        -> BEGIN
        ->   IF @@server_id=2 THEN dangerous_statement; END IF;
        ->   RETURN 1;
        -> END;
        -> //
    mysql> delimiter ;
    mysql> INSERT INTO t VALUES(unsafe_func());
    

    CREATE FUNCTIONステートメントおよびINSERTステートメントはバイナリログに書き込まれるので、スレーブサーバはそれらを実行します。スレーブSQLスレッドはフル権限を持っているので、それは危険なステートメントを実行します。このようにして、ファンクションの取り出しはマスタとスレーブに異なった効果を与え、複製は安全でなくなります。

    バイナリログを有効化したサーバに対するこの危険からサーバを守るには、保存されたファンクションの生成者は、要求された通常のCREATE ROUTINE権限に加え、SUPER権限も持っていなければなりません。同様に、ALTER FUNCTIONを使用するため、ユーザはALTER ROUTINE権限に加え、SUPER権限を持っていなければなりません。SUPER権限がないと、エラーが起こります:

    ERROR 1419 (HY000): You do not have the SUPER privilege and
    binary logging is enabled (you *might* want to use the less safe
    log_bin_trust_function_creators variable)
    

    ファンクションの生成者にSUPER権限を持つよう要求したくない場合(例えば、ユーザのシステム上にCREATE ROUTINE権限を持つすべてのユーザが経験豊かなアプリケーションデベロッパーである場合、グローバルlog_bin_trust_function_creatorsシステム変数を1にセットしてください。この場合、ユーザはこの値を--log-bin-trust-function-creators=1 オプションを使って、サーバを立ち上げる時セットすることができます。バイナリログが有効化されていない場合、 log_bin_trust_function_creatorsは適用されず、ファンクション生成に対してSUPERは要求されません。

  • 更新を実行するファンクションが非決定論的ある場合、それは反復可能ではありません。これは2つの望ましくない効果をもたらす場合があります。

    • それがスレーブをマスタと違ったものにします。

    • 修復されたデータが元のデータと異なります。

    これらの問題を処理するため、MySQLは以下の要件を実施しています:マスタサーバ上で、ユーザがファンクションに対して決定論的か、データを修正しないかの別を宣言しない限り、ファンクションの生成と変更は拒否されます。以下からなるファンクション特性の2つのセットが適用されます:

    • DETERMINISTIC特性とNOT DETERMINISTIC特性は、あるインプットに対して、いつも同じ結果を生成するか否かを示します。いずれかの特性を附与しない場合、デフォルト設定はNOT DETERMINISTICとなります。ファンクションが決定論的であると宣言するには、DETERMINISTIC を明確に規定しなければなりません。

      NOW() 関数(またはその同義語)またはRAND() を使用しても、必ずしもファンクションが決定論的になるとは限りません。NOW()の場合、バイナリログはタイムスタンプを含み、正しく複製されます。RAND()はファンクション内で1回呼び出した場合のみ、正しく複製されます。(ファンクション実行のタイムスタンプと乱数種を、マスタとスレーブ上で同等であるとする暗黙のインプットとみなすことができます。)

      SYSDATE() はバイナリログの中でのタイムスタンプによって影響されないので、ステートメントをベースとするログが使われる場合、それは、ストアドルーチンを非決定論的になるようにします。行をベースとするログが使用されるか、サーバを--sysdate-is-nowオプション使って立ち上げ、SYSDATE()NOW()と別名とする場合、これは起こりません。

    • CONTAINS SQL, NO SQLREADS SQL DATAおよびMODIFIES SQL DATA特徴はデータの読み取りか、書き込みかに関する情報を提供します。NO SQLまたはREADS SQL DATAは、特徴が附与されていない場合、ファンクションはデータを変更しないが、デフォルト設定はCONTAINS SQLとなっているので、ユーザはこれらの中からいずれか1つを選んで明確に規定しなければなりません。

    デフォルト設定によってCREATE FUNCTIONステートメントを容認させるには、ユーザは、DETERMINISTICもしくはNO SQLREADS SQL DATAかの中から1つを選んで明確に特定しなければなりません。これを怠ると、エラーが発生します:

    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
    or READS SQL DATA in its declaration and binary logging is enabled
    (you *might* want to use the less safe log_bin_trust_function_creators
    variable)
    

    log_bin_trust_function_creatorsを1にセットした場合、ファンクションを決定論的であるようにするか、データを修正しないにすべき要件は撤去されます。

    ファンクションの性質は、生成者の「誠意」に基づき評価されます。MySQLはファンクションが宣言したDETERMINISTICが非決定論的結果結果を生成しないことがないかチェックしません。

  • ストアドプロシージャの呼び出しはCALL レベルでなく、ステートメントレベルで登録されます。即ち、サーバはCALLステートメントを登録せず、実際に実行されたプロシージャ中にこれらのステートメントを登録します。結果として、マスタサーバに起こったと同じ変更がスレーブサーバに見られます。これは、異なった実行パス上に異なった機械を持つプロシージャから結果が得られる恐れがある問題を提供します。

    一般に、ストアドプロシージャ内の実行されたステートメントは、独立ファッションで実行すべきでステートメントに適用されたと同じ規則を使ってバイナリログに書き込まれます。プロシージャステートメントを登録する時、プロシージャ内でのステートメントの実行は、非プロシージャコンテキスト内での実行と全く同じではないので、注意が必要です。

    • 登録すべきステートメントにはローカルプロシージャ変数に対するリファレンスを含めなければならない場合があります。これらの変数はストアドプロシージャコンテキストの外側に存在しないので、当該変数を引用したステートメントを文章で登録することができません。これにも係わらず、ローカル変数に対する各リファレンスはログを目的として、これに置き換えされます:

      NAME_CONST(var_name, var_value)
      

      var_name ローカル変数の名称で、var_valueはステートメントを登録する時、その変数が持つ値を示す定数です。NAME_CONST()var_valueおよび「var_name」のnameの値を持っています。このようにして、ユーザこのファンクションを直接起動する場合、このような結果が得られます。

      mysql> SELECT NAME_CONST('myname', 14);
      +--------+
      | myname |
      +--------+
      |     14 |
      +--------+
      

      NAME_CONST() )は、登録された独立ステートメントがストアドプロシージャの中でマスタ上で実行された元のステートメントと同じ効果を持つように、スレーブ上で実行されることを許容します。

    • 登録すべきステートメントにはユーザが規定した変数に対するリファレンスを含めなければならない場合がります。これを扱うため、MySQLはSET ステートメントをバイナリログに書き込んで、マスタ上にあると同じ値を持つスレーブ上にその変数が存在することを確認します。例えば、ステートメントが変数@my_varを引用する場合、そのステートメントは以下のステートメントによって、バイナリログの中で先行します。この場合、valueはマスタ上@my_varの値です。

      SET @my_var = value;
      
    • プロシージャの呼び出しは、コミットされているもしくはロールバックトランザクション中で発生します。以前には、CALLステートメントは、それらがロールバックトランザクション中に発生した場合も、登録されました。MySQL 5.0.12より、プロシージャ実行のトランザクションが正しく複製されるように、トランザクションコンテキストが考慮されます。即ち、サーバは、これらのステートメントを、実際に実行し、データを修正するプロシージャ中に登録し更に、BEGIN, COMMITステートメントおよびROLLBACK ステートメントも必要に応じて登録します。例えば、プロシージャがトランザクションテーブルだけを更新し、ロールバックされる取引の中で実行される場合、これらの更新は登録されません。プロシージャがコミットされたトランザクション中で起こる場合、BEGINステートメントとCOMMITステートメントはその更新を使って登録されます。ロールバックトランザクション中で実行されるプロシージャに対して、そのステートメントは、取引が独立してで実行された場合に適用されたと同じ規則を使って登録されます。

      • トランザクションテーブルの更新は登録されません。

      • 非トランザクションテーブルの更新は、ロールバックがこれらをキャンセルしないので、登録されます。

      • トランザクションテーブルと非トランザクションテーブルを混ぜたものの更新は、スレーブがマスタ上で行われたと同じ変更とロールバック行うように、 BEGINROLLBACKの間に登録されます。

  • ストアドプロシージャの呼び出しは、保存されたファンクション中から呼び出す場合、ステートメントレベルのバイナリログに書き込まれません。この場合、登録される唯一つのものは、(それが登録されたステートメントの中で起こる場合)機能を取り出すステートメントまたは(それが登録されていないステートメントの中で起こる場合)DOステートメントです。よって、プロシージャを呼び出す保存されたファンクションの利用には、プロシージャそれ自体が安全でない限り、注意してください。

アダルトレンタルサーバー