1、while循环DELIMITER$$DROPPROCEDUREIFEXISTS`sp_test_while`$$CREATEPROCEDURE`sp_test_while`(INp_numberINT,#要循环的次数INp_startidINT#循环的其实值
)BEGINDECLAREv_valINTDEFAULT0;
SETv_val=p_startid;
outer_label:BEGIN#设置一个标记WHILEv_val<=p_numberDO
SETv_val=v_val+1;
IF(v_val=100)THEN
LEAVEouter_label;#满足条件,终止循环,跳转到endouter_label标记ENDIF;ENDWHILE;
SELECT‘我是while外,outer_label内的SQL‘;#由于这句SQL在outer_label代码块内,所以level后,这句SQL将不会执行;
#只要是在outer_label代码块内任意位置Leaveouter_label,那么Leave后的代码将不再执行ENDouter_label;
SELECTCONCAT(‘test‘,v_val)AStname;END$$
DELIMITER;
CALLsp_test_while(1000,0);
2、loop 循环DELIMITER$$DROPPROCEDUREIFEXISTS`sp_testloop`$$CREATEPROCEDURE`sp_testloop`(INp_numberINT,#要循环的次数INp_startidINT#循环的其实值
)BEGIN
DECLAREv_valINTDEFAULT0;
SETv_val=p_startid;
loop_label:LOOP#循环开始SETv_val=v_val+1;
IF(v_val>p_number)THEN
LEAVEloop_label;#终止循环ENDIF;ENDLOOP;
SELECTCONCAT(‘testloop_‘,v_val)AStname;END$$
delimiter;
CALLsp_testloop(1000,0);
3、repeat循环DELIMITER$$DROPPROCEDUREIFEXISTS`sp_test_repeat`$$CREATEPROCEDURE`sp_test_repeat`(INp_numberINT,#要循环的次数INp_startidINT#循环的其实值
)BEGINDECLAREv_valINTDEFAULT0;
SETv_val=p_startid;
REPEAT#repeat循环开始
SETv_val=v_val+1;
untilv_val>p_number#终止循环的条件,注意这里不能使用‘;‘分号,否则报错
ENDrepeat;#循环结束
SELECTCONCAT(‘test‘,v_val)AStname;END$$
DELIMITER;
CALLsp_test_repeat(1000,0);