关闭

存储过程中遍历天数

标签: 存储date测试
1123人阅读 评论(0) 收藏 举报

 好久没写东西了,今天写了个遍历天数的存储过程,放出来共享一下。

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;

 

已经测试通过!

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:72169次
    • 积分:1056
    • 等级:
    • 排名:千里之外
    • 原创:31篇
    • 转载:17篇
    • 译文:0篇
    • 评论:14条
    最新评论