MySQL and database series article directory

Classification of process control structure

• Classification:
  • Sequential structure: the program is executed from top to bottom;
  • Branch structure: the program selects one of two or more paths to execute;
  • Loop structure: the program repeatedly executes a piece of code on the basis of meeting certain conditions;

Branching structure

IF function

• Function: realize simple double branch;

• Syntax:

  if(Expression 1, expression 2, expression 3);
  

• Execution sequence:
  If expression 1 holds, return the value of expression 2; otherwise, return the value of expression 3;

• Application: anywhere;

CASE function

• Function: similar to switch statement and if else statement in programming language;

• characteristic:

  • As an expression, it is nested in other statements and can be placed anywhere, in or outside begin end;
    As an independent statement, it can only be used in begin end;
  • If the value in when satisfies or the condition holds, execute the statement after the corresponding then and end the case;
    If none is satisfied, execute the statement or value in else;
  • else statements can be omitted;
    If the else statement is omitted and all the when conditions are not met, null is returned;

• As an expression

  • Similar to switch statement

    case expression
    when Value 1 then Value 2
    when Value 2 then Value 2
    ...
    else value n
    end；
    

  • Similar to if else statement

    case
    when Condition 1 then Value 1
    when Condition 2 then Value 2
    ...
    else value n
    end；
    

• As a stand-alone statement

  • Similar to switch statement

    case expression
    when Value 1 then Statement 1;
    when Value 2 then Statement 2;
    ...
    else sentence n；
    end case；
    

  • Similar to if else statement

    case
    when Condition 1 then Statement 1;
    when Condition 2 then Statement 2;
    ...
    else sentence n；
    end case；
    

ELSEIF function

• Function: realize multiple branches;

• Syntax:

  if Condition 1 then Statement 1;
  elseif Condition 2 then Statement 2;
  ...
  else sentence n；
  end if；
  

• Application: used in begin end;

• Note: the last else statement can be omitted;

Cyclic structure

• Classification: while, loop, repeat;

• Cycle control:

  • iterate is similar to continue in programming language. End this cycle and execute the next cycle;
  • leave is similar to the break of programming language to end the current recent cycle;

• Application: all three loop structures are used in begin end;

• while statement

  • Syntax:

    [Label:] while Cycle condition do
    	Circulatory body;
    end while [Label];
    

  • while statements similar to programming languages;

• loop statement

  • Syntax:

    [Label:] loop
    	Circulatory body;
    end loop [Label];
    

  • It can be used to simulate a simple dead cycle;

• repeat statement

  • Syntax:

    [Label:] repeat
    	Circulatory body;
    until Conditions for ending the cycle
    end repeat [Label];
    

  • do while statement similar to programming language;

• No loop control is added;
  Case 1: batch insert, insert multiple records into the admin table according to the number of times;

  delimiter $
  create procedure pro_while1(in insertCount int) 
  begin
  	declare i int default 1;
  	while i <= insertCount do
  		insert into admin(username, `password`)
  		values (concat('Rose', i), 666);
  		set i = i + 1;
  	end while;
  end $
  
  call pro_while1(100) $
  

• Add a leave statement;
  Case 2: batch insert, insert multiple records in admin table according to the number of times, and stop if the number of times is > 20;

  delimiter $
  create procedure pro_while2(in insertCount int) 
  begin
  	declare i int default 1;
  	a: while i <= insertCount do
  		insert into admin(username, `password`) 
  		values(concat('xiaohua', i), '000');
  		if i >= 20 then leave a;
  		end if;
  		set i = i + 1;
  	end while a;
  end $
  
  call pro_while2(100) $	
  

• Add iterate statement
  Case 3: batch insert, insert multiple records in admin table according to the number of times, and insert only an even number of times;

  delimiter $
  create procedure pro_while3(in insertCount int)
  begin
  	declare i int default 0;
  	a: while i <= insertCount do
  		set i = i + 1;
  		if mod(i, 2) != 0 then iterate a;
  		end if;
  		insert into admin(username, 'password')
  		values (concat('xiaogou', i), '222');
  	end while a;
  end $
  
  call pro_while3(100) $
  

Comprehensive case

• Create a table stringContent, where the fields are id (integer, self growth) and content (string); And inserting a specified number of random strings into the table;

drop table if exists stringContent;
create table stringContent(
	id int primary key auto_increment,
    content varchar(20)
);

delimiter $
create procedure randstr_insert(in insertCount int)
begin
	declare i int default 1;
	declare str varchar(20) default 'abcdefghijklmnopqrstuvwxyz';
	declare startIndex int default 1;
	declare len int default 1;
	while i <= insertCount do
		set len = floor(rand() * (20 - startIndex + 1) + 1);
		set startIndex = floor(rand() * 26 + 1);
		insert into stringContent(content)
		values (substr(str, startIndex, len));
		set i = i + 1;
	end while;
end $

call randstr_insert(10) $

select * from stringContent $