日期补全

LOOP 
          INSERT /*+APPEND*/ INTO T NOLOGGING
             ( )                
          WITH TMP_DATE AS(         
                SELECT A.GL_DATE
                  FROM (SELECT TRUNC(PARAM.IN_BUSI_DT,'MM') - 1 + LEVEL AS GL_DATE
                          FROM DUAL
                        CONNECT BY LEVEL <= TO_CHAR(PARAM.IN_BUSI_DT,'DD')) A
                 WHERE A.GL_DATE NOT IN
                       (SELECT DISTINCT ACCT_DT
                          FROM TEST1113 B
                         WHERE TRUNC(B.ACCT_DT,'MM') = TRUNC(PARAM.IN_BUSI_DT,'MM'))
                   AND A.GL_DATE > (SELECT MIN(ACCT_DT)
                                      FROM TEST1113 C
                                     WHERE TRUNC(C.ACCT_DT,'MM') = TRUNC(PARAM.IN_BUSI_DT,'MM'))
                   )
          SELECT ...
            FROM TEST1113 TG, TMP_DATE TD
           WHERE TG.ACCT_DT = TD.GL_DATE - 1
             AND TRUNC(TG.ACCT_DT,'MM') = TRUNC(PARAM.IN_BUSI_DT,'MM');               
            COMMIT;
            
             SELECT COUNT(1)
               INTO V_DT_COUNT
              FROM (SELECT TRUNC(PARAM.IN_BUSI_DT,'MM') - 1 + LEVEL AS GL_DATE
                      FROM DUAL
                    CONNECT BY LEVEL <= TO_CHAR(PARAM.IN_BUSI_DT,'DD')) A
             WHERE A.GL_DATE NOT IN
                   (SELECT DISTINCT ACCT_DT
                      FROM TEST1113 B
                     WHERE TRUNC(B.ACCT_DT,'MM') = TRUNC(PARAM.IN_BUSI_DT,'MM'))
               AND A.GL_DATE > (SELECT MIN(ACCT_DT)
                                  FROM TEST1113 C
                                 WHERE TRUNC(C.ACCT_DT,'MM') = TRUNC(PARAM.IN_BUSI_DT,'MM'));  
       
           EXIT WHEN V_DT_COUNT = 0;           
         END LOOP; 

　　

 WHILE I_COUNT < 10 LOOP



INSERT INTO T()

 
WITH TMP_DATE AS( 
SELECT A.GL_DATE
FROM (SELECT TRUNC(date'2018-04-30', 'MM') - 1 + LEVEL AS GL_DATE
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(DATE'20180430', 'DD')) A
WHERE A.GL_DATE NOT IN
(SELECT DISTINCT ACCT_DT
FROM TEST1113 B
WHERE TRUNC(B.ACCT_DT,'MM') = TRUNC(DATE'2018-04-30','MM'))
AND A.GL_DATE >
(SELECT MIN(ACCT_DT)
FROM TEST1113 C
WHERE TRUNC(C.ACCT_DT,'MM') = TRUNC(DATE'2018-04-30','MM'))
)
SELECT TG...
FROM TEST1113 TG, TMP_DATE TD
WHERE TG.ACCT_DT = TD.GL_DATE - 1
AND TRUNC(TG.ACCT_DT,'MM') = TRUNC(DATE'2018-04-30','MM');

COMMIT;



I_COUNT := I_COUNT+1;
END LOOP;

--得到这一年中的所有月份
SELECT TO_CHAR(ADD_MONTHS(DATE '2004-01-01', ROWNUM-1 ), 'yyyyMM' ) AS year_month
           FROM   dual
           CONNECT BY ROWNUM <= 12

 

结果如下:

year_month

200401
200402
200403
200404
200405
200406
200407
200408
200409
200410
200411
200412

 

--得到两个日期之间所有的天数     
select to_date('2012-05-25','yyyy-mm-dd')+(ROWNUM-1) as year_month_day from dual 
     CONNECT BY ROWNUM <=

      (to_date('2012-06-5','yyyy-mm-dd') - to_date('2012-05-25','yyyy-mm-dd'))+1

 

结果如下:

year_month_day

2012-5-25
2012-5-26
2012-5-27
2012-5-28
2012-5-29
2012-5-30
2012-5-31
2012-6-1
2012-6-2
2012-6-3
2012-6-4
2012-6-5