Saturday, March 08, 2008

Heirarchical Query SQL in Oracle

create or replace
procedure company_listing (
p_start_with varchar2,
p_level number default 0 ) as
begin
dbms_output.put_line( lpad( ' ', p_level*2, ' ' ) || p_start_with );
for c in ( select *
from emp
where mgr in ( select empno
from emp
where ename = p_start_with )
order by ename )
loop
company_listing( c.ename, p_level+1 );
end loop;
end company_listing;
/
set serveroutput on format wrapped
exec company_listing( 'KING' );

select lpad( ' ', (level-1)*2, ' ' ) || ename ename
from emp
start with ename = 'KING'
connect by mgr = prior empno
/
select lpad( ' ', (level-1)*2, ' ' ) || ename ename
from emp
start with ename = 'JONES' or ename = 'SCOTT'
connect by prior empno = mgr
/
select nvl( to_char(mgr), 'NULL' ) mgr
from emp
where ename = 'KING';
select lpad( ' ', (level-1)*2, ' ' ) || ename ename
from emp
start with mgr is null
connect by mgr = prior empno;
select lpad( ' ', (level-1)*2, ' ' ) || ename ename
from emp
start with ename = 'SMITH'
connect by empno = prior mgr;
select lpad( ' ', (level-1)*2, ' ' ) || ename ename, prior ename manager
from emp
start with mgr is null
connect by prior empno = mgr;
select ename, level
from emp;
select lpad( ' ', (level-1)*2, ' ' ) || ename ename, level
from emp
start with mgr is null
connect by prior empno = mgr;
select lpad( ' ', (level-1)*2, ' ' ) || ename ename
from emp
start with mgr is null
connect by prior empno = mgr and level <= 3;

No comments: