SQL Flow Control Statements – Case

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

實際來看一個範例,用來檢視該物品的保存期限是否超過一年.

先來建立用來測試的資料.欄位為 K1 (INT – Primary Key) , ItemName 物品名稱 (VARCHAR) 與 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 ItemList (K1 INT(11) NOT NULL auto_increment, ItemName VARCHAR(50), T1 TIMESTAMP , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DESCRIBE ItemList;
+----------+-------------+------+-----+---------------------+-------------------------------+
| Field    | Type        | Null | Key | Default             | Extra                         |
+----------+-------------+------+-----+---------------------+-------------------------------+
| K1       | int(11)     | NO   | PRI | NULL                | auto_increment                |
| ItemName | varchar(50) | YES  |     | NULL                |                               |
| T1       | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
+----------+-------------+------+-----+---------------------+-------------------------------+
3 rows in set (0.004 sec)

並建立五筆資料.

MariaDB [testdb]> INSERT INTO ItemList (ItemName , T1 ) VALUES ('Can1' , CURRENT_TIMESTAMP() ) ,  ('Can2' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 180 DAY) ) , ('Can3' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) ) , ('Can4' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 270 DAY) ) , ('Can5' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 370 DAY) );
Query OK, 5 rows affected (0.004 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM ItemList;
+----+----------+---------------------+
| K1 | ItemName | T1                  |
+----+----------+---------------------+
|  1 | Can1     | 2020-05-23 11:00:03 |
|  2 | Can2     | 2019-11-25 11:00:03 |
|  3 | Can3     | 2019-05-23 11:00:03 |
|  4 | Can4     | 2019-08-27 11:00:03 |
|  5 | Can5     | 2019-05-19 11:00:03 |
+----+----------+---------------------+
5 rows in set (0.001 sec)

裡面使用到幾個與日期時間相關的函數:

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

裡面用到一些日期與時間的函數,詳細使用方式請參考 http://benjr.tw/102964

User-Defined Function + CASE Statements

通常指令太長時可以把它儲存成為 Stored Routines (Procedure 或是 User-Defined Function (UDF) , 關於 User-Defined Function (UDF) 請參考 – http://benjr.tw/102916

CASE 的寫法有兩種如下.

MariaDB [testdb]> HELP CASE STATEMENT;
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

開始定義 ItemStatus User-Defined Function (CASE 的寫法有兩種).

  1. 方法一 : Excludes expression
    DROP FUNCTION IF EXISTS ItemStatus;
    DELIMITER //
    
    CREATE FUNCTION ItemStatus (T2 TIMESTAMP)
    RETURNS TEXT
    BEGIN
    DECLARE status VARCHAR(20);
    CASE 
    WHEN T2 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN SET status='Expire'; 
    ELSE SET status='Working' ;
    END CASE;
    RETURN Status;
    END//
    
    DELIMITER ;
    
    • DROP FUNCTION IF EXISTS ItemStatus;
      先確認之前沒有定義過 ItemStatus 這個 User-Defined Function,有的話移除.
    • DELIMITER // //
      mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
    • DELIMITER ;
      把結束符號修改回為 分號 “;” .
    • CREATE FUNCTION ItemStatus (T2 TIMESTAMP)
      建立一個函數,須包含函數名稱與傳入變數與型態.
    • RETURNS TEXT
      回傳值的型態.
    • BEGIN END
      函數程式宣告必須包含在裡面.
    • DDECLARE status VARCHAR(20);
      宣告一個 User-Defined Function 裡面使用的變數.
    • CASE WHEN ELSE END CASE;
      Case Statement,這個範例每個 WHEN 之後只有帶一個條件式,當有多個條件式時,可以使用 Condition#1 AND / OR Condition#2 來使用.
    • RETURN Status;
      回傳變數 Status 的值.

    User-Defined Function 使用到的函數說明:

    • DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR)
      將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

    測試結果為.

    1. Working – 沒有超過一年的使用期限.
    2. Expire – 使用次數在 100 次內,但超過一年的使用期限.
    MariaDB [testdb]> SELECT ItemName , ItemStatus(T1) AS Status , T1 FROM ItemList;
    +----------+---------+---------------------+
    | ItemName | Status  | T1                  |
    +----------+---------+---------------------+
    | Can1     | Working | 2020-05-23 11:00:03 |
    | Can2     | Working | 2019-11-25 11:00:03 |
    | Can3     | Expire  | 2019-05-23 11:00:03 |
    | Can4     | Working | 2019-08-27 11:00:03 |
    | Can5     | Expire  | 2019-05-19 11:00:03 |
    +----------+---------+---------------------+
    5 rows in set (0.001 sec)
    
  2. 方法二 : Includes expression
    這個方式並不適合這個範例.

    CASE case_value
        WHEN when_value THEN statement_list
        [WHEN when_value THEN statement_list] ...
        [ELSE statement_list]
    END CASE
    

CASE OPERATOR 流程控制函數 (Control Flow Functions)

如果比較簡短的敘述時,可以使用 CASE OPERATOR 直接在 SELECT 裡面使用 CASE OPERATOR 流程控制函數 (Control Flow Functions) – http://benjr.tw/102747
CASE OPERATOR 的語法如下:

MariaDB [(none)]> HELP CASE OPERATOR;
Name: 'CASE OPERATOR'
Description:
Syntax:
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
SELECT ItemName , 
CASE 
WHEN T1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN 'Expire'
ELSE 'Working'
END  AS Status , 
T1 
FROM ItemList;

執行結果.

+----------+---------+---------------------+
| ItemName | Status  | T1                  |
+----------+---------+---------------------+
| Can1     | Working | 2020-05-23 11:00:03 |
| Can2     | Working | 2019-11-25 11:00:03 |
| Can3     | Expire  | 2019-05-23 11:00:03 |
| Can4     | Working | 2019-08-27 11:00:03 |
| Can5     | Expire  | 2019-05-19 11:00:03 |
+----------+---------+---------------------+
5 rows in set (0.002 sec)

遇過的問題

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

Case not found for case statement

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

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

發佈留言

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

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