oracle的start with connect by prior如何使用

递归查询语法： 

select ... from tablename where 条件4 start with 条件1 connect by 条件2 AND 条件3 

--如下面的表结构

CREATE TABLE SC_DISTRICT
(
  IID         NUMBER(10)                  NOT NULL,
  PARENT_ID  NUMBER(10),
  INAME       VARCHAR2(255 BYTE)          NOT NULL,
　BZ 　　　　　NUMBER(4)
);

ALTER TABLE SC_DISTRICT ADD (
  CONSTRAINT SC_DISTRICT_PK
 PRIMARY KEY
 (IID));

ALTER TABLE SC_DISTRICT ADD (
  CONSTRAINT SC_DISTRICT_R01 
 FOREIGN KEY (PARENT_ID) 
 REFERENCES SC_DISTRICT (IID));
INSERT INTO SC_DISTRICT(IID,INAME) VALUES(1,'四川省');

INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(2,1,'巴中市',0);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(3,1,'达州市',0); 

INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(4,2,'巴州区',0);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(5,2,'通江县',0);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(6,2,'平昌县',0);

INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(7,3,'通川区',0);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(8,3,'宣汉县',0);

INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(9,8,'塔河乡',1);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(10,8,'三河乡',1);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(11,8,'胡家镇',1);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(12,8,'南坝镇',1);
 
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(13,6,'大寨乡',2);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(14,6,'响滩镇',2);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(15,6,'龙岗镇',2);
INSERT INTO SC_DISTRICT(IID,PARENT_ID,INAME,BZ) VALUES(16,6,'白衣镇',2);

--获取平昌县的子节点名称
select * from SC_DISTRICT t start with iname='平昌县' connect by prior iid = parent_id;

--不加prior时，获取的是当前节点名称
select * from SC_DISTRICT t start with iname='平昌县' connect by  iid = parent_id;

 

 

--prior放到等号右边时，取该节点的父节点名称，或者
select * from SC_DISTRICT t start with iname='平昌县' connect by  iid = prior parent_id;

--此语句的结果也是获取子节点的上级节点
select * from SC_DISTRICT t start with iname='平昌县' connect by prior parent_id = iid;

 

 

--过滤BZ为0的子节点的办法，或者
select * from sc_district where bz = 0 start with iname = '平昌县' connect by prior parent_id= iid

--和上边语句结果相同

select * from sc_district start with iname = '平昌县' connect by prior parent_id= iid and bz = 0