推荐微信公众号:【纯洁的明依】文章由陈晓阳原创。本人微信:chenxiaoyangzxy. 提供本人整理的大量学习资料。
利用存储过程计算一个复杂的数据时,难免会用到自定义函数(Function);
一:实例:
CREATE FUNCTION `one_day_N`(lastAcctDate DATETIME, freqCounter DECIMAL, startDate DATETIME) RETURNS INT(11)
COMMENT '计算一天N次的计费次数'
BEGIN
DECLARE return_val INT DEFAULT 0;
DECLARE HOUR INT;
DECLARE CHOUR INT;
DECLARE start_time DATETIME;
IF lastAcctDate IS NULL
THEN
SELECT get_start_time(lastAcctDate,startDate) INTO start_time;
SELECT DATE_FORMAT(start_time , '%H') INTO HOUR;
IF HOUR<12 THEN
SET return_val=freqCounter;
ELSE
SET return_val=freqCounter/2;
END IF;
ELSE
IF DATE_FORMAT(NOW(),'%Y-%m-%d')!=DATE_FORMAT(lastAcctDate,'%Y-%m-%d