填充Time_Dimension？

Question

我写了这个剧本，但不管用。我没有收到任何错误信息，它只是挂着，你能帮帮我吗？

DECLARE
    vYear_ID VARCHAR(4);
    vQuarter CHARACTER(6);
    vMonth_Num SMALLINT;
    vMonth_Name VARCHAR(20);
    vWeekday_Num SMALLINT;
    vWeekday_Name VARCHAR(20); 
    vStartDate Date := '01/01/1998';
    vEndDate Date := '31/12/2002';
    vDate_ID Date;
    vDate_ID := vStartDate;

BEGIN

    WHILE vDate_ID < vEndDate 
    LOOP
        vYear_ID := TO_CHAR(vDate_ID,'YYYY');
        vQuarter := TO_CHAR(vDate_ID,'YYYY')||'Q'||TO_CHAR(vDate_ID,'Q');
        vMonth_Num := TO_NUMBER(TO_CHAR(vDate_ID,'MM'));
        vMonth_Name := TO_CHAR(vDate_ID,'Month');
        vWeekday_Num := TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(vDate_ID,'D')));
        vWeekday_Name := TO_CHAR(vDate_ID,'Day');

        INSERT INTO Time_Dim VALUES (vDate_ID, vYear_ID, vQuarter, vMonth_Name, vWeekday_Name, vMonth_Num, );

        vDate_ID = to_date(vDate_ID,'DD/MM/YYYY')+1

    END LOOP;

END;

我必须使用While循环而不是a For。

提前谢谢。

Answer 1

这并不能解决确切的问题。但是，由于实际问题是一个乏味的“猜测编译错误”，我选择回答一个更有趣的问题:)

INSERT INTO Time_Dim VALUES 
    (vDate_ID, vYear_ID, vQuarter, vMonth_Name, vMonth_Num, vWeekday_Name, vWeekday_Num)
with dt as ( select date '1998-01-01' as start_dt
                    , date '2002-12-31' as end_dt 
             from dual )
     , ths as ( select start_dt + ( level - 1) as id_date
                from dt
                connect by level <= ( end_dt - start_dt ) + 1
              )
select id_date
       , to_char(id_date, 'YYYY')
       , trim(to_char(id_date, 'YYYY'))||'Q'||to_char(id_date, 'Q')
       , to_char(id_date, 'Month')
       , to_number(to_char(id_date, 'MM'))
       , to_char(id_date, 'Day')
       , to_number(to_char(id_date, 'D'))
from ths;

这是语句的SELECT部分的SQL。