merge into ORA-00001 错误模拟

在后台中开发了一个存储过程用到merge into，主要是根据主键值来查看目标表中是否有该主键值，如果有进行更新，否则插入，然而在测试过程中经常出现ORA-00001 主键冲突。

前端开发人员口口声声说传入的数据不会有重复值，怀疑数据库的问题。我相信Oracle不会有这样的bug。

索性我在后台演示出现该错误的方法。

 

1、创建测试表
create table T_TEST
(                 
  A CHAR(1),      
  B NUMBER        
)                 
2、创建测试存储过程
create or replace procedure test(i_num in number) is                                 
begin                                                               
  MERGE INTO t_test t                                               
  USING (SELECT '1' a, ROWNUM b FROM DUAL CONNECT BY ROWNUM <= i_num) TA
  ON (t.a = TA.a)                                                   
  WHEN MATCHED THEN                                                 
    UPDATE SET T.b = t.b + TA.b                                     
  WHEN NOT MATCHED THEN                                             
    INSERT VALUES (TA.a, TA.b);                                     
   commit;                                                                 
end test;
3、执行存储过程test
SQL> exec test(12);                                                                               
                                      
PL/SQL procedure successfully completed
                                      
SQL> select * from t_test;            
                                      
A          B                          
- ----------                          
1         12                          
1         11                          
1         10                          
1          9                          
1          8                          
1          7                          
1          6                          
1          5                          
1          4                          
1          3                          
1          2                          
1          1                          
                                      
12 rows selected                      
                                      
SQL>
4、清空t_test
SQL> truncate table t_test;                        
                          
Table truncated           
                          
SQL>             
5、添加主键
SQL> ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (A)  USING INDEX;        
                                                                             
Table altered
6、在t_test中添加一行数据                                                            
 SQL> exec test(1);                        
                                           
 PL/SQL procedure successfully completed   
                                           
 SQL> select * from t_test;                
                                           
 A          B                              
 - ----------                              
 1          1           
7、此时尝试再次添加多行数据，此时会出现错误ORA-30926，原因是在Using选项后面中a有多个重复值
                                           
 SQL> exec test(10);                       
                                           
 begin test(10); end;                      
                                           
 ORA-30926: 无法在源表中获得一组稳定的行   
 ORA-06512: 在 "TEST.TEST", line 3         
 ORA-06512: 在 line 1                      
                                           
 SQL>        
 8、清空t_test
 SQL> truncate table t_test;                                          
                                           
 Table truncated                            
 9、尝试添加多行a值相同的数据，提示主键冲突                                          
 SQL> exec test(10);                        
                                           
 begin test(10); end;                       
                                           
 ORA-00001: 违反唯一约束条件 (TEST.PK_T_TEST)
 ORA-06512: 在 "TEST.TEST", line 3          
 ORA-06512: 在 line 1                       
                                           
 SQL>

 

由此可以证明还是由于传入的数据有重复值导致的。

具体得在程序中继续跟踪，好让他们心服口服。