目錄

一、創建表結構和插入數據

二、查詢所有子節點

三、查詢所有父節點

四、查詢指定節點的根節點

五、查詢指定節點的遞歸路徑

六、遞歸子類

七、遞歸父類

---

一、創建表結構和插入數據

CREATE TABLE "REGION" (	"ID" VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL ENABLE, "PARENT_ID" VARCHAR2(36), "NAME" VARCHAR2(255) NOT NULL ENABLE, "LATITUDE" NUMBER(10,6), "LONGITUDE" NUMBER(10,6), PRIMARY KEY ("ID"));COMMENT ON COLUMN REGION.ID IS '主鍵';
COMMENT ON COLUMN REGION.PARENT_ID IS '父鍵';
COMMENT ON COLUMN REGION.NAME IS '地區名';
COMMENT ON COLUMN REGION.LATITUDE IS '經度';
COMMENT ON COLUMN REGION.LONGITUDE IS '緯度';INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), NULL, '江蘇省', 31.2304, 120.663);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '江蘇省'), '蘇州市', 31.2988, 120.5853);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '蘇州市'), '張家港市', 31.8754, 120.5553);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '蘇州市'), '吳中區', 31.2622, 120.6446);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '蘇州市'), '相城區', 31.3697, 120.646);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '蘇州市'), '吳江區', 31.1791, 120.6411);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '張家港市'), '鳳凰鎮', 31.8754, 120.5553);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '張家港市'), '塘橋鎮', 31.8754, 120.5553);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吳中區'), '木瀆鎮', 31.2622, 120.6446);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '相城區'), '黃埭鎮', 31.3697, 120.646);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吳江區'), '平望鎮', 31.1791, 120.6411);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吳江區'), '黎里鎮', 31.1791, 120.6411);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '江蘇省'), '無錫市', 31.5704, 120.3055);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '無錫市'), '錫山區', 31.5887, 120.3573);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '無錫市'), '惠山區', 31.6514, 120.3036);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '無錫市'), '濱湖區', 31.5502, 120.2598);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '無錫市'), '江陰市', 31.9086, 120.2855);INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '無錫市'), '宜興市', 31.3623, 119.8233);

二、查詢所有子節點

SELECT *
FROM REGION 
START WITH NAME = '無錫市'
CONNECT BY PRIOR ID = PARENT_ID 

查詢結果：

三、查詢所有父節點

SELECT *
FROM REGION
START WITH NAME = '塘橋鎮'
CONNECT BY PRIOR PARENT_ID = ID

查詢結果：

四、查詢指定節點的根節點

SELECT r.*,
connect_by_root(r.ID),
connect_by_root(NAME)
FROM REGION r
WHERE r.NAME = '江陰市'
START WITH r.PARENT_ID = '18F2184511D13555E0630100007F8BFA'
CONNECT BY PRIOR r.ID = r.PARENT_ID 

注：18F2184511D13555E0630100007F8BFA是江蘇省的主鍵

查詢結果：

五、查詢指定節點的遞歸路徑

SELECTID,PARENT_ID ,NAME,sys_connect_by_path(NAME, '->') namepath,LEVEL
FROMREGION
START WITHNAME = '蘇州市'
CONNECT BYPRIOR ID = PARENT_ID

查詢結果：

六、遞歸子類

WITH t ( ID ,PARENT_ID,NAME ) 
AS(
SELECT ID ,PARENT_ID,NAME FROM REGION WHERE NAME='蘇州市'
UNION ALL
SELECT d.ID ,d.PARENT_ID , d.NAME FROM t , REGION d
WHERE t.ID = d.PARENT_ID
)
SELECT * FROM t

查詢結果：

七、遞歸父類

WITH t ( ID ,PARENT_ID, NAME)
AS(
SELECT ID ,PARENT_ID, NAME FROM REGION WHERE NAME='塘橋鎮'
UNION ALL
SELECT d.ID ,d.PARENT_ID,d.NAME FROM t , REGION d
WHERE t.PARENT_ID = d.ID
)
SELECT * FROM t;

查詢結果：