sql - Separate handling for two posisble unique constraint exceptions on the same insert -

i'm trying catch 2 different exceptions same statement in pl/sql.

• if 1 exception raised needs keep on looping
• if other 1 raised needs exit loop
• and if insert successful needs exit loop.

here code use:

create or replace procedure newcoupongen   v_min number(10) := 1000;   v_max number(10) := 99999;   v_winkel_id varchar2(200);   v_suc number(1,0);   v_new_code number(10);  cursor c_winkel      select id      winkel; begin   open c_winkel;       loop           fetch c_winkel v_winkel_id;           v_suc := 0;           while v_suc = 0           loop                select floor(dbms_random.value(v_min,v_max)) num v_new_code dual;               insert winkel_coupon (winkel_id, coupon_id) values (v_winkel_id, v_new_code);               -- catch unque exeption               --if v_winkel_id != unique v_suc = 1               --if v_new_code != unique keep on looping               --if insert succes v_suc = 1           end loop;           exit when c_winkel%notfound;       end loop;     close c_winkel;   end newcoupongen; 

the simplest thing not hit first exception @ all. there hint ignore duplicate violation, apply both unique constraints, isn't useful here. query see if there record winkel_id , insert if there not; or single statement use merge:

create or replace procedure newcoupongen   v_min number(10) := 1000;   v_max number(10) := 99999;   v_winkel_id varchar2(200);   v_new_code number(10);   cursor c_winkel     select id     winkel; begin   open c_winkel;   loop     fetch c_winkel v_winkel_id;     exit when c_winkel%notfound;     loop        begin         v_new_code := floor(dbms_random.value(v_min,v_max));         merge winkel_coupon tgt         using (select v_winkel_id winkel_id, v_new_code coupon_id dual) src         on (tgt.winkel_id = src.winkel_id)         when not matched         insert (tgt.winkel_id, tgt.coupon_id) values (src.winkel_id, src.coupon_id);       exception         when dup_val_on_index           continue; -- duplicate coupon id       end;       exit; -- merge skipped because winkel id exists, or successful     end loop;   end loop;     close c_winkel;   end newcoupongen; / 

the merge try insert if didn't see record existed winkel_id, won't unique constraint violation column. if 1 coupon_id constraint exception handler on inner block enclosing merge - exists allow exception caught - send around loop again.

i've taken out v_suc flag completely; , moved exit when clause straight after fetch - otherwise try insert 2 values last id cursor; , taken out context switch select .. dual since can assign random value directly variable.

you don't need v_new_code variable either, can value in merge instead:

        merge winkel_coupon tgt         using (select v_winkel_id winkel_id,           floor(dbms_random.value(v_min,v_max)) coupon_id dual) src         on (tgt.winkel_id = src.winkel_id)         when not matched         insert (tgt.winkel_id, tgt.coupon_id) values (src.winkel_id, src.coupon_id);