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

    xiaoxiao2021-03-25  14

    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方式:

    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方式:

    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 

    转载请注明原文地址: https://ju.6miu.com/read-50153.html

    最新回复(0)