Tag Archives: Cursor

SELECT文で得られた単一行の結果を変数へ代入するにはSELECT … INTO 、
SELECTから得られた結果を一行一行ずつ変数へ代入していくには、CURSORを利用します。

DECLARE(カーソル宣言)
OPEN(カーソルを開く)
FETCH(取得)
CLOSE(カーソルを閉じる)

MySQL では、サーバサイドカーソルは一時テーブルへと出力されます。
最初、これは MEMORY テーブルになりますが、そのサイズが max_heap_table_size
システム変数の値に達すると、MyISAM テーブルに変換されます。
この実装の制限のひとつとして、大きな結果セットでは、カーソルでの行の呼び出しに時間がかかる場合があります。

カーソルは読み取り専用で、カーソルを行のアップデートに使用することはできません。
UPDATE WHERE CURRENT OF および DELETE WHERE CURRENT OF は、
アップデート可能なカーソルはサポートされていないため実装されていません。

カーソルは保持不可能 ( コミットの後で開いたままにしておくことができない )。
カーソルはセンシティブです。
カーソルはスクロール不可能。
カーソルに名称は付いていません。ステートメント ハンドラがカーソル ID として作用します。
プリペアド ステートメントごとに、カーソルをひとつだけ開いておくことができます。
複数のカーソルが必要な場合は、複数のステートメントを準備しなければなりません。

結果セットを生成するステートメントで、準備モードでサポートされていないものにはカーソル
を使うことはできません。そのようなステートメントには、CHECK TABLES 、HANDLER READ 、
そして SHOW BINLOG EVENTS があります。

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


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ステートメントは各々が複数の条件で処理することができる
ハンドラを規定します。もし、これらの条件の１つが起った場合、ステートメントが実行されます。
この場合、ステートメントを単純なものにすることができます。 (例えば、 SET var_name = value),
もしくは、BEGIN と END を使って書いた複合ステートメントにすることができます。

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

ハンドラがまだ宣言されていない条件がしている場合､デフォルトアクションはEXITとなります。
A condition_valueは以下の値のいずれかにすることができます：
* SQLSTATE値もしくはMySQLエラーコード。
* 既に DECLARE … CONDITIONで指定されている条件名。
* SQLWARNINGは01で始まる全てのSQLSTATEコードに対する速記文字です。
* NOT FOUNDは02で始まる全てのSQLSTATEコードに対する速記文字です。
* SQLEXCEPTIONはSQLWARNINGまたはNOT FOUNDによって捕らえられなかった全ての
SQLSTATEコードの速記文字です。


delimiter //


CREATE PROCEDURE STUDY.cur_insert()
BEGIN
DECLARE done int DEFAULT 0;
DECLARE a int;
DECLARE b varchar(30);

DECLARE cur1 CURSOR FOR
SELECT id,n FROM STUDY.MYSQLIMP;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

REPEAT
FETCH cur1 INTO a, b;
IF NOT done THEN
IF a < 300 THEN INSERT INTO STUDY.MYSQLIMP2 VALUES (a,b); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; END //
delimiter ;

実行したらエラーになってしまった。。。

ERROR 1436 (HY000): Thread stack overrun:
11344 bytes used of a 131072 byte stack, and 128000 bytes needed.
Use 'mysqld -O thread_stack=#' to specify a bigger stack.

thread_stack は、動的に変更出来ないようなので、オプションファイルの設定を変更して再起動

#thread_stack = 64K
thread_stack = 256K

[root@colinux data]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@colinux data]#

mysql> show variables like 'thread_stack';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| thread_stack | 262144 |
+---------------+--------+
1 row in set (0.00 sec)

mysql>

SELECTの場合

delimiter //


mysql> CREATE PROCEDURE STUDY.cur_insert()
-> BEGIN
-> DECLARE done int DEFAULT 0;

-> DECLARE a int;
-> DECLARE b varchar(30);
->
-> DECLARE cur1 CURSOR FOR
-> SELECT id,n FROM STUDY.MYSQLIMP;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
->
-> OPEN cur1;
-> REPEAT
-> FETCH cur1 INTO a, b;
-> IF NOT done THEN
-> IF a < 300 THEN -> SELECT a,b;
-> END IF;
-> END IF;
-> UNTIL done END REPEAT;
-> CLOSE cur1;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

参考サイト
17.2.9. カーソル
D.2. サーバサイドカーソルの規制
17.2.1. CREATE PROCEDUREおよびCREATE FUNCTION 構文
17.2.8.2. DECLARE ハンドラ