文由網友whl供稿，特此感謝！
/**
* Desc: 取欄目樹 ,過濾用戶權限和無效欄目
* Author: WHL
* Date: 2009-05-31 15:17
*/
/** 1. 取某用戶有權限（np_cms_column_security表有記錄且t.action_1 = ‘1′）的欄目的樹 **/
01.createorreplaceviewV_NP_CTREE_BSas
02.selectB.*from(
03.selectA.*, lag(A.column_id) over(partitionbyA.column_idorderby0 ) RK
04. from(select/*+choose */
05. t.*
06. fromnp_cms_column t
07. wheret.is_active ='1'
08. connectbypriort.column_id = t.parent_id
09. startwitht.column_idin(selectt.column_id
10. fromnp_cms_column_security t
11. wheret.subject_id ='mazj'
12. /*這裡添加角色過濾*/
13. andt.action_1 ='1'))A) B
14. wherenotexists
15. (select0
16. from(selectdistinctd.column_id
17. fromnp_cms_column d
18. connectbypriord.column_id = d.parent_id
19. startwithd.column_idin
20. (selectt.column_id
21. fromnp_cms_column_security t
22. wheret.subject_id ='mazj'
23. /* 這裡添加角色過濾*/
24. andt.action_1 ='0'
25. /* 排除有權限樹下的非授權ID,既 Action_1=0的*/
26. andexists
27. (select0
28. from(selectdistinctd.column_id
29. fromnp_cms_column d
30. connectbypriord.column_id =
31. d.parent_id
32. startwithd.column_idin
33. (selectt.column_id
34. fromnp_cms_column_security t
35. wheret.subject_id =
36. 'mazj'
37. /*這裡添加角色過濾*/
38. andt.action_1 ='1')) C1
39. whereC1.column_id = t.column_id))
40. andd.is_active ='1') C
41. whereC.column_id = B.column_idandB.RKisnull)andB.RKisnull
42.unionall
43.selectc.*, 0 RKfromnp_cms_column cwherec.parent_id = 0;
————————————————————————
/** 2.得到欄目的虛擬父親ID（考慮到把斷層的節點接起來）**/
01.createorreplaceviewV_NP_CTREE_PAas
02.selectB.*,
03. (caseB.column_id
04. when1then0elsenvl(B.father, 1)end) VFA
05. from(selectv.*,
06. (selectvv.column_id
07. fromV_NP_CTREE_BS vv
08. wherevv.column_id = v.parent_id) FATHER
09. fromV_NP_CTREE_BS v) B;
————————————————————————
/** 3. 取出門戶需要的欄目樹 **/
1.--create or replace view V_NP_CTREE_RS as
2.select
3. D.*, LPAD(' ', 2 *level- 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME,'/') "Path"
4. from(selectc.*
5. fromV_NP_CTREE_PA c
6. orderbyc.VFA, c.disorderdesc, c.column_iddesc) D
7.connectbypriorD.column_id = D.VFA
8. startwithD.column_id = 1;
