Oracle -创建日期引用表时遇到麻烦

Question

我试图创建一个引用表，显示所有日期，并通过日期键在我的表中引用。然而，这并没有像我期望的那样工作。

DECLARE
  start_date DATE := TO_DATE('2009-01-01','YYYY-RR-DD');
  end_date DATE := TO_DATE('2021-01-01','YYYY-RR-DD');
BEGIN
  WHILE start_date < end_date LOOP
      SELECT 
            TO_CHAR(start_date, '-YYYYMMDD') AS DATE_KEY
          , TO_CHAR(start_date, 'YYYY-MM-DD') AS "ACTUAL_DATE"
          , EXTRACT(YEAR FROM start_date) AS "YEAR"
          , EXTRACT(MONTH FROM start_date) AS "MONTH"
          , TO_CHAR(start_date, 'MONTH') AS "MONTH_NAME"
          , TO_CHAR(start_date, 'WW') AS "WEEK_OF_YEAR"
          , EXTRACT(DAY FROM start_date) AS "DAY"
          , TO_CHAR(start_date, 'DDD') AS "DAY_OF_YEAR"
          , TO_CHAR(start_date, 'DAY') AS "WEEKDAY"
          , TO_CHAR(start_date, 'Q') AS "QTR_OF_YEAR"
          , TO_CHAR(start_date, 'MONTH DD, YYYY') AS FRIENDLY
      INTO V_DATE
      FROM dual;
      start_date := start_date+1;
  END LOOP;
END;

我得到了这个错误，但无论我怎么尝试，我总是得到错误。我试过声明V_DATE，但是没有正常工作。

作为一种变通方法，我能够在Excel中创建所需的表，但它有一个小问题一年中的一周。我可以修复加载此数据后的更新查询。理想的情况是有一个实际的查询来填充，因为这些数据可能会超过我列出的额外年份。

谢谢你，Allan

Answer 1

语法:

select . . .
into v_date
. . .

用于(在Oracle中)选择一个变量

您似乎想在表中插入行:

insert into ??( . . . )
    <your select here>;

??是您的表名。. . .是列的列表。

此外，您可以省去PL/SQL，并通过使用CTE生成所有日期(使用connect by或递归CTE)在单个查询中完成所有操作。

Answer 2

您可以使用如下语句创建表:

  CREATE TABLE refdates AS 
  SELECT 
        TO_CHAR(start_date, '-YYYYMMDD') AS DATE_KEY
      , TO_CHAR(start_date, 'YYYY-MM-DD') AS "ACTUAL_DATE"
      , EXTRACT(YEAR FROM start_date) AS "YEAR"
      , EXTRACT(MONTH FROM start_date) AS "MONTH"
      , TO_CHAR(start_date, 'MONTH') AS "MONTH_NAME"
      , TO_CHAR(start_date, 'WW') AS "WEEK_OF_YEAR"
      , EXTRACT(DAY FROM start_date) AS "DAY"
      , TO_CHAR(start_date, 'DDD') AS "DAY_OF_YEAR"
      , TO_CHAR(start_date, 'DAY') AS "WEEKDAY"
      , TO_CHAR(start_date, 'Q') AS "QTR_OF_YEAR"
      , TO_CHAR(start_date, 'MONTH DD, YYYY') AS FRIENDLY
  FROM (SELECT TO_DATE('2008-12-31','YYYY-MM-DD') + level as start_date
        FROM   dual
        CONNECT BY level < 10000);