1、while循环
DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_test_while`$$ CREATE PROCEDURE `sp_test_while`( IN p_number INT, #要循环的次数 IN p_startid INT #循环的其实值 ) BEGIN DECLARE v_val INT DEFAULT 0; SET v_val=p_startid; outer_label: BEGIN #设置一个标记 WHILE v_val<=p_number DO SET v_val=v_val+1; IF(v_val=100)THEN LEAVE outer_label; #满足条件，终止循环，跳转到end outer_label标记 END IF; END WHILE; SELECT ‘我是while外，outer_label内的SQL‘; #由于这句SQL在outer_label代码块内，所以level后，这句SQL将不会执行； #只要是在outer_label代码块内 任意位置 Leave outer_label,那么Leave后的代码将不再执行 END outer_label; SELECT CONCAT(‘test‘,v_val) AS tname; END$$ DELIMITER ; CALL sp_test_while(1000,0);2、loop 循环
DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_testloop`$$ CREATE PROCEDURE `sp_testloop`( IN p_number INT, #要循环的次数 IN p_startid INT #循环的其实值 ) BEGIN DECLARE v_val INT DEFAULT 0; SET v_val=p_startid; loop_label: LOOP #循环开始 SET v_val=v_val+1; IF(v_val>p_number)THEN LEAVE loop_label; #终止循环 END IF; END LOOP; SELECT CONCAT(‘testloop_‘,v_val) AS tname; END$$ delimiter ; CALL sp_testloop(1000,0);3、repeat循环下载地址
DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_test_repeat`$$ CREATE PROCEDURE `sp_test_repeat`( IN p_number INT, #要循环的次数 IN p_startid INT #循环的其实值 ) BEGIN DECLARE v_val INT DEFAULT 0; SET v_val=p_startid; REPEAT #repeat循环开始 SET v_val=v_val+1; until v_val>p_number #终止循环的条件，注意这里不能使用‘;‘分号，否则报错 END repeat; #循环结束 SELECT CONCAT(‘test‘,v_val) AS tname; END$$ DELIMITER ; CALL sp_test_repeat(1000,0); CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test1`(IN sapjson text,out result text) BEGIN #Routine body goes here... DECLARE done int DEFAULT 0; DECLARE agvname VARCHAR(20); DECLARE Line_namevalue VARCHAR(20); DECLARE testloop CURSOR FOR SELECT name FROM agv_info where name = 'A001'; DECLARE testloop1 CURSOR FOR SELECT Line_name FROM tab_line where Line_name = '测试线'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN testloop; read_loop:LOOP FETCH testloop into agvname; IF done=1 THEN LEAVE read_loop; END IF; set result = agvname; END LOOP read_loop; set done = 0;-- CONTINUE HANDLER FOR NOT FOUND 是不能重复定义 一次循环后需要重复使用 需要重写设值 OPEN testloop1; read_loop1:LOOP FETCH testloop1 into Line_namevalue; IF done=1 THEN LEAVE read_loop1; END IF; set result = Line_namevalue; END LOOP read_loop1; END