MySql 游标的使用

/*
	MySQL 存储过程编写以及游标的使用
	NonkeyJiang
	2017-09-14
*/
DROP TABLE IF EXISTS tbl_a;
CREATE TABLE tbl_a
(
	ID INT,
	NAME_A VARCHAR(20)
);

TRUNCATE tbl_a;
INSERT INTO tbl_a VALUES
(1,'a'),
(2,'ab'),
(3,'abc'),
(4,'abcd'),
(5,'abcde');

DROP PROCEDURE IF EXISTS simpleproc;
delimiter $$
CREATE PROCEDURE simpleproc(OUT cids VARCHAR(64),OUT cnames VARCHAR(64))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE cid VARCHAR(20);
	DECLARE cname VARCHAR(20);
/*
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	DECLARE cur1 CURSOR FOR SELECT ID,NAME_A FROM tbl_a; 
	
	如果设置done 变量在声明游标前，则会报错
	[Err] 1338 - Cursor declaration after handler declaration
 */
	DECLARE cur1 CURSOR FOR SELECT ID,NAME_A FROM tbl_a; 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
/*
	遍历游标必须在每次FETCH 后判断done ，否则将会吧最后一条记录多遍历一次
 */
	OPEN cur1;
	label:LOOP
		FETCH cur1 INTO cid,cname;
		IF done THEN LEAVE label;END IF;
		SET cids = CONCAT_WS(',',cids,cid);
		SET cnames = CONCAT_WS(',',cnames,cname);
	END LOOP label;
	CLOSE cur1;
END $$
delimiter ;

call simpleproc(@cids,@cnames);
SELECT @cids;
SELECT @cnames;