create table test(superid varchar2(20),id varchar2(20));

insert into test values('0','1');
insert into test values('0','2');

insert into test values('1','11');
insert into test values('1','12');

insert into test values('2','21');
insert into test values('2','22');

insert into test values('11','111');
insert into test values('11','112');

insert into test values('12','121');
insert into test values('12','122');

insert into test values('21','211');
insert into test values('21','212');

insert into test values('22','221');
insert into test values('22','222');

commit;

--层次查询示例
select level||'层',lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

select level||'层',connect_by_isleaf,lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with ... connect by ...
--功能：实现按照superid分组,把id用";"连接起来
--实现：以下两个例子都是通过构造2个伪列来实现connect by连接的。

/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
select superid,id,row_number() over(partition by superid order by superid) id1,
row_number() over(order by superid) + dense_rank() over(order by superid) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by superid order by superid;

/*------method two------*/
select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';')
from(
select superid,level l,sys_connect_by_path(id,';') id
from(
select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn
from test
)
connect by prior parent_rn = rn
);

--下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connect by.

create or replace function f_digit_add(innum integer) return number
is
outnum integer;
begin
if innum<0 then
return 0;
end if;
select sum(nm) into outnum from(
select substr(innum,rownum,1) nm from dual connect by rownum<length(innum)
);
return outnum;
end f_digit_add;
/

select f_digit_add(123456) from dual;