databaseoracle-databaseplsqlcursors

Function & Cursor HELP ORACLE PL/SQL


Trying to get this output:

enter image description here

   SELECT E.ID_EMPLEADO ,E.APEMPPAT  , E.ID_JEFE   
     FROM EMPLEADOS E
     WHERE E.ID_SUCURSAL=4
     CONNECT BY PRIOR E.ID_EMPLEADO = E.ID_JEFE ;

with this function

    CREATE OR REPLACE FUNCTION EMPLO_FUN
(V_EMPL_SUC IN EMPLEADOS.ID_SUCURSAL%TYPE) 
RETURN VARCHAR2
IS
    V_NEMP_HASR EMPLEADOS.ID_EMPLEADO%TYPE;
    V_DEP_HASR  EMPLEADOS.APEMPPAT%TYPE;
    V_JURIS_HASR EMPLEADOS.ID_JEFE%TYPE;
CURSOR C1
IS 
   SELECT E.ID_EMPLEADO ,E.APEMPPAT, E.ID_JEFE  
     INTO  V_NEMP_HASR, V_DEP_HASR, V_JURIS_HASR 
     FROM EMPLEADOS E
     WHERE E.ID_SUCURSAL=V_EMPL_SUC
     CONNECT BY PRIOR E.ID_EMPLEADO = E.ID_JEFE ;
   BEGIN

   OPEN c1;
    FETCH c1 INTO V_NEMP_HASR, V_DEP_HASR, V_JURIS_HASR;


   CLOSE c1;
   RETURN V_NEMP_HASR;
    RETURN V_DEP_HASR;
    RETURN V_JURIS_HASR;
    END;

 SELECT EMPLO_FUN (4) FROM DUAL;

I know that functions mostly return one value serching found that i can use a cursor to get multiples values, but i need help,I think some loop sentence is missing


Solution

  • In your Query you are returning VARCHAR2 which will return only a value. Instead you should return SYS_REFCURSOR

    CREATE OR REPLACE FUNCTION EMPLO_FUN (V_EMPL_SUC IN EMPLEADOS.ID_SUCURSAL%TYPE) 
    RETURN SYS_REFCURSOR
    AS
        V_MYRESULT SYS_REFCURSOR;
    BEGIN
        OPEN V_MYRESULT FOR
            SELECT E.ID_EMPLEADO ,E.APEMPPAT, E.ID_JEFE  
            FROM EMPLEADOS E
            WHERE E.ID_SUCURSAL=V_EMPL_SUC
            CONNECT BY PRIOR E.ID_EMPLEADO = E.ID_JEFE ;
    
    RETURN V_MYRESULT;
    END;
    

    Then SELECT EMPLO_FUN (4) FROM DUAL; should give you the expected result.