I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has shipped, order details will be replicated in deleted orders table. However, I get an error on the insert whenever I run the program. This is due to duplication - An UPDATE or INSERT statement attempted to insert a duplicate key. Is there anyway I can insert records for the returned item without showing an error? My procedure is:
CREATE PROCEDURE RET_ORD(PARAM_ONO ORDERS.ONO%TYPE)
IS
CURSOR CUR_RET
IS
SELECT * FROM ORDERS
WHERE ONO = PARAM_ONO;
V_CUR CUR_RET%ROWTYPE;
BEGIN
OPEN CUR_RET;
LOOP
FETCH CUR_RET INTO V_CUR;
EXIT WHEN CUR_RET%NOTFOUND;
IF V_CUR.SHIPPED IS NOT NULL THEN
INSERT INTO
DELETED_ORDERS(ONO,CNO,ENO,ENAME,ORD_DE_D,STATUS)
VALUES(V_CUR.ONO,V_CUR.CNO,V_CUR.ENO,USER,,SYSDATE,'RETURNED');
DELETE FROM ODETAILS
WHERE ONO = P_ONO;
ELSIF V_CUR.SHIPPED IS NULL THEN
DBMS_OUTPUT.PUT_LINE('CANNOT PROCESS ORDER '|| V_CUR.ONO);
END IF;
END LOOP;
CLOSE CUR_RET;
END;
EXCEPTION WHEN ...
block (assuming plsql tag in the question is correct). If you want to keep constraint but avoid duplicates, rewrite the query so it doesn't insert duplicates. However, you cannot have both - unique constraint, and insert that violates the constraint.