This article uses an example to describe the MySQL code execution structure. Share with you for your reference, the details are as follows:

In this article:

• What is the code execution structure?
• Sequence Structure
• Branch structure
• Loop Structure

Release date: 2018-04-18

What is the code execution structure:

• The code execution structure mentioned here is the execution order of multiple SQL statements.
• The code execution structure is mainly used to store multiple SQL statements in triggers, stored procedures, and functions.

Sequential structure:

• The sequential structure is to execute SQL statements from top to bottom
• Generally, the default structure is a sequence

Branch structure:

• The execution of the branch structure is to select the execution path based on certain conditions. It will choose to execute those SQL statements based on the conditions we give.
• The branch structure in MySQL is only if-else:
  • grammar:

    if condition then
     SQL statement [elseif condition then
     sql statement]
    [else
     sql statement]
    end if;

  • Example:

    -- 
    create table pass(id int primary key auto_increment,name varchar(15),score int );
    create table unpass(id int primary key auto_increment,name varchar(15),score int);
    
    -- Use stored procedures to create procedure myif(in name varchar(15),in score int)
    begin
     if score >=60 then
      insert into pass(name,score) values(name,score);
     else
      insert into unpass(name,score) values(name,score);
     end if;
    end;
    -- Call and view the result call myif("lilei",61);
    call myif("hanmeimei",95);
    select * from pass;
    select * from unpass;
    call myif("tuhao",59);
    select * from unpass;

  • The conditions in if can basically refer to the conditions of the while clause of the select statement. Anything like in\not in \= \!= etc. can be used.

    create procedure myif3(in a char(1))
    begin
     if a in('a','b') then
      select 1;
     else 
      select 2;
     end if;
    end;
    call myif3('a');
    call myif3('b');
    call myif3('c');

Replenish:

• Theoretically, if the judgment is not correct and you do not want to continue executing, you should execute a return (such as the return in C language to interrupt the function execution), but there is no corresponding interrupt mechanism in MySQL, so we need to actively interrupt (there are many ways to interrupt, such as executing a statement that conforms to the syntax but cannot be executed) [This scenario, for example, is to determine whether a student exists. If not, no operation will be performed, so a statement that cannot be successfully executed should be executed to report an error and return . 】
• In fact, there is another branching structure: case when [It seems that many books don’t talk about it much, so I won’t talk about it here. If you are interested, you can search on Baidu. 】

Loop structure:

• A loop structure refers to a program structure that is set up to repeatedly execute a certain function in a program. The loop structure in MySQL is used to loop and run the same SQL statement multiple times.
• The loop structures in MySQL include loop structure, while structure, and repeat structure. Here we only describe the while structure. If you are interested in learning about the others, you can search on Baidu.
• 
• grammar:

while condition do
 sql statement end while;

Those who have learned other languages ​​may know that there are continue (ending the loop early) and break (jumping out of the entire loop) in the loop structure.
In the loop structure of MySQL, leave is used instead of break, and iterate is used instead of continue, but their usage syntax is: leave\iterate loop name, so how to define the loop name?

Loop name: while condition do
 sql statement;
 leave_iterate loop name;
end while;

• Example:

  -- A meaningless example, just for demonstration create table whilenum(id int);
  -- create procedure mywhile() with built-in conditions
  begin
   declare num int;
   set num=10;
   c1:while num>0 do
     insert into whilenum values(num);
     set num=num-1;
    end while;
  end;
  -- Create procedure mywhile2(in num int) with the passed parameter as the condition
  begin
   c1:while num>0 do
     insert into whilenum values(num);
     set num=num-1;
    end while;
  end;
  -- create procedure mywhile3(in num int) with interrupt
  begin
   c1:while num>0 do
     if num%2=0 then
      set num=num-1;
      iterate c1;
     end if;
     insert into whilenum values(num);
     set num=num-1;
    end while;
  end;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.