看看你的步长,设置的是多少 ?select sequence_name, increment_by from user_sequences -- 看看
是1 啊 会不会是 merge into 语句引起的啊?
你提到了 Merge into ,那就有可能是这个问题。 比如你 select myseq.nextval from user_objects 这样一下子就会用 count(*) 这么多个。-- 4551552,下个就成了4552838 -- 这样解决就是通的。
1、 sequence.nextval 的使用 2、手动修改sequence的当前值
要连续的话,不建议用这个,这个序列只要有用到都会自动去改变值,而且借用到的都是同样的值,你可以试试 create sequence t1; create sequence t2;select t1.nextval,t2.nextval from dual; select t2.nextval from dual; select t1.nextval from dual;select t1.nextval,t2.nextval from dual; select t1.nextval from dual; select t2.nextval from dual; 看看就了解了。
only a single sequence number can be generated for each row. In other words, if NEXTVAL is referenced more than once in a single statement, then the first reference generates the next number, and all subsequent references in the statement return the same number.一些文档里的解释。
你提到了 Merge into ,那就有可能是这个问题。 比如你 select myseq.nextval from user_objects 这样一下子就会用 count(*) 这么多个。-- 4551552,下个就成了4552838 -- 这样解决就是通的。
结贴喽,可以确定是merge into 语句的问题了,如下这么写是有问题的: <insert id="updateOrInsertTableName" parameterClass="HashMap"> <![CDATA[ merge into tablename using (select count(*) count from tablename where condition1) numcount on (numcount.count <> 0) when matched then UPDATE SET A=A+1 WHERE condition2 when not matched then INSERT (ID,A,B,C,D) VALUES(tablename_SEQUENCE.NEXTVAL, #a#, #b#, #c#, #d# ) ]]> </insert>正确的写法是这样: <insert id="updateOrInsertTableName" parameterClass="HashMap"> <selectKey resultClass="java.lang.Long" keyProperty="tableid"> <![CDATA[select tablename_SEQUENCE.NEXTVAL as tableid from dual]]> </selectKey> <![CDATA[ merge into tablename using (select count(*) count from tablename where condition1) numcount on (numcount.count <> 0) when matched then UPDATE SET A=A+1 WHERE condition2 when not matched then INSERT (ID,A,B,C,D) VALUES(tablename_SEQUENCE.NEXTVAL, #a#, #b#, #c#, #d# ) ]]> </insert> 虽然还不是很清楚,为什么第一种写法会造成那样的现象
会不会是 merge into 语句引起的啊?
你提到了 Merge into ,那就有可能是这个问题。
比如你
select myseq.nextval from user_objects 这样一下子就会用 count(*) 这么多个。-- 4551552,下个就成了4552838
-- 这样解决就是通的。
2、手动修改sequence的当前值
create sequence t1;
create sequence t2;select t1.nextval,t2.nextval from dual;
select t2.nextval from dual;
select t1.nextval from dual;select t1.nextval,t2.nextval from dual;
select t1.nextval from dual;
select t2.nextval from dual;
看看就了解了。
你提到了 Merge into ,那就有可能是这个问题。
比如你
select myseq.nextval from user_objects 这样一下子就会用 count(*) 这么多个。-- 4551552,下个就成了4552838
-- 这样解决就是通的。
结贴喽,可以确定是merge into 语句的问题了,如下这么写是有问题的:
<insert id="updateOrInsertTableName" parameterClass="HashMap">
<![CDATA[
merge into tablename using
(select count(*) count from tablename where condition1) numcount
on (numcount.count <> 0)
when matched then
UPDATE SET A=A+1 WHERE condition2
when not matched then
INSERT (ID,A,B,C,D)
VALUES(tablename_SEQUENCE.NEXTVAL, #a#, #b#, #c#, #d# )
]]>
</insert>正确的写法是这样:
<insert id="updateOrInsertTableName" parameterClass="HashMap"> <selectKey resultClass="java.lang.Long" keyProperty="tableid">
<![CDATA[select tablename_SEQUENCE.NEXTVAL as tableid from dual]]>
</selectKey>
<![CDATA[
merge into tablename using
(select count(*) count from tablename where condition1) numcount
on (numcount.count <> 0)
when matched then
UPDATE SET A=A+1 WHERE condition2
when not matched then
INSERT (ID,A,B,C,D)
VALUES(tablename_SEQUENCE.NEXTVAL, #a#, #b#, #c#, #d# )
]]>
</insert>
虽然还不是很清楚,为什么第一种写法会造成那样的现象