为买卖提新大类定义功能性视图

create or replace view BASE_PD_CLS 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,

       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 = 0

connect by prior t.cls_id = t.par_id

转载于:https://www.cnblogs.com/xingxiudong/archive/2011/03/18/3986972.html