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