oracle 尝试使用PL/SQL填充APEX上的日期表时出错

yftpprvb 于 2023-04-05 发布在 Oracle

关注(0)|答案(2)|浏览(140)

我目前正在尝试在APEX上填充日期表，日期从“2017-01-01”到“2025-12- 31”。我已经使用以下命令创建了表和列：

CREATE TABLE CALENDAR_TBL (
    "DATE_ROWID" NUMBER NOT NULL ENABLE,
    "DATE" DATE,
    "DAY_NUMBER" NUMBER,
    "MONTH_NUMBER" NUMBER,
    "MONTH_NAME" VARCHAR2(20),
    "YEAR" NUMBER,
    "YEAR_MONTH_NUMBER" NUMBER,
    "YEAR_MONTH_NAME" VARCHAR2(20),
    "DAY_NAME" VARCHAR2(20),
    CONSTRAINT "DATE_ROWID_PK" PRIMARY KEY ("DATE_ROWID")
 USING INDEX ENABLE
)
/

我创建了一个触发器和一个序列，以便在每次添加一行时填充DATE_ROWID列。
我写了这个脚本来填充表格：

DECLARE
  start_date DATE := TO_DATE('2017-01-01','YYYY-MM-DD');
  end_date DATE := TO_DATE('2050-12-31','YYYY-MM-DD');
  current_date DATE := start_date;
BEGIN
  WHILE current_date <= end_date LOOP
    INSERT INTO CALENDAR_TBL (DATE, DAY_NUMBER, MONTH_NUMBER, MONTH_NAME, YEAR, YEAR_MONTH_NUMBER, YEAR_MONTH_NAME, DAY_NAME)
    VALUES (
      current_date,
      TO_NUMBER(TO_CHAR(current_date, 'DD')),
      TO_NUMBER(TO_CHAR(current_date, 'MM')),
      TO_CHAR(current_date, 'MONTH'),
      TO_NUMBER(TO_CHAR(current_date, 'YYYY')),
      TO_NUMBER(TO_CHAR(current_date, 'YYYYMM')),
      TO_CHAR(current_date, 'YYYY MONTH'),
      TO_CHAR(current_date, 'DAY')
    );
    current_date := current_date + 1;
  END LOOP;
END;

但是当我运行它时，我得到了错误：

ORA-06550: line 7, column 39:
PL/SQL: ORA-00928: missing SELECT keyword
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 673
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 659
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1829

5. BEGIN
6.   WHILE current_date <= end_date LOOP
7.     INSERT INTO CALENDAR_TBL (DATE, DAY_NUMBER, MONTH_NUMBER, MONTH_NAME, YEAR, YEAR_MONTH_NUMBER, YEAR_MONTH_NAME, DAY_NAME)
8.     VALUES (
9.       current_date,

我真的不明白我的程序哪里出错了...
非常感谢你的帮助。

oracle

来源：https://stackoverflow.com/questions/75875518/error-while-attempting-filling-a-date-table-on-apex-with-pl-sql

2条答案

按热度按时间

b09cbbtk1#

在CALENDAR_TBL中使用虚拟列，这样就不会出现DATE和其他列不同步的情况（主键为IDENTITY列）：

CREATE TABLE CALENDAR_TBL (
    DATE_ROWID NUMBER
               GENERATED ALWAYS AS IDENTITY,
    "DATE"     DATE
               NOT NULL
               UNIQUE,
    DAY        NUMBER(2,0)
               GENERATED ALWAYS AS (EXTRACT(DAY FROM "DATE")),
    MONTH      NUMBER(2,0)
               GENERATED ALWAYS AS (EXTRACT(MONTH FROM "DATE")),
    MONTH_NAME VARCHAR2(9)
               GENERATED ALWAYS AS (CAST(TO_CHAR("DATE", 'fmMONTH', 'NLS_DATE_LANGUAGE=English') AS VARCHAR2(9))),
    YEAR       NUMBER(4,0)
               GENERATED ALWAYS AS (EXTRACT(YEAR FROM "DATE")),
    YEAR_MONTH_NUMBER NUMBER(6,0)
               GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR("DATE", 'YYYYMM'))),
    YEAR_MONTH_NAME VARCHAR2(14)
               GENERATED ALWAYS AS (CAST(TO_CHAR("DATE", 'fmYYYY MONTH', 'NLS_DATE_LANGUAGE=English') AS VARCHAR2(14))),
    DAY_NAME   VARCHAR2(9)
               GENERATED ALWAYS AS (CAST(TO_CHAR("DATE", 'fmDAY', 'NLS_DATE_LANGUAGE=English') AS VARCHAR2(9))),
    CONSTRAINT "DATE_ROWID_PK" PRIMARY KEY ("DATE_ROWID") USING INDEX ENABLE
);

注意：DATE是一个保留字，您需要使用带引号的标识符才能使用它。最好使用其他标识符。*

然后，您可以使用插入：

INSERT INTO calendar_tbl ("DATE")
SELECT DATE '2017-01-01' + LEVEL - 1
FROM   DUAL
CONNECT BY DATE '2017-01-01' + LEVEL - 1 <= DATE '2050-12-31';

fiddle

赞(0）回复(0）举报 2023-04-05

x7rlezfr2#

我会使用一个单一的插入选择，如下所示：

INSERT INTO CALENDAR_TBL ("DATE", DAY_NUMBER, MONTH_NUMBER, MONTH_NAME, "YEAR", YEAR_MONTH_NUMBER, YEAR_MONTH_NAME, DAY_NAME)
    with td as (
    select TO_DATE('2017-01-01','YYYY-MM-DD')+level current_date
    from dual
    connect by level<=TO_DATE('2050-12-31','YYYY-MM-DD')-TO_DATE('2017-01-01','YYYY-MM-DD')
    )
    select
          current_date,
          TO_NUMBER(TO_CHAR(current_date, 'DD')),
          TO_NUMBER(TO_CHAR(current_date, 'MM')),
          TO_CHAR(current_date, 'MONTH'),
          TO_NUMBER(TO_CHAR(current_date, 'YYYY')),
          TO_NUMBER(TO_CHAR(current_date, 'YYYYMM')),
          TO_CHAR(current_date, 'YYYY MONTH'),
          TO_CHAR(current_date, 'DAY')
    from tb;

赞(0）回复(0）举报 2023-04-05

我来回答

oracle 尝试使用PL/SQL填充APEX上的日期表时出错

2条答案

相关问题

热门标签

最新问答