Mysql process control

know

From the programming languages I have come into contact with at present, C, R, VB, Python, Javascript..., we can see that variables, expressions, process control (sequence, branch, loop) encapsulate some more advanced data structures, but the difference is that the application scenarios and language characteristics, in fact, the logic is the same, only familiar.

Select structure if-else; case

-- if-esle grammar
IF search_condition THEN 
    statement_list;
[ELSEIF search_condition THEN
    statement_list; ....]
ELSE
    statement_list;
END IF;

-- CASE grammar
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE;

OR:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

-- Push a statement at random
drop procedure if exists sayLove;
delimiter //
create procedure sayLove()
begin
    declare num int default 0;
    -- Generate a 1-5 Random Numbers Between
    set num := round(rand()*5);
    -- judge
    case num
        when 1 then select "If time could stop at the moment when we first met";
        when 2 then select "Ten miles of spring breeze is not as good as you";
        when 3 then select "Loving you is like loving life.";
        else
            select "The moonlight tonight is beautiful.";
    end case;
end //
delimiter ;

call sayLove();

-- out
mysql> call sayLove();
+----------------+
| The moonlight tonight is beautiful. |
+----------------+
| The moonlight tonight is beautiful. |
+----------------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sayLove();
+----------------+
| Loving you is like loving life. |
+----------------+
| Loving you is like loving life. |
+----------------+
1 row in set (0.14 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sayLove();
+----------------+
| Ten miles of spring breeze is not as good as you |
+----------------+
| Ten miles of spring breeze is not as good as you |
+----------------+
1 row in set (0.11 sec)

CASE can achieve, IF-ELSE can also fully, but only provides more options.

-- use if-esle Realization
drop procedure if exists sayLove;
delimiter //
create procedure sayLove()
begin
    declare num int default 0;
    -- Generate a 1-5 Random Numbers Between
    set num := round(rand()*5);
    -- judge
    if num=1 then select "If time could stop at the moment when we first met";
    elseif num=2 then select "Ten miles of spring breeze is not as good as you";
    elseif num-3 then select "Loving you is like loving life.";
    else
        select "The moonlight tonight is beautiful.";
    end if;
end //
delimiter ;

call sayLove();

MySql cycle

• WHILE... DO... END WHILE is called a "Dang" type cycle, satisfying conditions to enter the circulatory body.
• LOOP... LEAVE... END LOOP "Up to Type Cycle"
• REPEAT ... UNTIL ... END REPEAT

While... do... loop

while search_condition do
    statement_list;
end while;

Repat... until... loop

repeat
    statement_list;
until search_condition;
end repeat;

Loop... leave loop

[begin_label:] loop
    statement_list;
    leave [begin_label];
end loop [end_label];

Cycle - Case 1+2+...n

-- while Achieve 1+2+3+..n and
-- What's easy for you to mix up: Forget at the end; end; Variable forget set;
-- Afferent parameter: in Incoming value; out: Input variable to receive the returned value; inout Input and Output
drop procedure if exists sumN_while;
delimiter //
create procedure sumN_while(in n int)
begin
    declare total int default 0;
    declare i int default 0;
    -- while ...do ....
    while i <= n do
        set total := total + i;
        set i := i + 1;
    -- Print out the results
    end while;
    select concat("1+2+..", n, "The sum is:", total) as 'Output';
end //
delimiter ;

call sumN_while(100);

-- out
call sumN_while(100);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------------------+
| Output               |
+----------------------+
| 1+2+..100 The sum is:5050 |
+----------------------+
1 row in set (0.10 sec)

Similarly with repeat... until... implementation, by the way, practice out type parameters

-- repeat Achieve 1+2+..n And
drop procedure if exists sumN_repeat;
delimiter //
-- Set in out Type parameter variable, Used to receive output values
create procedure sumN_repeat(out total int)
begin
    xxxx
end //
delimiter ;

drop procedure if exists sumN_repeat;
delimiter //
-- Settings re-entry out Type parameter variable, Used to receive output values
create procedure sumN_repeat(in n int)
begin
    declare i int default 0;
    declare total int default 0;
    
    -- repeat ... until ...
    repeat
        set total := total + i;
        set i := i + 1;
        -- Exit conditions until..True Just quit., Pay attention to while Difference
        until i > n
    end repeat;
    -- Print out the results internally
    select total;
end //
delimiter ;

-- out
call sumN_repeat(100);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.09 sec)

Use out type parameters.

-- repeat Achieve 1+2+..n And
drop procedure if exists sumN_repeat;
delimiter //
-- Settings re-entry out Type parameter variable, Used to receive output values
create procedure sumN_repeat(in n int, out total int)
begin
    declare i int default 0;
    
    set total := 0;  -- order: before decalre Again set, Can not be confused,Brother
    -- repeat ... until ...
    repeat
        set total := total + i;
        set i := i + 1;
        -- Exit conditions until..Note that the condition is True Time to quit
        until i > n
    end repeat;
end //
delimiter ;

-- set @ret := 0;
-- call sumN_repeat(100, @ret);
-- select @ret;

-- out
mysql> set @ret := 0;  -- This global variable @ret Used for receiving processes. total Value oh 
Query OK, 0 rows affected (0.00 sec)

mysql> call sumN_repeat(10000, @ret);
Query OK, 0 rows affected (0.04 sec)

mysql> select @ret;
+----------+
| @ret     |
+----------+
| 50005000 |
+----------+
1 row in set (0.08 sec)

Then use loop....leave to complete the wave.

-- loop ...leave ... Realize 1+2+..n And
drop procedure if exists sumN_loop;
delimiter //
create procedure sumN_loop(in n int, out total int)
begin
    declare i int default 0;
    set total := 0;
    -- loop, Take a label name first, Rewriting exit conditions, if-then...
    myLoop: loop
        if i > n then
            leave myLoop;
        end if;
        set total := total + i;
        set i := i + 1;
    end loop;
end //
delimiter ;

-- out
mysql> set @ret := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call sumN_loop(100, @ret);
Query OK, 0 rows affected (0.00 sec)

mysql> select @ret;
+------+
| @ret |
+------+
| 5050 |
+------+
1 row in set (0.11 sec)

Summary of MySql Control Flow

• Selection structure (if, case):
  • if - elseif- esle -end if;
  • case value when value1 then ; when value2 ...then .. else .. . end case;
• Loop structure (while, repeat, loop)
  • while .... do .... end while;
  • repeat ... until .... end repeat;
  • myLoop: loop ..... if ... then leave myLoop; end if ; ..... end loop;