如何实现SQL栏目树_Oracle技术

/**
* Desc: 取栏目树 ,过滤用户权限和无效栏目
*/

/** 1. 取某用户有权限（np_cms_column_security表有记录且t.action_1 = ’1′）的栏目的树 **/

createorreplaceviewV_NP_CTREE_BS as
selectB.* from(
selectA.*, lag(A.column_id) over(partition byA.column_id orderby0 ) RK
  from(select/*+choose */
         t.*
          fromnp_cms_column t
         wheret.is_active = '1'
        connectbypriort.column_id = t.parent_id
         start witht.column_id in(selectt.column_id
                                      fromnp_cms_column_security t
                                     wheret.subject_id = 'mazj'
                                          /*这里添加角色过滤*/
                                       andt.action_1 = '1'))A) B
 wherenotexists
 (select0
          from(selectdistinctd.column_id
                  fromnp_cms_column d
                connectbypriord.column_id = d.parent_id
                 start withd.column_id in
                    (selectt.column_id
                       fromnp_cms_column_security t
                      wheret.subject_id = 'mazj'
                           /* 这里添加角色过滤*/
                        andt.action_1 = '0'
                           /* 排除有权限树下的非授权ID,既 Action_1=0的*/
                        andexists
                      (select0
                               from(selectdistinctd.column_id
                                       fromnp_cms_column d
                                     connectbypriord.column_id =
                                                 d.parent_id
                                      start withd.column_id in
                                                 (selectt.column_id
                                                    fromnp_cms_column_security t
                                                   wheret.subject_id =
                                                         'mazj'
                                                        /*这里添加角色过滤*/
                                                     andt.action_1 = '1')) C1
                              whereC1.column_id = t.column_id))
                        andd.is_active = '1') C
         whereC.column_id = B.column_id andB.RK isnull) andB.RK isnull
unionall
selectc.*, 0 RK fromnp_cms_column c wherec.parent_id = 0;

————————————————————————
/** 2.得到栏目的虚拟父亲ID（考虑到把断层的节点接起来）**/

createorreplaceviewV_NP_CTREE_PA as
selectB.*,
       (caseB.column_id
         when1 then0 elsenvl(B.father, 1) end) VFA
  from(selectv.*,
               (selectvv.column_id
                  fromV_NP_CTREE_BS vv
                 wherevv.column_id = v.parent_id) FATHER
          fromV_NP_CTREE_BS v) B;

————————————————————————
/** 3. 取出门户需要的栏目树 **/

--create or replace view V_NP_CTREE_RS as
select
 D.*, LPAD(' ', 2 * level- 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME, '/') &quot;Path&quot;
  from(selectc.*
          fromV_NP_CTREE_PA c
         orderbyc.VFA, c.disorder desc, c.column_id desc) D
connectbypriorD.column_id = D.VFA
 start withD.column_id = 1;

————————————————————————