Oracle insert into select sequence distinct

我在用到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