在 Oracle SQL 中创建默认约束

Question

我来自 SQL Server，正在学习一些 Oracle 语法。

这是我的表 DDL，

CREATE TABLE "CDR"."EXTDL_REPORTSETS"
  (
    "ID"                NUMBER(38,0) NOT NULL ENABLE,
    "SHORTNAME"         NUMBER(38,0) NOT NULL ENABLE,
    "DESCRIPTION"       NUMBER(38,0) NOT NULL ENABLE,
    "ASOFSTARTDATETIME" NUMBER(38,0) NOT NULL ENABLE,
    "ASOFENDDATETIME"   NUMBER(38,0) NOT NULL ENABLE,
    CONSTRAINT "PK_EXTDL_REPORTSETS" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CDR_DATA" ENABLE
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "CDR_DATA" ;

我想将 ASOFSTARTDATETIME 默认为 SysDate。如何在 PL/SQL 中设置默认约束？

编辑

数据类型变得混乱，所以我更正了它。

CREATE TABLE CDR.ExtDL_Reportsets(
    Id                   NUMBER(38, 0)    NOT NULL,
    ShortName            VARCHAR2(255)    NOT NULL,
    Description          VARCHAR2(500)    NOT NULL,
    AsOfStartDateTime    DATE             NOT NULL,
    AsOfEndDateTime      DATE,
    CONSTRAINT PK_ExtDL_Reportsets PRIMARY KEY (Id)
)
;

Answer 1

使用：

ALTER TABLE EXTDL_REPORTSETS
     MODIFY last_ASOFSTARTDATETIME DATE DEFAULT SYSDATE

我在挖掘 10g 参考时遇到问题文档 - 这是针对 11g 的。

如果您想在 CREATE TABLE 语句中执行此操作：

CREATE TABLE "CDR"."EXTDL_REPORTSETS" (
  "ID"                NUMBER(38,0) NOT NULL ENABLE,
  "SHORTNAME"         VARCHAR2(255) NOT NULL ENABLE,
  "DESCRIPTION"       VARCHAR2(500) NOT NULL ENABLE,
  "ASOFSTARTDATETIME" DATE DEFAULT SYSDATE NOT NULL ENABLE,
  "ASOFENDDATETIME"   DATE NOT NULL ENABLE,
  CONSTRAINT "PK_EXTDL_REPORTSETS" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CDR_DATA" ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE (
   INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "CDR_DATA" ;