剑破冰山之十一章 层次查询

层次查询的函数的两种方式：
SYS_CONNECT_BY_PATH
WMSYS.WM_CONCAT()

语法：
SELECT…START WITH…CONNECT BY PRIOR X1=X2

练习数据，中国省份部分城市和区号（没有台湾省）

insert into prov_cw_area (ID,GOV_CODE,LEVEL1_ID,AREA_CODE,DEP_CODE,SORT,LEVEL2_ID, NAME,CLASSID) values 
(366, 'KIZ', 6, '0908', null, 361, 353, '克孜勒苏柯尔克孜自治州', 3)
,(367, 'ILD', 6, '0999', null, 362, 353, '伊犁哈萨克自治州', 3)
,(368, 'TCD', 6, '0901', null, 363, 353, '塔城地区', 3)
,(369, 'WJS', 6, '0992', null, 364, 353, '奎屯', 3)
,(6, 'CN', null, null, '100000', 1, null, '中国', 1)
,(7, 'DGC', 6, null, '200000', 2, null, '北京', 2)
,(8, 'BJ', 6, '010', '609001', null, 7, '北京市', 3)
,(9, 'SH', 6, '021', '600102', null, 5, '上海市', 3)
,(10, 'TJ', 6, '022', '609902', null, 4, '天津市', 3)
,(11, 'CQ', 6, '023', '600304', null, 3, '重庆市', 3)
,(12, 'HK', 6, '852', '609801-1', null, 2, '香港特别行政区', 3)
,(13, 'MO', 6, '853', '609801-2', null, 1, '澳门特别行政区', 3)
,(14, 'HE', 6, null, '609906', 9, null, '河北省', 2)
,(15, 'HDS', 6, '0310', null, 10, 14, '邯郸市', 3)
,(16, 'SJW', 6, '0311', null, 11, 14, '石家庄市', 3)
,(17, 'BDS', 6, '0312', null, 12, 14, '保定市', 3)
,(18, 'ZJK', 6, '0313', null, 13, 14, '张家口市', 3)
,(19, 'CDS', 6, '0314', null, 14, 14, '承德市', 3)
,(20, 'TGS', 6, '0315', null, 15, 14, '唐山市', 3)
,(21, 'LFS', 6, '0316', null, 16, 14, '廊坊市', 3)
,(22, 'CGZ', 6, '0317', null, 17, 14, '沧州市', 3)
,(23, 'HGS', 6, '0318', null, 18, 14, '衡水市', 3)
,(24, 'XTS', 6, '0319', null, 19, 14, '邢台市', 3)
,(25, 'SHP', 6, '0335', null, 20, 14, '秦皇岛市', 3)
,(26, 'SX', 6, null, '609907', 21, null, '山西省', 2)
,(27, 'SZJ', 6, '0349', null, 22, 26, '朔州市', 3)
,(28, 'XZS', 6, '0350', null, 23, 26, '忻州市', 3)
,(29, 'TYN', 6, '0351', null, 24, 26, '太原市', 3)
,(30, 'DTG', 6, '0352', null, 25, 26, '大同市', 3)
,(31, 'YQS', 6, '0353', null, 26, 26, '阳泉市', 3)
,(32, 'JZN', 6, '0354', null, 27, 26, '晋中市', 3)
,(33, 'CZS', 6, '0355', null, 28, 26, '长治市', 3)
,(34, 'JCG', 6, '0356', null, 29, 26, '晋城市', 3)
,(35, 'LFN', 6, '0357', null, 30, 26, '临汾市', 3)
,(36, 'LLH', 6, '0358', null, 31, 26, '吕梁市', 3)
,(37, 'YCE', 6, '0359', null, 32, 26, '运城市', 3)
,(38, 'NM', 6, null, '609908', 33, null, '内蒙古自治区', 2)
,(39, 'HBR', 6, '0470', null, 34, 38, '呼伦贝尔市', 3)
,(40, 'HET', 6, '0471', null, 35, 38, '呼和浩特市', 3)
,(41, 'BTS', 6, '0472', null, 36, 38, '包头市', 3)
,(42, 'WHM', 6, '0473', null, 37, 38, '乌海市', 3)
,(43, 'ULS', 6, '0474', null, 38, 38, '乌兰察布市', 3)
,(44, 'TLO', 6, '0475', null, 39, 38, '通辽市', 3)
,(45, 'CFS', 6, '0476', null, 40, 38, '赤峰市', 3)
,(46, 'ODS', 6, '0477', null, 41, 38, '鄂尔多斯市', 3)
,(47, 'BYR', 6, '0478', null, 42, 38, '巴彦淖尔市', 3)
,(48, 'XGO', 6, '0479', null, 43, 38, '锡林郭勒盟', 3)
,(49, 'HIN', 6, '0482', null, 44, 38, '兴安盟', 3)
,(50, 'ALM', 6, '0483', null, 45, 38, '阿拉善盟', 3)
,(51, 'LN', 6, null, '609905', 46, null, '辽宁省', 2)
,(52, 'SHE', 6, '024', null, 47, 51, '沈阳市', 3)
,(54, 'DLC', 6, '0411', null, 49, 51, '大连市', 3)
,(55, 'ASN', 6, '0412', null, 50, 51, '鞍山市', 3)
,(57, 'BXS', 6, '0414', null, 52, 51, '本溪市', 3)
,(58, 'DDG', 6, '0415', null, 53, 51, '丹东市', 3)
,(59, 'JNZ', 6, '0416', null, 54, 51, '锦州市', 3)
,(60, 'YIK', 6, '0417', null, 55, 51, '营口市', 3)
,(61, 'FXS', 6, '0418', null, 56, 51, '阜新市', 3)
,(62, 'LYL', 6, '0419', null, 57, 51, '辽阳市', 3)
,(63, 'CYS', 6, '0421', null, 58, 51, '朝阳市', 3)
,(64, 'PJS', 6, '0427', null, 59, 51, '盘锦市', 3)
,(65, 'HLD', 6, '0429', null, 60, 51, '葫芦岛市', 3)
,(66, 'JL', 6, null, '609909', 61, null, '吉林省', 2)
,(67, 'CGQ', 6, '0431', null, 62, 66, '长春市', 3)
,(68, 'JLS', 6, '0432', null, 63, 66, '吉林市', 3)
,(69, 'YBZ', 6, '0433', null, 64, 66, '延边朝鲜族自治州', 3)
,(70, 'SPS', 6, '0434', null, 65, 66, '四平市', 3)
,(71, 'THS', 6, '0435', null, 66, 66, '通化市', 3)
,(72, 'BCS', 6, '0436', null, 67, 66, '白城市', 3)
,(73, 'LYH', 6, '0437', null, 68, 66, '辽源市', 3)
,(74, 'SYU', 6, '0438', null, 69, 66, '松原市', 3)
,(75, 'BSN', 6, '0439', null, 70, 66, '白山市', 3)
,(76, 'HUC', 6, '0440', null, 71, 66, '珲春市', 3)
,(77, 'MHK', 6, '0448', null, 72, 66, '梅河口', 3)
,(78, 'HL', 6, null, '609910', 73, null, '黑龙江省', 2)
,(79, 'HRB', 6, '0451', null, 74, 78, '哈尔滨市', 3)
,(80, 'NDG', 6, '0452', null, 75, 78, '齐齐哈尔市', 3)
,(81, 'MDG', 6, '0453', null, 76, 78, '牡丹江市', 3)
,(82, 'QTH', 6, '0464', null, 77, 78, '七台河市', 3)
,(83, 'JMU', 6, '0454', null, 78, 78, '佳木斯市', 3)
,(84, 'SUH', 6, '0455', null, 79, 78, '绥化市', 3)
,(85, 'HEK', 6, '0456', null, 80, 78, '黑河市', 3)
,(86, 'DHL', 6, '0457', null, 81, 78, '大兴安岭地区', 3)
,(87, 'YCH', 6, '0458', null, 82, 78, '伊春市', 3)
,(88, 'DQG', 6, '0459', null, 83, 78, '大庆市', 3)
,(89, 'JXI', 6, '0467', null, 84, 78, '鸡西市', 3)
,(90, 'HEG', 6, '0468', null, 85, 78, '鹤岗市', 3)
,(91, 'SYS', 6, '0469', null, 86, 78, '双鸭山市', 3)
,(92, 'JS', 6, null, '600103', 87, null, '江苏省', 2)
,(93, 'NKG', 6, '025', null, 88, 92, '南京市', 3)
,(94, 'WUX', 6, '0510', null, 89, 92, '无锡市', 3)
,(95, 'ZHE', 6, '0511', null, 90, 92, '镇江市', 3)
,(96, 'SZH', 6, '0512', null, 91, 92, '苏州市', 3)
,(97, 'NTG', 6, '0513', null, 92, 92, '南通市', 3)
,(98, 'YZH', 6, '0514', null, 93, 92, '扬州市', 3)
,(99, 'YCK', 6, '0515', null, 94, 92, '盐城市', 3)
,(100, 'XUZ', 6, '0516', null, 95, 92, '徐州市', 3)
,(101, 'HAS', 6, '0517', null, 96, 92, '淮安市', 3)
,(102, 'LYG', 6, '0518', null, 97, 92, '连云港市', 3)
,(103, 'CZX', 6, '0519', null, 98, 92, '常州市', 3)
,(104, 'TZS', 6, '0523', null, 99, 92, '泰州市', 3)
,(105, 'SUQ', 6, '0527', null, 100, 92, '宿迁市', 3)
,(106, 'ZJ', 6, null, '600104', 101, null, '浙江省', 2)
,(107, 'QUZ', 6, '0570', null, 102, 106, '衢州市', 3)
,(108, 'HGH', 6, '0571', null, 103, 106, '杭州市', 3)
,(109, 'HZH', 6, '0572', null, 104, 106, '湖州市', 3)
,(110, 'JIX', 6, '0573', null, 105, 106, '嘉兴市', 3)
,(111, 'NGB', 6, '0574', null, 106, 106, '宁波市', 3)
,(112, 'SXG', 6, '0575', null, 107, 106, '绍兴市', 3)
,(113, 'TZZ', 6, '0576', null, 108, 106, '台州市', 3)
,(114, 'WNZ', 6, '0577', null, 109, 106, '温州市', 3)
,(115, 'LSS', 6, '0578', null, 110, 106, '丽水市', 3)
,(116, 'JHA', 6, '0579', null, 111, 106, '金华市', 3)
,(117, 'ZOS', 6, '0580', null, 112, 106, '舟山市', 3)
,(118, 'AH', 6, null, '600301', 113, null, '安徽省', 2)
,(119, 'CUZ', 6, '0550', null, 114, 118, '滁州市', 3)
,(120, 'HFE', 6, '0551', null, 115, 118, '合肥市', 3)
,(121, 'BBU', 6, '0552', null, 116, 118, '蚌埠市', 3)
,(122, 'WHI', 6, '0553', null, 117, 118, '芜湖市', 3)
,(123, 'HNS', 6, '0554', null, 118, 118, '淮南市', 3)
,(124, 'MAA', 6, '0555', null, 119, 118, '马鞍山市', 3)
,(125, 'AQG', 6, '0556', null, 120, 118, '安庆市', 3)
,(126, 'SUZ', 6, '0557', null, 121, 118, '宿州市', 3)
,(127, 'FYS', 6, '0558', null, 122, 118, '阜阳亳州', 3)
,(128, 'HSN', 6, '0559', null, 123, 118, '黄山市', 3)
,(129, 'HBE', 6, '0561', null, 124, 118, '淮北市', 3)
,(130, 'TOL', 6, '0562', null, 125, 118, '铜陵市', 3)
,(131, 'XCI', 6, '0563', null, 126, 118, '宣城市', 3)
,(132, 'LAW', 6, '0564', null, 127, 118, '六安市', 3)
,(133, 'CAH', 6, '0565', null, 128, 118, '巢湖市', 3)
,(134, 'CIZ', 6, '0566', null, 129, 118, '池州市', 3)
,(135, 'FJ', 6, null, '600105', 130, null, '福建省', 2)
,(136, 'FOC', 6, '0591', null, 131, 135, '福州市', 3)
,(137, 'XMN', 6, '0592', null, 132, 135, '厦门市', 3)
,(138, 'NDS', 6, '0593', null, 133, 135, '宁德市', 3)
,(139, 'PUT', 6, '0594', null, 134, 135, '莆田市', 3)
,(140, 'QZJ', 6, '0595', null, 135, 135, '泉州市', 3)
,(141, 'ZZU', 6, '0596', null, 136, 135, '漳州市', 3)
,(142, 'LYF', 6, '0597', null, 137, 135, '龙岩市', 3)
,(143, 'SMS', 6, '0598', null, 138, 135, '三明市', 3)
,(144, 'NPS', 6, '0599', null, 139, 135, '南平市', 3)
,(145, 'JX', 6, null, '600305', 140, null, '江西省', 2)
,(146, 'XYU', 6, '0790', null, 141, 145, '新余市', 3)
,(147, 'KHN', 6, '0791', null, 142, 145, '南昌市', 3)
,(148, 'JIU', 6, '0792', null, 143, 145, '九江市', 3)
,(149, 'SRS', 6, '0793', null, 144, 145, '上饶市', 3)
,(150, 'FUZ', 6, '0794', null, 145, 145, '抚州市', 3)
,(151, 'YCN', 6, '0795', null, 146, 145, '宜春市', 3)
,(152, 'JAS', 6, '0796', null, 147, 145, '吉安市', 3)
,(153, 'GZH', 6, '0797', null, 148, 145, '赣州市', 3)
,(154, 'JDZ', 6, '0798', null, 149, 145, '景德镇市', 3)
,(155, 'PXS', 6, '0799', null, 150, 145, '萍乡市', 3)
,(156, 'YTS', 6, '0701', null, 151, 145, '鹰潭市', 3)
,(157, 'SD', 6, null, '609903', 152, null, '山东省', 2)
,(158, 'HZS', 6, '0530', null, 153, 157, '菏泽市', 3)
,(159, 'TNA', 6, '0531', null, 154, 157, '济南市', 3)
,(160, 'TAO', 6, '0532', null, 155, 157, '青岛市', 3)
,(161, 'ZBO', 6, '0533', null, 156, 157, '淄博市', 3)
,(162, 'DZS', 6, '0534', null, 157, 157, '德州市', 3)
,(163, 'YNT', 6, '0535', null, 158, 157, '烟台市', 3)
,(164, 'WEF', 6, '0536', null, 159, 157, '潍坊市', 3)
,(165, 'JNG', 6, '0537', null, 160, 157, '济宁市', 3)
,(166, 'TAI', 6, '0538', null, 161, 157, '泰安市', 3)
,(167, 'LYI', 6, '0539', null, 162, 157, '临沂市', 3)
,(168, 'BZH', 6, '0543', null, 163, 157, '滨州市', 3)
,(169, 'DYG', 6, '0546', null, 164, 157, '东营市', 3)
,(170, 'WEH', 6, '0631', null, 165, 157, '威海市', 3)
,(171, 'ZZG', 6, '0632', null, 166, 157, '枣庄市', 3)
,(172, 'RZH', 6, '0633', null, 167, 157, '日照市', 3)
,(173, 'LWS', 6, '0634', null, 168, 157, '莱芜市', 3)
,(174, 'LCH', 6, '0635', null, 169, 157, '聊城市', 3)
,(175, 'HA', 6, null, '609904', 170, null, '河南省', 2)
,(176, 'SQS', 6, '0370', null, 171, 175, '商丘市', 3)
,(177, 'CGO', 6, '0371', null, 172, 175, '郑州市', 3)
,(178, 'AYS', 6, '0372', null, 173, 175, '安阳市', 3)
,(179, 'XXS', 6, '0373', null, 174, 175, '新乡市', 3)
,(180, 'XCS', 6, '0374', null, 175, 175, '许昌市', 3)
,(181, 'PDS', 6, '0375', null, 176, 175, '平顶山市', 3)
,(182, 'XYG', 6, '0376', null, 177, 175, '信阳市', 3)
,(183, 'NYS', 6, '0377', null, 178, 175, '南阳市', 3)
,(184, 'KFS', 6, '0378', null, 179, 175, '开封市', 3)
,(185, 'LYA', 6, '0379', null, 180, 175, '洛阳市', 3)
,(186, 'JZY', 6, '0391', null, 181, 175, '焦作市', 3)
,(203, 'ESH', 6, '0718', null, 198, 193, '恩施土家族苗族自治州', 3)
,(187, 'HBS', 6, '0392', null, 182, 175, '鹤壁市', 3)
,(188, 'PYS', 6, '0393', null, 183, 175, '濮阳市', 3)
,(189, 'ZKS', 6, '0394', null, 184, 175, '周口市', 3)
,(190, 'LHS', 6, '0395', null, 185, 175, '漯河市', 3)
,(191, 'ZMD', 6, '0396', null, 186, 175, '驻马店市', 3)
,(192, 'SMX', 6, '0398', null, 187, 175, '三门峡市', 3)
,(193, 'HB', 6, null, '600202', 188, null, '湖北省', 2)
,(194, 'WUH', 6, '027', null, 189, 193, '武汉市', 3)
,(195, 'XFN', 6, '0710', null, 190, 193, '襄樊市', 3)
,(196, 'EZS', 6, '0711', null, 191, 193, '鄂州市', 3)
,(197, 'XGE', 6, '0712', null, 192, 193, '孝感市', 3)
,(198, 'HGE', 6, '0713', null, 193, 193, '黄冈市', 3)
,(199, 'HSI', 6, '0714', null, 194, 193, '黄石市', 3)
,(200, 'XNS', 6, '0715', null, 195, 193, '咸宁市', 3)
,(201, 'JGZ', 6, '0716', null, 196, 193, '荆州市', 3)
,(202, 'YCO', 6, '0717', null, 197, 193, '宜昌市', 3)
,(204, 'SYE', 6, '0719', null, 199, 193, '十堰市', 3)
,(205, 'SZR', 6, '0722', null, 200, 193, '随州市', 3)
,(206, 'JMS', 6, '0724', null, 201, 193, '荆门市', 3)
,(207, 'XNT', 6, '0728', null, 202, 193, '仙桃', 3)
,(208, 'HN', 6, null, '600203', 203, null, '湖南省', 2)
,(209, 'YYG', 6, '0730', null, 204, 208, '岳阳市', 3)
,(210, 'CSX', 6, '0731', null, 205, 208, '长沙市 湘潭市 株州市', 3)
,(213, 'HNY', 6, '0734', null, 208, 208, '衡阳市', 3)
,(214, 'CNZ', 6, '0735', null, 209, 208, '郴州市', 3)
,(215, 'CDE', 6, '0736', null, 210, 208, '常德市', 3)
,(216, 'YYS', 6, '0737', null, 211, 208, '益阳市', 3)
,(217, 'LDI', 6, '0738', null, 212, 208, '娄底市', 3)
,(218, 'SYR', 6, '0739', null, 213, 208, '邵阳市', 3)
,(219, 'XXZ', 6, '0743', null, 214, 208, '湘西土家族苗族自治州', 3)
,(220, 'ZJJ', 6, '0744', null, 215, 208, '张家界市', 3)
,(221, 'HHS', 6, '0745', null, 216, 208, '怀化市', 3)
,(222, 'YZS', 6, '0746', null, 217, 208, '永州市', 3)
,(223, 'GD', 6, null, '600101', 218, null, '广东省', 2)
,(224, 'CAN', 6, '020', null, 219, 223, '广州市', 3)
,(225, 'JMN', 6, '0750', null, 220, 223, '江门市', 3)
,(226, 'HSC', 6, '0751', null, 221, 223, '韶关市', 3)
,(227, 'HUI', 6, '0752', null, 222, 223, '惠州市', 3)
,(228, 'MXZ', 6, '0753', null, 223, 223, '梅州市', 3)
,(229, 'SWA', 6, '0754', null, 224, 223, '汕头市', 3)
,(230, 'SZX', 6, '0755', null, 225, 223, '深圳市', 3)
,(231, 'ZUH', 6, '0756', null, 226, 223, '珠海市', 3)
,(232, 'FOS', 6, '0757', null, 227, 223, '佛山市', 3)
,(233, 'ZQG', 6, '0758', null, 228, 223, '肇庆市', 3)
,(234, 'ZHA', 6, '0759', null, 229, 223, '湛江市', 3)
,(235, 'ZSN', 6, '0760', null, 230, 223, '中山市', 3)
,(236, 'HEY', 6, '0762', null, 231, 223, '河源市', 3)
,(237, 'QYN', 6, '0763', null, 232, 223, '清远市', 3)
,(238, 'YFS', 6, '0766', null, 233, 223, '云浮市', 3)
,(239, 'CZY', 6, '0768', null, 234, 223, '潮州市', 3)
,(240, 'DGG', 6, '0769', null, 235, 223, '东莞市', 3)
,(241, 'SWE', 6, '0660', null, 236, 223, '汕尾市', 3)
,(242, 'YJI', 6, '0662', null, 237, 223, '阳江市', 3)
,(243, 'JIY', 6, '0663', null, 238, 223, '揭阳市', 3)
,(244, 'MMI', 6, '0668', null, 239, 223, '茂名市', 3)
,(245, 'GX', 6, null, '600302', 240, null, '广西壮族自治区', 2)
,(246, 'FAN', 6, '0770', null, 241, 245, '防城港市', 3)
,(247, 'NNG', 6, '0771', null, 242, 245, '南宁崇左', 3)
,(248, 'LZH', 6, '0772', null, 243, 245, '柳州来宾', 3)
,(249, 'KWL', 6, '0773', null, 244, 245, '桂林市', 3)
,(250, 'WUZ', 6, '0774', null, 245, 245, '梧州贺州', 3)
,(251, 'YUL', 6, '0775', null, 246, 245, '玉林贵港', 3)
,(252, 'BSS', 6, '0776', null, 247, 245, '百色市', 3)
,(253, 'QZH', 6, '0777', null, 248, 245, '钦州市', 3)
,(254, 'HCS', 6, '0778', null, 249, 245, '河池市', 3)
,(255, 'BHY', 6, '0779', null, 250, 245, '北海市', 3)
,(256, 'HI', 6, null, '600403', 251, null, '海南省', 2)
,(257, 'HAK', 6, '0898', null, 252, 256, '海南', 3)
,(258, 'SC', 6, null, '600201', 253, null, '四川省', 2)
,(259, 'CTU', 6, '028', null, 254, 258, '成都市', 3)
,(260, 'PZH', 6, '0812', null, 255, 258, '攀枝花市', 3)
,(261, 'ZGS', 6, '0813', null, 256, 258, '自贡市', 3)
,(262, 'MYG', 6, '0816', null, 257, 258, '绵阳市', 3)
,(263, 'NCO', 6, '0817', null, 258, 258, '南充市', 3)
,(264, 'DAZ', 6, '0818', null, 259, 258, '达州市', 3)
,(265, 'SNS', 6, '0825', null, 260, 258, '遂宁市', 3)
,(266, 'GAC', 6, '0826', null, 261, 258, '广安市', 3)
,(267, 'BZS', 6, '0827', null, 262, 258, '巴中市', 3)
,(268, 'LUZ', 6, '0830', null, 263, 258, '泸州市', 3)
,(269, 'YBS', 6, '0831', null, 264, 258, '宜宾市', 3)
,(270, 'NJS', 6, '0832', null, 265, 258, '内江资阳', 3)
,(271, 'LES', 6, '0833', null, 266, 258, '乐山眉山', 3)
,(272, 'LSY', 6, '0834', null, 267, 258, '凉山彝族自治州', 3)
,(273, 'YAS', 6, '0835', null, 268, 258, '雅安市', 3)
,(274, 'GAZ', 6, '0836', null, 269, 258, '甘孜藏族自治州', 3)
,(275, 'ABA', 6, '0837', null, 270, 258, '阿坝藏族羌族自治州', 3)
,(276, 'DEY', 6, '0838', null, 271, 258, '德阳市', 3)
,(277, 'GYC', 6, '0839', null, 272, 258, '广元市', 3)
,(278, 'GZ', 6, null, '600402', 273, null, '贵州省', 2)
,(279, 'KWE', 6, '0851', null, 274, 278, '贵阳市', 3)
,(280, 'ZNY', 6, '0852', null, 275, 278, '遵义市', 3)
,(281, 'ASS', 6, '0853', null, 276, 278, '安顺市', 3)
,(282, 'QNZ', 6, '0854', null, 277, 278, '黔南布依族苗族自治州', 3)
,(283, 'QND', 6, '0855', null, 278, 278, '黔东南苗族侗族自治州', 3)
,(284, 'TRD', 6, '0856', null, 279, 278, '铜仁地区', 3)
,(285, 'BJD', 6, '0857', null, 280, 278, '毕节地区', 3)
,(286, 'LPS', 6, '0858', null, 281, 278, '六盘水市', 3)
,(287, 'QXZ', 6, '0859', null, 282, 278, '黔西南布依族苗族自治州', 3)
,(288, 'YN', 6, null, '600205', 283, null, '云南省', 2)
,(289, 'ZTS', 6, '0870', null, 284, 288, '昭通市', 3)
,(290, 'KMG', 6, '0871', null, 285, 288, '昆明市', 3)
,(291, 'DLZ', 6, '0872', null, 286, 288, '大理白族自治州', 3)
,(292, 'HHZ', 6, '0873', null, 287, 288, '红河哈尼族彝族自治州', 3)
,(293, 'QJS', 6, '0874', null, 288, 288, '曲靖市', 3)
,(294, 'BOS', 6, '0875', null, 289, 288, '保山市', 3)
,(295, 'WSZ', 6, '0876', null, 290, 288, '文山壮族苗族自治州', 3)
,(296, 'YXS', 6, '0877', null, 291, 288, '玉溪市', 3)
,(297, 'CXD', 6, '0878', null, 292, 288, '楚雄彝族自治州', 3)
,(298, 'PRS', 6, '0879', null, 293, 288, '普洱市', 3)
,(299, 'XSB', 6, '0691', null, 294, 288, '西双版纳傣族自治州', 3)
,(300, 'DHG', 6, '0692', null, 295, 288, '德宏傣族景颇族自治州', 3)
,(301, 'LIH', 6, '0883', null, 296, 288, '临沧市', 3)
,(302, 'NUJ', 6, '0886', null, 297, 288, '怒江傈僳族自治州', 3)
,(303, 'DEZ', 6, '0887', null, 298, 288, '迪庆藏族自治州', 3)
,(304, 'LJH', 6, '0888', null, 299, 288, '丽江市', 3)
,(305, 'XZ', 6, null, '600406', 300, null, '西藏自治区', 2)
,(306, 'LXA', 6, '0891', null, 301, 305, '拉萨市', 3)
,(307, 'XID', 6, '0892', null, 302, 305, '日喀则地区', 3)
,(308, 'NYD', 6, '0894', null, 303, 305, '林芝地区', 3)
,(309, 'QAD', 6, '0895', null, 304, 305, '昌都地区', 3)
,(310, 'NAD', 6, '0896', null, 305, 305, '那曲地区', 3)
,(311, 'NGD', 6, '0897', null, 306, 305, '阿里地区', 3)
,(312, 'SND', 6, '0893', null, 307, 305, '山南地区', 3)
,(313, 'SN', 6, null, '600204', 308, null, '陕西省', 2)
,(314, 'SIA', 6, '029', null, 309, 313, '西安市', 3)
,(315, 'XYS', 6, '0910', null, 310, 313, '咸阳市', 3)
,(316, 'YNA', 6, '0911', null, 311, 313, '延安市', 3)
,(317, 'YLN', 6, '0912', null, 312, 313, '榆林市', 3)
,(318, 'WNA', 6, '0913', null, 313, 313, '渭南市', 3)
,(319, 'SLH', 6, '0914', null, 314, 313, '商洛市', 3)
,(320, 'ANK', 6, '0915', null, 315, 313, '安康市', 3)
,(321, 'HZJ', 6, '0916', null, 316, 313, '汉中市', 3)
,(322, 'BJI', 6, '0917', null, 317, 313, '宝鸡市', 3)
,(323, 'TCN', 6, '0919', null, 318, 313, '铜川市', 3)
,(324, 'GS', 6, null, '600401', 319, null, '甘肃省', 2)
,(325, 'LXH', 6, '0930', null, 320, 324, '临夏回族自治州', 3)
,(326, 'LHW', 6, '0931', null, 321, 324, '兰州市', 3)
,(327, 'DNX', 6, '0932', null, 322, 324, '定西市', 3)
,(328, 'PLS', 6, '0933', null, 323, 324, '平凉市', 3)
,(329, 'QYI', 6, '0934', null, 324, 324, '庆阳市', 3)
,(330, 'JCS', 6, '0935', null, 325, 324, '金昌武威', 3)
,(331, 'ZYE', 6, '0936', null, 326, 324, '张掖市', 3)
,(332, 'JYG', 6, '0937', null, 327, 324, '嘉峪关酒泉', 3)
,(333, 'TSU', 6, '0938', null, 328, 324, '天水市', 3)
,(334, 'LGN', 6, '0939', null, 329, 324, '陇南市', 3)
,(335, 'GNZ', 6, '0941', null, 330, 324, '甘南藏族自治州', 3)
,(336, 'BYS', 6, '0943', null, 331, 324, '白银市', 3)
,(337, 'QH', 6, null, '600405', 332, null, '青海省', 2)
,(338, 'XNN', 6, '0971', null, 333, 337, '西宁市', 3)
,(339, 'HDD', 6, '0972', null, 334, 337, '海东地区', 3)
,(340, 'HNZ', 6, '0973', null, 335, 337, '黄南藏族自治州', 3)
,(341, 'HNN', 6, '0974', null, 336, 337, '海南藏族自治州', 3)
,(342, 'YSZ', 6, '0976', null, 337, 337, '玉树藏族自治州', 3)
,(343, 'GOL', 6, '0975', null, 338, 337, '果洛藏族自治州', 3)
,(344, 'HXZ', 6, '0977', null, 339, 337, '海西蒙古族藏族自治州', 3)
,(345, 'HBZ', 6, '0970', null, 340, 337, '海北藏族自治州', 3)
,(346, 'GOS', 6, '0979', null, 341, 337, '格尔木市', 3)
,(347, 'NX', 6, null, '600404', 342, null, '宁夏回族自治区', 2)
,(348, 'INC', 6, '0951', null, 343, 347, '银川市', 3)
,(349, 'SZS', 6, '0952', null, 344, 347, '石嘴山市', 3)
,(350, 'WZS', 6, '0953', null, 345, 347, '吴忠市', 3)
,(351, 'GYN', 6, '0954', null, 346, 347, '固原市', 3)
,(352, 'ZWS', 6, '0955', null, 347, 347, '中卫市', 3)
,(353, 'XJ', 6, null, '600303', 348, null, '新疆维吾尔自治区', 2)
,(354, 'AKD', 6, '0997', null, 349, 353, '阿克苏地区', 3)
,(355, 'ALD', 6, '0906', null, 350, 353, '阿勒泰地区', 3)
,(356, 'CJZ', 6, '0994', null, 351, 353, '昌吉回族自治州', 3)
,(357, 'HMD', 6, '0902', null, 352, 353, '哈密地区', 3)
,(358, 'HOD', 6, '0903', null, 353, 353, '和田地区', 3)
,(359, 'KSI', 6, '0998', null, 354, 353, '喀什地区', 3)
,(360, 'KAR', 6, '0990', null, 355, 353, '克拉玛依市', 3)
,(362, 'URC', 6, '0991', null, 357, 353, '乌鲁木齐市', 3)
,(363, 'SHZ', 6, '0993', null, 358, 353, '石河子市', 3)
,(364, 'BOR', 6, '0909', null, 359, 353, '博尔塔拉蒙古自治州', 3)
,(365, 'BAG', 6, '0996', null, 360, 353, '巴音郭楞蒙古自治州', 3)
,(5, null, null, null, null, 3, null, '上海', 2)
,(4, null, null, null, null, 4, null, '天津', 2)
,(3, null, null, null, null, 5, null, '重庆', 2)
,(2, null, null, null, null, 6, null, '香港', 2)
,(1, null, null, null, null, 7, null, '澳门', 2)
,(361, 'TUD', 6, '0995', null, 356, 353, '吐鲁番地区', 3);

查询各省和其下属所有二级城市：

SELECT id,area_code,sys_connect_by_path(name,'/') AS PATH,LEVEL,connect_by_isleaf FROM prov_cw_area
WHERE LEVEL=2
CONNECT BY PRIOR id=level2_id;