Interpretation of MySQL process control statements

In MySQL, common procedural SQL statements can be used in a stored procedure body.

These include: IF statements, CASE statements, LOOP statements, WHILE statements, REPEAT statements, LEAVE statements and ITERATE statements, which can be controlled through process.

1. IF statement

IF statements are used to make conditional judgments and perform different operations according to different conditions.

When executing this statement, first determines whether the condition after IF is true, then executes the statement after THEN. If it is false, continue 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 IF statements to make conditional judgments.

-- Create stored procedures
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 stored procedures
CALL example_if(2);

2. CASE statement

The CASE statement is a multi-branch statement structure. This statement first finds the value equal to the VALUE after WHEN and VALUE after CASE. If it is found, the content of the branch will be executed, otherwise the content of the ELSE will be executed.

The CASE statement is represented as follows:

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

Another syntax representation 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 stored procedures
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 stored procedures
CALL example_case(5);

3. WHILE loop statement

When executing the WHILE loop statement, first determine whether the condition is true. If so, the loop body will be executed, otherwise the loop will be exited.

The syntax is represented as follows:

WHILE condition DO
...
END WHILE;

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

-- Create stored procedures
CREATE PROCEDURE example_while(OUT sum INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE s INT DEFAULT 0;

	WHILE i &lt;= 100 DO
		SET s = s+i;
		SET i = i+1;
	END WHILE;

	SET sum = s;
END;

-- Call stored procedures
CALL example_while(@sum);
SELECT @sum;

4. LOOP loop statement

LOOP loops do not have built-in loop conditions, but they can be exited through the LEAVE statement.

The LOOP statement is represented as follows:

LOOP
	...
END LOOP

LOOP statements allow repeated execution of a specific statement or statement group, implementing a simple loop construction, where statements in the loop are repeated until the loop is exited, and the loop is applied to the LEAVE statement.

The LEAVE statement is often used with BEGIN...END or loops, and its representation 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 stored procedures
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&gt;100 THEN
			-- quitLOOPcycle
			LEAVE loop_label;  
		END IF;
	END LOOP;

	SET sum = s;
END;

-- Call stored procedures
CALL example_loop(@sum);
SELECT @sum;

5. REPEAT loop statement

The REPEAT loop statement executes the loop body first, and then determines whether the condition condition is true, then exits the loop, otherwise continues to execute the loop.

The REPEAT statement is represented as follows:

REPEAT
	...
	UNTIL condition
END REPEAT

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

-- Create stored procedures
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 &gt; 100
	END REPEAT;
	
	SET sum = s;
END;

-- Call stored procedures
CALL example_repeat(@sum);
SELECT @sum;

6. ITERATE statement

The ITERATE statement can appear in 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 starts a loop again.

Example:Find the sum of odd values within 10.

-- Create stored procedures
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 &gt; 10 THEN
			-- Exit the entire loop
			LEAVE loop_label;  
		END IF;

		IF (i mod 2) THEN
			SET s = s+i;
		ELSE
			-- Exit this cycle，Continue with the next cycle
			ITERATE  loop_label;
		END IF;

	END LOOP;

	SET sum = s;
END;

-- Call stored procedures
CALL example_iterate(@sum);
SELECT @sum;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.