SQL Flow Control Statements – IF

測試環境為 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 + IF

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

IF 的寫法如下.

MariaDB [testdb]> HELP IF STATEMENT;
Name: 'IF STATEMENT'
Description:
Syntax:
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELS

開始定義 ItemStatus User-Defined Function.

DROP FUNCTION IF EXISTS ItemStatus;
DELIMITER //

CREATE FUNCTION ItemStatus (T2 TIMESTAMP)
RETURNS TEXT
BEGIN
DECLARE status VARCHAR(20);
IF T2 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN SET status='Expire'; 
ELSE SET status='Working' ;
END IF;
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 裡面使用的變數.
  • IF ELSE END IF;
    IF Statement,這個範例每個 IF 或是 ELSE 之後只有帶一個條件式,當有多個條件式時,可以使用 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)

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

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

MariaDB [(none)]> HELP IF FUNCTION;
Name: 'IF FUNCTION'
Description:
Syntax:
IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns
expr2; otherwise it returns expr3. IF() returns a numeric or string
value, depending on the context in which it is used.
SELECT ItemName , 
IF( T1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) , 'Expire' , 'Working') 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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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