SQL Control Flow Functions – Case , IF

測試環境為 CentOS 7 x86_64 (虛擬機)

CASE 的語法如下,詳細請參考 https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#operator_case 官網的說明.

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

實際來看一個範例,該物品的使用上限為 100 次,且使用期限不能超過一年.先來建立用來測試的資料.欄位為 loops(使用次數) , testloops(使用上限) 與 t1( 進貨時間 timestamp).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> CREATE TABLE testing (loops varchar(6), testloops varchar(6), t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

並建立五筆資料.

MariaDB [testdb]> INSERT INTO testing VALUES ('9' , '100' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('100' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 DAY) );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('78' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('100' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('90' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM testing;
+-------+-----------+---------------------+
| loops | testloops | t1                  |
+-------+-----------+---------------------+
| 9     | 100       | 2019-06-05 09:47:55 |
| 100   | 100       | 2019-06-03 09:48:02 |
| 78    | 100       | 2017-06-05 09:48:08 |
| 100   | 100       | 2017-06-05 09:48:13 |
| 90    | 100       | 2017-06-05 09:48:19 |
+-------+-----------+---------------------+
5 rows in set (0.01 sec)

函數說明:

  • CURRENT_TIMESTAMP()
    功能與 NOW 一樣,都是回傳目前系統的日期時間.
  • DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR)
    將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

裡面用到一些日期與時間的函數,詳細使用方式請參考 https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

我們要怎麼透過這些資料 + case 的功能把物品區分為三類.

  1. Working – 使用次數在 100 次內,且沒有超過一年的使用期限.
  2. Damage – 使用次數超過 100 次了.
  3. Expire – 使用次數在 100 次內,但超過一年的使用期限.

CASE 語法

MariaDB [testdb]> SELECT 
    ->     loops, 
    ->     testloops, 
    ->     t1,
    ->     CASE WHEN CAST(loops AS SIGNED) >= CAST(testloops AS SIGNED) THEN 'Damage' WHEN t1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN 'Expire' ELSE 'Working' END AS Status
    -> FROM testing;
+-------+-----------+---------------------+---------+
| loops | testloops | t1                  | Status  |
+-------+-----------+---------------------+---------+
| 9     | 100       | 2019-06-05 09:47:55 | Working |
| 100   | 100       | 2019-06-03 09:48:02 | Damage  |
| 78    | 100       | 2017-06-05 09:48:08 | Expire  |
| 100   | 100       | 2017-06-05 09:48:13 | Damage  |
| 90    | 100       | 2017-06-05 09:48:19 | Expire  |
+-------+-----------+---------------------+---------+
5 rows in set (0.00 sec)

函數說明:

  • CAST(loops AS SIGNED)
    因為當初 loops , testloops 的格式都是 varchar() 文字,在比較的時候會出現問題,透過 CAST 函數作轉換(文字轉數字 SIGNED),更多關於 CAST 的使用請參考 https://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast
  • DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR)
    將目前時間剪一年,用來判斷是否超過一年.

通常指令太長時可以把它自訂成 Function , 關於 Function 請參考 – http://benjr.tw/99035 , http://benjr.tw/99048

Function + CASE

先確認之前沒有定義過 loopstatus 這個 FUNCTION,有的話移除.

MariaDB [testdb]> DROP FUNCTION IF EXISTS loopstatus;
Query OK, 0 rows affected (0.00 sec)

開始定義 loopstatus FUNCTION.

DELIMITER //

CREATE FUNCTION loopstatus (loops VARCHAR(20), testloops VARCHAR(20) ,t1 TIMESTAMP)
RETURNS TEXT
BEGIN
DECLARE status VARCHAR(20);
CASE 
WHEN CAST(loops AS SIGNED) >= CAST(testloops AS SIGNED) THEN SET status='Damage';
WHEN t1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN SET status='Expire'; 
ELSE SET status='Working' ;
END CASE;
RETURN status;
END//

DELIMITER ;

剛剛查指定可以使用 loopstatus FUNCTION.

MariaDB [testdb]> SELECT loops, testloops, t1, loopstatus (loops, testloops, t1) AS Status FROM testing;
+-------+-----------+---------------------+---------+
| loops | testloops | t1                  | Status  |
+-------+-----------+---------------------+---------+
| 9     | 100       | 2019-11-26 10:27:33 | Working |
| 100   | 100       | 2019-11-24 10:27:39 | Damage  |
| 78    | 100       | 2017-11-26 10:27:47 | Expire  |
| 100   | 100       | 2017-11-26 10:27:54 | Damage  |
| 90    | 100       | 2017-11-26 10:28:02 | Expire  |
+-------+-----------+---------------------+---------+
5 rows in set (0.00 sec)

Function + IF

先確認之前沒有定義過 loopstatus 這個 FUNCTION,有的話移除.

MariaDB [testdb]> DROP FUNCTION IF EXISTS loopstatus;
Query OK, 0 rows affected (0.00 sec)

開始定義 loopstatus FUNCTION.

DELIMITER //

CREATE FUNCTION loopstatus (loops VARCHAR(20), testloops VARCHAR(20) ,t1 TIMESTAMP)
RETURNS TEXT
BEGIN
DECLARE status VARCHAR(20);
IF CAST(loops AS SIGNED) >= CAST(testloops AS SIGNED) THEN SET status='Damage';
ELSEIF t1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN SET status='Expire'; 
ELSE SET status='Working' ;
END IF;
RETURN status;
END//

DELIMITER ;

剛剛查指定可以使用 loopstatus FUNCTION.

MariaDB [testdb]> SELECT loops, testloops, t1, loopstatus (loops, testloops, t1) AS Status FROM testing;
+-------+-----------+---------------------+---------+
| loops | testloops | t1                  | Status  |
+-------+-----------+---------------------+---------+
| 9     | 100       | 2019-11-26 10:27:33 | Working |
| 100   | 100       | 2019-11-24 10:27:39 | Damage  |
| 78    | 100       | 2017-11-26 10:27:47 | Expire  |
| 100   | 100       | 2017-11-26 10:27:54 | Damage  |
| 90    | 100       | 2017-11-26 10:28:02 | Expire  |
+-------+-----------+---------------------+---------+
5 rows in set (0.00 sec)

遇過的問題

執行時出現以下的錯誤訊息.

Case not found for case statement

當輸入值不在定義好的 CASE 狀況中,而且又沒有定義 ELSE 時,就會出現這種錯誤,需加入 ELSE 來預防輸入值不再預設情況時.

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料