MySql 游标的使用

/*MySQL 存储过程编写以及游标的使用NonkeyJiang2017-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))BEGINDECLARE 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:LOOPFETCH 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;