Mysql while, loop, and repeat loops.

Mysql while, loop, and repeat loops.
1. while Loop

DELIMITER $ drop procedure if exists 'SP _ test_while '$ create procedure 'SP _ test_while' (IN p_number INT, # The number of times to be cyclically IN p_startid INT # the actual value of the loop) begin declare v_val int default 0; SET v_val = p_startid; outer_label: BEGIN # SET a flag WHILE v_val <= p_number do set v_val = v_val + 1; IF (v_val = 100) then leave outer_label; # IF the conditions are met, terminate the loop and jump to the end outer_label label to Mark end if; end while; SELECT 'SQL in outer_label and out of while '; # Because this SQL statement is in the outer_label code block, this SQL statement will not be executed after the level; # As long as it is in any position in the outer_label code block Leave outer_label, then the Leave code will not execute END outer_label; select concat ('test', v_val) AS tname; END $ DELIMITER; CALL sp_test_while );

 

2. loop

DELIMITER $ drop procedure if exists 'SP _ testloop '$ create procedure 'SP _ testloop' (IN p_number INT, # The number of times to be cyclically IN p_startid INT # the actual value of the loop) 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 );

 

3. repeat loop

DELIMITER $ drop procedure if exists 'SP _ test_repeat '$ create procedure 'SP _ test_repeat' (IN p_number INT, # Number of times IN p_startid INT # actual value of the loop) begin declare v_val int default 0; SET v_val = p_startid; REPEAT # SET v_val = v_val + 1 when the repeat loop starts; until v_val> p_number # When the loop ends, note that '; 'semicolon; otherwise, the END repeat; # loop end select concat ('test', v_val) AS tname; END $ DELIMITER; CALL sp_test_repeat );