PRAGMA EXCEPTION_INIT

开始

set serveroutput on;

DEFINE p_deptno = 10

DECLARE
   e_emps_remaining EXCEPTION;
   
   PRAGMA EXCEPTION_INIT
    (e_emps_remaining, -2292);

BEGIN
   DELETE FROM dept  WHERE deptno = & p_deptno;
   
   COMMIT;

EXCEPTION
   WHEN  e_emps_remaining THEN
      DBMS_OUTPUT.PUT_LINE('Cannot remove dept' || TO_CHAR(&p_deptno) || '. Employees exist. ');

END;

运行结果：

anonymous block completed
Cannot remove dept10. Employees exist. 

至于为何，那是因为在 emp 表上有 constraint 存在。

     CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
      REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

结束