oracle plsql采用for loop和rollback savepoint实现重复值可继续插入exception-CSDN博客

--创建表并构建唯一索引
SQL> create table t_unique(a int);

Table created

SQL> create unique index idx_t_unique on t_unique(a);

Index created

SQL> insert into t_unique values(1);

1 row inserted

SQL> commit;

Commit complete

--如果再相同值则报错
SQL> insert into t_unique values(1);

insert into t_unique values(1)

ORA-00001: unique constraint (TBL_BCK.IDX_T_UNIQUE) violated

--让重复插入可以有5次机会，最终可以插入数据
create or replace procedure proc_same_continue_insert
as
v_a pls_integer:=1;
begin
for i in 1..5 loop
    dbms_output.put(i||' insert ');--标记为第几次插入
      begin
        savepoint transaction_savepoint1;--真正插入前标记一个保存点，便于回滚
        insert into t_unique values(v_a);
        commit;
        dbms_output.put_line(i||' insert succeed');--仅起到一个输出日志的功能
        exit;--插入完记录后直接退出存储过程的运行
      exception
        when dup_val_on_index then --如果重复插入
           dbms_output.put_line('insert same key,please retry');--先提示重复插入
           v_a:=v_a+1;--累加重复插入的值为一个新值
           rollback to transaction_savepoint1;--注：此行语句与上行代码v_a:=v_a+1顺序先后皆不影响代码实现
      end;
end loop;
end;
/

Procedure created

SQL> select * from t_unique;

                                      A
---------------------------------------
                                      1

SQL> exec proc_same_continue_insert;

1 insert insert same key,please retry
2 insert 2 insert succeed

PL/SQL procedure successfully completed

SQL> select * from t_unique;

                                      A
---------------------------------------
                                      1
                                      2 --插入一个新记录

小结：1,适用于高并发的环境，可能会发生多客户端同时插入记录，会出现插入相同值的情况,可采用此法
      2,实现机制：采用exception异常处理模块，累加出错列值为另一新值,再用rollback savepoint继续执行原
        insert代码实现
      3,成功insert后,commit提交，尔后是exit;直接退出即可存储过程即可