oracle start with connect by base_pd_clazz

最新推荐文章于 2025-04-24 20:00:00 发布

原创最新推荐文章于 2025-04-24 20:00:00 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

DB.Oracle 专栏收录该内容

34 篇文章

订阅专栏

本文介绍 Oracle 数据库中 LPAD 函数的基本用法，并通过示例展示如何使用 LPAD 函数进行字符串左填充操作。此外，还提供了一个复杂的视图创建示例，该视图涉及到多个表的连接及层级查询。

select lpad('test',10) from dual;
将返回“ test”

select lpad('test',10,'ee') from dual;
结果将返回eeeeeetest。


create or replace view base_pd_clazz as
select t.cls_id,
       t.cls_name,
       LPAD('|-', (level - 1) * 4, '　| ') || LPAD('『', 2) || t.cls_name ||
       RPAD('』', 2) tree_name,
       CONNECT_BY_ISLEAF IS_LEAF,
       level CLS_LEVEL,
       t.par_id,
       t1.cls_name par_name,
       t.root_id,
       t2.cls_name root_name,
       ltrim(sys_connect_by_path(t.cls_name, ','), ',') FULL_NAME,
       t.order_value,
       t.is_del
  from base_pd_class t
  left join base_pd_class t1 on t.par_id = t1.cls_id
  left join base_pd_class t2 on t.root_id = t2.cls_id
 start with t.par_id = -1
connect by prior t.cls_id = t.par_id;

SELECT
    'public static final Integer PRE_' || item_cd || ' = ' || item_cd || '; // '|| item_nm
FROM
    table article_mod
ORDER BY
    item_cd

[color=red]
start with 和 where条件一起时候时，放在where条件的最后面并且没有and
[/color]


<isNotEmpty prepend="  " property="map.level_3_start_with_not_in">
			start with a.CLS_LEVEL = 3 and a.cls_id not in 
			<iterate close=")" open="(" conjunction="," property="map.level_3_start_with_not_in">#map.level_3_start_with_not_in[]#</iterate>
			connect by prior cls_id = par_id
		</isNotEmpty>