Oracle-递归查询、树操作

已于 2023-11-02 10:11:30 修改
阅读量1.6k 收藏 4
点赞数 1
分类专栏： # Oracle 文章标签： oracle 数据库
于 2023-10-27 16:37:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/FuTian0715/article/details/134060357
版权

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. 语法

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;

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

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;

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

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;

查询结果：
在这里插入图片描述
这是因为父节点的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;

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

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;

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

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

UntifA
关注
专栏目录
Oracle通过递归查询父子兄弟节点方法示例
09-09
主要给大家介绍了关于Oracle如何通过递归查询父子兄弟节点的相关资料，递归查询对各位程序员来说应该都不陌生，文中通过示例代码介绍的非常详细，需要的朋友可以参考借鉴，下面随着小编来一起学习学习吧。
Oracle中的树状查询(递归查询)
08-12
Oracle中的树状查询(递归查询),权限查询，树形结构
Oracle递归查询研究-20240905
最新发布
weixin_43820659的博客
09-05 624
Oracle的递归查询是一种强大的功能，可以用于处理具有层次结构的数据（如组织架构、树形结构等）。递归查询基于CONNECT BY和PRIOR关键字，可以在SQL语句中实现递归的操作。递归查询的起始条件：使用START WITH子句来指定递归查询的起始条件，即从哪个节点开始递归。递归查询的递归条件：使用CONNECT BY PRIOR子句来指定递归查询的递归条件，即如何从一个节点递归到下一个节点。
ORACLE递归查询
萧若寒
12-03 637
oracle递归查询 向下查询 select d.name from T_IN_DEPARTMENT d start with d.id = #{saledept,jdbcType=VARCHAR} connect by prior d.id=d.parentid 向上查询 select d.name from T_IN_DEPARTMENT d start w
Oracle递归查询
hyyyer
01-21 172
创建表 建表语句： 复制代码 CREATE TABLE SC_DISTRICT ( ID NUMBER(10) NOT NULL, PARENT_ID NUMBER(10), NAME VARCHAR2(255 BYTE) NOT NULL ); ALTER TABLE SC_...
Oracle 的树形递归查询
心雨-------王林
05-10 143
一、树型表结构： 节点ID  上级ID  节点名称 二、公式： select 节点ID,节点名称,level from 表 connect by prior 节点ID=上级节点ID start with 上级节点ID=节点值 说明： 1、常见的树形结构为公司组织机构、地区…… 2、求节点ID以上的结构，或以上的结构，将“节点ID=上级节点ID”左右顺序换一下...
Oracle 递归查询
weixin_33694172的博客
05-23 87
start with connect by 层次查询（Hierarchical Queries） 从顶级向下级查询 Select a.areaid,a.name,a.parentid,a.levelcodeFrom prnbsn_area aStart With a.areaid = 3 Connect By Prior a.areaid=a.parentid 该查询会查出所有的重庆子级区...
Oracle树查询及相关函数
CaiHuajiang的专栏
10-29 188
      Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法，我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的 与树查询相关的Oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。       以我做过的一个项目...
递归查询菜单树，支持mysql,oracle
09-28
这里，我们将深入探讨如何使用递归查询来构建菜单树，并特别关注在MySQL和Oracle这两种广泛使用的数据库系统中的实现。 首先，我们要理解什么是递归查询。递归查询是一种在数据库中处理层次数据的方法，它通过自身...
深入sql oracle递归查询
09-10
在SQL中，递归查询是一种强大的工具，尤其在处理层级数据或者树形结构时非常有用。Oracle数据库系统提供了两种主要的递归查询方法：一种是使用`WITH`子句配合`UNION ALL`，另一种是使用`START WITH`和`CONNECT BY`。...
Oracle递归树形结构查询功能
09-09
oracle树状结构查询即层次递归查询，是sql语句经常用到的，在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。这篇文章给大家介绍了Oracle递归树形结构查询功能，需要的朋友参考下
SQL语句-递归查询、分组、统计.pdf
11-10
在Oracle数据库中，我们可以使用`CONNECT BY`子句实现递归查询。 - 1.2.1 递归查询：通过`CONNECT BY`和`PRIOR`关键字，从指定的根节点（`t.orgid = 82`）开始，查找所有直接或间接上级的记录。 - 1.2.2 通过添加`...
【Oracle】Oracle语法之递归查询
低调做人，低调编码，神码都是浮云
07-16 2670
oracle递归查询
oracle 递归查询（树结构）
qq_36407753的博客
04-09 699
oracle 数据库提供递归查询语句： start with connect by prior： create table provincial_city ( parentid varchar2(10), subid varchar2(10)); insert into provincial_city values ( '陕西省', '西安市' ); insert into provincial_city values ( '陕西省', '宝鸡市' ); insert into
Oracle实现递归查询
xuspCSDN的博客
12-28 412
有如下表Tree： 现在要递归查询冶炼（id=2）的所有父节点和子节点： [sql] view plain copy  print? SELECT * FROM tree START WITH id = 2 CONNECT BY PRIOR pid = id -- 递归查询父节点   union   SELECT * FROM tree 
Oracle 10g 中的递归查询(树型查询)
weixin_33778778的博客
10-05 91
一、树型表结构： 节点ID上级ID节点名称 二、公式： select节点ID,节点名称,level from表 connectbyprior节点ID=上级节点ID startwith上级节点ID=节点值 说明： 1、常见的树形结构为公司组织机构、地区…… 2、求节点ID以上的结构，或以上的结构，将“节点ID=上级节点ID”左右顺序换一下即可。 ...
Oracle 树操作、递归查询(select…start with…connect by…prior)
03-28 6141
Oracle 树操作、递归查询(select…start with…connect by…prior) 一、Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是： select … from tablename where 条件1, 可选; connect by 条件2，是连接条件，其中用PRIOR表示上一条记录，...
oracle递归查询上级
05-31
可以使用Oracle的递归查询功能来查询上级。假设你有一个员工表，其中包含员工ID和上级ID两列，你可以使用以下SQL语句来查询某个员工的上级： WITH emp_hierarchy (emp_id, mgr_id, emp_level) AS ( SELECT emp_id, mgr_id, 0 FROM employee WHERE emp_id = :emp_id UNION ALL SELECT e.emp_id, e.mgr_id, eh.emp_level + 1 FROM employee e, emp_hierarchy eh WHERE e.emp_id = eh.mgr_id ) SELECT emp_id, mgr_id, emp_level FROM emp_hierarchy; 其中，:emp_id是要查询的员工ID。这个SQL语句使用了公用表表达式（WITH子句）和递归查询（UNION ALL）。它会从employee表中找到指定的员工记录，然后递归查询该员工的上级，直到查询到顶层领导为止。查询结果会返回每个员工的ID、上级ID以及层级（从0开始）。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值