Tag Archives: Log_bin_trust_function_creators

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

1) ルーチンを呼び出すと、必然的に USE db_nameが実行されます(ルーチンの実行が終了すると停止します)。
ストアドルーチンの中でUSEステートメントの使用は禁止されています。
2) データベース名を使ってルーチン名を認定することができます。これは現在データベース中に含まれていない
ルーチンを参照するのに使用することができます。例えば、testデータベースに関連するストアドプロシージャ p
またはファンクション f を呼び出すため、CALL test.p()またはtest.f() とするとができます
3) データベースを撤去すると、それに関連する一切のストアドルーチンも撤去されます。

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

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

複数のクライアントアプリケーションが、複数の言語で書かれている場合、または異なるプラットフォーム
で作動するが、同じデータベースオペレーションを行う必要がある場合。

セキュリティを最優先する場合。例えば、銀行はすべての共通オペレーションにストアドプロシージャと
ファンクションを使います。これは一貫して安全な環境を提供するので、ルーチンは各オペレーションが
適切にログされていることを保証します。このようなセットアップでは、アプリケーションとユーザは直接
データベーステーブルにアクセス権は無く、特定のストアドルーチンのみ実行することができます。

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

——————————————————————
Stored Procedure
Stored Routine
Stored Function
——————————————————————

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc1()
-> BEGIN
-> SELECT AREA,PREF FROM REGION;
-> END
-> //
Query OK, 0 rows affected (0.39 sec)

SQLクエリーの最後は「;」を付けますが、ストアドプロシージャを記述する場合、
スクリプト中のセンテンスの終了にも「;」を使用するため、区別のためにデリミタ（SQLクエリーの区切り文字）
を上記のようにして変更します。例では「//」としましたが、「|」など、任意の文字で構いません。

simpleproc1は、引数も戻り値もない、最もシンプルな例です

以下のように呼び出すことで、SELECT AREA,PREF FROM REGIONが実行されます。

mysql> delimiter ;
mysql> CALL simpleproc1();

次に戻り値を伴う例を見てみましょう。先ほどの例では、
CALLされた時点でSELECT AREA,PREF FROM REGIONが実行されましたが、
今度はCALLで結果を変数に渡し、SELECTで変数の値を引き出します。


CREATE PROCEDURE simpleproc2(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM REGION;
END


上記例では、SELECT * INTOクエリーを使用しています。
MySQLはSELECT …… INTO TABLEクエリーに対応しておらず、INSERT INTO …… TABLEクエリー
を代わりに使用しますが、格納先が変数の場合はSELECT …… INTO 変数クエリーが使用可能です。
ここでは、REGIONテーブルの総レコード数を変数param1に引き渡しています。

mysql> delimiter ;
mysql> CALL simpleproc2(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+——+
| @a |
+——+
| 47 |
+——+
1 row in set (0.00 sec)

CALLLを実行する際は、戻り値を受け取るローカル変数を指定します。こうすることで、
ストアドプロシージャの結果が変数@aに格納されます。@aの値を引き出すには、上記のように
SELECTクエリーを用います。

STORED PROCEDUREの応用編

※　ここでは、INとOUTの値を利用しています。


CREATE PROCEDURE simpleproc3(IN Param_IN int,OUT Param_OUT VARCHAR(50))
/* 戻り値と引数はOUT／INで区別*/
BEGIN
/* ストアドプロシージャsimpleproc3の中でのみ有効なローカル変数を宣言します　*/
DECLARE myStr VARCHAR(50);
DECLARE myNo INT;
DECLARE myArea VARCHAR(50);
DECLARE myPref VARCHAR(50);
DECLARE myCur CURSOR FOR SELECT * FROM REGION WHERE NO > Param_IN;
/* カーソルの宣言 */

SET Param_OUT = '指定範囲に秋田県は見つかりません';

OPEN myCur;
/* カーソルをオープン */
SELECT COUNT(*) INTO @myCounter FROM REGION WHERE NO > Param_IN;
/* 何件の該当データがあるのかをを使って調べておき、その件数分だけWHILEループを実行 */
SET @pos = 0;
WHILE @myCounter > @pos DO
FETCH myCur INTO myNo,myArea,myPref;
IF MyPref ='秋田県' THEN
SET Param_OUT = '指定範囲に秋田県を含みます';
END IF;
SET @pos = @pos +1;
END WHILE;

CLOSE myCur;
END


メモ： カーソルはDECLARE→OPEN→FETCH→CLOSEの順で使用し、結果セットを1件ずつ処理

上記ストアドプロシジャーを実行してみます。
mysql> delimiter ;
mysql> call simpleproc3(10,@a);
/ * INの値を10に設定して、OUTの値を@aに格納 */
mysql> select @myCounter,@pos,@a;

mysql> call simpleproc3(3,@a);
/ * INの値を3に設定して、OUTの値を@aに格納 */
Query OK, 0 rows affected (0.01 sec)
mysql> select @myCounter,@pos,@a;

—————————
■ストアドファンクションの作成
ストアドプロシージャと同様の手続きで、ストアドファンクションを定義できます。
—————————

CREATE FUNCTION Hello_W(str_w CHAR(20)) RETURNS CHAR(50)
RETURN
CONCAT('Hello, ',str_w,'!');


mysql> show variables like 'log_bin_trust_function_creators';
mysql> SET GLOBAL log_bin_trust_function_creators = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';

mysql> delimiter //
mysql> CREATE FUNCTION Hello_W(str_w CHAR(20)) RETURNS CHAR(50)
-> RETURN
-> CONCAT('Hello, ',str_w,'!');
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> SELECT Hello_W('World');

ファンクション生成に対する(SUPER権限を持たなければならず、決定論的か、データを修正しないかの別を宣言しなければならない)
前の規制を緩和するには、グローバル log_bin_trust_function_creatorsシステム変数を１に設定します。
デフォルトで、これは０に設定されていますが、ユーザはこのようにして変更することができます。
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
この変数を、サーバを立ち上げる時–log-bin-trust-function-creators=1オプションを使って設定することもできます。
バイナリログが有効化されていない場合、log_bin_trust_function_creators は適用されず、ファンクション生成に対してSUPERは要求されません。

[確認]
SHOW PROCEDURE STATUS;

mysql> desc information_schema.ROUTINES;


select SPECIFIC_NAME,ROUTINE_SCHEMA,ROUTINE_NAME,SECURITY_TYPE
from information_schema.ROUTINES;


mysql> select * from information_schema.ROUTINES limit 1\G

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

————————————–
DECLARE cur1 CURSOR FOR
SELECT id,n FROM STUDY.MYSQLIMP;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;
OPEN cur1;
————————————–
B.2. Server Error Codes and Messages
http://dev.mysql.com/doc/refman/5.1/ja/error-messages-server.html
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

参考サイト
————————————–
[MYSQL] Stored Procedure
MySQL 5.0のストアドプロシージャをチェック