MySql 游标

MySql的游标CURSOR使用方法

举例：

DROP PROCEDURE IF EXISTS  `p_test_cusor`;
DELIMITER $$
CREATE PROCEDURE `p_test_cusor`(
)
begin 
    DECLARE _ret VARCHAR(1000);
    DECLARE _id, _name VARCHAR(50);
    DECLARE _done int;
    DECLARE cur1 CURSOR FOR SELECT `id`,`name` FROM `t_test`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done=1;
    

    drop table IF EXISTS `t_test`;
    CREATE TEMPORARY TABLE IF NOT EXISTS `t_test`
	(    
		`id` VARCHAR(36),
		`name` VARCHAR(20)
	)CHARSET=utf8mb4 ;
    
	insert into t_test(`id`, `name`) 
    values('d54b5659-2d2c-11eb-8e9b-408d5ca44e7a','test1'),
		('ddf30a4b-2d2c-11eb-8e9b-408d5ca44e7a','t2'),
        ('ddf30a4c-2d2c-11eb-8e9b-408d5ca44e7a','t3');
    
    SET _done = 0;
    SET _ret = "";
    
    OPEN cur1;
	cursor_loop:loop
	   FETCH cur1 INTO _id,_name;
	   if _done=1 then
		 leave cursor_loop;
	   end if;
			set _ret = concat(_ret, _name,', ');
	 end loop cursor_loop;
	 CLOSE cur1;

    select _ret as Result;
end
$$
DELIMITER ;