ORACLE生成日歷表

• Oracle生成日歷表
• • 代碼

Oracle生成日歷表

oracle日期轉換

代碼

CREATE OR REPLACE PROCEDURE SP_CREATE_TIME_DIMENSION(BEGIN_DATE IN VARCHAR2,
END_DATE IN VARCHAR2) IS
DDATE DATE;
V_THE_DATE NUMBER;
V_THE_YEAR VARCHAR2(4);
V_THE_QUARTER VARCHAR2(2);
V_THE_MONTH VARCHAR2(10);
V_THE_MONTH2 VARCHAR2(2);
V_THE_WEEK VARCHAR2(2);
V_THE_DAY VARCHAR2(10);
V_THE_DAY2 VARCHAR2(2);
V_WEEK_DAY NVARCHAR2(10);

ADDDAYS INT;

BEGIN
ADDDAYS := 1;
DDATE := TO_DATE(BEGIN_DATE, ‘yyyymmdd’);

WHILE (DDATE <= TO_DATE(END_DATE, 'yyyymmdd')) LOOPV_THE_DATE := TO_NUMBER(TO_CHAR(DDATE, 'yyyymmdd')); --key值V_THE_YEAR := TO_CHAR(DDATE, 'yyyy'); --年V_THE_QUARTER := TO_CHAR(DDATE, 'q'); --季度V_THE_MONTH := TO_CHAR(DDATE, 'mm'); --月份(字符型)V_THE_MONTH2 := TO_NUMBER(TO_CHAR(DDATE, 'mm')); --月份(數字型)V_THE_DAY := TO_CHAR(DDATE, 'dd'); --日(字符型)V_THE_DAY2 := TO_CHAR(DDATE, 'dd');V_THE_WEEK := TO_CHAR(DDATE, 'fmww'); --年的第幾周V_WEEK_DAY := TO_CHAR(DDATE, 'day'); --星期幾INSERT INTO DW_TIME_DIMENSION(THE_DATE, DATE_NAME, THE_YEAR, YEAR_NAME, THE_QUARTER, QUARTER_NAME, THE_MONTH, MONTH_NAME, THE_WEEK, WEEK_NAME, WEEK_DAY)VALUES(V_THE_DATE, V_THE_YEAR || '年' || V_THE_MONTH2 || '月' || V_THE_DAY2 || '日', V_THE_YEAR, V_THE_YEAR || '年', V_THE_YEAR || 'Q' ||V_THE_QUARTER, V_THE_YEAR || '年' || V_THE_QUARTER || '季度', TO_NUMBER(V_THE_YEAR ||V_THE_MONTH), V_THE_YEAR || '年' || V_THE_MONTH2 || '月', V_THE_WEEK, '第' ||V_THE_WEEK || '周', V_WEEK_DAY);DDATE := DDATE + ADDDAYS; END LOOP;

END SP_CREATE_TIME_DIMENSION;

總結

以上是生活随笔為你收集整理的Oracle生成日历表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。