create or replace
FUNCTION  K_HIER(p_mgrno NUMBER) RETURN VARCHAR2 AS

v_abc number:=1;
v_a number;

CURSOR parent_cursor(p_cc number) IS 
    SELECT  empno FROM   emp where empno=p_cc;
CURSOR parent_child_hier(p_c_mgrno number) is 
    SELECT mgr ,empno,ename FROM emp WHERE  mgr = p_c_mgrno;
BEGIN
dbms_output.put_line('Mananger'||' '||p_mgrno);
dbms_output.put_line('mgr'||' '||'empno');

FOR i IN parent_cursor(p_mgrno) LOOP
v_a:=i.empno;

while(v_abc>0)loop
begin
     select count(*) into v_abc from emp where mgr=v_a;
  if v_abc>1 then
     FOR j IN parent_child_hier(i.empno) LOOP
     dbms_output.put_line(j.mgr||' '||j.ename);
     v_a:=j.empno;
end loop;
  end if;
end;
end loop;
end loop;
END K_HIER;

SQL> create or replace
  2  FUNCTION  K_HIER(p_mgrno NUMBER) RETURN VARCHAR2 AS
  3  
  4  v_abc number:=1;
  5  v_a number;
  6  
  7  CURSOR parent_cursor(p_cc number) IS 
  8      SELECT  empno FROM   emp where empno=p_cc;
  9  CURSOR parent_child_hier(p_c_mgrno number) is 
 10      SELECT mgr ,empno,ename FROM emp WHERE  mgr = p_c_mgrno;
 11  BEGIN
 12  dbms_output.put_line('Mananger'||' '||p_mgrno);
 13  dbms_output.put_line('mgr'||' '||'empno');
 14  
 15  FOR i IN parent_cursor(p_mgrno) LOOP
 16  v_a:=i.empno;
 17  
 18  while(v_abc>0)loop
 19  begin
 20       select count(*) into v_abc from emp where mgr=v_a;
 21    if v_abc>1 then
 22       FOR j IN parent_child_hier(i.empno) LOOP
 23       dbms_output.put_line(j.mgr||' '||j.ename);
 24       v_a:=j.empno;
 25  end loop;
 26    end if;
 27  end;
 28  end loop;
 29  end loop;
 30  END K_HIER;
 31  /

Function created.

SQL> select K_HIER(null) from dual;
select K_HIER(null) from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MICHEL.K_HIER", line 29


Mananger
mgr empno

SCOTT@orcl_11g> COLUMN ename FORMAT A15
SCOTT@orcl_11g> SELECT empno,
  2  	    RPAD (LPAD (' ', LEVEL * 2) || ename, 15) AS name,
  3  	    mgr
  4  FROM   emp
  5  START  WITH mgr IS NULL
  6  CONNECT BY PRIOR empno = mgr
  7  ORDER  SIBLINGS BY ename
  8  /

     EMPNO NAME                   MGR
---------- --------------- ----------
      7839   KING
      7698     BLAKE             7839
      7499       ALLEN           7698
      7900       JAMES           7698
      7654       MARTIN          7698
      7844       TURNER          7698
      7521       WARD            7698
      7782     CLARK             7839
      7934       MILLER          7782
      7566     JONES             7839
      7902       FORD            7566
      7369         SMITH         7902
      7788       SCOTT           7566
      7876         ADAMS         7788

14 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE k_hier
  2    (p_mgrno IN NUMBER,
  3  	p_level IN NUMBER DEFAULT 1)
  4  AS
  5  BEGIN
  6    FOR r IN
  7  	 (SELECT empno, ename
  8  	  FROM	 emp
  9  	  WHERE  NVL (mgr, 0) = NVL (p_mgrno, 0)
 10  	  ORDER  BY ename)
 11    LOOP
 12  	 DBMS_OUTPUT.PUT_LINE
 13  	   (LPAD (r.empno, 10) || ' ' ||
 14  	    RPAD (LPAD (' ', p_level * 2) || r.ename, 15) || ' ' ||
 15  	    LPAD (p_mgrno, 10));
 16  	 k_hier (r.empno, p_level + 1);
 17    END LOOP;
 18  END k_hier;
 19  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SET SERVEROUTPUT ON FORMAT WRAPPED
SCOTT@orcl_11g> EXEC k_hier (NULL)
      7839   KING
      7698     BLAKE             7839
      7499       ALLEN           7698
      7900       JAMES           7698
      7654       MARTIN          7698
      7844       TURNER          7698
      7521       WARD            7698
      7782     CLARK             7839
      7934       MILLER          7782
      7566     JONES             7839
      7902       FORD            7566
      7369         SMITH         7902
      7788       SCOTT           7566
      7876         ADAMS         7788

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>