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
