java学习心得

讨论区

存储过程中遍历天数

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

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;

 

已经测试通过!

阅读更多
文章标签: 存储 date 测试
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

存储过程中遍历天数

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭