MySQL Cursor在存储过程中的使用
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。
declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor.
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
-
Asensitive: The server may or may not make a copy of its result table
-
Read only: Not updatable
-
Nonscrollable: Can be traversed only in one direction and cannot skip rows
新建以下表
create table t3( id int not null, num_t int not null, primary key (id));create table t4( id int not null, num_tt int not null, primary key (id));insert into t3 (id,num_t) values(1,2),(3,4),(5,6),(6,2),(7,4),(8,6);insert into t4 (id,num_tt) values(1,2),(3,4),(5,6),(9,2),(12,4),(76,6);create table t5( id int not null, num_ttt int not null, primary key (id));
新建一个存储过程，并在存储过程中使用游标对数据集进行遍历，并操作数据，如下：
mysql> DELIMITER //mysql>mysql> CREATE PROCEDURE curdemo() -> BEGIN -> DECLARE done INT DEFAULT FALSE; -> DECLARE a, b, c INT; -> DECLARE cur1 CURSOR FOR SELECT id,num_t FROM test.t3; -> DECLARE cur2 CURSOR FOR SELECT num_tt FROM test.t4; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -> -> OPEN cur1; -> OPEN cur2; -> -> read_loop: LOOP -> FETCH cur1 INTO a, b; -> FETCH cur2 INTO c; -> IF done THEN -> LEAVE read_loop; -> END IF; -> IF b < c THEN -> INSERT INTO test.t5(id,num_ttt) VALUES (a,b); -> ELSE -> INSERT INTO test.t5(id,num_ttt) VALUES (a,c); -> END IF; -> END LOOP; -> -> CLOSE cur1; -> CLOSE cur2; -> END -> //Query OK, 0 rows affected (0.00 sec)mysql>mysql> DELIMITER ;mysql> CALL curdemo();Query OK, 0 rows affected (0.55 sec)mysql> select * from t5;+----+---------+| id | num_ttt |+----+---------+| 1 | 2 || 3 | 4 || 5 | 6 || 6 | 2 || 7 | 4 || 8 | 6 |+----+---------+6 rows in set (0.06 sec)mysql>
通过代码，你可以看到在存储过程中通过游标进行数据集的遍历。
以上的存储过程涉及到mysql游标的语法，请看：
==============END==============