Oracle Database 23ai新特性之INSERT语句增强

Oracle Database 23ai（23.9）针对传统的 INSERT INTO VALUES 以及 INSERT INTO SELECT 语句进行了增强，新的语法形式可以进一步简化代码，提高可读性。

示例表

我们首先创建一个用于测试的示例表 EMP：

   create table emp (
  empno    number(4,0), 
  ename    varchar2(10 byte), 
  job      varchar2(9 byte), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno)
);
  
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
  (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
  (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
  (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
  (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
  (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
  (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
  (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
  (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
  (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
  (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
  (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
  (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
  (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

然后基于 EMP 复制一个新表 EMP2，不包含数据：

   create table emp2 as
select *
from   emp
where  1 = 2;

INSERT INTO SET

以下是传统的 INSERT INTO VALUES 语法示例：

   -- 全部字段
insert into emp2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9998, 'HALL', 'DBA', 6798, to_date('06-AUG-2025','DD-MON-YYYY'), 1000, null, 20);

-- 部分字段
insert into emp2 (empno, ename, job)
values (9999, 'HALL', 'DBA');

rollback;

如果 VALUES 子句按照字段定义的顺序提供了全部数据，可以省略括号中的字段名称列表；或者也可以插入必填字段。当表中的字段比较多时，很难直观地看出 INSERT 语句中每个字段对应的数据值。

为此，Oracle Database 23ai（23.9）引入了一个新的 INSERT INTO SET 语法，可以使用类似 UPDATE 语句的形式编写 INSERT 语句，阅读起来更加简单。

以下语句使用了新语法为 EMP2 插入了一条包含全部字段的数据：

   insert into emp2 set
  empno    = 9998,
  ename    = 'HALL',
  job      = 'DBA',
  mgr      = 6798,
  hiredate = to_date('06-AUG-2025','DD-MON-YYYY'),
  sal      = 1000,
  comm     = null,
  deptno   = 20;

1 row created.

SQL>

新的语法同样支持插入部分字段：

   insert into emp2 set
  empno    = 9999,
  ename    = 'HALL',
  job      = 'DBA';

1 row created.

SQL>

rollback;

下面的语句同时插入了多条记录：

   insert into emp2 set
  (empno = 9997, ename = 'HALL', job = 'DBA', deptno = 20),
  (empno = 9998, ename = 'SMITH', job = 'DBA', sal = 1000),
  (empno = 9999, ename = 'JONES', job = 'DBA', hiredate = to_date('06-AUG-2025','DD-MON-YYYY'));

3 rows created.

SQL>

rollback;

每一条记录都使用了括号，不同的记录可以指定不同的字段。

INSERT INTO BY NAME

以下是一个传统的 INSERT INTO SELECT 语句示例：

   -- 隐式复制所有字段
insert into emp2
select *
from   emp;

14 rows created.

SQL>

rollback;


-- 显式复制所有字段
insert into emp2 (empno, ename, job ,mgr, hiredate, sal, comm, deptno)
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from emp;

14 rows created.

SQL>

rollback;


-- 复制部分字段
insert into emp2 (empno, ename, job)
select empno,
       ename,
       job
from   emp;

14 rows created.

SQL>

rollback;

第一个语句没有指定字段列表，因为两个表的结构完全相同；第二个语句明确指定了全部字段；第三个语句指定了部分字段。无论哪种方式，目标字段和查询结果中的字段必须一一对应，增加了查询编写难度。

Oracle Database 23ai（23.9）增加了 INSERT INTO BY NAME 语法，通过字段名（或者别名）自动进行匹配，不再需要关心字段出现的顺序。例如：

   -- 隐式复制所有字段
insert into emp2 by name
select *
from   emp;

14 rows created.

SQL>

rollback;


-- 显式复制所有字段
insert into emp2 by name
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from   emp;

14 rows created.

SQL>

rollback;


-- 显式复制所有字段，查询结果中的字段顺序不同
insert into emp2 by name
select deptno,
       sal,
       comm,
       empno,
       ename,
       job,
       mgr,
       hiredate
from   emp;

14 rows created.

SQL>

rollback;

第三个语句中，查询返回的字段顺序和 EMP2 的定义不同，但是仍然能够正确执行，因为插入数据时按照字段名进行匹配。

如果插入部分字段，只需要提供必填的字段数据即可：

   -- 复制部分字段
insert into emp2 by name
select empno,
       ename,
       job
from   emp;

14 rows created.

SQL>

rollback;

只需要字段名（别名）和数据类型能够匹配，可以随意修改查询语句：

   insert into emp2 by name
select empno,
       ename,
       job,
       sal*2 as sal
from   emp;

14 rows created.

SQL>

rollback;

即使目标表中的字段比查询结果多，INSERT INTO BY NAME 语法也可以正常执行：

   -- EMP2增加额外的字段
alter table emp2 add (extra_column VARCHAR2(10));


-- 传统的INSERT INTO SELECT语法缺少数据
insert into emp2
select * from emp;
            *
ERROR at line 1:
ORA-00947: not enough values
Help: https://docs.oracle.com/error-help/db/ora-00947/

SQL>


-- INSERT INTO BY NAME可以正常执行
insert into emp2 by name
select * from emp;

14 rows created.

SQL>

rollback;

INSERT INTO BY POSITION

INSERT INTO BY POSITION 语法是传统按照字段顺序插入方法的显式形式，例如：

   insert into emp2 by position
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from   emp;

如果想要插入部分字段，查询结果中缺少的字段必须使用 null 替代：

   insert into emp2 by position
select empno,
       ename,
       job,
       mgr,
       null,
       null,
       null,
       null
from   emp;

参考文档：

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/INSERT.html