/* 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;
MySql 游标的使用
最新推荐文章于 2022-07-04 22:07:56 发布