What does mysql flow control statement include?

The flow control statements in mysql include: IF statement, CASE statement, LOOP statement, WHILE statement, REPEAT statement, LEAVE statement and ITERATE statement, all of which can control the flow of the program.

#Flow control statements can be used in stored procedures and custom functions to control the flow of the program. The flow control statements in MySQL include: IF statement, CASE statement, LOOP statement, WHILE statement, REPEAT statement, LEAVE statement and ITERATE statement, which can perform process control. (Recommended tutorial: mysql video tutorial)

1. IF statement

IF statement is used to make conditional judgments. Perform different operations based on different conditions. When this statement is executed, it first judges whether the condition after IF is true, then executes the statement after THEN. If it is false, it continues to judge the IF statement until it is true. When none of the above is satisfied, the content after the ELSE statement is executed. The expression form of the IF statement is as follows:

IF condition THEN
	...
ELSE condition THEN
	...
ELSE
	...
END IF

Example: Use the IF statement to make conditional judgments.

-- 创建存储过程
CREATE PROCEDURE example_if (IN x INT)
BEGIN
	IF x = 1 THEN
		SELECT 1;
	ELSEIF x = 2 THEN 
		SELECT 2;
	ELSE
		SELECT 3;
	END IF;
END;

-- 调用存储过程
CALL example_if(2);

Note: The IF() function in MySQL is different from the IF statement here.

2. CASE statement

The CASE statement is a multi-branch statement structure. This statement first searches for the VALUE after WHEN and The VALUE after CASE is equal to the value. If found, the content of the branch will be executed, otherwise the content after ELSE will be executed. The expression form of the CASE statement is as follows:

CASE value
	WHEN value THEN ...
	WHEN value THEN ...
	ELSE ...
END CASE

Another grammatical expression form of the CASE statement is as follows:

CASE
	WHEN value THEN ...
	WHEN value THEN ...
	ELSE ...
END CASE

Example: Use the CASE statement to make conditional judgments.

-- 创建存储过程
CREATE PROCEDURE example_case(IN x INT)
BEGIN
	CASE x
		WHEN 1 THEN SELECT 1;
		WHEN 2 THEN SELECT 2;
		ELSE SELECT 3;
	END CASE;
END;

-- 调用存储过程
CALL example_case(5);

3. WHILE loop statement

When the WHILE loop statement is executed, first determine whether the condition condition is true, and if so, execute the loop body. Otherwise exit the loop. The syntax is expressed as follows:

WHILE condition DO
...
END WHILE;

Example: Use the WHILE loop statement to find the sum of the first 100.

-- 创建存储过程
CREATE PROCEDURE example_while(OUT sum INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE s INT DEFAULT 0;

	WHILE i <= 100 DO
		SET s = s+i;
		SET i = i+1;
	END WHILE;

	SET sum = s;
END;

-- 调用存储过程
CALL example_while(@sum);
SELECT @sum;

4. LOOP loop statement

The LOOP loop has no built-in loop conditions, but you can exit the loop through the LEAVE statement. The expression of the LOOP statement is as follows:

LOOP
	...
END LOOP

The LOOP statement allows the repeated execution of a specific statement or statement group to implement a simple loop structure. The statements in the loop are repeated until the loop is exited. When exiting the loop, the LEAVE statement is used. .

The LEAVE statement is often used together with BEGIN...END or loops, and its expression is as follows:

LEAVE label

label is the name marked in the statement, and this name is customized.

Example: Use the LOOP loop statement to find the sum of the first 100.

-- 创建存储过程
CREATE PROCEDURE example_loop(OUT sum INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE s INT DEFAULT 0;
	
	loop_label:LOOP
		SET s = s+i;
		SET i = i+1;
	
		IF i>100 THEN
			-- 退出LOOP循环
			LEAVE loop_label;  
		END IF;
	END LOOP;

	SET sum = s;
END;

-- 调用存储过程
CALL example_loop(@sum);
SELECT @sum;

5. REPEAT loop statement

The REPEAT loop statement first executes the loop body once, and then determines whether the condition condition is true, then exits the loop , otherwise the loop continues. The representation of the REPEAT statement is as follows:

REPEAT
	...
	UNTIL condition
END REPEAT

Example: Use the REPEAT loop statement to find the sum of the first 100.

-- 创建存储过程
CREATE PROCEDURE example_repeat(OUT sum INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE s INT DEFAULT 0;

	REPEAT
		SET s = s+i;
		SET i = i+1;
		
		UNTIL i > 100
	END REPEAT;
	
	SET sum = s;
END;

-- 调用存储过程
CALL example_repeat(@sum);
SELECT @sum;

6. ITERATE statement

The ITERATE statement can appear within the LOOP, REPEAT and WHILE statements, which means "loop again". The statement format is as follows:

ITERATE label

The format of this statement is similar to that of LEAVE. The difference is that: the LEAVE statement leaves a loop, while the ITERATE statement restarts a loop.

Example: Find the sum of odd values ​​within 10.

-- 创建存储过程
CREATE PROCEDURE example_iterate(OUT sum INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE s INT DEFAULT 0;
	
	loop_label:LOOP

		SET i = i+1;		

		IF i > 10 THEN
			-- 退出整个循环
			LEAVE loop_label;  
		END IF;

		IF (i mod 2) THEN
			SET s = s+i;
		ELSE
			-- 退出本次循环，继续下一个循环
			ITERATE  loop_label;
		END IF;

	END LOOP;

	SET sum = s;
END;

-- 调用存储过程
CALL example_iterate(@sum);
SELECT @sum

For more programming-related knowledge, please visit: Programming Learning Website! !

The above is the detailed content of What does mysql flow control statement include?. For more information, please follow other related articles on the PHP Chinese website!