MySQL collapse 20: process control statements for programmability

itread01 2021-01-22 16:18:00
mysql collapse process control statements


background

When it comes to process control statements , We use it more in program grammar , such as C# Of if..else...,while...,?: etc. . The same , stay MySQL in , There are also some syntax for process control , It's convenient for us to write functions 、 Control and process the logic while storing the process .

The common process SQL Statements can be used in stored procedures or function bodies . These include :IF Function 、IF Conditional statement 、CASE Sentence 、LOOP Sentence 、WHILE Sentence 、REPEAT Sentence 、LEAVE Sentences 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 set20 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 a little similar C# The ternary expression in grammar , Yes 3 Arguments , The first argument is the expression , The last two are values , When the expression holds, take the first value , When the expression does not hold, take the second value .

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

Output name and gender in student information (1 For men ,0 For women , This way if Function conversion )

 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 statement

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 , Then 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 THEN2 ...3 ELSEIF condition THEN4 ...5 ELSE6 ...7 END IF 

Code samples , Different grades are distributed according to test scores

 1 mysql> 2 /* If there is a function func_test2, Delete */ 3 DROP FUNCTION IF EXISTS fun_if; 4 /* The ending operator is $*/ 5 DELIMITER $ 6 /* Create functions */ 7 CREATE FUNCTION fun_if(score DECIMAL(10,2)) 8 RETURNS CHAR 9 BEGIN10 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 Query and CASE After VALUE Equal value , If it is found, the content of the branch will be executed , Otherwise ELSE Later content .CASE The statement is expressed as follows , Similar C# in switch:

1 CASE expr2 WHEN val1 THEN result1 or state1[;]( Optional , If it's a statement, you need to add a semicolon , The result value can be added )3 WHEN val2 THEN result2 or state24 ...5 ELSE resultn or staten6 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 

Use examples in functions or stored procedures

 1 mysql> 2 /* If there is a function func_test2, Delete */ 3 DROP FUNCTION IF EXISTS fun_case; 4 /* The ending operator is $*/ 5 DELIMITER $ 6 /* Create functions */ 7 CREATE FUNCTION fun_case(sex INT) 8 RETURNS VARCHAR(20) 9 BEGIN10 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 back while

Loop back while Similar to C# Medium while Turn around , We know it is C# Of while perhaps for In the sentence , Two key grammars are often used : Skip the current loop (continue) and End loop (break).

The same , stay MySQL There are also two grammars corresponding to skip and end loop in .

1 ITERATE loop_label; -- Skip the current loop 
1 LEAVE loop_label; -- End loop 
while Grammar
1 [loop_label:]while condition do2 --Todo:loop body3 end while [loop_label]; 

loop_label: Loop label , and iterateleave It is used to control the loop inside the loop : Such as : Skip this loop 、 End loop .

condition: Loop condition , When the conditions are met , It will execute the loop body , When the conditions don't hold, the loop ends .

while Example

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

 1 /* Clear away first studentCount Table records */ 2 truncate table studentcount; 3 /* Delete the stored procedure if it exists */ 4 DROP PROCEDURE IF EXISTS sp_while1; 5 /* The ending operator is $*/ 6 DELIMITER $ 7 /* Establish storage process */ 8 CREATE PROCEDURE sp_while1(varial_count int) 9 BEGIN10 DECLARE idx int DEFAULT 1;11 DECLARE uname VARCHAR(30) DEFAULT '';12 loop_label:WHILE idx<=varial_count DO13 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 terminator is set to ;*/19 DELIMITER ; 

Call store process , The range of values given is 10, So take it out here 1~10 Save your data to 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 the loop , Similar to C# Medium continue and break. Let's test it in an example :

encounter studentname=lala When , End loop , Skip a single loop when you meet an even number .

 1 /* Clear away first studentCount Table records */ 2 truncate table studentcount; 3 /* Delete the stored procedure if it exists */ 4 DROP PROCEDURE IF EXISTS sp_while2; 5 /* The ending operator is $*/ 6 DELIMITER $ 7 /* Establish storage process */ 8 CREATE PROCEDURE sp_while2(varial_count int) 9 BEGIN10 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 For lala My classmates , End loop */16 IF uname='lala' THEN17 LEAVE loop_label;18 /* If idx Even number , Then skip this loop */19 ELSEIF idx%2=0 THEN20 ITERATE loop_label;21 END IF; 22 INSERT into studentCount values (idx,uname); 23 END WHILE;24 END $25 /* The terminator is set to ;*/26 DELIMITER ; 

Call store process , Output data that meet the requirements :

 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 back repeat

repeat Grammar
1 [loop_label:]repeat2 -- Todo loop body3 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 first, and then execute loop body ,repeat Loop is more like do...while Turn around , That is, the loop will always be executed first , Then judge the conditions for ending the loop , Not meeting the end condition , The loop body continues to execute .

 1 /* Clear away first studentCount Table records */ 2 truncate table studentcount; 3 /* Delete the stored procedure if it exists */ 4 DROP PROCEDURE IF EXISTS sp_repeat; 5 /* The ending operator is $*/ 6 DELIMITER $ 7 /* Establish storage process */ 8 CREATE PROCEDURE sp_repeat(varial_count int) 9 BEGIN10 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 For lala My classmates , End loop */16 IF uname='lala' THEN17 LEAVE loop_label;18 /* If idx Even number , Then skip this loop */19 ELSEIF idx%2=0 THEN20 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 terminator is set to ;*/27 DELIMITER ; 

Pay attention to the change of conditions , Here's the call store process , Output the required information :

 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 back loop

loop Grammar
1 [loop_label:]loop2 --Todo loop body3 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 circle , It needs to be used in the loop body iterate perhaps leave To control the execution of loop .

 1 /* Clear away first studentCount Table records */ 2 truncate table studentcount; 3 /* Delete the stored procedure if it exists */ 4 DROP PROCEDURE IF EXISTS sp_loop; 5 /* The ending operator is $*/ 6 DELIMITER $ 7 /* Establish storage process */ 8 CREATE PROCEDURE sp_loop(varial_count int) 9 BEGIN10 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 For lala My classmates , End loop */16 IF uname='lala' THEN17 LEAVE loop_label;18 /* If idx Even number , Then skip this loop */19 ELSEIF idx%2<>0 THEN20 ITERATE loop_label;21 /* Add a condition for the end count to jump out */22 ELSEIF idx>varial_count THEN23 LEAVE loop_label;24 END IF; 25 INSERT into studentCount values (idx,uname); 26 27 END LOOP;28 END $29 /* The terminator is set to ;*/30 DELIMITER ; 

Call store process , And output the information 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、 Got it IF Function , It is often used in SELECT In the sentence , Similar to C# The ternary expression in .
2、IF Conditional expression , Similar to C# Medium IF... ELSE..., It is often used in the logic of judgment and selection in functions or stored procedures .
3、 Understanding CASE There are two usages of the statement , One is used in SELECT Use in , One is used in functions and stored procedures .
4、 Understand the use of three kinds of loop body ,while、repeat Corresponding to C# Medium while and do while Turn around ,loop It's like a while(true) The circle of death .
5、 The gyrosomes are all contained in begin end in , The control of gyrosomes depends on leave and iterate,leave It is equivalent to break, That is, exit the whole loop body ,iterate Similar to continue, That is to skip this cycle

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210122161608052t.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课程百度云