Oracle实现对树形结构的数据进行层级查询

最新推荐文章于 2024-01-18 11:25:09 发布

liuccn

最新推荐文章于 2024-01-18 11:25:09 发布

阅读量2k

点赞数 1

本文链接：https://blog.csdn.net/lc_2014c/article/details/125624077

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

Oracle实现对树形结构的数据进行层级查询。

方式一：使用SYS_CONNECT_BY_PATH

--专题目录的Tree层级显示
SELECT level as "level",
pkid,
specialclname,
specialclcode,
       SYS_CONNECT_BY_PATH(specialclname, '-') as "fullpath"
FROM MDS_DM_SPECIALCATALOG
START WITH parentid is null
CONNECT BY PRIOR pkid = parentid
ORDER SIBLINGS BY specialclname

在这里插入图片描述

方式二：循环查询单个结果插入表结构

--创建临时表
CREATE TABLE "T_TMP_DIR" ("DIRECTORYID" NUMBER,"DIRS" VARCHAR2(4000));

--插入数据到数据集-目录层级的临时表
DECLARE cursor dir IS SELECT
	DIRECTORYID 
	FROM
		core_dm_datasourcedirectory;
	v_DIRECTORYID NUMBER;
	BEGIN
			open dir;
		LOOP
		EXIT WHEN dir % notfound;
		FETCH dir INTO v_DIRECTORYID;
		dbms_output.put_line ( v_DIRECTORYID );	
			INSERT INTO t_tmp_dir(
			directoryid,dirs
		) (SELECT
			v_DIRECTORYID AS directoryid,
			listagg ( to_char( dirt.directoryname ), '-' ) within GROUP ( ORDER BY dirt.dircode ) AS dirs 
		FROM
			( SELECT * FROM core_dm_datasourcedirectory CONNECT BY nocycle PRIOR parentdirectoryid = directoryid START WITH directoryid = v_DIRECTORYID ) dirt);
	END LOOP;
	close dir;
	END;