动态Oracle Pivot_In_Clause

Question

我有点卡住了。我想做一个用户角色关系枢轴表，到目前为止，我的查询如下所示：

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中编写pivot_in_clause呢？我自己试过：

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

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

Answer 1

如果您想为枢轴实现某种类型的动态列列表，那么您可以考虑使用生成动态SQL的过程。该过程的基本语法是：

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select USERNAME ';

    begin
        for x in (select distinct ROLE from DBA_ROLES@DB_LINK)
        loop
            sql_query := sql_query ||
                ' , sum(case when GRANTED_ROLE = '''||x.Role||''' then 1 else 0 end) as '||x.Role;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' FROM DBA_USERS@DB_LINK U 
                                    LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
                                      ON U.USERNAME = R.GRANTEE
                                    GROUP BY USERNAME';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

然后，您可以使用以下方法测试结果：

variable x refcursor
exec dynamic_pivot(:x)
print x;

这将产生类似于以下结果的结果：

+----------+-----+-------+-------+
| Username | DBA | Owner | Admin |
+----------+-----+-------+-------+
| Bob      |   0 |     0 |     1 |
| Jane     |   1 |     1 |     1 |
| Jim      |   1 |     1 |     0 |
+----------+-----+-------+-------+

Answer 2

这可能对您有帮助，也可能无助于您，但是使用PIVOT XML而不是简单的PIVOT，您可以做您想做的事情。

将XML关键字添加到PIVOT运算符允许我们将生成的pivot结果转换为XML格式。它还使支点更加灵活，允许我们用子查询或任意通配符替换硬编码的IN子句。

(来源)

这显然会返回一个XML，这就是为什么它可能无助于您.