Trying to get this output:
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
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.