測試環境為 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.
裡面用到一些日期與時間的函數,詳細使用方式請參考 https://benjr.tw/102964
User-Defined Function + CASE Statements
通常指令太長時可以把它儲存成為 Stored Routines (Procedure 或是 User-Defined Function (UDF) , 關於 User-Defined Function (UDF) 請參考 – https://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 的寫法有兩種).
- 方法一 : 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.
測試結果為.
- Working – 沒有超過一年的使用期限.
- 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)
- DROP FUNCTION IF EXISTS ItemStatus;
- 方法二 : 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) – https://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 來預防輸入值不再預設情況時.
沒有解決問題,試試搜尋本站其他內容