层次查询的函数的两种方式:
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;