I'm trying to catch two different exceptions from the same statement in PL/SQL.
- If one exception is raised then it needs to keep on looping
- If the other one is raised then it needs to exit the loop
- And if the insert is successful it needs to exit the loop.
Here is the code I use:
create or replace procedure NewCouponGen
IS
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 IS
SELECT id
FROM WINKEl;
BEGIN
OPEN c_winkel;
LOOP
FETCH c_winkel INTO v_winkel_id;
v_suc := 0;
WHILE v_suc = 0
LOOP
select floor(dbms_random.value(v_min,v_max)) num INTO v_new_code from dual;
INSERT INTO WINKEL_COUPON (WINKEL_ID, COUPON_ID) VALUES (v_winkel_id, v_new_code);
-- CATCH UNQUE EXEPTION
--IF v_winkel_id != UNIQUE THEN v_suc = 1
--IF v_new_code != UNIQUE THEN KEEP ON LOOPING
--IF INSERT IS SUCCES THEN v_suc = 1
END LOOP;
EXIT WHEN c_winkel%notfound;
END LOOP;
CLOSE c_winkel;
END NewCouponGen;
Best How To :
The simplest thing is not to hit the first exception at all. There is a hint to ignore the duplicate violation, but that would apply to both unique constraints, so it isn't useful here. You could query to see if there is already a record with the WINKEL_ID and only insert if there is not; or as a single statement you could use a merge:
create or replace procedure NewCouponGen
IS
v_min number(10) := 1000;
v_max number(10) := 99999;
v_winkel_id varchar2(200);
v_new_code number(10);
CURSOR c_winkel IS
SELECT id
FROM WINKEl;
BEGIN
OPEN c_winkel;
LOOP
FETCH c_winkel INTO v_winkel_id;
EXIT WHEN c_winkel%notfound;
LOOP
BEGIN
v_new_code := floor(dbms_random.value(v_min,v_max));
MERGE INTO WINKEL_COUPON TGT
USING (SELECT v_winkel_id AS WINKEL_ID, v_new_code AS COUPON_ID FROM DUAL) SRC
ON (TGT.WINKEL_ID = SRC.WINKEL_ID)
WHEN NOT MATCHED THEN
INSERT (TGT.WINKEL_ID, TGT.COUPON_ID) VALUES (SRC.WINKEL_ID, SRC.COUPON_ID);
EXCEPTION
WHEN dup_val_on_index THEN
CONTINUE; -- duplicate coupon ID
END;
EXIT; -- merge was skipped because winkel ID exists, or was successful
END LOOP;
END LOOP;
CLOSE c_winkel;
END NewCouponGen;
/
The merge will only try to insert if it didn't see that a record already existed for the WINKEL_ID, so you won't get a unique constraint violation from that column. If you do get one from the COUPON_ID constraint then the exception handler on that inner block enclosing the merge - which exists only allow the exception to be caught - will send you around the loop again.
I've also taken out the v_suc flag completely; and moved the exit when clause to straight after the fetch - otherwise you will always try to insert two values for the last ID from the cursor; and taken out the context switch from the select .. from dual since you can just assign that random value directly to the variable.
You don't really need that v_new_code variable either, you can get the value in the merge instead:
MERGE INTO WINKEL_COUPON TGT
USING (SELECT v_winkel_id AS WINKEL_ID,
floor(dbms_random.value(v_min,v_max)) AS COUPON_ID FROM DUAL) SRC
ON (TGT.WINKEL_ID = SRC.WINKEL_ID)
WHEN NOT MATCHED THEN
INSERT (TGT.WINKEL_ID, TGT.COUPON_ID) VALUES (SRC.WINKEL_ID, SRC.COUPON_ID);
