Oracle 23c 中列默认值定义为 DEFAULT ON NULL FOR INSERT AND UPDATE

山东Oracle用户组 2024-02-03

在 Oracle 23c 中，可以将列定义为 DEFAULT ON NULL FOR INSERT AND UPDATE。这会将更新语句中的显式空值替换为默认值。

在 12c 之前的版本中，只有在插入语句中未显式引用具有默认值的列时，才会在插入操作期间应用默认值。在 Oracle 12c 中，可以将列定义为 DEFAULT ON NULL，从而允许为其分配默认值，即使在 INSERT 语句中显式为其分配了空值。Oracle 23c 为 UPDATE 操作带来了类似的默认值处理。

DEFAULT

最初，只有在插入语句中未显式引用具有默认值的列时，才会在 INSERT 操作期间应用默认值。

为了演示这一点，我们创建一个表，其中一列具有默认值。

drop table if exists t1 purge;


create table t1 (
  id           number,
  description  varchar2(15) default 'banana'
);

我们插入两行。第一个显式引用描述（description）字段并分配一个空值。第二个省略了描述字段。

insert into t1 (id, description) values (1, null);
insert into t1 (id) values (2);


select * from t1;


        ID DESCRIPTION
---------- ---------------
         1
         2 banana


SQL>

正如预期的那样，仅当插入语句中省略描述字段时才应用默认值。

如果我们将描述字段更新为空值，我们会看到默认值未应用。

update t1 set  description = null;




select * from t1;


        ID DESCRIPTION
---------- ---------------
         1
         2


SQL>

DEFAULT ON NULL [FOR INSERT ONLY]

在 Oracle 12c 中，我们能够将列定义为 DEFAULT ON NULL，从而允许为其分配默认值，即使在插入语句中显式为其分配了空值。23c 中添加了 DEFAULT ON NULL FOR INSERT ONLY 形式，以允许我们显式声明仅插入，但它在功能上与 DEFAULT ON NULL 相同。

为了演示这一点，我们使用两种语法变体创建一个表，其中两列上有默认值。

drop table if exists t1 purge;


create table t1 (
  id            number,
  description1  varchar2(15) default on null 'banana',
  description2  varchar2(15) default on null for insert only 'apple'
);

我们插入两行。第一个显式引用描述字段并分配一个空值。第二个省略描述字段。

insert into t1 (id, description1, description2) values (1, null, null);
insert into t1 (id) values (2);


select * from t1;


        ID DESCRIPTION1    DESCRIPTION2
---------- --------------- ---------------
         1 banana          apple
         2 banana          apple


SQL>

这次，无论描述字段被省略还是显式设置为空值，都会应用默认值。

请记住，DEFAULT ON NULL 使列成为强制列，因此我们无法使用更新语句将值设置为 null。

desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DESCRIPTION1                              NOT NULL VARCHAR2(15)
 DESCRIPTION2                              NOT NULL VARCHAR2(15)


SQL>




update t1
set    description1 = null,
       description2 = null;
set    description1 = null,
       *
ERROR at line 2:
ORA-01407: cannot update ("TESTUSER1"."T1"."DESCRIPTION1") to NULL


SQL>

DEFAULT ON NULL FOR INSERT AND UPDATE

在 Oracle 23c 中，我们能够将列定义为 DEFAULT ON NULL FOR INSERT AND UPDATE，因此如果指定了显式空值，则在更新操作期间将应用默认值。

为了演示这一点，我们创建一个表，其中一列具有默认值。

drop table if exists t1 purge;


create table t1 (
  id           number,
  description  varchar2(15) default on null for insert and update 'banana'
);

我们插入两行。第一个显式引用描述字段并分配一个空值。第二个省略了描述字段。

insert into t1 (id, description) values (1, null);
insert into t1 (id) values (2);


select * from t1;


        ID DESCRIPTION
---------- ---------------
         1 banana
         2 banana


SQL>

正如预期的那样，无论描述字段被省略还是显式设置为空值，都会应用默认值。

我们将描述字段更新为不同的值。

update t1 set    description = 'apple';


select * from t1;


        ID DESCRIPTION
---------- ---------------
         1 apple
         2 apple


SQL>

这次我们使用显式空值更新描述字段。

update t1 set    description = null;


select * from t1;


        ID DESCRIPTION
---------- ---------------
         1 banana
         2 banana


SQL>

请注意，这次更新没有出错，并且分配了默认值来代替显式空值。

oracle t1

文章转载自山东Oracle用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 23c 中列默认值定义为 DEFAULT ON NULL FOR INSERT AND UPDATE

评论