PL/SQL:返回的函数没有值

Question

我写了下面的函数来检查给定的数据是否存在。

CREATE OR REPLACE FUNCTION PSPIWEB_USER.AB_GetAuditTypeID (in_DeptID NUMBER)
   RETURN NUMBER
IS
   intAuditTypeID   NUMBER;
BEGIN

   SELECT audittypeid
     INTO intAuditTypeID
     FROM iqa_department
    WHERE seqno = in_deptid;

   RETURN intAuditTypeID;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      RAISE;
END;
/

它以前是有效的，但当我在名为iqa_department的表上添加新数据时，它在触发函数后突然返回错误，我在oracle中对mysql到oracle有些陌生。

我是这么称呼它的。

Select IQA_CAR.CAR_NO,IQA_CAR.REFNO,NVL(null,' '),NVL(IQA_CAR.BFGSECTION ,' ') ,IQA_CAR.ERR_PROOF,NVL(IQA_SECTION.SECTIONNAME,' '),NVL (To_char(iqa_car.auditdate,'YYYY-MM-DD'), ' '),NVL(IQA_CAR.ISSUEDTO,' '),NVL(TQM_VWEMPLOYEE.NAME,' '), NVL (To_Char(iqa_car.issueddate,'YYYY-MM-DD'), ' '),NVL (To_Char(iqa_car.closeddate,'YYYY-MM-DD'), 'N'),NVL(VW_CURR_APPROVER.current_approver,' ') from IQA_CAR 
                   left outer join IQA_SECTION 
                    on IQA_SECTION.SECTIONID = IQA_CAR.AREAID 
                    left outer join TQM_VWEMPLOYEE 
                    on TQM_VWEMPLOYEE.EMP_NUMBER = IQA_CAR.ISSUEDTO 
                    full outer join VW_CURR_APPROVER 
                    on VW_CURR_APPROVER.car_no = IQA_CAR.CAR_NO 
                    where ab_getaudittypeid(deptid) = 11

我得到一个错误

ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "PSPIWEB_USER.AB_GETAUDITTYPEID", line 19

Answer 1

试试这个：

CREATE OR REPLACE FUNCTION PSPIWEB_USER.AB_GetAuditTypeID (in_DeptID NUMBER)
   RETURN NUMBER
IS
   intAuditTypeID   NUMBER;
BEGIN

   SELECT audittypeid
     INTO intAuditTypeID
     FROM iqa_department
    WHERE seqno = in_deptid;

   RETURN intAuditTypeID;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
   WHEN OTHERS
   THEN
      RAISE;
END;