MySQL collapse 20: process control statement of programmability

Weng Zhihua 2021-01-22 15:28:24
mysql collapse process control statement


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 .

版权声明
本文为[Weng Zhihua]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210122152657382h.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云