oracle返回00001错误,How to avoid the error - "ORA-00001: unique constraint violated"

It works for me:

SVIL>create or replace procedure INS_DUP_COD (p_id in varchar2) is

2 begin

3 insert into my_tab

4 select pk_id, cod_id, name_first, name_last, email, city, flag from (

5 select substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),1,instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2))||

6 (to_number(substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2)+1))+1) pk_id,

7 cod_id, name_first, name_last, email, city, '0' flag,

8 rank() over (order by pk_id desc) rn

9 from my_tab

10 where cod_id=p_id)

11 where rn=1;

12 end;

13 /

Procedura creata.

SVIL>

SVIL>insert into my_tab (pk_id, cod_id) values ('LUISS_SAM99','009999');

Creata 1 riga.

SVIL>select pk_id from my_tab;

PK_ID

--------------------

LUISS_SAM99

SVIL>exec INS_DUP_COD('009999')

Procedura PL/SQL completata correttamente.

SVIL>select pk_id from my_tab;

PK_ID

--------------------

LUISS_SAM99

LUISS_SAM99_1

SVIL>exec INS_DUP_COD('009999')

Procedura PL/SQL completata correttamente.

SVIL>select pk_id from my_tab;

PK_ID

--------------------

LUISS_SAM99

LUISS_SAM99_1

LUISS_SAM99_2Max