MySQL Stored Procedure learning notes-blocks, conditions, and loops

Block Definition

[Label:] Begin Variable and condition Declarations Cursor declarations Handler declarations Program code End [label];

 

You can use leave [label] to jump out of a block.

Example:

mysql> Create procedure nested_blocks5 ()
outer_block: Begin
declare l_status int;
set l_status = 1;
inner_block: begin
If (l_status = 1) Then
Leave inner_block;
end if;
select 'this statement will never be executed ';
end inner_block;
select 'end of program ';
end outer_block

Result:
Mysql> call nested_blocks5 () $
+ ---------------- +
| End of program |
+ ---------------- +
| End of program |
 

Note: If you create a stored procedure on the command line, you may need to use the delimiter command to change the default command terminator to another symbol, such as delimiter $

 

Nesting of blocks:Blocks can be nested, but note the valid range of the variables.

1. variables declared in the block do not exist outside the block.

Mysql> Create procedure nested_blocks1 ()
Begin
Begin
Declare inner_variable varchar (20 );
Set inner_variable = 'This is my private data ';
End;
Select inner_variable;
End;

Result:
Mysql> call nested_blocks1 ()
Error 1054 (42s22): Unknown column 'inner _ variable' in 'field list'
 

2. The block can overwrite the variable with the same name declared outside the block.

Mysql> Create procedure nested_blocks2 ()
Begin
Declare my_variable varchar (20 );
Set my_variable = 'this value was set in the outer Block ';
Begin
Set my_variable = 'this value was set in the inner Block ';
End;
Select my_variable, 'Changes in the inner Block are visible in the outer Block ';
End;
Result:
Mysql> call nested_blocks2 ()
+ --------------------- + ----------------------------------------------------------- +
| My_variable | changes in the inner Block are visible in the outer block |
+ --------------------- + ----------------------------------------------------------- +
| This value was set |
| In the inner Block | changes in the inner Block are visible in the outer block |
+ --------------------- + ----------------------------------------------------------- +
3. Changes to the variables declared in the block do not affect the variables with the same name declared outside the block.
Mysql> Create procedure nested_blocks3 ()
Begin
Declare my_variable varchar (20 );
Set my_variable = 'this value was set in the outer Block ';
Begin
Declare my_variable varchar (20 );
Set my_variable = 'this value was set in the inner Block ';
End;
Select my_variable, 'can't see changes made in the inner Block ';
End;

Result:

Mysql> call nested_blocks3 () $
+ --------------------------- + ------------------------------------------- +
| My_variable | can't see changes made in the inner Block |
+ --------------------------- + ------------------------------------------- +
| This value was set in the |
| Outer block | can't see changes made in the inner Block |
+ --------------------------- + ------------------------------------------- +
 

Condition Statement
 
If then statement

If expression then commands [Elseif expression then commands...] [Else commands] End if;

 
This statement can be nested
Example:
If (sale_value> 200) then call free_shipping (sale_id );
If (customer_status = 'platinum') then
Call apply_discount (sale_id, 20 );
Elseif (customer_status = 'gold') then
Call apply_discount (sale_id, 15 );
Else call apply_discount (sale_id, 5 );
End if;
End if;
 
Case statement
The case statement has two types of syntax:

Syntax 1:

Case expression When value then statements [When value then statements...] [Else statements] End case;

 
 
Syntax 2:

Case When condition then statements [When condition then statements...] [Else statements] End case;

 
Loop statement
 
Loop

[Label:] Loop Statements End loop [label];

 
 
The loop will not end automatically. You must useLeaveTo jump out. Similarly, useIterate label; To execute the next loop, similar to the GeneralProgramming LanguageContinue
Example
Set I = 0;
Loop1: loop
Set I = I + 1;
If I> = 10 then leave loop1;
Elseif Mod (I, 2) = 0 then iterate loop1;
End if;
Select Concat (I, "is an odd number ");
End loop loop1;

Repeat... Until Loop

[Label:] Repeat Statements Until expression End repeat [label]

 
When expression is true, it jumps out, with the same effect:

Some_label: loop Statements If expression then leave some_label; End if; End loop;

 
 
While Loop
 

[Label:] While expression do Statements End while [label]

Similar to repeat... until, the difference is that while first judges the condition and then executes the statement. Repeat... until first executes the statement and then judges the condition.