mysql游标循环的三种方式及打印结果查看

1、while方式：

DELIMITER 
USE `ct1_test`
DROP PROCEDURE IF EXISTS `searchDate`
CREATE DEFINER=`greesj1b`@`%` PROCEDURE `searchDate`()
BEGIN
DECLARE tmpName VARCHAR(20) DEFAULT '' ;  
DECLARE temp_id VARCHAR(1024) DEFAULT '' ;  
DECLARE cur1 CURSOR FOR SELECT _id FROM `wp04_project` ORDER BY _id ASC LIMIT 5 ; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = NULL; 
OPEN cur1;
FETCH cur1 INTO tmpName;
WHILE ( tmpname IS NOT NULL) DO 

/*自己的业务逻辑(我是把字符串相加)*/

SET tmpName = CONCAT(tmpName ," ") ; 
SET temp_id = CONCAT(temp_id ,tmpName) ; 
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
SELECT temp_id ;-- 打印结果
 END
DELIMITER ;

2、Repeat方式：

```sql
DELIMITER 
USE `ct1_test`
DROP PROCEDURE IF EXISTS `searchDate`
CREATE DEFINER=`greesj1b`@`%` PROCEDURE `searchRepeat`()
BEGIN
DECLARE done INT DEFAULT 0; 
 DECLARE a VARCHAR(200) DEFAULT '';
DECLARE temp_id VARCHAR(1024) DEFAULT '' ;  
DECLARE mycursor CURSOR FOR SELECT _id FROM `wp04_project` ORDER BY _id ASC LIMIT 5 ; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN mycursor;
  REPEAT 
    FETCH mycursor INTO a;
     IF NOT done THEN
            SET temp_id=CONCAT(temp_id,a,' ');/*字符串相加,自己的业务逻辑*/
        END IF;
     UNTIL done END REPEAT;
CLOSE mycursor;
SELECT temp_id ;-- 打印结果
   END$$
DELIMITER ;

3、Loop方式：

```sql
DELIMITER 
USE `ct1_test`
DROP PROCEDURE IF EXISTS `searchLoop`
CREATE DEFINER=`greesj1b`@`%` PROCEDURE `searchLoop`()
BEGIN
DECLARE id INT;  
     DECLARE temp_id VARCHAR(1024) DEFAULT '' ;  
     DECLARE done INT DEFAULT 0;  
     DECLARE cur1 CURSOR FOR SELECT _id FROM `wp04_project` ORDER BY _id ASC LIMIT 5 ; 
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
     OPEN cur1;  
     emp_loop: LOOP  
         FETCH cur1 INTO id;  
         IF done=1 THEN  
            LEAVE emp_loop; 
         END IF;  
          SET temp_id=CONCAT(temp_id,id,' ');/*字符串相加,自己的业务逻辑*/       
     END LOOP emp_loop;  
     CLOSE cur1;  
   SELECT temp_id ;-- 打印结果
    END
DELIMITER ;

执行方法：CALL searchDate()

结果打印：100001 100002 100003 100004 100005