/*--建表语句
CREATE TABLE SC_DISTRICT
(
ID NUMBER(10) NOT NULL,
PARENT_ID NUMBER(10),
NAME VARCHAR2(255 BYTE) NOT NULL
);
ALTER TABLE SC_DISTRICT ADD (
CONSTRAINT SC_DISTRICT_PK
PRIMARY KEY
(ID));
ALTER TABLE SC_DISTRICT ADD (
CONSTRAINT SC_DISTRICT_R01
FOREIGN KEY (PARENT_ID)
REFERENCES SC_DISTRICT (ID));*/
-- 插入数据:
--INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(0,null,'中国');
/*INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,'广东省');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,'广州市');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,'深圳市');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,'越秀区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,'天河区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,'黄浦区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(7,3,'福田区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(8,3,'南山区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(9,8,'南山');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(10,8,'南头');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(11,8,'蛇口街道');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(12,8,'沙河街道');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(13,6,'外滩街道');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(14,6,'南京东路街道');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(15,6,'老西门街道');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(16,6,'半淞园路街道');*/
-- Oracle中的select语句可以用start with...connect by prior子句实现递归查询，connect by 是结构化查询中用到的
--查询某节点的所有子孙节点
--查询广州市下面的所有行政组织(结果包含当前节点):
SELECT * FROM SC_DISTRICT START WITH NAME='广州市'
CONNECT BY PRIOR ID=PARENT_ID
--查询指定节点的递归根节点
--查询老西门街道所属的市：
--CONNECT_BY_ROOT 操作的功能就是获取根节点记录的字段信息。这个功能在 9i当中可以利用 SYS_CONNECT_BY_PATH来实现
--虽然9i中的实现比10g要麻烦一些，但是利用SYS_CONNECT_BY_PATH还是可以实现这个功能的。
SELECT ID, PARENT_ID, NAME,
SUBSTR( SYS_CONNECT_BY_PATH(ID, '/') || '/', 2,
INSTR(SYS_CONNECT_BY_PATH(ID, '/') || '/', '/', 1, 2) - 2)
/* CONNECT_BY_ROOT(ID) */ CITY_ID,
SUBSTR( SYS_CONNECT_BY_PATH(NAME, '/') || '/', 2,
INSTR(SYS_CONNECT_BY_PATH(NAME, '/') || '/', '/', 1, 2) - 2)
/*CONNECT_BY_ROOT(NAME)*/ CITY_NAME FROM SC_DISTRICT WHERE NAME='老西门街道'
START WITH PARENT_ID=1 CONNECT BY PRIOR ID=PARENT_ID
--在10g中 Oracle提供了新的操作： CONNNECT_BY_ROOT，通过这个操作，可以获取树形查询根记录的字段
SELECT ID, PARENT_ID, NAME,CONNECT_BY_ROOT(ID) CITY_ID,
CONNECT_BY_ROOT(NAME) CITY_NAME FROM SC_DISTRICT WHERE NAME='老西门街道'
START WITH PARENT_ID=1 CONNECT BY PRIOR ID=PARENT_ID
--CONNECT BY子句伪列的应用 LEVEL:查询节点层次，从1开始。
--CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
--在10g中Oracle提供了新的伪列：CONNECT_BY_ISLEAF，通过这个伪列，可以判断当前的记录是否是树的叶节点
SELECT ID, NAME, PARENT_ID, LEVEL, CONNECT_BY_ISLEAF
FROM SC_DISTRICT START WITH NAME='广州市'
CONNECT BY PRIOR ID=PARENT_ID ORDER BY ID;
--CONNECT_BY_ISLEAF可以判断当前记录是否是树的叶节点。而这个功能在9i中没有简单的方法来实现，只能通过分析函数来进行判断：
--利用分析函数可以相对简单的在9i实现CONNECT_BY_ISLEAF伪列的功能
SELECT ID, NAME, PARENT_ID, -- LEVEL, CONNECT_BY_ISLEAF
case when lead(levels)over(order by rn)>levels then 0 else 1 end leaf
FROM (select rownum rn,id,PARENT_ID,NAME,level levels from SC_DISTRICT
START WITH NAME='广州市' CONNECT BY PRIOR ID=PARENT_ID ORDER BY ID);
--查询递归路径 查询广州市下行政组织递归路径
SELECT ID, NAME, PARENT_ID, SUBSTR(SYS_CONNECT_BY_PATH(NAME,'->'),3) NAME_PATH
FROM SC_DISTRICT START WITH NAME='广州市' CONNECT BY PRIOR ID=PARENT_ID
