如何在甲骨文中用序列进行多行插入?

Question

我想在 Oracle 的表中插入多行。但这让我感到困惑： 我不能使用

select seq_table1.nextval  into table1 form dual

因为表 1 需要是一个新的。我需要在现有的表1中插入一些行。

而且我也不能使用

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
(select seq_paper.nextval,'Multi 8000',1 from dual
union all select seq_paper.nextval,'Multi 8001',1 from dual)

因为甲骨文告诉我：

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the  
following constructs:  
■ A SELECT statement that is combined with another SELECT statement with the  
UNION, INTERSECT, or MINUS set operator ;
...and other constructs

Answer 1

您可以改写select，以便nextval不在union中。

结果将是这样的：

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE) 
select seq_paper.nextval, NULL, a, b from 
(select 'Multi 8000' a, 1 b from dual 
union all 
select seq_paper.nextval,'Multi 8001',1 from dual)

Answer 2

使用 nextval 一次并在子查询中创建数据：

SQL> CREATE TABLE pager (PAG_ID NUMBER,PAG_PARENT VARCHAR2(10), PAG_ACTIVE NUMBER);
Table created
SQL> CREATE SEQUENCE seq_paper START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE;
Sequence created
SQL> 
SQL> INSERT INTO pager
2      (pag_id,
3       pag_parent,
4       pag_active)
5      SELECT seq_paper.nextval,
6             pag_parent,
7             pag_active
8        FROM (SELECT 'Multi 8000' pag_parent,
9                     1 pag_active
10                FROM dual
11              UNION ALL
12              SELECT 'Multi 8001',
13                     1
14                FROM dual);
2 rows inserted
SQL>