MySQL Process Control

1. MySQL Process Control Statement

• The IF, case, loop, leave, iterate, repeat, and while statements can be used in MySQL for process control.

1.1 If statement

• The IF implementation condition is judged to satisfy different conditions to execute a different list of statements.

  # if 语句# IF     search_condition   THEN     statement_list  [ELSEIF search_condition THEN statement_list] ...  [ELSE statement_list]  END IF> if     i_staff_id = 2   then    set @x1 = @x1 + d_amount;  else    set @x2 = @x2 + d_amount;  end if;

1.2 Case statement

• Case implementations are more complex than if conditional constructs.

  # case 语句# CASE  WHEN     search_condition   THEN     statement_list  [WHEN search_condition THEN statement_list] ...  [ELSE statement_list]  END CASE# CASE     case_value  WHEN     when_value   THEN     statement_list  [WHEN when_value THEN statement_list] ...  [ELSE statement_list]  END CASE> case  when     i_staff_id = 2   then    set @x1 = @x1 + d_amount;  else    set @x2 = @x2 + d_amount;  end case;> case     i_staff_id  when     2   then    set @x1 = @x1 + d_amount;  else    set @x2 = @x2 + d_amount;  end case;

1.3 Loop statement

• Loop implements a simple loop, and the conditions for exiting the loop require other statement definitions, which can often be implemented using the Leave statement.

  # loop 语句# [begin_label:] LOOP    statement_list  END LOOP [end_label]

• If you do not statement_list add a statement that exits the loop, the loop statement can be used to implement a simple dead loop.

1.4 Leave statements

• Leave is used to exit from the callout's process construct, usually and BEGIN ... END or a loop to use together.

• Here is a simple example using loops and leave, looping 100 times to insert a record into the actor table, and exiting the loop when 100 records are inserted.

  # leave 语句> create procedure actor_insert ()  BEGIN    set @x = 0;  ins: LOOP    set @x = @x + 1;  IF     @x = 100   THEN    LEAVE ins;  END IF;  INSERT INTO actor (first_name, last_name) VALUES ('Test', '201');  END LOOP ins;  END;  $$    Query OK, 0 rows affected (0.00 sec)> call actor_insert();    Query OK, 0 rows affected (0.01 sec)> select count(*) from actor where first_name = 'Test';    +----------+    | count(*) |    +----------+    |      100 |    +----------+    1 row in set (0.00 sec)

1.5 Iterate statements

• Iterate must be used in loops to skip the remaining statements of the current loop and go directly to the next round of loops.

• The following example uses the iterate statement, and when the @x variable is even, the remaining statements in the loop are no longer executed, and the next round of loops is performed directly.

    # Iterate statement > CREATE PROCEDURE actor_insert () BEGIN Set @x = 0;  Ins:loop Set @x = @x + 1;  IF @x = ten then LEAVE ins;  ELSEIF mod (@x,2) = 0 then iterate ins;  END IF;  INSERT into actor (actor_id,first_name,last_name) VALUES (@x+200, ' Test ', @x);  END LOOP ins;  END;    $$ Query OK, 0 rows Affected (0.00 sec) > Call Actor_insert ();    Query OK, 0 rows Affected (0.00 sec) > select Actor_id,first_name,last_name from actor where first_name= ' Test '; +----------+------------+-----------+    | actor_id | first_name |    last_name |      +----------+------------+-----------+    |       201 |         Test |    1 |      |       203 |         Test |    3 |      |       205 |         Test |    5 |      |       207 |         Test |    7 |      |       209 |         Test |    9 | +----------+------------+-----------+ 5 rows in Set (0.00 sec)  

1.6 Repeat Statements

• Repeat a conditional loop control statement that exits the loop when the condition is met.

  # repeat 语句# [begin_label:] REPEAT    statement_list  UNTIL     search_condition  END REPEAT [end_label]> REPEAT  FETCH cur_payment INTO i_staff_id, d_amount;  if     i_staff_id = 2   then    set @x1 = @x1 + d_amount;  else    set @x2 = @x2 + d_amount;  end if;  UNTIL     0   END REPEAT;

1.7 While statement

• The while implementation is also a conditional loop control statement that executes the contents of the loop when the condition is met.

• The difference between a while loop and a repeat loop

  • While is satisfying the condition to execute the loop, repeat is satisfying the conditional exit loop;
  • While the condition is judged before the first loop executes, the loop executes at least 0 times, and the repeat is judged after the first execution of the loop, so the loop executes at least 1 times.

  # while 语句# [begin_label:] WHILE search_condition DO  statement_list  END WHILE [end_label]> delimiter $$> CREATE PROCEDURE loop_demo ()  BEGIN    set @x = 1 , @x1 = 1;  REPEAT    set @x = @x + 1;  UNTIL     @x > 0   END REPEAT;  WHILE     @x1 < 0   DO    set @x1 = @x1 + 1;  END WHILE;  END;  $$    Query OK, 0 rows affected (0.00 sec)  > delimiter ;  > call loop_demo();    Query OK, 0 rows affected (0.00 sec)  > select @x,@x1;    +------+------+    | @x   | @x1  |    +------+------+    |    2 |    1 |    +------+------+    1 row in set (0.00 sec)

MySQL Process Control