異常邏輯在 Oracle PL/SQL 函式中不起作用

這些是我創建並插入了一些值的表：

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30) NOT NULL
);

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
EMAIL VARCHAR(25) NOT NULL,
PHONE_NUMBER VARCHAR(20) NOT NULL,
HIRE_DATE DATE NOT NULL,
JOB_ID NUMBER NOT NULL,
SALARY DECIMAL NOT NULL,
DEPARTMENT_ID NUMBER NOT NULL,
CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
);

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(3,'Accounting');

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (1,'Tony','Starc','starc@gmail.com','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (2,'Bruce','Wayne','bruce@gmail.com','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (3,'Larry','Ellison','larry@gmail.com','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (4,'Steve','Jobs','steve@gmail.com','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (5,'Remy','Lebeau','remy@gmail.com','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (6,'Clark','Kent','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

然後我創建了一個函式來顯示在特定部門工作的員工人數：

CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912 (dept_name VARCHAR)
RETURN VARCHAR 
IS
no_of_employees NUMBER;
BEGIN
SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;
DBMS_OUTPUT.PUT_LINE ('No of employees');
RETURN no_of_employees;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('IT'));
END;

DBMS OUTPUT:
No of employees
3

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Sales'));
END;

DBMS OUTPUT:
No of employees
3

正如您從上面的程式碼中看到的那樣，當我輸入有效的部門名稱時，該函式成功地顯示了在特定部門工作的員工人數。但是，我面臨的問題是當我輸入無效的部門名稱（部門表中不存在）時，它應該顯示錯誤消息“部門不可用”，因為我已經設置

$$ EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (‘Department Not Available’); $$ 相反，當我輸入無效的部門名稱時，我會收到此消息：

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Medicine'));
END;

DBMS OUTPUT:
No of employees
0

我哪裡可能出錯？

您正在嘗試查找不存在錯誤的錯誤。當您進行計數時，即使您要查找的值在表中不存在，它也將始終返回有效結果。

您需要做的是兩個單獨的計數並檢查返回值是什麼。

SELECT COUNT(*) INTO no_of_departments
FROM    DEPARTMENT
WHERE   DEPARTMENT_NAME = dept_name;

IF no_of_departments = 0 THEN
   * ERROR LOGIC HERE *
END IF;

IF no_of_departments > 1 THEN
   * ERROR LOGIC HERE *
END IF;

SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;

IF no_of_employees = 0 THEN
   * ERROR LOGIC HERE *
END IF;

引用自：https://dba.stackexchange.com/questions/183428