merge后再查询，怎么查出来的序号在commit前后不一样啊

Question

这与以前的一个问题相关，
http://topic.csdn.net/u/20091209/16/85bab2c7-6bd8-464f-8a06-81d7f7bfb9de.html?472098105 。
很多人推荐我用merge，但是使用了merge后却发现新问题。
我有一个表，表的id用sequence生成。在merge一条新的数据时，我使用了sequence的NEXTVAL来生成新行的id值，然后我需要取得新行的id值返回。这时我发现在事务提交前和事务提交后，select得到的id值不一样。这是怎么回事呢？
代码见下：
procedure CategoryIWL3 (P_CATEGORY_NAME varchar2,O_idCategory out number) is
  begin
   begin
      EXECUTE IMMEDIATE 'set transaction isolation level serializable';
      MERGE INTO TA_CATEGORIES D USING (select P_CATEGORY_NAME as CATEGORY_NAME from dual) S on (D.CATEGORY_NAME=S.CATEGORY_NAME)
        WHEN NOT MATCHED THEN INSERT (CATEGORY_ID,CATEGORY_NAME) VALUES (CATEGORIES_SEQ.NEXTVAL,S.CATEGORY_NAME);      
      SELECT CATEGORY_ID INTO O_idCategory FROM TA_CATEGORIES WHERE CATEGORY_NAME=P_CATEGORY_NAME ;
      dbms_output.put_line('CATEGORY_ID=' || to_char(O_idCategory));
   EXCEPTION  
   WHEN OTHERS THEN
        ROLLBACK;
   RAISE;
   end;
   COMMIT;
    SELECT CATEGORY_ID INTO O_idCategory FROM TA_CATEGORIES WHERE CATEGORY_NAME=P_CATEGORY_NAME ;
      dbms_output.put_line('  CATEGORY_ID=' || to_char(O_idCategory));
  end CategoryIWL3;
表结构如下：
    EXECUTE IMMEDIATE 'CREATE SEQUENCE CATEGORIES_SEQ  START WITH 1 INCREMENT BY 1' ;
    EXECUTE IMMEDIATE 'CREATE CLUSTER CL_CATEGORIES (CATEGORY_ID number(10))' ;
    EXECUTE IMMEDIATE 'CREATE INDEX CI_CATEGORIES on CLUSTER CL_CATEGORIES' ;
    EXECUTE IMMEDIATE 'CREATE TABLE TA_CATEGORIES(
          CATEGORY_ID number(10) CONSTRAINT PK_CATEGORIES PRIMARY KEY NOT NULL , 
          CATEGORY_NAME VARCHAR2(40) NOT NULL 
      )CLUSTER CL_CATEGORIES(CATEGORY_ID)' ;
    EXECUTE IMMEDIATE 'CREATE INDEX I_CATEGORYNAME ON TA_CATEGORIES ( CATEGORY_NAME )' ;

Answer 1

EXECUTE IMMEDIATE 'CREATE SEQUENCE CATEGORIES_SEQ  START WITH 1 INCREMENT BY 1 noCACHE' ; 出现这种的情况的可能原因是sequence得值是事先放在缓存中的，在commit的时候产生了变化。
加上nocache后用一个值就取一个。

Answer 2

rollback后，已经取了的序列sequence又不会回滚我觉得序列没有必要都是连续的

Answer 3

使用了nocache没用。
另外还有个奇怪的事情是，我用一个新的name值调用这个函数，到第一条“SELECT CATEGORY_ID INTO O_idCategory”的地方总会出错，报ORA-00512 : PL/SQL : numric or value error 。但是什么都不改，第二次执行又通过。这是怎么回事呢？

Answer 4

楼主你需要得到插入的那个id返回的话，可以一开始就把
CATEGORIES_SEQ.NEXTVAL 放在一个变量里面，下面如果执行了insert，就是merge语句影响行数>0
（IF SQL%ROWCOUNT>0 THEN     O_idCategory :=那个seqnextval的值; END IF;）
就把输出参数值改为那个变量，不用每次都根据CATEGORY_NAME去表里面搜啊。

Answer 5

crazylaa ，能否把你的方法写完整些？我感觉merge中的WHEN NOT MATCHED THEN INSERT子句似乎不适用你说的情况啊？

Answer 6

返回0的时候是因为数据重复。
第2次成功返回3，是因为返回0的那次，序列号也加了一次。SQL> create or replace procedure CategoryIWL3 (
  2  P_CATEGORY_NAME in varchar2,
  3  O_idCategory out number)
  4  is
  5  var_seq number(10);
  6  begin
  7     set transaction isolation level serializable;
  8     begin
  9     select CATEGORIES_SEQ.NEXTVAL into var_seq from dual;
 10     MERGE INTO TA_CATEGORIES D USING (select P_CATEGORY_NAME as CATEGORY_NAME from dual) S on (D.CATEGORY_NAME=S.CATEGORY_NAME)
 11     WHEN NOT MATCHED THEN INSERT (CATEGORY_ID,CATEGORY_NAME) VALUES (var_seq,S.CATEGORY_NAME);
 12     IF SQL%ROWCOUNT>0 THEN
 13             O_idCategory := var_seq;
 14     else
 15             O_idCategory :=0;
 16     end if;
 17     dbms_output.put_line('CATEGORY_ID=' || to_char(O_idCategory));
 18     EXCEPTION WHEN OTHERS THEN
 19             ROLLBACK;
 20             O_idCategory :=0;
 21             RAISE;
 22     end;
 23     COMMIT;
 24     dbms_output.put_line('  CATEGORY_ID=' || to_char(O_idCategory));
 25  end CategoryIWL3;
 26  /过程已创建。SQL> variable a number;
SQL> exec CategoryIWL3('asdf',:a);PL/SQL 过程已成功完成。SQL> print a;         A
----------
         1SQL> select * from TA_CATEGORIES;CATEGORY_ID
-----------
CATEGORY_NAME
--------------------------------------------------------------------------------
          1
asdf
SQL> exec CategoryIWL3('asdf',:a);PL/SQL 过程已成功完成。SQL> print a;         A
----------
         0SQL> exec CategoryIWL3('asfdf',:a);PL/SQL 过程已成功完成。SQL> print a;         A
----------
         3

Answer 7

seq这里用了nocacheCREATE SEQUENCE CATEGORIES_SEQ  START WITH 1 INCREMENT BY 1 nocache;

Answer 8

先谢谢crazylaa。
这的确是一个解决办法，不过你的代码里面会对SEQUENCE的浪费比较严重吧，即SEQUENCE的连续性没法保证了。
另外，如果在你的代码的merge语句之后再select ： SELECT CATEGORY_ID INTO O_idCategory FROM TA_CATEGORIES WHERE CATEGORY_NAME=P_CATEGORY_NAME ; 往往第一次执行会出错，报ora-06502 exception (numeric or value error) ，第二次执行又不出错，这你知道是为什么吗？

Answer 9

不过你的代码里面会对SEQUENCE的浪费比较严重吧，即SEQUENCE的连续性没法保证了。sequence为什么一定要保持连续呢？它只是一个标识啊，保证记录的唯一性而已，本身就没有什么意义

Answer 10

关于第一次执行会出错，报ora-06502 exception (numeric or value error) ，第二次执行又不出错，的问题，详见另一篇帖子。
http://topic.csdn.net/u/20091217/11/7e04f479-17b1-44f8-9f52-3bf70f6ad411.html

Answer 11

sequence没必要保证连续，如果一定要连续，那么这么做：
1.除此存储过程外，不会有别的存储过程或sql或函数来select seq.nextVal，
2.此存储过程要保证被线性调用，即不能多线程同时调用，在前一个没有commit或rollback之前，另外不允许有别的调用，然后修改：在rollback之后，alter sequence 修改它的起始值为当前－1。
我想这与你的初衷可能有违背。。我修改后的应该不存在第一次出错，第二次不出错的问题啊，我N多存储过程都这么弄的，没出现过第一次出错第二次不出错至于后面加句select就会出错，搞不明白了

Answer 12

ora-06502　 -6502　 在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常
这个select into select出来的是个number，into的那个也是个number，应该不会出错
要不你在存储过程前面，as后面显式定义一个变量 var_no TA_CATEGORIES.CATEGORY_ID％TYPE;
保证它的类型和长度与select 里面的一致，
然后再 select CATEGORY_ID into var_no .....
下面再 out参数＝var_no ，看看这样子会不会出错？