好久没写东西了,今天写了个遍历天数的存储过程,放出来共享一下。
declare
SqlString VARCHAR2(32767);
tempDate NUMBER(8,0);
lnm_DateFrom NUMBER(8,0); -- 开始时间
lnm_DateTo NUMBER (8,0); -- 结束时间
begin
lnm_DateFrom:=20070305;
lnm_DateTo:=20070308;
SqlString := 'SELECT
aa.YearMonth as GetDataDate, bb.DayAverageValue
FROM
(
';
tempDate := Floor(lnm_DateFrom);
WHILE (tempDate<=Floor(lnm_DateTo))
LOOP
SqlString := SqlString || ' SELECT ' || tempDate || ' AS YearMonth FROM DUAL';
IF (tempDate <> Floor(lnm_DateTo)) THEN
SqlString := SqlString || ' UNION ALL '||chr(10);
END IF;
if (TO_DATE(tempDate) = last_day(TO_DATE(tempDate)))THEN
SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(tempDate),1),'yyyymm'))||'01' INTO tempDate FROM dual;
else
tempDate :=tempDate+1;
END IF;
END LOOP;
SqlString := SqlString ||
'
) aa,
(
SELECT
A. 时间字段 AS GetDataDate
,A.字段2 AS DayAverageValue
,A.字段3 AS DayMaxValue
,A.字段4 AS DayMinValue
FROM
aire.表名 A
WHERE
A.时间字段 BETWEEN '|| lnm_DateFrom ||' AND '|| lnm_DateTo ||'
AND A.字段CD = '||1314||'
AND A.CD = '||121||'
) bb
WHERE
aa.YearMonth = bb.GetDataDate(+)
ORDER BY aa.YearMonth
' ;
dbms_output.put_line(SqlString);
end;
--其实时间的循环很简单,关键就下面两句
if (TO_DATE(tempDate) = last_day(TO_DATE(tempDate)))THEN --判断是不是一个月的最后一天
SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(tempDate),1),'yyyymm'))||'01' INTO tempDate FROM dual; --生成下个月1日的时间
else
tempDate :=tempDate+1; --日期增加1天
END IF;
已经测试通过!