动态Oracle Pivot_In_Clause [英] Dynamic Oracle Pivot_In_Clause
问题描述
我有点卡住了.我想做一个用户角色关系数据透视表,到目前为止我的查询看起来像这样:
I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:
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
中搜索的角色,因为我们很喜欢其中的许多角色,而且我不想检查每次有任何变化.
It works really fine and does the job, but I don't want to write to write any role I want to search for in the pivot_in_clause
, because we got like tons of them and I don't want to check every time if there are any changes.
那么有什么方法可以在pivot_in_clause
中写入SELECT
吗?我自己尝试过:
So is there a way to write a SELECT
in the pivot_in_clause
? I tried it myself:
[...]
PIVOT
(
COUNT(GRANTED_ROLE)
FOR GRANTED_ROLE
IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]
但是它总是给我一个ORA-00936:整个查询的第1行中的缺少表达式",我不知道为什么. pivot_in_clause
中不能有SELECT
还是我做错了吗?
But it always gives me an ORA-00936: "missing expression" in line 1 of the whole query and I don't know why. Can't there be a SELECT
in the pivot_in_clause
or am I doing it wrong?
推荐答案
您可以在脚本中构建动态查询, 看这个例子:
You can build dynamic query in your script, look at this example:
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
这里是结果(只有很小的片段,因为它很宽很长)
Here is the result (only small fragment, because it is very wide and long)
-----------------------------------------------------------------------------------------------------------------------------------
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
这篇关于动态Oracle Pivot_In_Clause的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!