数据结构
网络
关系型数据库管理系统
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
JavaScript
PHP
物理
化学
生物
数学
英语
经济学
心理学
社会研究
时尚研究
法律研究如何在存储过程中使用 MySQL CASE 语句？
实际上，CASE 语句具有 IF-THEN-ELSE 语句的功能。它的语法如下 −
CASE
WHEN condition_1 THEN
{...statements to execute when condition_1 is TRUE...}
[ WHEN condition_2 THEN
{...statements to execute when condition_2 is TRUE...} ]
[ WHEN condition_n THEN
{...statements to execute when condition_n is TRUE...} ]
[ ELSE
{...statements to execute when all conditions were FALSE...} ]
END CASE;如果没有 WHEN 子句被执行，CASE 语句将执行 ELSE 子句。
为了演示在 MySQL 存储过程中使用CASE 语句，我们根据表“student_info”中的值创建以下存储过程，如下所示 −
mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 125 | Raman | Shimla | Computers | +------+---------+------------+------------+ 3 rows in set (0.00 sec)
以下查询将创建一个名为“coursedetails_CASE”的存储过程，其中包含 CASE 语句 −
mysql> Delimiter // mysql> CREATE PROCEDURE coursedetails_CASE(IN S_subject Varchar(20), OUT S_Course Varchar(50)) -> BEGIN -> DECLARE SUB VArchar(20); -> SELECT SUBJECT INTO Sub -> FROM Student_Info WHERE S_Subject = Subject; -> CASE S_Subject WHEN 'Computers' THEN -> SET S_Course = 'B.Tech(CSE)’; -> WHEN 'History' THEN -> SET S_Course = 'Masters in History'; -> WHEN 'Literature' THEN -> SET S_Course = 'Masters in English'; -> ELSE -> SET S_Course = 'Subject not in the table'; -> END CASE ; -> END // Query OK, 0 rows affected (0.11 sec)
现在，当我们调用此存储过程时，可以看到以下结果 −
mysql> DELIMITER ;
mysql> CALL coursedetails_CASE ('Computers', @S_course);
Query OK, 1 row affected (0.08 sec)
mysql> Select @S_Course;
+-------------+
| @S_Course |
+-------------+
| B.Tech(CSE) |
+-------------+
1 row in set (0.00 sec)
mysql> CALL coursedetails_CASE ('literature', @S_course);
Query OK, 1 row affected (0.00 sec)
mysql> Select @S_Course;
+--------------------+
| @S_Course |
+--------------------+
| Masters in English |
+--------------------+
1 row in set (0.00 sec)
mysql> CALL coursedetails_CASE ('Math', @S_course);
Query OK, 0 rows affected (0.00 sec)
mysql> Select @S_Course;
+--------------------------------+
| @S_Course |
+--------------------------------+
| Subject Not in the table |
+--------------------------------+
1 row in set (0.00 sec)
mysql> CALL coursedetails_CASE ('History', @S_course);
Query OK, 1 row affected (0.01 sec)
mysql> Select @S_Course;
+--------------------+
| @S_Course |
+--------------------+
| Masters in History |
+--------------------+
1 row in set (0.00 sec)
更新于：22-Jun-2020
2K+ 浏览
广告