我在用到Oracle中 insert into select sequence distinct,业务中的用到:

所不同是:以下用的是union,我用的是distinct,呵呵，解决方法是一样的。

Q:

insert into bps_giftcode

(giftcode, gift, giftact)

select SEQ_BPS_GIFTCODE.nextval as s, '00000003' as b, '00000000' as c from dual

union all

select SEQ_BPS_GIFTCODE.nextval as s, '00000008' as b, '00000000' as c from dual

为什么一直报

ORA-02287:sequence number not allowed here

谢谢。

A:

汗，楼上写的有问题吧，INSERT可以UNION?

楼主的问题原因

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the

following constructs:

■ A subquery in a DELETE, SELECT, or UPDATE statement

■ A query of a view or of a materialized view

■ A SELECT statement with the DISTINCT operator

■ A SELECT statement with a GROUP BY clause or ORDER BY clause

■ A SELECT statement that is combined with another SELECT statement with the

UNION, INTERSECT, or MINUS set operator

■ The WHERE clause of a SELECT statement

■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

■ The condition of a CHECK constrain

楼主的问题解决方案

insert into bps_giftcode

(giftcode, gift, giftact)

select SEQ_BPS_GIFTCODE.nextval as s, t.b, t.c

from (select '00000003' as b, '00000000' as c from dual

union all

select  '00000008' as b, '00000000' as c from dual ) t