主页 / dba / 问题 / 183428
Accepted
stranger
Asked: 2017-08-15 04:50:13 +0800 CST

异常逻辑在 Oracle PL/SQL 函数中不起作用

  • 772

这些是我创建并插入了一些值的表：

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','[email protected]','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','[email protected]','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','[email protected]','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','[email protected]','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','[email protected]','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','[email protected]','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 ('部门不可用');]

相反，我在输入无效的部门名称时收到此消息：

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

DBMS OUTPUT:
No of employees
0

我哪里可能出错？

oracle plsql

3 个回答

  1. Best Answer

    您正在尝试查找错误不可能存在的错误。当您进行计数时，即使您要查找的值在表中不存在，它也始终会返回有效结果。

    您需要做的是两个单独的计数并检查返回值是什么。

    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;
    • 5

  2. 此代码可能引发 no_data_found 错误。您应该避免使用“WHEN OTHERS”，因为您可能不知道究竟是什么导致了异常。

    CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912
     ( p_dept_name VARCHAR )
RETURN VARCHAR
IS
   no_of_employees NUMBER;
BEGIN
   SELECT department.department_name
     INTO no_of_employees
     FROM department
    INNER JOIN employees
    WHERE department.department_id   = employees.department_id
      AND department.department_name = p_dept_name;

    DBMS_OUTPUT.PUT_LINE ('No of employees');
    RETURN no_of_employees;
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;
/
    • 2

  3. count(*) 永远不会返回 NO_DATA_FOUND 异常。如果没有找到任何行，它不应该返回错误。它给出 1 行，结果为 0（零）

    • 1

相关问题