background

When it comes to process control statements , We use it more in program syntax , such as C# Of if..else...,while...,?: etc. . alike , stay MySQL in , There are also some syntax for process control , So we're writing functions 、 Control and process the logic when storing procedure .

The common process SQL Statements can be used in stored procedures or function bodies . These include :IF function 、IF Conditional statements 、CASE sentence 、LOOP sentence 、WHILE sentence 、REPEAT sentence 、LEAVE Statement and ITERATE sentence , They greatly facilitate our process control .

Let's look at it one by one .

Process statement decomposition

Data base

 1 mysql> select * from students;
2 +-----------+-------------+-------+---------+-----+
3 | studentid | studentname | score | classid | sex |
4 +-----------+-------------+-------+---------+-----+
5 | 1 | brand | 105.5 | 1 | 1 |
6 | 2 | helen | 98.5 | 1 | 0 |
7 | 3 | lyn | 97 | 1 | 0 |
8 | 4 | sol | 97 | 1 | 1 |
9 | 5 | b1 | 89 | 2 | 1 |
10 | 6 | b2 | 90 | 2 | 1 |
11 | 7 | c1 | 76 | 3 | 0 |
12 | 8 | c2 | 73.5 | 3 | 0 |
13 | 9 | lala | 73 | 0 | 0 |
14 | 10 | A | 100 | 3 | 1 |
15 | 16 | test1 | 100 | 0 | 1 |
16 | 17 | trigger2 | 107 | 0 | 1 |
17 | 22 | trigger1 | 100 | 0 | 0 |
18 +-----------+-------------+-------+---------+-----+
19 13 rows in set
20
21 mysql> select * from scores;
22 +-----------+---------+-------+
23 | scoregrad | downset | upset |
24 +-----------+---------+-------+
25 | A | 81 | 90 |
26 | B | 71 | 80 |
27 | C | 61 | 70 |
28 | D | 51 | 60 |
29 | S | 91 | 100 |
30 | S+ | 101 | 120 |
31 +-----------+---------+-------+
32 6 rows in set 

IF function

It's kind of similar C# Ternary expressions in grammar , Yes 3 Parameters , The first argument is the expression , The last two are values , When the expression holds, take the first value , Take the second value when the expression doesn't hold .

1 if(expr,val1,val2); -- grammar  

Output the name and gender in the student information (1 For male ,0 For female , This way if Function transformation )

 1 mysql> select studentname,if(sex=0,' Woman ',' male ') from students where classid<>0;
2 +-------------+---------------------+
3 | studentname | if(sex=0,' Woman ',' male ') |
4 +-------------+---------------------+
5 | brand | male |
6 | helen | Woman |
7 | lyn | Woman |
8 | sol | male |
9 | b1 | male |
10 | b2 | male |
11 | c1 | Woman |
12 | c2 | Woman |
13 | A | male |
14 +-------------+---------------------+
15 9 rows in set 

IF Conditional statements

IF Statement is used for conditional judgment , Perform different operations according to different conditions . The statement first determines IF Whether the latter condition is true or not , execute THEN The following sentence , If it is false, continue to judge IF Statement until it is true , When none of the above is satisfied, execute ELSE What follows the statement .

1 IF condition THEN
2 ...
3 ELSEIF condition THEN
4 ...
5 ELSE
6 ...
7 END IF 

Code example , Different grades are distributed according to test scores

 1 mysql>
2 /* If a function exists func_test2, Delete */
3 DROP FUNCTION IF EXISTS fun_if;
4 /* The declaration terminator is $*/
5 DELIMITER $
6 /* Create a function */
7 CREATE FUNCTION fun_if(score DECIMAL(10,2))
8 RETURNS CHAR
9 BEGIN
10 DECLARE score_grad VARCHAR(5) DEFAULT '';
11 IF score>100 THEN SET score_grad='S';
12 ELSEIF (score BETWEEN 91 AND 100) THEN SET score_grad='A';
13 ELSEIF (score BETWEEN 81 AND 90) THEN SET score_grad='B';
14 ELSEIF (score BETWEEN 71 AND 80) THEN SET score_grad='C' ;
15 ELSE set score_grad='D';
16 END IF;
17 return score_grad;
18 END $
19 /* Reset the terminator to ;*/
20 DELIMITER ;
21 Query OK, 0 rows affected 

Execution results

1 mysql> select fun_if(101),fun_if(100),fun_if(90),fun_if(80),fun_if(70);
2 +-------------+-------------+------------+------------+------------+
3 | fun_if(101) | fun_if(100) | fun_if(90) | fun_if(80) | fun_if(70) |
4 +-------------+-------------+------------+------------+------------+
5 | S | A | B | C | D |
6 +-------------+-------------+------------+------------+------------+
7 1 row in set 

CASE sentence

CASE The statement is a multi branch statement structure , The statement starts with WHEN After VALUE Search for and CASE After VALUE Equal value , If it is found, the content of the branch is executed , Otherwise execution ELSE Later content .CASE The statement is expressed as follows , similar C# in switch:

1 CASE expr
2 WHEN val1 THEN result1 or state1[;]( optional , If it's a statement, you need a semicolon , The result value can be added )
3 WHEN val2 THEN result2 or state2
4 ...
5 ELSE resultn or staten
6 END [CASE] ( optional , stay begin end We need to add case,select You don't need to ) 

stay select Example used in

 1 mysql> select studentname,case sex WHEN 0 THEN ' Woman ' WHEN 1 THEN ' male ' end as sex
2 from students where classid<>0;
3 +-------------+-----+
4 | studentname | sex |
5 +-------------+-----+
6 | brand | male |
7 | helen | Woman |
8 | lyn | Woman |
9 | sol | male |
10 | b1 | male |
11 | b2 | male |
12 | c1 | Woman |
13 | c2 | Woman |
14 | A | male |
15 +-------------+-----+
16 9 rows in set 

Using examples in functions or stored procedures

 1 mysql>
2 /* If a function exists func_test2, Delete */
3 DROP FUNCTION IF EXISTS fun_case;
4 /* The declaration terminator is $*/
5 DELIMITER $
6 /* Create a function */
7 CREATE FUNCTION fun_case(sex INT)
8 RETURNS VARCHAR(20)
9 BEGIN
10 DECLARE sexStr VARCHAR(20) DEFAULT '';
11 CASE sex
12 WHEN 0 then set sexStr=' Woman ';
13 WHEN 1 then set sexStr=' male ';
14 ELSE set sexStr=' Not sure ';
15 END CASE;
16 return sexStr;
17 END $
18 /* Reset the terminator to ;*/
19 DELIMITER ;
20
21 Query OK, 0 rows affected 

Function execution result

 1 mysql> select studentname,fun_case(sex) from students where classid<>0;
2 +-------------+---------------+
3 | studentname | fun_case(sex) |
4 +-------------+---------------+
5 | brand | male |
6 | helen | Woman |
7 | lyn | Woman |
8 | sol | male |
9 | b1 | male |
10 | b2 | male |
11 | c1 | Woman |
12 | c2 | Woman |
13 | A | male |
14 +-------------+---------------+
15 9 rows in set 

Loop statement while

Loop statement while Be similar to C# Medium while loop , We know that C# Of while perhaps for In the sentence , Two key grammars are often used : Skip the current loop (continue) and End of cycle (break).

alike , stay MySQL There are also two grammars corresponding to skip and end loops in .

1 ITERATE loop_label; -- Skip the current loop 
1 LEAVE loop_label; -- End of cycle 
while grammar
1 [loop_label:]while condition do
2 --Todo:loop body
3 end while [loop_label]; 

loop_label: Circular label , and iterateleave Combined to control the loop within the loop : Such as : Skip this cycle 、 End of cycle .

condition: The loop condition , When conditions are met , The loop body is executed , End the cycle when the condition doesn't hold .

while Example

The following script demonstrates how to students In the table studentid Data within a given range of values is stored in another table .

 1 /* Clear away first studentCount Table record */
2 truncate table studentcount;
3 /* Delete the stored procedure if it exists */
4 DROP PROCEDURE IF EXISTS sp_while1;
5 /* The declaration terminator is $*/
6 DELIMITER $
7 /* Create stored procedure */
8 CREATE PROCEDURE sp_while1(varial_count int)
9 BEGIN
10 DECLARE idx int DEFAULT 1;
11 DECLARE uname VARCHAR(30) DEFAULT '';
12 loop_label:WHILE idx<=varial_count DO
13 select studentname into uname from students where studentid = idx;
14 INSERT into studentCount values (idx,uname);
15 SET idx=idx+1;
16 END WHILE;
17 END $
18 /* The ending character is set to ;*/
19 DELIMITER ; 

Calling stored procedure , The range of values given is 10, So take it out here 1~10 Data stored in studentCount In the table

 1 mysql> CALL sp_while1(10);
2 Query OK, 1 row affected
3
4 mysql> select * from studentCount;
5 +-----------+-------------+
6 | studentid | studentname |
7 +-----------+-------------+
8 | 1 | brand |
9 | 2 | helen |
10 | 3 | lyn |
11 | 4 | sol |
12 | 5 | b1 |
13 | 6 | b2 |
14 | 7 | c1 |
15 | 8 | c2 |
16 | 9 | lala |
17 | 10 | A |
18 +-----------+-------------+
19 10 rows in set 
while Example : contain iterate/leave

As we explained earlier ,iterate and leave They represent skipping this cycle , Be similar to C# Medium continue and break. Let's test it in an example :

encounter studentname=lala when , End of cycle , Skip a single loop when even numbers are encountered .

 1 /* Clear away first studentCount Table record */
2 truncate table studentcount;
3 /* Delete the stored procedure if it exists */
4 DROP PROCEDURE IF EXISTS sp_while2;
5 /* The declaration terminator is $*/
6 DELIMITER $
7 /* Create stored procedure */
8 CREATE PROCEDURE sp_while2(varial_count int)
9 BEGIN
10 DECLARE idx int DEFAULT 0;
11 DECLARE uname VARCHAR(30) DEFAULT '';
12 loop_label:WHILE idx<=varial_count DO
13 SET idx=idx+1;
14 select studentname into uname from students where studentid = idx;
15 /* If you encounter studentname by lala Classmate , End of cycle */
16 IF uname='lala' THEN
17 LEAVE loop_label;
18 /* If idx For the even , Then skip this cycle */
19 ELSEIF idx%2=0 THEN
20 ITERATE loop_label;
21 END IF;
22 INSERT into studentCount values (idx,uname);
23 END WHILE;
24 END $
25 /* The ending character is set to ;*/
26 DELIMITER ; 

Calling stored procedure , Output qualified data :

 1 mysql> CALL sp_while2(10);
2 Query OK, 1 row affected
3
4 mysql> select * from studentCount;
5 +-----------+-------------+
6 | studentid | studentname |
7 +-----------+-------------+
8 | 1 | brand |
9 | 3 | lyn |
10 | 5 | b1 |
11 | 7 | c1 |
12 +-----------+-------------+
13 4 rows in set

Loop statement repeat

repeat grammar
1 [loop_label:]repeat
2 -- Todo loop body
3 until condition
4 end repeat [loop_label]; 

You can compare the above while The grammar of ,while It is to judge whether the condition is tenable before executing the circulatory body ,repeat Circulation is more like do...while loop , The loop is always executed first , Then judge the condition of ending the cycle , Does not meet the end condition , The circulatory body continues to execute .

 1 /* Clear away first studentCount Table record */
2 truncate table studentcount;
3 /* Delete the stored procedure if it exists */
4 DROP PROCEDURE IF EXISTS sp_repeat;
5 /* The declaration terminator is $*/
6 DELIMITER $
7 /* Create stored procedure */
8 CREATE PROCEDURE sp_repeat(varial_count int)
9 BEGIN
10 DECLARE idx int DEFAULT 0;
11 DECLARE uname VARCHAR(30) DEFAULT '';
12 loop_label:REPEAT
13 SET idx=idx+1;
14 select studentname into uname from students where studentid = idx;
15 /* If you encounter studentname by lala Classmate , End of cycle */
16 IF uname='lala' THEN
17 LEAVE loop_label;
18 /* If idx For the even , Then skip this cycle */
19 ELSEIF idx%2=0 THEN
20 ITERATE loop_label;
21 END IF;
22 INSERT into studentCount values (idx,uname);
23 UNTIL idx>varial_count
24 END REPEAT;
25 END $
26 /* The ending character is set to ;*/
27 DELIMITER ; 

Pay attention to the change of conditions , Here's calling stored procedures , Output the data you need :

 1 mysql> CALL sp_repeat(10);
2 Query OK, 1 row affected
3
4 mysql> select * from studentCount;
5 +-----------+-------------+
6 | studentid | studentname |
7 +-----------+-------------+
8 | 1 | brand |
9 | 3 | lyn |
10 | 5 | b1 |
11 | 7 | c1 |
12 +-----------+-------------+
13 4 rows in set 

Loop statement loop

loop grammar
1 [loop_label:]loop
2 --Todo loop body
3 end loop [loop label]; 

loop Unlike while and repeat There are control conditions , If you don't meet the conditions, you will jump out . So it's actually going to be implemented all the time , If you don't take the initiative to interrupt or jump out , It's like a dead cycle , It needs to be used in the circulatory body iterate perhaps leave To control the execution of the loop .

 1 /* Clear away first studentCount Table record */
2 truncate table studentcount;
3 /* Delete the stored procedure if it exists */
4 DROP PROCEDURE IF EXISTS sp_loop;
5 /* The declaration terminator is $*/
6 DELIMITER $
7 /* Create stored procedure */
8 CREATE PROCEDURE sp_loop(varial_count int)
9 BEGIN
10 DECLARE idx int DEFAULT 0;
11 DECLARE uname VARCHAR(30) DEFAULT '';
12 loop_label:LOOP
13 SET idx=idx+1;
14 select studentname into uname from students where studentid = idx;
15 /* If you encounter studentname by lala Classmate , End of cycle */
16 IF uname='lala' THEN
17 LEAVE loop_label;
18 /* If idx For the even , Then skip this cycle */
19 ELSEIF idx%2<>0 THEN
20 ITERATE loop_label;
21 /* Add a condition for the end count to jump out */
22 ELSEIF idx>varial_count THEN
23 LEAVE loop_label;
24 END IF;
25 INSERT into studentCount values (idx,uname);
26
27 END LOOP;
28 END $
29 /* The ending character is set to ;*/
30 DELIMITER ; 

Calling stored procedure , And output the data you need :

 1 mysql> CALL sp_loop(6);
2 Query OK, 1 row affected
3
4 mysql> select * from studentCount;
5 +-----------+-------------+
6 | studentid | studentname |
7 +-----------+-------------+
8 | 2 | helen |
9 | 4 | sol |
10 | 6 | b2 |
11 +-----------+-------------+
12 3 rows in set

summary

1、 I understand IF function , It is often used in SELECT In the sentence , Be similar to C# The ternary expression in .
2、IF Conditional expression , Be similar to C# Medium IF... ELSE..., It is mostly used in the judgment and selection logic of functions or stored procedures .
3、 understand CASE There are two usages of the statement , One for SELECT Use in , One is used in functions and stored procedures .
4、 Understand the use of three kinds of circulators ,while、repeat They correspond to each other C# Medium while and do while loop ,loop Similar to a while(true) The death of the loop .
5、 The loop body is contained in the begin end in , The control of the circulatory system depends on leave and iterate,leave amount to break, That is, exit the whole circulation body ,iterate Be similar to continue, That is to skip this cycle .

MySQL Break down altogether 20: More articles on process control statements for programmability

  1. relational database SQL Programmability transactions for

    Preface Previously, relational databases SQL The programmability function of ( User defined functions ) The article mentioned that relational databases provide programmable functions . stored procedure . Business . Triggers and cursors , The function has been introduced before . stored procedure , This article introduces the use of transactions .( Or with the silver in front ...

  2. relational database SQL Programmability of stored procedures

    Preface Previously, relational databases SQL The programmability function of ( User defined functions ) The article mentioned that relational databases provide programmable functions . stored procedure . Business . Triggers and cursors , The function has been introduced before , This article introduces the creation of stored procedures . perform . Delete .( Still before ...

  3. relational database SQL The programmability function of ( User defined functions )

    Preface In the relational database, in addition to the previous several basic database and data table operations , Programmable functions are also provided . stored procedure . Business . Triggers and cursors . This article is about functions . There are two kinds of functions : System function User defined functions preparation Here's silver ...

  4. 《[MySQL Technology insider :SQL Programming 》 Reading notes

    <[MySQL Technology insider :SQL Programming > Reading notes 2019 year 3 month 31 Japan 23:12:11 It is strictly prohibited to reprint !!! <MySQL Technology insider :SQL Programming > This book is one of my favorite domestic authors, Jiang Chengyao , ...

  5. relational database SQL The programmable trigger of

    Preface Previously, relational databases SQL The programmability function of ( User defined functions ) The article mentioned that relational databases provide programmable functions . stored procedure . Business . Triggers and cursors , The function has been introduced before . stored procedure . Business , This article introduces the use of triggers .( Or to ...

  6. SQL Server 2008 Spatial data application series 6 : be based on SQLCRL Spatial data programmability of

    original text :SQL Server 2008 Spatial data application series 6 : be based on SQLCRL Spatial data programmability of Friendship tips , The prerequisites for you to read this blog are as follows : 1. This example is based on Microsoft SQL Server 2008 ...

  7. mysql 5.6.20 Installation 、 Configure the service 、 Set the encoding format

    One . install Installation environment        System :Window 32        edition :Mysql 5.6.20 1. First of all, from the official website http://dev.mysql.com/downloads/mysql/ ...

  8. win 2012 install mysql 5.7.20 And error report This application requires Visual Studio 2013 Redistributable. Please install the Redistributable then run this installer again Solutions for

    This paper addresses :http://www.cnblogs.com/jying/p/7764147.html    Reprint please indicate the source . The installation process is actually quite simple , Basically the next step, the next step , You can refer to my other article mysql Installation article : ...

  9. Windows Next MySql 5.7.20 Installation and data and my.ini Configuration of files ( turn )

    Windows Next MySql 5.7.20 Installation and data and my.ini Configuration of files     In this paper, through the form of pictures and text to introduce MySql 5.7.20 Installation and data and my.ini How to configure files . my ...

  10. mysql 5.7.20 server status yes stopped Solutions for

    mysql 5.7.20 server status yes stopped Solutions for In the installation mysql 5.7.20 In the process of , There's nothing wrong with the first few processes , But the last step went wrong . When check Always hint con ...

Random recommendation

  1. MySQL Fabric and MyBatis Problems encountered in the integration process of

    This is what I was integrating yesterday MySQL Fabric and MyBatis Problems encountered in , Problems that took more than half a day to solve , In the process of solving the problem, I searched the Internet for a long time , No solution has been found . Now write it down , Hope to help friends with the same problems . If you friends ...

  2. javascript Duck argument method to achieve the interface

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/ ...

  3. Use DocX Open source components , Realize the filling of dynamic data .

    1. Explain first , What is dynamic data , Dynamic data means , The format of a piece of data is fixed , But the number of data is not fixed . 2. Application environment , In a table, if , Now the table has three lines n Column , If you need to add a line of the same specification or n That's ok , should ...

  4. [BTS] Correct the specified Action, or refer to the documentation on the allowed formats for the Actions

    A message sent to adapter "WCF-SAP" on send port "CNILG.iHouse.SAP.WCFSAP" with ...

  5. cocos2dx 3.x Build the above version Mac Environmental Science ( It's 100 percent feasible )

    Recently, due to work , Have the opportunity to contact the game industry , Tell the truth , My original intention of learning the program is to play games , So I created a cocos2d-x To record what I'm learning cocos2d-x The process of growing up . First, chapter one , Want to learn cocos2 ...

  6. DataGridView binding List

    DataGridView binding List<T> Will not automatically update The right way is to  List<T>  Set to BindingList<T> that will do ( Two way binding )

  7. The two most frequently used C++ How to use serialization scheme (protobuf and boost serialization)

    Reading guide 1.  What is serialization ? 2.  Why serialize ? Where are the advantages ? 3. C++ Four ways to serialize objects 4.  Two of the most frequently used serialization schemes use experience Text 1.  What is serialization ? When programming an application, you often have to ...

  8. Unity Release WebGL How to modify the default loading progress bar

    Unity Release WebGL After version , Need to get rid of Unity Of Logo, First close Splash Image Remove Made with Unity Start screen ( stay File->Build Settings->Pl ...

  9. Map plotting system V1.0 The beta 【 Affirming : Source network 】

    Map plotting system V1.0 The beta [ Affirming : Source network ] Address :http://blog.csdn.net/allgis/article/details/39718085

  10. Unite Shanghai 2019 Full schedule exposure ( Recommended collection )

    https://mp.weixin.qq.com/s/KvAyXpDhqWROtTX1Ol3a4Q 5 month 10-12 Japan ,Unite Shanghai 2019 It will be officially opened in Shanghai International Conference Center . This conference consists of ...