Oracle树问题（10G平台）

函数介绍：
1、sys_connect_by_path(str, delimiter) --将每层的字段内容列出来，以delimiter为分隔符;
2、connect_by_isleaf(col)--判断当前列是否是叶结点，如果是叶子节点则返回1，否则返回0;
3、level--返回当前所在的层级数;
4、不支持connect_by_isleaf(col)的情况下如何找到叶子节点：
      case when rownum = max(rownum) over(partition by st.grantee) then 1 else 0 end
      以该语句来实现。

实例：
select ab.grantee, ltrim(ab.path,'; ')
  from (select connect_by_isleaf isleaf,
               aa.grantee,
               aa.p_child,
               aa.p_parent,
               sys_connect_by_path(aa.privilege, '; ') path
          from (select st.grantee,
                       st.admin_option,
                       st.privilege,
                       rownum p_child,
                       lag(rownum, 1, 0) over(partition by st.grantee order by st.privilege) p_parent
                  from (select to_char(sysdate, 'MM/DD/YY') datum,
                               substr(grantee, 1, 27) grantee,
                               substr(privilege, 1, 40) privilege,
                               substr(admin_option, 1, 10) admin_option
                          from dba_sys_privs
                         order by 1, 2, 3) st) aa
         start with aa.p_parent = 0
        connect by nocycle prior aa.p_child = aa.p_parent) ab
 where ab.isleaf = 1

说明：
1、构建基础表，这是显示系统中所有授权所拥有的权限的语句：
select to_char(sysdate, 'MM/DD/YY') datum,
   substr(grantee, 1, 27) grantee,
   substr(privilege, 1, 40) privilege,
   substr(admin_option, 1, 10) admin_option
from dba_sys_privs
order by 1, 2, 3

DATUM    GRANTEE                  PRIVILEGE                      ADMIN_OPTION
-------- ------------------------ ------------------------------ ------------
02/19/08 ANONYMOUS                CREATE SESSION                 NO
02/19/08 AQ_ADMINISTRATOR_ROLE    CREATE EVALUATION CONTEXT      YES
02/19/08 AQ_ADMINISTRATOR_ROLE    CREATE RULE                    YES
02/19/08 AQ_ADMINISTRATOR_ROLE    CREATE RULE SET                YES
02/19/08 AQ_ADMINISTRATOR_ROLE    DEQUEUE ANY QUEUE              YES
02/19/08 AQ_ADMINISTRATOR_ROLE    ENQUEUE ANY QUEUE              YES
02/19/08 AQ_ADMINISTRATOR_ROLE    MANAGE ANY QUEUE               YES
02/19/08 BI                       ALTER SESSION                  NO
02/19/08 BI                       CREATE CLUSTER                 NO
02/19/08 BI                       CREATE DATABASE LINK           NO
02/19/08 BI                       CREATE SEQUENCE                NO
02/19/08 BI                       CREATE SESSION                 NO
02/19/08 BI                       CREATE SYNONYM                 NO
02/19/08 BI                       CREATE TABLE                   NO
02/19/08 BI                       CREATE VIEW                    NO
02/19/08 BI                       UNLIMITED TABLESPACE           NO
02/19/08 CONNECT                  CREATE SESSION                 NO
02/19/08 CTXSYS                   ALTER SESSION                  NO
02/19/08 CTXSYS                   CREATE PUBLIC SYNONYM          NO
02/19/08 CTXSYS                   CREATE SESSION   NO
..............

2、构建树节点
select rownum p_child,
       lag(rownum, 1, 0) over(partition by st.grantee order by st.privilege) p_parent,
       st.grantee,
       st.admin_option,
       st.privilege
  from (select to_char(sysdate, 'MM/DD/YY') datum,
        substr(grantee, 1, 27) grantee,
        substr(privilege, 1, 40) privilege,
        substr(admin_option, 1, 10) admin_option
   from dba_sys_privs
  order by 1, 2, 3) st

   P_CHILD   P_PARENT GRANTEE                          ADMIN_OPTION PRIVILEGE
---------- ---------- -------------------------------- ------------ ---------------------------
         1          0 ANONYMOUS                        NO           CREATE SESSION
         2          0 AQ_ADMINISTRATOR_ROLE            YES          CREATE EVALUATION CONTEXT
         3          2 AQ_ADMINISTRATOR_ROLE            YES          CREATE RULE
         4          3 AQ_ADMINISTRATOR_ROLE            YES          CREATE RULE SET
         5          4 AQ_ADMINISTRATOR_ROLE            YES          DEQUEUE ANY QUEUE
         6          5 AQ_ADMINISTRATOR_ROLE            YES          ENQUEUE ANY QUEUE
         7          6 AQ_ADMINISTRATOR_ROLE            YES          MANAGE ANY QUEUE
         8          0 BI                               NO           ALTER SESSION
         9          8 BI                               NO           CREATE CLUSTER
        10          9 BI                               NO           CREATE DATABASE LINK
        11         10 BI                               NO           CREATE SEQUENCE
        12         11 BI                               NO           CREATE SESSION
        13         12 BI                               NO           CREATE SYNONYM
        14         13 BI                               NO           CREATE TABLE
        15         14 BI                               NO           CREATE VIEW
        16         15 BI                               NO           UNLIMITED TABLESPACE
        17          0 CONNECT                          NO           CREATE SESSION
        18          0 CTXSYS                           NO           ALTER SESSION
        19         18 CTXSYS                           NO           CREATE PUBLIC SYNONYM
        20         19 CTXSYS                           NO           CREATE SESSION
说明：
由于第一个步骤中的语句已经将结果集排序，因此grantee列的所有记录都是按排好序来的（如上面数据所显示的）
为下面的节点编号创造了条件。
1、p_child 给每条记录编制唯一的编号；
2、p_parent 的功能是给将每个grantee分组的第一条记录作为本分组的父节点，这些父节点都已0作为他们的根节点。
            每个分组中每条记录都是下一条记录的父节点。因此在执行sys_connect_by_path函数的时候，会将整条
     路径显示出来。当达到叶结点的时候就是所有的权限了。

3、得到每条记录所对应的权限路径，并给出当前记录是否是叶子节点。
select connect_by_isleaf isleaf,
       aa.grantee,
       aa.p_child,
       aa.p_parent,
       ltrim(sys_connect_by_path(aa.privilege, '; '),'; ') path
  from (select rownum p_child,
        lag(rownum, 1, 0) over(partition by st.grantee order by st.privilege) p_parent,
        st.grantee,
        st.admin_option,
        st.privilege
   from (select to_char(sysdate, 'MM/DD/YY') datum,
         substr(grantee, 1, 27) grantee,
         substr(privilege, 1, 40) privilege,
         substr(admin_option, 1, 10) admin_option
    from dba_sys_privs
   order by 1, 2, 3) st) aa
 start with aa.p_parent = 8
connect by nocycle prior aa.p_child = aa.p_parent

0 BI 9 8 CREATE CLUSTER
0 BI 10 9 CREATE CLUSTER; CREATE DATABASE LINK
0 BI 11 10 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE
0 BI 12 11 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION
0 BI 13 12 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION; CREATE SYNONYM
0 BI 14 13 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION; CREATE SYNONYM; CREATE TABLE
0 BI 15 14 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION; CREATE SYNONYM; CREATE TABLE; CREATE VIEW
1 BI 16 15 CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION; CREATE SYNONYM; CREATE TABLE; CREATE VIEW; UNLIMITED TABLESPACE

4、取出是叶子节点的数据，即每个授权下对应的所有权限列表（以但条记录表示）：
select ab.grantee, ab.path
  from (select connect_by_isleaf isleaf,
               aa.grantee,
               aa.p_child,
               aa.p_parent,
               ltrim(sys_connect_by_path(aa.privilege, '; '),'; ') path
          from (select rownum p_child,
                       lag(rownum, 1, 0) over(partition by st.grantee order by st.privilege) p_parent,
                       st.grantee,
                       st.admin_option,
                       st.privilege
                  from (select to_char(sysdate, 'MM/DD/YY') datum,
                               substr(grantee, 1, 27) grantee,
                               substr(privilege, 1, 40) privilege,
                               substr(admin_option, 1, 10) admin_option
                          from dba_sys_privs
                         order by 1, 2, 3) st) aa
         start with aa.p_parent = 8
        connect by nocycle prior aa.p_child = aa.p_parent) ab
 where ab.isleaf = 1

BI CREATE CLUSTER; CREATE DATABASE LINK; CREATE SEQUENCE; CREATE SESSION; CREATE SYNONYM; CREATE TABLE; CREATE VIEW; UNLIMITED TABLESPACE