创建一个表循环插入某个月或整年的日期

来源：互联网发布：如何使用风险矩阵表编辑：程序博客网时间：2024/05/19 04:06

--写查询或存储过程时，有时可能会需要创建一个中间表，比如日期，下面是循环插入当月所有日期和整年所有日期的SQL

--创建会话级临时表用来存储日期，不过我这里日期存储的是varchar2 类型CREATE GLOBAL TEMPORARY TABLE RQ(ID NUMBER PRIMARY KEY ,SJ VARCHAR2(20))ON COMMIT PRESERVE ROWSSELECT * FROM RQ--循环插入日期  插入当月的日期 DECLARE C_I NUMBER :=1;V_I NUMBER ;V_FIRSTDAY VARCHAR2(20);V_LASTDAY  VARCHAR(20);BEGIN  SELECT TO_CHAR(SYSDATE,'YYYY-MM')||'-' INTO V_FIRSTDAY FROM DUAL;  SELECT TO_CHAR(LAST_DAY(SYSDATE),'YYYY-MM-DD') INTO V_LASTDAY FROM DUAL;  SELECT CEIL(LAST_DAY(SYSDATE)-TRUNC(SYSDATE,'MM'))+1 INTO V_I FROM DUAL;  LOOP    INSERT INTO RQ (ID,SJ) VALUES(C_I,V_FIRSTDAY||TO_CHAR(C_I));    C_I:=C_I+1;    EXIT WHEN C_I=V_I;    END LOOP;  END;--循环插入日期 插入某年的日期DECLARE C_I NUMBER :=0;V_I NUMBER ;V_FIRSTDAY DATE;V_LASTDAY  DATE;BEGIN  SELECT TRUNC(TO_DATE('2015','YYYY'),'YYYY') INTO V_FIRSTDAY FROM DUAL;  SELECT ADD_MONTHS(TRUNC(TO_DATE('2015','YYYY'),'YYYY'),12) INTO V_LASTDAY FROM DUAL;  SELECT V_LASTDAY-V_FIRSTDAY INTO V_I FROM DUAL;  LOOP    INSERT INTO RQ (ID,SJ) VALUES(C_I,TO_CHAR(V_FIRSTDAY+C_I,'YYYY-MM-DD'));    C_I:=C_I+1;    EXIT WHEN C_I=V_I;    END LOOP;  END;

0 0