oracle中start with connect by 用法

oracle中start with ... connect by ... 主要用于对树形结构的数据进行查询，即递归查询。
start with 后面接起始行的条件，connect by 后面是后续查询的条件。
如：
select * from regioninfo t start with t.regioncode='430102400000' connect by prior t.parentregioncode = t.regioncode;
start with 后是起始行的条件 t.regioncode='430102400000' 即根据这个条件查出regioninfo 表中的数据（相当于select * from regioninfo t where t.regioncode='430102400000' ）；
connect by 后是后续查询的条件，关键字prior可以理解为先前的、上一行的数据，这个关键字的位置决定后续查询的结果，
在这里prior后面是t.parentregioncode = t.regioncode，意思是把上一次查询结果中的parentregioncode字段值当作本次查询的条件，即本次查询条件为：t.regioncode=上一次查询结果的parentregioncode，循环此步骤直到查不出数据

创建表 regionInfo：

create table regionInfo(
 id varchar2(50) primary key,
 regionCode varchar2(20),
 regionName varchar2(200),
 regionType varchar2(2),
 parentRegionCode varchar2(20)
);

插入数据：

insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430000','湖南省','1','CN');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430100','长沙市','2','430000'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102','芙蓉区','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430103','天心区','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430104','岳麓区','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430105','开福区','2','430100');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430111','雨花区','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430112','望城区','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430181','浏阳市','2','430100'); 
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430182','宁乡市','2','430100');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430121','长沙县','2','430100');

insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102001000','文艺路街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102002000','朝阳街街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102003000','韭菜园街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102008000','五里牌街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102009000','火星街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102010000','马王堆街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102011000','东屯渡街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102012000','湘湖街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102013000','定王台街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102014000','荷花园街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102015000','东岸街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102016000','马坡岭街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102017000','东湖街道','3','430102');
insert into regioninfo(id, regioncode, regionname, regiontype, parentregioncode)values(sys_guid(),'430102400000','隆平高科技园','3','430102');