Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
- (CUSTOMER_ID NUMBER) AS
- TOT_CUSTOMERS NUMBER;
- BEGIN
- DELETE FROM CUSTOMERS
- WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
- TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
- END;
- /
- EXECUTE DELETE_CUSTOMER(01);
- Error starting at line : 120 in command -
- BEGIN DELETE_CUSTOMER(01); END;
- Error report -
- ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
- ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
- ORA-06512: at line 1
- 02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
- *Cause: attempted to delete a parent key value that had a foreign
- dependency.
- *Action: delete dependencies first then parent or disable constraint.
- CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
- (CUSTOMER_ID_IN NUMBER) AS
- TOT_CUSTOMERS NUMBER;
- CURSOR C1 IS
- DELETE FROM ORDERS
- WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
- CURSOR C2 IS
- DELETE FROM ORDER_DETAILS
- WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
- CURSOR C3 IS
- DELETE FROM CUSTOMERS
- WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
- BEGIN
- OPEN C1;
- OPEN C2;
- OPEN C3;
- IF C1%FOUND AND C2%FOUND AND C3%FOUND
- THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
- END IF;
- CLOSE C1;
- CLOSE C2;
- CLOSE C3;
- END;
- /
Add Comment
Please, Sign In to add comment