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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles