|
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; CASE语句将在没有任何WHEN子句被执行时执行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)登录后复制 |
