【Oracle】关于oracle递归查询

已于 2024-12-19 15:41:14 修改
阅读量877 收藏 5
点赞数 4
分类专栏： # 数据库 开发专题 文章标签： oracle 数据库
于 2024-12-19 15:37:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42647609/article/details/144579238
版权

关于oracle递归查询

功能需求

  1. 有一颗树结构数据，以父节点方式存在数据库中，现在已知最小节点，通过递归查询，查询这个最小节点的上级所有节点；
  2. 查询资源树下的所有文件，查询文件时，同时查询文件所在的所在节点及其父级所有节点名称，并已路径的方式进行展示；且文件排序有如下规则：文件先依照资源树节点排序，同一节点下多个文件，按照文件名称中文拼音排序。

数据准备

--资源树信息表
CREATE TABLE "TEST"."TREE_INFO" (
  "TREE_NODE_ID" VARCHAR2(64 BYTE) NOT NULL,
  "TREE_ID" VARCHAR2(64 BYTE) NOT NULL,
  "TREE_NODE_NAME" VARCHAR2(500 BYTE),
  "PARENT_NODE_ID" VARCHAR2(64 BYTE),
  "TREE_NODE_ORDER" NUMBER(8,0)
);
COMMENT ON COLUMN "TEST"."TREE_INFO"."TREE_NODE_ID" IS '节点ID';
COMMENT ON COLUMN "TEST"."TREE_INFO"."TREE_ID" IS '资源树ID';
COMMENT ON COLUMN "TEST"."TREE_INFO"."TREE_NODE_NAME" IS '节点名称';
COMMENT ON COLUMN "TEST"."TREE_INFO"."PARENT_NODE_ID" IS '父节点ID';
COMMENT ON COLUMN "TEST"."TREE_INFO"."TREE_NODE_ORDER" IS '节点顺序';
COMMENT ON TABLE "TEST"."TREE_INFO" IS '资源树信息表';
ALTER TABLE "TEST"."TREE_INFO" ADD CONSTRAINT "PK_TREE_INFO" PRIMARY KEY ("TREE_NODE_ID");

INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('7e0a28a894b1436c821b3fdfbabb9efb', 'de78b79591a4411c94724f959312dcec', '资源树', NULL,  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('6b58df4b298347afbf5e6afcc6682116', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '1');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('49d7657173a3401db21bd27c0d7ae071', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '1');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('de36cb89c9504f0e9fa6515f77a9d5dd', 'de78b79591a4411c94724f959312dcec', '节点1', '7e0a28a894b1436c821b3fdfbabb9efb',  '1');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('a93d87efc64a4fc4a19503e1973a0a01', 'de78b79591a4411c94724f959312dcec', '节点n', '9dd50f998df94f05949b57fb27359a35',  '1');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('fdc153e2028d440bbee740502ab5cdf6', 'de78b79591a4411c94724f959312dcec', '节点n', '9dd50f998df94f05949b57fb27359a35',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('012c2e7a989041f999482ea2c537fe7a', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('93a665f0e7b04fdeb609d769ed3e4a22', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('c835819de0094d60b9b43313d3435ce5', 'de78b79591a4411c94724f959312dcec', '节点n', 'aa72e0891c50445896cdde4743dddba8',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('c8a00fe298154855a0ad63197dcac4e5', 'de78b79591a4411c94724f959312dcec', '节点1', '7e0a28a894b1436c821b3fdfbabb9efb',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('9dd50f998df94f05949b57fb27359a35', 'de78b79591a4411c94724f959312dcec', '节点n', 'aa72e0891c50445896cdde4743dddba8',  '2');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('9f4c2e657def4937b684271cd2f4fa51', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('a0286fdd78f94ad4bb5fc42d996564c0', 'de78b79591a4411c94724f959312dcec', '节点n', '9dd50f998df94f05949b57fb27359a35',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('aa72e0891c50445896cdde4743dddba8', 'de78b79591a4411c94724f959312dcec', '节点1', '7e0a28a894b1436c821b3fdfbabb9efb',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('e9d2469b75f0401791e32e23fdbd1ed9', 'de78b79591a4411c94724f959312dcec', '节点n', '9dd50f998df94f05949b57fb27359a35',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('85718f2dfeb04af1bc4d9db65ab79369', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('913706fc485a46eb9103ec4ef9b25007', 'de78b79591a4411c94724f959312dcec', '节点n', 'aa72e0891c50445896cdde4743dddba8',  '3');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('5aefe513a7ee47b3a65eb91034bccb28', 'de78b79591a4411c94724f959312dcec', '节点n', 'aa72e0891c50445896cdde4743dddba8',  '4');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('77b71250c402404a9dddce115637d372', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '4');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('23fe8a71ae9048baaebaada5b648b28c', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '4');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('1195a50d53a34bc6a663cbfbb386d8fd', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '5');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('8ded98efd18c45958d4ca814c1d633c5', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '5');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('f143742974154f93aa1e2ae97125ffa2', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '5');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('0ca82465970f45a6a7b47616dc6010f9', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '6');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('313b0275ca34460aa38d2365e57b3a30', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '6');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('5d0e4bc493fc47d1bd1841f8f0095582', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '7');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('f7f7b7bf4687460dbf03558c0f54603d', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '7');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('e49f6a852ed24c5aab20230091ddf7f8', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '8');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('beb500f032d246a2826368ae9125e78c', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '9');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('ac4b0c9c5dda4653b87e443c4819f97e', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '10');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('4eaa8bca784946b48f1ddd772d82e589', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '10');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('f7671d1a5ec74850b57a6a6911ceaeba', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '11');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('0cc88e00200b4bf09f078183384f5b7c', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '11');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('315c18e7c0334a0984eebdc7e75b13b1', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '11');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('2e32caf86d8441f590889b7b2e1ed50e', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '12');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('bca27e431c144df79ccb888ca4df2552', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '13');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('2328d40a355e4134b58a0186e73d1040', 'de78b79591a4411c94724f959312dcec', '节点n', 'aa72e0891c50445896cdde4743dddba8',  '14');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('937923b3699e4488be902c6415e72391', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '14');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('21709a072c2145e7afb0cb78d9cb1b53', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '15');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('67df5d2f4b7a4a0e9825a773f7b0a52f', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '110');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('520ba6a61c47422baf6a4b1c509528ca', 'de78b79591a4411c94724f959312dcec', '节点n', '1195a50d53a34bc6a663cbfbb386d8fd',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('9b2cf444104d4d1fb45cb2df0bb9c2fd', 'de78b79591a4411c94724f959312dcec', '节点n', '1195a50d53a34bc6a663cbfbb386d8fd',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('7ca865c30d144bfa93b85ae1f09acac9', 'de78b79591a4411c94724f959312dcec', '节点n', 'fdc153e2028d440bbee740502ab5cdf6',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('7b8b52abd83e46a6a5cdcbe0de80b832', 'de78b79591a4411c94724f959312dcec', '节点n', '1195a50d53a34bc6a663cbfbb386d8fd',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('5650b1abfb9c443ba75550945e22cf3e', 'de78b79591a4411c94724f959312dcec', '节点n', 'fdc153e2028d440bbee740502ab5cdf6',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('664df0aa82374c6eb153f717fdd76b42', 'de78b79591a4411c94724f959312dcec', '节点n', 'fdc153e2028d440bbee740502ab5cdf6',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('13375e831c1c44789d5ff55c890f53bf', 'de78b79591a4411c94724f959312dcec', '节点n', 'de36cb89c9504f0e9fa6515f77a9d5dd',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('b60dfa773904431ca643eb4a77da413f', 'de78b79591a4411c94724f959312dcec', '节点n', 'fdc153e2028d440bbee740502ab5cdf6',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('2f2b65780d884ff78ea629a761a03806', 'de78b79591a4411c94724f959312dcec', '节点n', 'fdc153e2028d440bbee740502ab5cdf6',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('96fb6b57c2e44811bd6505ef43e104a2', 'de78b79591a4411c94724f959312dcec', '节点n', 'c8a00fe298154855a0ad63197dcac4e5',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('29c03d409bd04c0bbf7ffd8683efeeac', 'de78b79591a4411c94724f959312dcec', '节点n', '1195a50d53a34bc6a663cbfbb386d8fd',  '0');
INSERT INTO "TEST"."TREE_INFO"("TREE_NODE_ID", "TREE_ID", "TREE_NODE_NAME", "PARENT_NODE_ID", "TREE_NODE_ORDER") VALUES ('068e3e6e8f694712ae15db58f44e5e75', 'de78b79591a4411c94724f959312dcec', '节点n', '1195a50d53a34bc6a663cbfbb386d8fd',  '0');




--文件信息表
CREATE TABLE "TEST"."FILE_INFO" (
  "FILE_ID" VARCHAR2(64 BYTE) NOT NULL,
  "TREE_NODE_ID" VARCHAR2(64 BYTE) NOT NULL,
  "FILE_NAME" VARCHAR2(500 BYTE),
  "FILE_ORDER" NUMBER(8,0)
);
COMMENT ON COLUMN "TEST"."FILE_INFO"."FILE_ID" IS '文件主键';
COMMENT ON COLUMN "TEST"."FILE_INFO"."TREE_NODE_ID" IS '材料节点ID';
COMMENT ON COLUMN "TEST"."FILE_INFO"."FILE_NAME" IS '电子文件名称';
COMMENT ON COLUMN "TEST"."FILE_INFO"."FILE_ORDER" IS '文件排序';
COMMENT ON TABLE "TEST"."FILE_INFO" IS '电子文件基本信息表';
ALTER TABLE "TEST"."FILE_INFO" ADD CONSTRAINT "PK_FILE_INFO" PRIMARY KEY ("FILE_ID");


INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ac12d32554ed4f9c984503c554d7ec3e', '93a665f0e7b04fdeb609d769ed3e4a22', '文件1', '2');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5aacb03eeefe45fcab6ba8525d1faa35', '93a665f0e7b04fdeb609d769ed3e4a22', '文件2', '2');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5e18c694ac0443a38a0c8c6278f14ce5', '93a665f0e7b04fdeb609d769ed3e4a22', '文件3', '2');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('aca7744336a646c7bbfc953e1509e1d7', 'beb500f032d246a2826368ae9125e78c', '文件4', '9');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('df0d84f76d624bf8a08f25563348a135', '93a665f0e7b04fdeb609d769ed3e4a22', '文件5', '2');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2b5b400010144ab9a66eeaf61bacbbe2', 'beb500f032d246a2826368ae9125e78c', '文件6', '9');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('f581d8e05ef34e8da89c74eea88a704a', 'f7f7b7bf4687460dbf03558c0f54603d', '文件7', '7');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('9b5897347ba0401da06e7f92a6549f85', 'ac4b0c9c5dda4653b87e443c4819f97e', '文件8', '10');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ad23a6d35ff244518e2f139a7f05218f', 'f7671d1a5ec74850b57a6a6911ceaeba', '文件9', '11');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3e8b517e7671415eabdaf4c9b8c18c1c', '313b0275ca34460aa38d2365e57b3a30', '文件10', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('090e0c5ac0144c57ae96b50f470f7641', '313b0275ca34460aa38d2365e57b3a30', '文件11', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e36173dce0204bef84a34e156756145c', '313b0275ca34460aa38d2365e57b3a30', '文件12', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('543a4403bdab43e6aef2664940e59782', '313b0275ca34460aa38d2365e57b3a30', '文件13', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ceca61a6895341b8a602f19bea5332b1', '8ded98efd18c45958d4ca814c1d633c5', '文件14', '5');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('a5f75e4681304d28b0c60cc5f8df2404', '13375e831c1c44789d5ff55c890f53bf', '文件15', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('75c04d269e4d45c2a1779b111eb3bea8', '96fb6b57c2e44811bd6505ef43e104a2', '文件16', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4bb1dedf6e084a8f90cc85f4749fd6c4', '9f4c2e657def4937b684271cd2f4fa51', '文件17', '3');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4ee70eefa24348d594b947baf1fd40f6', '77b71250c402404a9dddce115637d372', '文件18', '4');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4840f3820e694044b566b24e30102dbe', '6b58df4b298347afbf5e6afcc6682116', '文件19', '1');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('84c0ceadd8494bbe9755470d63419777', '313b0275ca34460aa38d2365e57b3a30', '文件20', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4f1147394595469ba566c7a3dfe6b97d', '313b0275ca34460aa38d2365e57b3a30', '文件21', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('07ab318a02454ded86c3992cd5f105f1', '313b0275ca34460aa38d2365e57b3a30', '文件22', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('b267cc528acf4b7684e2c119f9ab35d8', '068e3e6e8f694712ae15db58f44e5e75', '文件23', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('57abac77cbb54bfd840852495296c5ad', '068e3e6e8f694712ae15db58f44e5e75', '文件24', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3681fb4cf7444a8f994e417a7dab0870', '068e3e6e8f694712ae15db58f44e5e75', '文件25', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('427eb45943ea443890956191c1e0ba70', '068e3e6e8f694712ae15db58f44e5e75', '文件26', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('aa653c3a31d0483eb53e688a0a04ea3f', '068e3e6e8f694712ae15db58f44e5e75', '文件27', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e7a2a32fc8f546769a16175063b8695e', '068e3e6e8f694712ae15db58f44e5e75', '文件28', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3096e86119ef4c05a9654413d10319bd', '068e3e6e8f694712ae15db58f44e5e75', '文件29', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4e1d913f0d154fc295d8fcca484979b6', '068e3e6e8f694712ae15db58f44e5e75', '文件30', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('63dca4fd3e0a4fe7b588627a79675ced', '068e3e6e8f694712ae15db58f44e5e75', '文件31', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('37a33b5498cc4f6984393acb51e78384', '068e3e6e8f694712ae15db58f44e5e75', '文件32', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d918c0ccf8e34a1f82b94d84a82f7bf5', '068e3e6e8f694712ae15db58f44e5e75', '文件33', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d1c60af1a3454496b7a354d34bef7062', '068e3e6e8f694712ae15db58f44e5e75', '文件34', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('1f6bb5294d014c6390addfabcb58ea53', '068e3e6e8f694712ae15db58f44e5e75', '文件35', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('839c24c0ab754e8b8a4cb3a4a336dd80', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件36', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('294d4029351d44b6902792b00f1396f2', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件37', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('bdb8907f3fe1440eb5aa2a3cfde01080', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件38', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('91ea3a8acfdd4a8eac28c616f36171e4', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件39', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('b23c4eccbf9344e0a0e387b4d51e2898', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件40', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('bbb9466419864a64aecbc1d040a811a2', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件41', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('93f8a6f9968347ef9fc3c533644bfd7a', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件42', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e5d3d7b65fef451a9ded7ca00c860b92', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件43', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2ffa3fd0131d4dcaa74387b992102a12', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件44', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('9e77cbbf2e784f10a57e20b8626a9537', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件45', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2491b087adba47ffa05bf4539c2e5852', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件46', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3f2559a75749431494811436daf4f6e5', '29c03d409bd04c0bbf7ffd8683efeeac', '文件47', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('525ee673ec1745e0b315fee509e54221', '29c03d409bd04c0bbf7ffd8683efeeac', '文件48', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3468a15e45f24e84a0a2058ebab549ec', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件49', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('21f49a7af8f14976b366d1290bba0968', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件50', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('363b1f3fa2334e6e96e72d3cb4f00af8', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件51', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('af54bb3a44124e6492b73d7a85f2c37e', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件52', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('bd09804c2057468fa10e0b9f279b49cd', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件53', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('b2526a836f4e4dee960328a8f44a5ba9', '29c03d409bd04c0bbf7ffd8683efeeac', '文件54', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('aa55faff013549e7b184f8adb8371876', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件55', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ceca6d5480494125bc1ea86008b3bde5', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件56', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('91728485b6134db4b1eb4812fa252e51', '29c03d409bd04c0bbf7ffd8683efeeac', '文件57', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('a39f6b376a5c44bf8966fb3be70fcd6c', '29c03d409bd04c0bbf7ffd8683efeeac', '文件58', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('7c0ecc3628564ea09d777851b86a9d67', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件59', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('988552bb0ed940c9887a1b40fc4b1300', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件60', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2b8bf4ab8be44770b438d556a6e93d7c', '7b8b52abd83e46a6a5cdcbe0de80b832', '文件61', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('0582c1d3dcde4ef286b63c83397186ca', '29c03d409bd04c0bbf7ffd8683efeeac', '文件62', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('0010a566f1d74093bb19a71bf54711e1', '29c03d409bd04c0bbf7ffd8683efeeac', '文件63', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('eaddf03cbec444f9a2f3097fd9208f20', '29c03d409bd04c0bbf7ffd8683efeeac', '文件64', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2747e68e1adc4708984bbccb153b2ad9', '29c03d409bd04c0bbf7ffd8683efeeac', '文件65', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('137b3b8469be4f5291ee5dc558f9cefe', '520ba6a61c47422baf6a4b1c509528ca', '文件66', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('c3aba393df4545cc81f853eddfe685f9', '520ba6a61c47422baf6a4b1c509528ca', '文件67', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ada8b5e132db4a469029dc37b1e56ff7', '520ba6a61c47422baf6a4b1c509528ca', '文件68', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('ff766a8b3cfb4b978eb1c9da5995091c', '520ba6a61c47422baf6a4b1c509528ca', '文件69', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('773c92d6088448deb0843b9c0143fdc7', '520ba6a61c47422baf6a4b1c509528ca', '文件70', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('61860d1c4f4645febab13c93e6951a2a', '9b2cf444104d4d1fb45cb2df0bb9c2fd', '文件71', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('9045002e9354414d969a610886f5dd0b', 'ac4b0c9c5dda4653b87e443c4819f97e', '文件72', '10');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('733bc307134145a794e27cef8d955390', 'ac4b0c9c5dda4653b87e443c4819f97e', '文件73', '10');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('76e018c8ec124e9b9c8bc88aedea85ce', '6b58df4b298347afbf5e6afcc6682116', '文件74', '1');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('53313ba28de44f988482b497168ebf4f', 'ac4b0c9c5dda4653b87e443c4819f97e', '文件75', '10');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('f64b7ba83ab14b298db08c770e05f7f3', '49d7657173a3401db21bd27c0d7ae071', '文件76', '1');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d2412c24c1824793a11c42e7ef1f4982', '0cc88e00200b4bf09f078183384f5b7c', '文件77', '11');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d4b7a62b284743f7998ef1f8d5c28b8d', '67df5d2f4b7a4a0e9825a773f7b0a52f', '文件78', '110');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('984151ef45b9414899d4cb3ef1cf9779', '0ca82465970f45a6a7b47616dc6010f9', '文件79', '6');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('58f38f8ae24344279821deb62ce3c13d', 'f143742974154f93aa1e2ae97125ffa2', '文件80', '5');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('8065c752d4514c1d8475bd0eb5cf0bf0', '4eaa8bca784946b48f1ddd772d82e589', '文件81', '10');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e522972a1ee747f990f43eba2ff4d9ca', '315c18e7c0334a0984eebdc7e75b13b1', '文件82', '11');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('638f02b12a53478e94454ba4c1ab6fe7', '913706fc485a46eb9103ec4ef9b25007', '文件83', '3');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('180d79d4371d4cb2b4c9c26f7aa2a5ba', '5aefe513a7ee47b3a65eb91034bccb28', '文件84', '4');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('fa3606f7de494bd2a34048c7d190a01e', '664df0aa82374c6eb153f717fdd76b42', '文件85', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('50bed8a654724a498553f00aec62efa8', '664df0aa82374c6eb153f717fdd76b42', '文件86', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('07a055af1957452ebca9336ccac668a8', '664df0aa82374c6eb153f717fdd76b42', '文件87', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('fa156fbd7cb34d579404a003af2a9129', '664df0aa82374c6eb153f717fdd76b42', '文件88', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('52c072f3054e47c5bc461cf8cab401bf', '664df0aa82374c6eb153f717fdd76b42', '文件89', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('09baa6bbbc8c4de786869a77c6f42514', '664df0aa82374c6eb153f717fdd76b42', '文件90', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('40ee547407a3484cbf3701cfe4470a2e', '664df0aa82374c6eb153f717fdd76b42', '文件91', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d0af56e3e9564ff3a63fa0a6cba1e6b7', '664df0aa82374c6eb153f717fdd76b42', '文件92', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('233345cb7a0d4369afb73e8a13a6c545', '664df0aa82374c6eb153f717fdd76b42', '文件93', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('1933333a018940b2b98982817c08e5f5', '664df0aa82374c6eb153f717fdd76b42', '文件94', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('384dc7b58b2c4ccba3346ede81ae284a', '2f2b65780d884ff78ea629a761a03806', '文件95', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('10321b1facad4b279c0a3543cedccd4e', '2f2b65780d884ff78ea629a761a03806', '文件96', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('b1f1dc860930486cbe7a05226ba1ec8e', '2f2b65780d884ff78ea629a761a03806', '文件97', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('41af7c5c20844979a2ab3a5b515e0290', '2f2b65780d884ff78ea629a761a03806', '文件98', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('1eda0c96797a44cfb0dc39a19f5caac1', 'a93d87efc64a4fc4a19503e1973a0a01', '文件99', '1');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4beb30bd40814583bf98f7cb0bde6df9', '2f2b65780d884ff78ea629a761a03806', '文件100', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('585bb5be18164ecbaa06fd2028929ef2', '5650b1abfb9c443ba75550945e22cf3e', '文件101', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5c4455aeb1994410a211bb0f68a49083', '5650b1abfb9c443ba75550945e22cf3e', '文件102', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('32f6a8e7b93245e8926bb46fa995e347', '5650b1abfb9c443ba75550945e22cf3e', '文件103', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5089920151e24427a57ffd4f2df0dd17', '5650b1abfb9c443ba75550945e22cf3e', '文件104', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('2fdd6a27899547f6a1e54e59251468bb', '5650b1abfb9c443ba75550945e22cf3e', '文件105', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('8e45f4712b7e4ffb8534f16ac43a77a2', '5650b1abfb9c443ba75550945e22cf3e', '文件106', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5d3b85c58c1a4e81949d2ebd10fb1bd8', '5650b1abfb9c443ba75550945e22cf3e', '文件107', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('4cb0b3674d8b4aaa83fb35a13fb8e53b', '5650b1abfb9c443ba75550945e22cf3e', '文件108', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('326eb8d4d4be4eab81929194d6261179', '5650b1abfb9c443ba75550945e22cf3e', '文件109', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('a5f7cfe961664480a3313d848b785556', '5650b1abfb9c443ba75550945e22cf3e', '文件110', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('1d3697c87be94d6098e6a6c275d02821', '5650b1abfb9c443ba75550945e22cf3e', '文件111', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('8ed4f6087a644169818cb99f540783ca', '5650b1abfb9c443ba75550945e22cf3e', '文件112', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('99a1b9eceefa49d48e5dd56c31545aca', '5650b1abfb9c443ba75550945e22cf3e', '文件113', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d683391b86174e8e963e7c154d97532e', '7ca865c30d144bfa93b85ae1f09acac9', '文件114', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('6ce6674613364fb1983de2a1e66babc7', '7ca865c30d144bfa93b85ae1f09acac9', '文件115', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('bc2b07bceb3e45c197371e64864c890e', '7ca865c30d144bfa93b85ae1f09acac9', '文件116', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('5ac64897244847c1b5a81b766036cec6', '7ca865c30d144bfa93b85ae1f09acac9', '文件117', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('8f3a289c244f48ecb48184ec8225d7b2', '7ca865c30d144bfa93b85ae1f09acac9', '文件118', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('b1557ea461f84c9a9a7a07fe84d4bce1', '7ca865c30d144bfa93b85ae1f09acac9', '文件119', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('596c25081cc747ae87e6b8bf644ef45c', '7ca865c30d144bfa93b85ae1f09acac9', '文件120', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e556128407b84fc8af2489a70e657151', '7ca865c30d144bfa93b85ae1f09acac9', '文件121', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('33908b9fdbb646039dedb8bb23dbb8d8', '7ca865c30d144bfa93b85ae1f09acac9', '文件122', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('229e792b2e8f44eea07ef3cda948dc84', '7ca865c30d144bfa93b85ae1f09acac9', '文件123', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('fae91ad9186449c6beb3e25c74f525cb', '7ca865c30d144bfa93b85ae1f09acac9', '文件124', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('301024b5446140e88fe912ad638a76bf', '7ca865c30d144bfa93b85ae1f09acac9', '文件125', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('c734efe57e954636a71825fa110fb55b', 'c835819de0094d60b9b43313d3435ce5', '文件126', '2');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('3d9923dd98a14673a2f5567c047f5eda', 'b60dfa773904431ca643eb4a77da413f', '文件127', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d43f5ed9f102483ebb2ac8ffe8f120b3', 'b60dfa773904431ca643eb4a77da413f', '文件128', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('295327a4ffcf460a84e71d1e62711740', 'b60dfa773904431ca643eb4a77da413f', '文件129', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('88e4d87dad174439b00fed08e2a0d3ca', 'b60dfa773904431ca643eb4a77da413f', '文件130', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('e3973f272f2c4160abecd595228c4706', 'b60dfa773904431ca643eb4a77da413f', '文件131', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('9dae4b5a440d421b90c9d5fedf70d1c9', 'b60dfa773904431ca643eb4a77da413f', '文件132', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d5afccdbfec743a49fb551ace05bc3c7', 'b60dfa773904431ca643eb4a77da413f', '文件133', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('d2bde71c3b8c42069f47be95fd6a1050', 'b60dfa773904431ca643eb4a77da413f', '文件134', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('77aea15bff374784ae8127436d321f23', 'b60dfa773904431ca643eb4a77da413f', '文件135', '0');
INSERT INTO "TEST"."FILE_INFO"("FILE_ID", "TREE_NODE_ID", "FILE_NAME", "FILE_ORDER") VALUES ('a3b75e687d904cb385eff18f8611c908', 'bca27e431c144df79ccb888ca4df2552', '文件136', '13');

查询分析及查询结果

需求1查询

查询比较简单，只需要使用oracle递归查询即可，用法如下：

SELECT 列名 FROM 表名 START WITH 条件 CONNECT BY PRIOR 列名 = 值

由此，得到查询sql如下：

SELECT
	s.* 
FROM
	TREE_INFO s 
WHERE
	s.TREE_ID = 'de78b79591a4411c94724f959312dcec' START WITH s.TREE_NODE_ID = '1195a50d53a34bc6a663cbfbb386d8fd' CONNECT BY PRIOR s.PARENT_NODE_ID = s.TREE_NODE_ID

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

需求2查询

这个查询相对来说比较复杂了，我们需要查询文件信息，同时利用子查询，查询文件所在资源树最小子节点及其父节点的所有节点名称，并利用字符拼接函数将节点名称分组排列展示：

START WITH 条件 CONNECT BY PRIOR 列名 = 值

LISTAGG(measure_expr, delimiter) WITHIN GROUP (ORDER BY order_by_clause)

由此，得到sql如下：

SELECT
	safi.*,
	(SELECT LISTAGG ( TREE_NODE_NAME, '  /  ' ) WITHIN GROUP ( ORDER BY LEVEL DESC ) AS NODE_PATH_NAME 
FROM TREE_INFO a WHERE a.TREE_ID = saft.TREE_ID START WITH a.TREE_NODE_ID = saft.TREE_NODE_ID CONNECT BY PRIOR a.PARENT_NODE_ID = a.TREE_NODE_ID ) NODE_PATH_NAME
	,(SELECT LISTAGG ( TREE_NODE_ORDER, '' ) WITHIN GROUP ( ORDER BY LEVEL DESC ) AS TREE_NODE_ORDER 
	FROM TREE_INFO a WHERE a.TREE_ID = saft.TREE_ID START WITH a.TREE_NODE_ID = saft.TREE_NODE_ID CONNECT BY PRIOR a.PARENT_NODE_ID = a.TREE_NODE_ID ) TREE_NODE_ORDER 
FROM FILE_INFO safi LEFT JOIN TREE_INFO saft ON saft.TREE_NODE_ID = safi.TREE_NODE_ID 
WHERE saft.TREE_ID = 'de78b79591a4411c94724f959312dcec' 
ORDER BY TREE_NODE_ORDER,NLSSORT( safi.FILE_NAME, 'NLS_SORT=SCHINESE_PINYIN_M' ) ASC

查询结果如下：
在这里插入图片描述
以上查询，在百万级别的查询下，响应时间在3秒左右。

总结及拓展

在进行复杂的递归查询时，可以通过自定义函数、存储过程、视图等多种方法来满足需求，在mysql和postgreSql等其他数据库中，都有类似的解决办法和解决方案。比如我们使用postgreSql来满足上面需求1的查询可以这么写，如下：

WITH RECURSIVE tree AS (
    SELECT "TREE_NODE_ID","TREE_ID","TREE_NODE_NAME", "PARENT_NODE_ID","TREE_NODE_ORDER"
    FROM "TREE_INFO"
    WHERE "TREE_NODE_ID" = '1195a50d53a34bc6a663cbfbb386d8fd' 
    UNION ALL
    SELECT e."TREE_NODE_ID",e."TREE_ID",e."TREE_NODE_NAME", e."PARENT_NODE_ID",e."TREE_NODE_ORDER"
    FROM "TREE_INFO" e
    INNER JOIN tree s ON e."TREE_NODE_ID" = s."PARENT_NODE_ID"
)
SELECT * FROM tree;
Oracle数据库数据编程SQL＜递归函数详解＞
2302_81846649的博客
03-30 1083
递归函数是一种在函数体内直接或间接调用自身的函数。这种函数通过将复杂问题分解为更小的相同问题来解决特定类型的编程任务。 递归函数是解决分治问题和层次结构处理的强大工具，但需要谨慎设计以避免性能问题和栈溢出错误。在Oracle环境中，对于深度递归问题，有时使用迭代方法或递归WITH子句可能是更好的选择。
Oracle中递归查询（START WITH……CONNECT BY……）
时代各有不同，青春一脉相承。
06-19 2790
在Oracle中START WITH……CONNECT BY……一般用来查找存在父子关系的数据，也就是树形结构的数据。start with设置起点，用来限制第一层的数据，或者叫根节点数据；connect by用来指明在查找数据时以怎样的一种关系去查找；prior表示上一层级的标识符。level 伪列（关键字）这个必须配合connect by使用。connect_by_root显示根节点列。
Oracle递归树形结构查询功能
09-09
oracle树状结构查询即层次递归查询，是sql语句经常用到的，在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。这篇文章给大家介绍了Oracle递归树形结构查询功能，需要的朋友参考下
【Oracle】Oracle语法之递归查询
低调做人，低调编码，神码都是浮云
07-16 5435
oracle递归查询
【Sql语句】递归查询用法和简单示例（层级查询）
最新发布
鑫仔的记忆
02-25 889
层次化查询（Hierarchical Query）也称为递归查询，是一种用于处理具有层次结构数据的查询方式。在实际业务场景中，许多数据都呈现出层次关系，例如公司的组织架构（公司下有部门，部门下有子部门）、家族谱系（人有父母，父母又有他们的父母）、文件系统的目录结构等。层次化查询能够从根节点开始，递归地遍历整个层次结构，获取每个节点及其子节点的信息。
Oracle 递归查询
种一抹馨香的博客
09-02 756
Oracle 递归查询
Oracle递归查询
cpdw840624的专栏
03-26 241
    有的情况下，我们需要用递归的方法整理数据，这才程序中很容易做到，但是在数据库中，用SQL语句怎么实现？下面我以最典型的树形结构来说明下如何在Oracle使用递归查询。     为了说明方便，创建一张数据库表，用于存储一个简单的树形结构 create table TEST_TREE ( ID NUMBER, PID NUMBER, IND NUMBER, ...
oracle递归查询
虫二的专栏~~在路上~~~
05-06 1781
Select * from …. Where [结果过滤条件语句]Start with  [and起始条件过滤语句]Connect by prior [and中间记录过滤条件语句]一、Start with.........Connect By 子句         START WITH specifies the root row(s) of the hierarchy.
ORACLE递归查询
qq_22481167的专栏
03-06 526
公司中有各种大大小小的团队，团队里面有团队成员，团队成员可以是小团队或者个人，给定任意一个人的ID，需要统计出这个人所属的所有团队名称，包括上级团队。
深入sql oracle递归查询
09-10
Oracle数据库系统提供了两种主要的递归查询方法：一种是使用`WITH`子句配合`UNION ALL`，另一种是使用`START WITH`和`CONNECT BY`。这两种方法都能解决查询层次关系的问题，例如找出某个节点的所有子节点或父节点。 ...
Oracle通过递归查询父子兄弟节点方法示例
09-09
在Oracle数据库中，递归查询是一种强大的工具，用于处理层级数据结构，如组织结构、文件系统或树形关系。在本篇文章中，我们将探讨如何利用递归查询来查找父子兄弟节点，这对于理解和处理这类关系非常关键。 首先，...
pl/sql树操作
zhouminchinahz的博客
10-30 188
connect by 是结构化查询中用到的，其基本语法是： select ... from tablename start with cond1 connect by cond2 where cond3; 简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。 用上述语法的查询可以取得...
对于oracle进行简单树查询(递归查询)
i believe i can touch the sky
03-09 94
对于oracle进行简单树查询(递归查询)   DEPTID PAREDEPTID NAME NUMBER NUMBER CHAR (40 Byte) 部门id 父部门id(所属部门id) 部门名称   通过子节点向根节点追朔. Sql代码   select * from persons.dept start with depti...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

筱星_wu

你的鼓励将是我创作的最大动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值