Hierarchial Loop I

Oracle

This procedure finds all the employees working under ‘KING’ from the ‘SCOTT.EMP’ table.
Starting with ‘KING’ all the employees working under ‘KING’ or under his subordinates are found.

For my procedure, I had created a table PRIOR_JP. (Details given in comments section)

Author: JP Vijaykumar

set serverout on size 1000000
 declare
   v_count  number(10);
   v_empno  varchar2(10);
  v_mgr  varchar2(10);
   v_ename  varchar2(10);
   v_str  varchar2(1000);
   begin
   /* create table prior_jp(empno number(4),
   	ename varchar2(10),mgr number(4));
   select empno,ename,mgr from scott.emp
   connect by prior empno = mgr start with ename = ‘KING’;  */
   execute immediate (‘truncate table prior_jp’);
   execute immediate
‘insert into prior_jp select empno ,
	ename,mgr from scott.emp where ename=”KING”’;
   v_str:=’select count(*) from prior_jp’;
   execute immediate v_str into v_count;
   while (v_count > 0) loop
    for c1 in (select empno,ename, mgr  from prior_jp) loop
     v_empno:=c1.empno;
                   v_ename:=c1.ename;
     v_mgr:=c1.mgr;
     select count(*) into v_count from scott.emp
      where mgr = v_empno;
     if (v_count = 0) then
      dbms_output.put_line(v_empno||’ ‘||c1.ename||’ ‘||c1.mgr);
      delete from  prior_jp where empno=v_empno;
     else
      insert into prior_jp
      select empno, ename,mgr from scott.emp where mgr=v_empno;
      dbms_output.put_line(v_empno||’ ‘||c1.ename||’ ‘||c1.mgr);
      delete from  prior_jp where empno=v_empno;
      for c2 in (select empno,ename,mgr from scott.emp
                 where mgr = V_EMPNO) loop
       v_empno:=c2.empno;
                                   v_ename:=c2.ename;
       v_mgr:=c2.mgr;
      end loop;
     end if;
    end loop;
    select count(*) into v_count from prior_jp;
   end loop;
   end;
   /
7839 KING
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7902 FORD 7566
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7934 MILLER 7782
7876 ADAMS 7788
7369 SMITH 7902
PL/SQL procedure successfully completed.

The same results can be derived from the following ‘SELECT .. CONNECT BY PRIOR ..’ query

 set pagesize 0
 select empno,ename,mgr from scott.emp
    connect by prior empno = mgr start with ename = ‘KING’;
      7839 KING
      7566 JONES            7839
      7788 SCOTT            7566
      7876 ADAMS            7788
      7902 FORD             7566
      7369 SMITH            7902
      7698 BLAKE            7839
      7499 ALLEN            7698
      7521 WARD             7698
      7654 MARTIN           7698
      7844 TURNER           7698
      7900 JAMES            7698
      7782 CLARK            7839
      7934 MILLER           7782
14 rows selected.

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home