PL/SQL“all_search” procedure 不是一个过程或者未定义

Question

该函数位于包内，但是当我调用该函数时，出现以下错误：PL/SQL“all_search”不是过程或未定义。有人可以帮助我吗？

CREATE OR REPLACE PACKAGE employee_tab IS

       FUNCTION all_search (ID_EMP in NUMBER) RETURN O_T_EMPL PIPELINED;
                     
END employee_tab;
/
CREATE OR REPLACE TYPE O_T_EMPL AS TABLE OF O_EMPLOYEE;
/
CREATE OR REPLACE PACKAGE BODY employee_tab IS 

FUNCTION all_search (ID_EMP in NUMBER) RETURN O_T_EMPL PIPELINED
        IS
          TAB_OBJC_EMP  O_T_EMPL;
          MY_QUERY_SEARCH VARCHAR2(400);
          REF_C  SYS_REFCURSOR;
          MAX_ROW NUMBER := 25;
      
        BEGIN
       
         MY_QUERY_SEARCH := 'SELECT *
                              FROM EMPLOYEES
                             WHERE EMPLOYEE_ID = ID_EMP';    
            
         open REF_C for MY_QUERY_SEARCH using ID_EMP;
            
              loop
                --
                 fetch REF_C bulk collect into TAB_OBJC_EMP limit MAX_ROW;
                 exit when TAB_OBJC_EMP.count = 0;
                 for i in 1..TAB_OBJC_SEE.count
                 --
                   loop
                       pipe row(O_EMPLOYEE(TAB_OBJC_EMP(i).V_O_EMP_ID,
                                                TAB_OBJC_EMP(i).V_O_HIRE_ID,
                                                TAB_OBJC_EMP(i).V_O_DEP_ID)
                             );
         
                   end loop;
                 --
               END loop;
             --
          CLOSE REF_C;
          RETURN;
       --
        END all_search;

END employee_tab;

/

调用函数：employee_tab.all_search(1);

Answer 1

您需要将功能的结果分配给某些内容，请尝试以下操作：

DECLARE
  l_emp_id NUMBER;
BEGIN
  SELECT EMP_ID
    INTO l_emp_id 
    FROM TABLE(employee_tab.all_search(1))
   WHERE rownum = 1;

  DBMS_OUTPUT.put_line(TO_CHAR(EMP_ID));
END;
/