mysql儲存過程及日期函式實踐

業務場景說明：

假設待稽核的訂單表有三種狀態，分別是“正常待稽核”|“即將過期”|“已經過期”

訂單提交後10小時內未處理：正常待稽核

訂單提交後10-20小時之間未處理：即將過期

訂單提交後20小時後未處理：已經過期

表結構如圖：

資料準備：

要解決的問題：

校驗訂單表中不同狀態訂單的數量與前臺顯示是否一致

解決思路：

1、透過儲存過程，獲取資料庫中的不同時間段的待稽核訂單數量

2、將後臺獲取的資料與前臺進行比較

實現步驟如下：

=============================================================================

1、連線資料庫

mysql -uroot -hlocalhost -p123456 -P3306

=============================================================================

2、進入資料庫

use sqltestdb；

=============================================================================

3、建立儲存過程（直接複製到mysql命令列執行就可以了）：

DELIMITER //

CREATE PROCEDURE order_state(IN will_timeout int,IN already_timeout int)

BEGIN

declare count_normal int;

declare count_will int;

declare count_already int;

set @current_time=now();

set @time1=date_sub(@current_time, interval will_timeout hour);

select count(*) into count_normal from lijuan where order_time>@time1 and new_db=0;

select count_normal;

set @current_time=now();

set @time2=date_sub(@current_time, interval already_timeout hour);

select count(*) into count_will from lijuan where order_time>@time2 and new_db=0;

set count_will=count_will-count_normal;

select count_will;

select count(*) into count_already from lijuan where new_db=0;

set count_already=count_already-(count_will+count_normal);

select count_already;

END //

DELIMITER ;

==============================================================================

4、呼叫儲存過程

call order_state(10,20);

==============================================================================

5、刪除儲存過程

drop procedure order_state;

==============================================================================

儲存過程程式碼解釋說明：

1-1宣告結束符為“//”

DELIMITER //

1-2宣告儲存過程名稱及輸入引數的數量和型別：

儲存過程名稱：order_state

輸入引數的數量：2

IN:表示是入參

will_timeout:即將超時所用的時間（例：10小時）

already_timeout:已經超時所用的時間（例：20小時）

CREATE PROCEDURE order_state(IN will_timeout int,IN already_timeout int)

1-3表示開始

BEGIN

1-4宣告變數count_normal表示“正常”狀態的訂單的數量

declare count_normal int;

1-5宣告變數count_will表示“即將超時”狀態的訂單的數量

declare count_will int;

1-6宣告變數count_already表示“已經超時”狀態的訂單的數量

declare count_already int;

1-7獲取當前時間並賦值給變數@current_time

set @current_time=now();

1-8將當前時間減去“即將超時所用的時間”，然後再賦值給變數@time1

set @time1=date_sub(@current_time, interval will_timeout hour);

1-9查詢出“正常”狀態的訂單數量，並賦值給變數count_normal

select count(*) into count_normal from lijuan where order_time>@time1 and new_db=0;

1-10輸出“正常”狀態的訂單數量

select count_normal;

1-11將當前時間減去“已經超時所用的時間”，然後再賦值給變數@time2

set @time2=date_sub(@current_time, interval already_timeout hour);

1-12查詢出“正常”狀態+“即將超時”狀態的訂單數量之和，並賦值給變數count_will

select count(*) into count_will from lijuan where order_time>@time2 and new_db=0;

1-13將“正常”狀態+“即將超時”狀態的訂單數量之和（即count_will）減去“正常”狀態的訂單數量（即count_normal）,然後再賦值給count_will

set count_will=count_will-count_normal;

1-14輸出“即將超時”狀態的訂單數量

select count_will;

1-15查詢出所有狀態的訂單數量（透過new_db=0過濾舊資料庫的資料）

select count(*) into count_invalid from lijuan where new_db=0;

1-16將所有訂單的數量減去“正常”狀態+“即將超時”狀態的訂單數量之和(即count_will+count_normal)

set count_invalid=count_already-(count_will+count_normal);

1-17輸出“已經超時”狀態的訂單數量

select count_already;

1-18結束

END //

1-19將結束符修改為“;”

DELIMITER ;

**********書山有路，學海無涯，無數個孤獨的夜晚，需要一點小小的成就感！**********

作者：菩提狗
連結：