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);

總結

以上是生活随笔為你收集整理的mysql怎么跳出while循环_mysql while,loop,repeat循环，符合条件跳出循环的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。