如何创建一个删除存储过程，确保当它在另一个表中有附属记录时，它不会被删除

Question

我有表

• 供应商（供应商id（pk），名称，地址）供应商发票（供应商id，余额）
• 供应商_产品（sup_prod_id（pk）、供应商_id（fk）、产品_id（fk）、supp_invoice_id（fk））

我正在尝试执行一个删除过程：

set serveroutput on;
create or replace
procedure delete_supp
(d_supplier_id int)
is
v_count  int;
begin
select count(*) into v_count from supplier_product where d_supplier_id=supplier_id;
    if v_count > 0 then
        dbms_output.put_line('Supplier cannot be deleted because there is an existing invoice in the system');
    else    
begin
delete from supplier where supplier_id=d_supplier_id;
 DBMS_OUTPUT.PUT_LINE('Deleted '  || SQL%ROWCOUNT || ' Rows.'); 
commit;
  end;
end if;
    Exception
when others then
    dbms_output.put_line('Delete failed');          
end;
/

此过程有效，但我不想检查supplier_product中是否存在该记录，而是想确保如果供应商在supplier_invoice表中有未结发票，则不能删除该供应商。我尝试在supplier_invoice表中循环，但无法正常工作。

Answer 1

删除语句的小更改

DELETE FROM supplier
WHERE  supplier_id = d_supplier_id
       AND NOT EXISTS (SELECT 1
                       FROM   supplier_product,
                              supplier_invoice
                       WHERE  supp_invoice_id = supp_invoice_id
                              AND supplier_id = d_supplier_id); 
IF SQL%ROWCOUNT = 0 THEN
   RAISE Invoice_exists_exception;
END IF;

并且这将确保只有在没有具有相同supplier_ id&链接的发票id存在于supplier_product表中。

当然，如果FK已经设置（正如您所提到的），那么当您试图删除时，它应该会引发一个异常。。

---

更新：我使用SQLFiddle构建了一个示例模式/数据来显示：

如果您设置了外键，您将遇到一个ORA-02292：违反完整性约束的异常，您可以捕获&处理得当。