CREATE OR REPLACE PROCEDURE sp_Utl_CalculateTimeBorder(
iBeginTime DATE, -- 输入起始时间
iEndTime DATE, -- 输入结束时间
iAdmBeginTime DATE, -- 输入偏移开始时间
iAdmEndTime DATE, -- 输入偏移结束时间
iTimeSectFlag INTEGER, -- 步长
oTime OUT DATE, -- 下一步长的边界
oUpTime OUT DATE, -- 本轮的统计截止时间
sValue OUT VARCHAR2 -- 统计标志, 返回给调用过程的StatisticalTimeValue
)
AS
SectTime DATE; -- 间隔时间
BEGIN
-- 判断TimeStatFlag, 为TempTime赋值
-- 统计步长为时段, 没有偏移
IF iTimeSectFlag = 1 THEN
SectTime := iBeginTime + (iEndTime - iBeginTime);
oUpTime := SectTime;
oTime := SectTime;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'/'
||TO_CHAR(iBeginTime, 'MM')||'/'
||TO_CHAR(iBeginTime, 'DD')||' '
||TO_CHAR(iBeginTime, 'HH24')
||'-'
||TO_CHAR(iEndTime, 'YYYY')||'/'
||TO_CHAR(iEndTime, 'MM')||'/'
||TO_CHAR(iEndTime, 'DD')||' '
||TO_CHAR(iEndTime, 'HH24');
END IF;
-- 统计步长为小时
IF iTimeSectFlag = 2 THEN
SectTime := iBeginTime + 1/24;
oUpTime := SectTime;
oTime := iBeginTime + 1/24 ;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'/'
||TO_CHAR(iBeginTime, 'MM')||'/'
||TO_CHAR(iBeginTime, 'DD')||' '||
TO_CHAR(iBeginTime, 'HH24')||'-'||TO_CHAR(oUpTime, 'HH24');
END IF;
-- 统计步长为半小时
IF iTimeSectFlag = 10 THEN
SectTime := iBeginTime + 1/48;
oUpTime := SectTime;
oTime := iBeginTime + 1/48 ;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'/'
||TO_CHAR(iBeginTime, 'MM')||'/'
||TO_CHAR(iBeginTime, 'DD')||' '||
TO_CHAR(iBeginTime, 'HH24:MI')||'-'||TO_CHAR(oUpTime, 'HH24:MI');
END IF;
-- 统计步长为天
IF iTimeSectFlag = 3 THEN
IF iAdmBeginTime >= iAdmEndTime THEN
SectTime := iBeginTime + ((iAdmEndTime + 1) - iAdmBeginTime);
ELSE
SectTime := iBeginTime + (iAdmEndTime - iAdmBeginTime);
END IF;
oUpTime := SectTime;
oTime := iBeginTime + 1 ;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'/'
||TO_CHAR(iBeginTime, 'MM') ||'/'
||TO_CHAR(iBeginTime, 'DD') ;
END IF;
-- 统计步长为周
IF iTimeSectFlag = 4 THEN
oUpTime := iBeginTime + 1;
oTime := iBeginTime + 1 ;
DECLARE
DayOfWeek INTEGER; -- 一周中的第几天
BEGIN
DayOfWeek := TO_NUMBER(TO_CHAR(iBeginTime, 'D'));
IF DayOfWeek = 1 THEN
sValue := '星期日';
ELSIF DayOfWeek = 2 THEN
sValue := '星期一';
ELSIF DayOfWeek = 3 THEN
sValue := '星期二';
ELSIF DayOfWeek = 4 THEN
sValue := '星期三';
ELSIF DayOfWeek = 5 THEN
sValue := '星期四';
ELSIF DayOfWeek = 6 THEN
sValue := '星期五';
ELSIF DayOfWeek = 7 THEN
sValue := '星期六';
ELSE
sValue := '';
END IF;
END;
END IF;
-- 统计步长为月
IF iTimeSectFlag = 6 THEN
IF iAdmBeginTime >= iAdmEndTime THEN
SectTime := ADD_MONTHS(iBeginTime + (iAdmEndTime - iAdmBeginTime), 1);
ELSE
SectTime := iBeginTime + (iAdmEndTime - iAdmBeginTime);
END IF;
oUpTime := SectTime;
oTime := ADD_MONTHS(iBeginTime,1) ;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'/'||TO_CHAR(iBeginTime, 'MM');
END IF;
-- 统计步长为季度
IF iTimeSectFlag = 7 THEN
IF iAdmBeginTime >= iAdmEndTime THEN
SectTime := ADD_MONTHS(iBeginTime + (iAdmEndTime - iAdmBeginTime), 3);
ELSE
SectTime := iBeginTime + (iAdmEndTime - iAdmBeginTime);
END IF;
oUpTime := SectTime;
oTime := ADD_MONTHS(iBeginTime, 3);
sValue := TO_CHAR(iBeginTime, 'YYYY');
DECLARE
tMonth INTEGER;
BEGIN
tMonth := TO_NUMBER(TO_CHAR(iBeginTime, 'MM'));
IF tMonth <= 3 THEN
sValue := sValue||'年一季度';
ELSIF tMonth <= 6 THEN
sValue := sValue||'年二季度';
ELSIF tMonth <= 9 THEN
sValue := sValue||'年三季度';
ELSE
sValue := sValue||'年四季度';
END IF;
END;
END IF;
-- 统计步长为半年
IF iTimeSectFlag = 8 THEN
IF iAdmBeginTime >= iAdmEndTime THEN
SectTime := ADD_MONTHS(iBeginTime + (iAdmEndTime - iAdmBeginTime), 6);
ELSE
SectTime := iBeginTime + (iAdmEndTime - iAdmBeginTime);
END IF;
oUpTime := SectTime;
oTime := ADD_MONTHS(iBeginTime, 6) ;
sValue := TO_CHAR(iBeginTime, 'YYYY');
DECLARE
tMonth INTEGER;
BEGIN
tMonth := TO_NUMBER(TO_CHAR(iBeginTime, 'MM'));
IF tMonth <= 6 THEN
sValue := sValue||'年上半年';
ELSE
sValue := sValue||'年下半年';
END IF;
END;
END IF;
-- 统计步长为年
IF iTimeSectFlag = 9 THEN
IF iAdmBeginTime >= iAdmEndTime THEN
SectTime := iBeginTime + (ADD_MONTHS(iAdmEndTime, 12) - iAdmBeginTime);
ELSE
SectTime := iBeginTime + (iAdmEndTime - iAdmBeginTime);
END IF;
oUpTime := SectTime;
oTime := ADD_MONTHS(iBeginTime, 12) ;
sValue := TO_CHAR(iBeginTime, 'YYYY')||'年';
END IF;
-- 统计步长为旬
IF iTimeSectFlag = 5 THEN
IF TO_NUMBER(TO_CHAR(iBeginTime,'DD')) < 10 THEN
SectTime := iBeginTime + 10;
ELSIF TO_NUMBER(TO_CHAR(iBeginTime,'DD')) < 20 THEN
SectTime := iBeginTime + 10;
ELSE
SectTime := to_date(TO_CHAR(ADD_MONTHS(iBeginTime,1),'YYYY-MM-') || '01 00:00:00','YYYY-MM-DD HH24:MI:SS');
END IF;
oUpTime := SectTime;
oTime := SectTime;
IF TO_NUMBER(TO_CHAR(iBeginTime,'DD')) <= 10 THEN
sValue := TO_CHAR(iBeginTime, 'YYYY')||'年'||TO_CHAR(iBeginTime,'MM')||'月上旬';
ELSIF TO_NUMBER(TO_CHAR(iBeginTime,'DD')) <= 20 THEN
sValue := TO_CHAR(iBeginTime, 'YYYY')||'年'||TO_CHAR(iBeginTime,'MM')||'月中旬';
ELSE
sValue := TO_CHAR(iBeginTime, 'YYYY')||'年'||TO_CHAR(iBeginTime,'MM')||'月下旬';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END sp_Utl_CalculateTimeBorder;
常用函数--不同时间粒度循环取数之sp_Utl_CalculateTimeBorder
最新推荐文章于 2020-12-07 12:50:01 发布