• ORACLE-递归查询、树操作

    1. 数据准备

    -- 测试数据准备
DROP TABLE untifa_test;

CREATE TABLE untifa_test(
  child_id NUMBER(10) NOT NULL, --子id
  title VARCHAR2(50), --标题
  relation_type VARCHAR(10) --关系,
  parent_id NUMBER(10) --父id
);

insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (1, '父菜单1', 'A', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (1, '父菜单1', 'B', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (1, '父菜单1', 'C', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (2, '父菜单2', 'A', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (2, '父菜单2', 'B', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (2, '父菜单2', 'C', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (3, '父菜单3', 'A', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (3, '父菜单3', 'B', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (3, '父菜单3', 'C', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (4, '父菜单4', 'A', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (4, '父菜单4', 'B', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (4, '父菜单4', 'C', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (5, '父菜单5', 'A', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (5, '父菜单5', 'B', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (5, '父菜单5', 'C', 0);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (6, '一级菜单6', 'A', 1);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (7, '一级菜单7', 'B', 1);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (8, '一级菜单8', 'C', 1);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (9, '一级菜单9', 'A', 2);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (10, '一级菜单10', 'B', 2);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (11, '一级菜单11', 'C', 2);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (12, '一级菜单12', 'A', 3);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (13, '一级菜单13', 'B', 3);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (14, '一级菜单14', 'C', 3);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (15, '一级菜单15', 'A', 4);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (16, '一级菜单16', 'B', 4);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (17, '一级菜单17', 'C', 4);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (18, '一级菜单18', 'A', 5);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (19, '一级菜单19', 'B', 5);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (20, '一级菜单20', 'C', 5);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (21, '二级菜单21', 'A', 6);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (22, '二级菜单22', 'B', 6);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (23, '二级菜单23', 'C', 6);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (24, '二级菜单24', 'A', 7);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (25, '二级菜单25', 'B', 7);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (26, '二级菜单26', 'C', 7);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (27, '二级菜单27', 'A', 8);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (28, '二级菜单28', 'B', 8);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (29, '二级菜单29', 'C', 8);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (30, '二级菜单30', 'A', 9);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (31, '二级菜单31', 'B', 9);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (32, '二级菜单32', 'C', 9);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (33, '二级菜单33', 'A', 10);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (34, '二级菜单34', 'B', 10);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (35, '二级菜单35', 'C', 10);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (36, '二级菜单36', 'A', 11);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (37, '二级菜单37', 'B', 11);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (38, '二级菜单38', 'C', 11);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (39, '二级菜单39', 'A', 12);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (40, '二级菜单40', 'B', 12);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (41, '二级菜单41', 'C', 12);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (42, '二级菜单42', 'A', 13);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (43, '二级菜单43', 'B', 13);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (44, '二级菜单44', 'C', 13);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (45, '二级菜单45', 'A', 14);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (46, '二级菜单46', 'B', 14);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (47, '二级菜单47', 'C', 14);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (48, '二级菜单48', 'A', 15);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (49, '二级菜单49', 'B', 15);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (50, '二级菜单50', 'C', 15);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (51, '二级菜单51', 'A', 16);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (52, '二级菜单52', 'B', 16);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (53, '二级菜单53', 'C', 16);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (54, '二级菜单54', 'A', 17);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (55, '二级菜单55', 'B', 17);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (56, '二级菜单56', 'C', 17);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (57, '二级菜单57', 'A', 18);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (58, '二级菜单58', 'B', 18);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (59, '二级菜单59', 'C', 18);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (60, '二级菜单60', 'A', 19);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (61, '二级菜单61', 'B', 19);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (62, '二级菜单62', 'C', 19);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (63, '二级菜单63', 'A', 20);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (64, '二级菜单64', 'B', 20);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (65, '二级菜单65', 'C', 20);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (66, '三级菜单66', 'A', 21);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (67, '三级菜单67', 'B', 21);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (68, '三级菜单68', 'C', 21);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (69, '三级菜单69', 'A', 22);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (70, '三级菜单70', 'B', 22);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (71, '三级菜单71', 'C', 22);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (72, '三级菜单72', 'A', 23);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (73, '三级菜单73', 'B', 23);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (74, '三级菜单74', 'C', 23);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (75, '三级菜单75', 'A', 24);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (76, '三级菜单76', 'B', 24);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (77, '三级菜单77', 'C', 24);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (78, '三级菜单78', 'A', 25);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (79, '三级菜单79', 'B', 25);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (80, '三级菜单80', 'C', 25);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (81, '三级菜单81', 'A', 26);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (82, '三级菜单82', 'B', 26);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (83, '三级菜单83', 'C', 26);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (84, '三级菜单84', 'A', 27);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (85, '三级菜单85', 'B', 27);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (86, '三级菜单86', 'C', 27);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (87, '三级菜单87', 'A', 28);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (88, '三级菜单88', 'B', 28);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (89, '三级菜单89', 'C', 28);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (90, '三级菜单90', 'A', 29);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (91, '三级菜单91', 'B', 29);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (92, '三级菜单92', 'C', 29);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (93, '三级菜单93', 'A', 30);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (94, '三级菜单94', 'B', 30);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (95, '三级菜单95', 'C', 30);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (96, '三级菜单96', 'A', 31);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (97, '三级菜单97', 'B', 31);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (98, '三级菜单98', 'C', 31);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (99, '三级菜单99', 'A', 32);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (100, '三级菜单100', 'B', 32);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (101, '三级菜单101', 'C', 32);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (102, '三级菜单102', 'A', 33);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (103, '三级菜单103', 'B', 33);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (104, '三级菜单104', 'C', 33);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (105, '三级菜单105', 'A', 34);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (106, '三级菜单106', 'B', 34);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (107, '三级菜单107', 'C', 34);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (108, '三级菜单108', 'A', 35);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (109, '三级菜单109', 'B', 35);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (110, '三级菜单110', 'C', 35);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (111, '三级菜单111', 'A', 36);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (112, '三级菜单112', 'B', 36);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (113, '三级菜单113', 'C', 36);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (114, '三级菜单114', 'A', 37);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (115, '三级菜单115', 'B', 37);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (116, '三级菜单116', 'C', 37);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (117, '三级菜单117', 'A', 38);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (118, '三级菜单118', 'B', 38);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (119, '三级菜单119', 'C', 38);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (120, '三级菜单120', 'A', 39);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (121, '三级菜单121', 'B', 39);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (122, '三级菜单122', 'C', 39);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (123, '三级菜单123', 'A', 40);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (124, '三级菜单124', 'B', 40);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (125, '三级菜单125', 'C', 40);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (126, '三级菜单126', 'A', 41);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (127, '三级菜单127', 'B', 41);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (128, '三级菜单128', 'C', 41);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (129, '三级菜单129', 'A', 42);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (130, '三级菜单130', 'B', 42);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (131, '三级菜单131', 'C', 42);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (132, '三级菜单132', 'A', 43);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (133, '三级菜单133', 'B', 43);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (134, '三级菜单134', 'C', 43);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (135, '三级菜单135', 'A', 44);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (136, '三级菜单136', 'B', 44);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (137, '三级菜单137', 'C', 44);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (138, '三级菜单138', 'A', 45);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (139, '三级菜单139', 'B', 45);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (140, '三级菜单140', 'C', 45);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (141, '三级菜单141', 'A', 46);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (142, '三级菜单142', 'B', 46);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (143, '三级菜单143', 'C', 46);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (144, '三级菜单144', 'A', 47);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (145, '三级菜单145', 'B', 47);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (146, '三级菜单146', 'C', 47);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (147, '三级菜单147', 'A', 48);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (148, '三级菜单148', 'B', 48);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (149, '三级菜单149', 'C', 48);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (150, '三级菜单150', 'A', 49);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (151, '三级菜单151', 'B', 49);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (152, '三级菜单152', 'C', 49);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (153, '三级菜单153', 'A', 50);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (154, '三级菜单154', 'B', 50);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (155, '三级菜单155', 'C', 50);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (156, '三级菜单156', 'A', 51);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (157, '三级菜单157', 'B', 51);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (158, '三级菜单158', 'C', 51);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (159, '三级菜单159', 'A', 52);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (160, '三级菜单160', 'B', 52);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (161, '三级菜单161', 'C', 52);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (162, '三级菜单162', 'A', 53);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (163, '三级菜单163', 'B', 53);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (164, '三级菜单164', 'C', 53);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (165, '三级菜单165', 'A', 54);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (166, '三级菜单166', 'B', 54);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (167, '三级菜单167', 'C', 54);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (168, '三级菜单168', 'A', 55);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (169, '三级菜单169', 'B', 55);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (170, '三级菜单170', 'C', 55);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (171, '三级菜单171', 'A', 56);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (172, '三级菜单172', 'B', 56);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (173, '三级菜单173', 'C', 56);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (174, '三级菜单174', 'A', 57);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (175, '三级菜单175', 'B', 57);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (176, '三级菜单176', 'C', 57);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (177, '三级菜单177', 'A', 58);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (178, '三级菜单178', 'B', 58);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (179, '三级菜单179', 'C', 58);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (180, '三级菜单180', 'A', 59);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (181, '三级菜单181', 'B', 59);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (182, '三级菜单182', 'C', 59);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (183, '三级菜单183', 'A', 60);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (184, '三级菜单184', 'B', 60);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (185, '三级菜单185', 'C', 60);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (186, '三级菜单186', 'A', 61);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (187, '三级菜单187', 'B', 61);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (188, '三级菜单188', 'C', 61);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (189, '三级菜单189', 'A', 62);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (190, '三级菜单190', 'B', 62);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (191, '三级菜单191', 'C', 62);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (192, '三级菜单192', 'A', 63);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (193, '三级菜单193', 'B', 63);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (194, '三级菜单194', 'C', 63);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (195, '三级菜单195', 'A', 64);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (196, '三级菜单196', 'B', 64);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (197, '三级菜单197', 'C', 64);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (198, '三级菜单198', 'A', 65);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (199, '三级菜单199', 'B', 65);
insert into untifa_test (CHILD_ID, TITLE, RELATION_TYPE, PARENT_ID)values (200, '三级菜单200', 'C', 65);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221

    1. 树状关系图

    在这里插入图片描述

    2. 语法

    SELECT colnum FROM tablename WHERE 条件1
      START WITH 条件2
      CONNECT BY [NOCYCLE] 条件3（PRIOR 列名1 = 列名2 | 列名1 = PRIOR 列名2 …）


    WHERE 条件1 过滤条件语句，对树状结构遍历后得到的记录进行过滤。
    START WITH 条件2 限定条件语句，限定根节点，可以将跟节点限制为1个或多个。
    CONNECT BY 条件3（PRIOR 列名1 = 列名2 | 列名1 = PRIOR 列名2） 连接条件语句，其中PRIOR表示的是上一条记录。
    NOCYCLE 参数避免进入死循环


    比如 CONNECT BY PRIOR child_id = parent_id 表示上一条记录的child_id是本条记录的parent_id，即本条记录的父节点是上一条记录的子节点。
    CONNCET BY 说明每行数据是按层次检索，并规定将表中的数据链入树形结构的关系中。
    PRIOR在=的左侧，代表左侧的为父节点，否则右侧为父节点，这决定了遍历树的方向。


    执行顺序

    1. 根据START WITH 条件2 确定要访问的根节点有哪些
    2. 访问此节点
    3. 判断此节点有无未访问的子节点，若有，则转向最左侧的未被访问的子节点，并执行步骤2、步骤3，否则执行步骤4
    4. 若该节点为根节点，则访问完毕，否则执行步骤5
    5. 返回到此节点的父节点，并执行步骤3
    6. 根据where 条件1 过滤最终遍历树后的查询结果

    3. 适用函数

    • LPAD
      LPAD(STR1 =>
      , LEN =>
      , PAD => )
      STR1 准备被填充的字符串，长度不能为空–为空时无效果；
      LEN 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符
      PAD 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格

    • LEVEL
      LEVEL 树状关系的层级，根节点的层级始终为1

    • connect_by_isleaf
      此函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，返回0；反之，如果不包含下级节点，这里返回1

    • sys_connect_by_path(colnum,‘str’)
      此函数将遍历到的路径根据函数中的分隔符，组成一个新的字符串

    • connect_by_iscycle
      须带参数NOCYCLE，当前行中引用了某个父亲节点的内容并在树中出现了循环，如果循环显示"1"，否则就显示"0"

    • WITH tablename AS () SELECT … FROM tablename WHERE …;
      ()中写查询的逻辑，将查询到的结果放入临时表tablename中，再根据条件按需查询

    4. 查询案例

    4.1 案例1

    -- 想要得到从父节点1开始，关系级别是A的所有子节点
SELECT LPAD('#', 2 * (LEVEL - 1) + 1, '-') || t.title,
       LEVEL,
       connect_by_isleaf,
       sys_connect_by_path(t.title || t.relation_type, '/'),
       t.*
  FROM untifa_test t
 START WITH t.child_id = 1
        AND t.relation_type = 'A' -- 限定跟结果集
CONNECT BY NOCYCLE PRIOR t.child_id = t.parent_id
       AND t.relation_type = 'A' -- 限定子节点结果集
 ORDER BY LEVEL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    查询结果：
    在这里插入图片描述

    4.2 案例2

    -- 想要得到从子节点66开始，关系级别是A的所有父节点
SELECT LPAD('#', 2 * (LEVEL - 1) + 1, '-') || t.title,
       LEVEL,
       connect_by_isleaf,
       sys_connect_by_path(t.title || t.relation_type, '/'),
       t.*
  FROM untifa_test t
 START WITH t.child_id = 66
        AND t.relation_type = 'A' -- 限定跟结果集
CONNECT BY NOCYCLE t.child_id = PRIOR t.parent_id
       AND t.relation_type = 'A' -- 限定子节点结果集
 ORDER BY LEVEL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    查询结果：
    在这里插入图片描述

    4.3 案例3

    -- 将父节点1A的parent_id改为1
UPDATE untifa_test t SET t.parent_id = '1' WHERE t.child_id = '1' AND t.relation_type = 'A';
-- 再查询从父节点1开始，关系级别是A的所有子节点
SELECT LPAD('#', 2 * (LEVEL - 1) + 1, '-') || t.title,
       LEVEL,
       connect_by_isleaf,
       sys_connect_by_path(t.title || t.relation_type, '/'),
       t.*
  FROM untifa_test t
 START WITH t.child_id = 1
        AND t.relation_type = 'A'
CONNECT BY PRIOR t.child_id = t.parent_id
       AND t.relation_type = 'A'
 ORDER BY LEVEL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    查询结果：
    在这里插入图片描述
    这是因为父节点的child_id与parent_id都为1，形成了死循环，所有我们要加入NOCYCLE来规避死循环

    SELECT LPAD('#', 2 * (LEVEL - 1) + 1, '-') || t.title,
       LEVEL,
       connect_by_isleaf,
       sys_connect_by_path(t.title || t.relation_type, '/'),
       t.*
  FROM untifa_test t
 START WITH t.child_id = 1
        AND t.relation_type = 'A'
CONNECT BY NOCYCLE PRIOR t.child_id = t.parent_id
       AND t.relation_type = 'A'
 ORDER BY LEVEL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    查询结果：
    在这里插入图片描述

    4.4 案例4

    -- 想要得到从子节点66开始，关系级别是A的所有父节点
-- 同案例3一样 CONNECT BY 后面不加 NOCYCLE 则会陷入死循环报错
SELECT LPAD('#', 2 * (LEVEL - 1) + 1, '-') || t.title,
       LEVEL,
       connect_by_isleaf,
       sys_connect_by_path(t.title || t.relation_type, '/'),
       t.*
  FROM untifa_test t
 START WITH t.child_id = 66
        AND t.relation_type = 'A'
CONNECT BY NOCYCLE t.child_id = PRIOR t.parent_id
       AND t.relation_type = 'A'
 ORDER BY LEVEL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    查询结果：
    在这里插入图片描述

    对比案例4与案例2的结果不难发现，在将child_id为1的parent_id从0改为1后，查询结果少了一条，还没弄清楚原因，猜测是因为死循环，过滤掉了parend_id=1和child_id=1的记录，但是不明白为什么案例3没有过滤掉parend_id=1和child_id=1的记录。

  • 相关阅读:
    Flink 内存模型
    flink 技术总结待续
    邮箱被攻击，后悔没有早早的做好安全措施
    Android Studio实现简易计算器（带横竖屏，深色浅色模式，更该按钮颜色，selector，style的使用）
    NFC技术的定义通信方式
    长安链可验证数据库，保证数据完整性的可信存证方案
    c# iot .net 6 树莓派 读取光敏传感器四针+模拟转数字模块 代码实例
    LLC 三相移相PWM产生原理分析
    Android 注解与注解处理器简述
    告别登录烦恼，WPS免登录修改器体验！（如何实现不登录使用WPS）
  • 原文地址：https://blog.csdn.net/FuTian0715/article/details/134060357