PL/SQL Date_Dimension数据填充- DataWarehouse

Question

可以使用pl/sql中的一些指导来使用存储过程将数据添加到Oracle中的表中。目前使用的是OracleXE 11g。我的存储过程现在看起来很赤裸，希望能得到一些帮助。下面是我的存储过程(sp_DATE_DIMENSION)，它接受两个参数，以及我已经创建的DATE_DIMENSION表的定义。

CREATE OR REPLACE PROCEDURE sp_DATE_DIMENSION(v_START_YEAR IN INT, v_END_YEAR IN INT) AS


DECLARE
v_CURRENT_DATE;
v_FULL_DATE_DESCRIPTION VARCHAR;

BEGIN 

DELETE FROM DATE_DIMENSION;

WHILE v_CURRENT_DATE <= v_END_DATE
LOOP 

/

CREATE TABLE DATE_DIMENSION
(  
DATE_KEY DATE NOT NULL,
FULL_DATE_DESCRIPTION VARCHAR2(64) NOT NULL,
DAY_OF_WEEK NUMBER(1,0) NOT NULL,
DAY_OF_MONTH NUMBER(2,0) NOT NULL,
DAY_OF_YEAR NUMBER(3,0) NOT NULL,
LAST_DAY_OF_WEEK_INDICATOR CHAR(1) NOT NULL,
LAST_DAY_OF_MONTH_INDICATOR CHAR(1) NOT NULL,
WEEK_ENDING_DATE DATE NOT NULL,
MONTH_NUMBER NUMBER(2,0) NOT NULL,
MONTH_NAME VARCHAR2(32) NOT NULL,
YEAR_MONTH CHAR(32) NOT NULL,
QUARTER_NUMBER NUMBER(1,0) NOT NULL,
YEAR_QUARTER CHAR(32) NOT NULL,
YEAR_NUMBER NUMBER(4,0) NOT NULL,
CONSTRAINT DATE_DIMENSION_PK PRIMARY KEY (DATE_KEY)
)

/

增加：

参数将是两年。

例子: exec sp_DATE_DIMENSION(2010,2012年)

该过程将填充表中从开始到结束之间的所有日期。我假设关键是开始年份的第一天，直到年底的最后一天。如果你同意的话请告诉我。为了自己的学习，我也更喜欢一个存储的proc。

Answer 1

经过相当一段时间，在阅读和帮助下，我完成了我最初发布的内容。因此，下面的存储过程再次更详细地接受两个输入参数，即(2001,2005)，并以不同的格式填充从START_YEAR的第一天到END_YEAR的最后一天的所有日期。

create or replace PROCEDURE sp_DATE_DIMENSION(v_START_YEAR IN INT, v_END_YEAR IN INT) AS

--Declare two variables as DATE datatypes
v_CURRENT_DATE DATE;
v_END_DATE     DATE;

BEGIN

--Assign the start year and end year to it's respective variables
v_CURRENT_DATE := TO_DATE('0101' || v_START_YEAR, 'MMDDYYYY');
v_END_DATE     := TO_DATE('1231' || v_END_YEAR,   'MMDDYYYY');

--Clear/Dump what is currently stored in the table
DELETE FROM DATE_DIMENSION;

--Check the condition to see if the start year is less than the end year (Input Parameters)
WHILE v_CURRENT_DATE <= v_END_DATE
LOOP
--DATE_DIMENSION Table
INSERT INTO DATE_DIMENSION
(
    DATE_KEY,
    FULL_DATE_DESCRIPTION,
    DAY_OF_WEEK,
    DAY_OF_MONTH,
    DAY_OF_YEAR,
    LAST_DAY_OF_WEEK_INDICATOR,
    LAST_DAY_OF_MONTH_INDICATOR,
    WEEK_ENDING_DATE,
    MONTH_NUMBER,
    MONTH_NAME,
    YEAR_MONTH,
    QUARTER_NUMBER,
    YEAR_QUARTER,
    YEAR_NUMBER       
)    
VALUES
(
    v_CURRENT_DATE,                                 --DATE_KEY
    TO_CHAR(v_CURRENT_DATE, 'Day, Month DD, YYYY'), --FULL_DATE_DESCRIPTION
    TO_NUMBER(TO_CHAR(v_CURRENT_DATE, 'D')) -1,     --DAY_OF_WEEK
    TO_CHAR(v_CURRENT_DATE,'DD'),                   --DAY_OF_MONTH        
    TO_CHAR(v_CURRENT_DATE,'DDD'),                  --DAY_OF_YEAR
    CASE                                            --LAST_DAY_OF_WEEK_INDICATOR
        WHEN TO_CHAR(v_CURRENT_DATE,'FMDay') = 'Saturday' THEN 'Y'
        ELSE 'N'
    END,
    CASE                                            --LAST_DAY_OF_MONTH_INDICATOR
        WHEN LAST_DAY(TO_DATE(v_CURRENT_DATE, 'MM/DD/YYYY')) = TO_DATE(v_CURRENT_DATE, 'MM/DD/YYYY') THEN 'Y'
        ELSE 'N'
    END,
    CASE                                            --WEEK_ENDING_DATE OF CURRENT WEEK ENDING ON SATURDAY
        WHEN TO_CHAR(v_CURRENT_DATE,'FMDay') = 'Saturday' THEN v_CURRENT_DATE
        ELSE NEXT_DAY(v_CURRENT_DATE,'SATURDAY')
    END,
    TO_CHAR(v_CURRENT_DATE,'MM'),                   --MONTH_NUMBER
    TO_CHAR(v_CURRENT_DATE,'MONTH'),                --MONTH_NAME
    TO_CHAR(v_CURRENT_DATE,'MONTH YYYY'),           --YEAR_MONTH        
    TO_CHAR(v_CURRENT_DATE,'Q'),                    --QUARTER_NUMBER
    TO_CHAR(v_CURRENT_DATE,'YYYY Q'),               --YEAR_QUARTER
    TO_CHAR(v_CURRENT_DATE,'YYYY')                  --YEAR_NUMBER    

);
--Increment and assign the current date value to be re-evaluated
v_CURRENT_DATE := v_CURRENT_DATE + 1;

END LOOP;
END;

Answer 2

好吧，我想我明白了。您希望为日期填充数据仓库的表，其中包含提取和存储的日期的所有方面，以便在仓库中进行更容易/更快的查询。我是这样做的：

CREATE OR REPLACE PROCEDURE sp_DATE_DIMENSION (v_START_YEAR IN INT, v_END_YEAR IN INT) AS
  l_start_date DATE;
  l_end_date DATE;

BEGIN
  l_current_date = to_date('1-JAN-' || v_start_year);
  l_end_date = to_date('31-DEC-' || v_end_year);

  WHILE l_current_date <= l_end_date LOOP

    INSERT INTO date_dimension VALUES (
      l_current_date, 
      to_char(l_current_date, 'Day, Month DDth, YYYY'),
      ...  for each column, use to_char and the proper format mask ...  
    );

    l_current_date := l_current_date + 1;
  END LOOP;
END;

如果这能解决你的问题请告诉我。