PL SQL 数据透视表 VS 自定义 Json 解决方案

Question

在我的一个 Oracle APEX 项目中，我需要为特定个人的特定应用程序实施不同级别的安全性。

首先，我创建了一个笛卡尔坐标系，其中包含 user 表、app 表和 role 表中的信息。

它看起来像这样：

SELECT
A.user_id, B.app_id, C.role_id
FROM user A, app B, role C
ORDER BY A.user_id ASC, B.app_id ASC, C.role_id ASC

这允许我返回 user、app 和 role 的每个组合。如果不使用 where 子句，它会返回超过 303k 行。目前有近 500 个用户、6 个角色和 100 多个应用程序。

当我从此视图中为特定用户选择时，它会在大约 10 毫秒内返回，这是可以接受的。

现在，我还有一个存储每个用户的应用程序/角色分配的 vw。我已按以下方式将该表加入到笛卡尔表中。

SELECT
A.*,
DECODE(B.app_right_id, null, 0, 1) AS user_access 
FROM
            vw_user_app_role A -- My cartesian view
LEFT JOIN   vw_tbl_user_app_role B 
        ON  A.user_id = B.user_id
        AND A.app_id = B.app_id
        AND A.role_id = B.role_id

这会返回一组非常有用的数据，就像

user_id app_id role_id user_access
50      5      1       0  
50      10     2       1
50      15     3       1
75      5      1       1
75      10     2       0
75      15     3       0

我正在考虑下一步应该做什么一样，如果我应该创建数据的数据透视表，其中 app_id 是行，role_id 是列，user_access 是“数据”。 “数据”最终将呈现为网站上具有适当行/列标题的复选框。

我还在考虑使用纯 ajax/json 解决方案，其中我将使用 pl sql 构建 json 字符串，并将整个字符串返回到客户端以通过 jquery 进行处理。

我担心第一个选项的难度（我对 pl sql 非常陌生，并且我不确定如何生成要在此版本的 oracle (v 10) 中使用的数据透视表），并且我担心创建包含如此多数据的整个 json 字符串的费用。

任何建议将不胜感激。

编辑

我已经通过以下sql获得了我想要的数据透视表：

SELECT
B.application_nm,
A.user_id,
MAX(DECODE(b.role_name, 'role 1', A.USER_ACCESS, NULL)) "role 1",
MAX(DECODE(b.role_name, 'role 2', A.USER_ACCESS, NULL)) "role 2",
MAX(DECODE(b.role_name, 'role 3', A.USER_ACCESS, NULL)) "role 3",
MAX(DECODE(b.role_name, 'role 4', A.USER_ACCESS, NULL)) "role 4",
MAX(DECODE(b.role_name, 'role 5', A.USER_ACCESS, NULL)) "role 5",
MAX(DECODE(b.role_name, 'role 6', A.USER_ACCESS, NULL)) "role 6"
FROM 
vw_user_app_access A LEFT JOIN vw_tbl_app B ON A.app_id = B.app_id
LEFT JOIN vw_tbl_roles C ON A.role_id = C.role_id
GROUP BY B.application_name, A.user_id
ORDER BY A.user_id DESC

唯一的问题是将来我们必须添加“角色7”。然后我必须返回到此查询并添加行 MAX(DECODE(b.role_name, 'role 7', A.USER_ACCESS, NULL)) "role 7"

提前思考，这可能是不便，但考虑到 APEX 的框架，我认为我必须以任何方式进入报告来手动更新列数。

我认为这可能是目前“最好”的解决方案，除非有人有任何其他建议......

Answer 1

基于动态 SQL 查询的 Apex 报表区域可以在查询更改时返回不同数量的列。我在 apex.oracle.com 上设置了一个简单的演示 。在“列”表格中输入新的列名称，然后按“添加行”，矩阵报告将使用该名称的额外列重新绘制。

您必须：

1. 使报表基于返回要作为字符串运行的 SQL 的函数
2. 选择区域属性“使用通用列名（仅在运行时解析查询）”
3. 将报表标题类型设置为 PL/SQL，然后使用函数以冒号分隔的列表形式动态返回所需的列标题。请注意，这可能与列名称不同，尽管我的示例对两者使用相同的文本。

如果我的示例不够清楚，我稍后会添加更多信息 - 我现在没时间了。