我有点卡住了。我想做一个用户角色关系数据透视表，到目前为止我的查询如下所示：
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，因为我们有很多这样的东西，我不想每次都检查是否有任何变化。
那么有没有办法写一个SELECT in the pivot_in_clause？我自己尝试了一下：
[...]
PIVOT
(
COUNT(GRANTED_ROLE)
FOR GRANTED_ROLE
IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]
但它总是给我一个 ORA-00936: 在整个查询的第 1 行中“缺少表达式”，我不知道为什么。难道不能有一个SELECT in the pivot_in_clause或者我做错了？
您可以在脚本中构建动态查询，
看这个例子：
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
