sql – 动态Oracle Pivot_In_Clause

发布时间：2020-12-12 07:01:11 所属栏目：MsSql教程来源：网络整理

导读：我有点卡住了.我想做一个用户角色关系数据透视表,我的查询到目前为止看起来像这样： WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME,GRANTED_ROLE FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R ON U.USERNAME = R.GRANTEE ))S

我有点卡住了.我想做一个用户角色关系数据透视表,我的查询到目前为止看起来像这样：

WITH PIVOT_DATA AS (
     SELECT *
     FROM
     (
         SELECT USERNAME,GRANTED_ROLE
         FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
         ON U.USERNAME = R.GRANTEE
      )
)
SELECT *
FROM PIVOT_DATA
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN('CONNECT') -- Just an example
)
ORDER BY USERNAME ASC;

它工作得非常好并且完成了工作,但是我不想编写我想在pivot_in_clause中搜索的任何角色,因为我们得到了大量的这些,我不想每次检查是否有任何变化.

那么有没有办法在pivot_in_clause中编写SELECT？我亲自尝试过：

[...]
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]

但它总是在整个查询的第1行给我一个ORA-00936：“缺少表达式”,我不知道为什么.在pivot_in_clause中不能有SELECT或者我做错了吗？

解决方法

您可以在脚本中构建动态查询,
看看这个例子：

variable rr refcursor

declare 
  bb varchar2(4000);
  cc varchar2( 30000 );
begin 
    WITH PIVOT_DATA AS (
         SELECT *
         FROM
         (
             SELECT USERNAME,GRANTED_ROLE
             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R
             ON U.USERNAME = R.GRANTEE
          )
    )
    select ''''|| listagg( granted_role,''',''' ) 
            within group( order by granted_role ) || '''' as x 
    into bb
    from (
      select distinct granted_role from pivot_data
    )
    ;

    cc := q'[
    WITH PIVOT_DATA AS (
         SELECT *
         FROM
         (
             SELECT USERNAME,GRANTED_ROLE
             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R
             ON U.USERNAME = R.GRANTEE
          )
    )
    SELECT *
    FROM PIVOT_DATA
    PIVOT
    (
        COUNT(GRANTED_ROLE)
        FOR GRANTED_ROLE
        IN(]'  || bb || q'[) -- Just an example
    )
    ORDER BY USERNAME ASC]';

    open :rr for cc;
end;
/

SET PAGESIZE 200
SET LINESIZE 16000
print :rr

这是结果(只有小片段,因为它很宽很长)

-----------------------------------------------------------------------------------------------------------------------------------
    USERNAME                       'ADM_PARALLEL_EXECUTE_TASK' 'APEX_ADMINISTRATOR_ROLE' 'AQ_ADMINISTRATOR_ROLE' 'AQ_USER_ROLE'        
    ------------------------------ --------------------------- ------------------------- ----------------------- ----------------------
    ANONYMOUS                      0                           0                         0                       0          
    APEX_030200                    0                           0                         0                       0        
    APEX_PUBLIC_USER               0                           0                         0                       0    
    APPQOSSYS                      0                           0                         0                       0   
..............
    IX                             0                           0                         1                       1  
    OWBSYS                         0                           0                         1                       1

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!