資料結構
網路
關係型資料庫管理系統
作業系統
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;如果 CASE 子句未執行，則 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-06-2020
2K+ 檢視次數
廣告