时间戳

首页 > 解决方案 > 使用 PL/SQL 函数体返回 SQL 查询

sql - 使用 PL/SQL 函数体返回 SQL 查询

问题描述

我正在使用 Oracle APEX，并且正在尝试将动态 sql 查询存储在 PL/SQL 函数主体中。这是我当前对经典报告的 SQL 查询：

SELECT 
    RELEASE,
    COUNT(*) as "Total Tests", --total
    SUM(CASE WHEN TYPE = 'P1' THEN 1 ELSE 0 END) as "P1",
    SUM(CASE WHEN TYPE = 'P2' THEN 1 ELSE 0 END) as "P2",
    SUM(CASE WHEN TYPE = 'P3' THEN 1 ELSE 0 END) as "P3",
    SUM(CASE WHEN TYPE = 'P4' THEN 1 ELSE 0 END) as "P4"
    FROM TABLENAME
group by RELEASE
ORDER BY case
    when RELEASE = '19.3' then 1
    when RELEASE = '18.11' then 2
    when RELEASE = '18.9' then 3
    when RELEASE = '18.7' then 4
    when RELEASE = '17.3' then 5
    else 6
end asc

我正在尝试将“表名”存储在页面项目中，以便每当用户更改页面项目时，此查询将自动从给定表中提取。我试图围绕这个构建一个 pl/sql 函数，但继续遇到“字符串”错误。

有谁知道如何将其转换为动态 pl/sql 函数？

提前致谢。

标签： sqloracleplsqloracle-apex

解决方案

你应该

  • 创建一个经典报表，其源是一个返回查询的函数

  • 该查询应如下所示：

    return '
  SELECT 
    RELEASE,
    COUNT(*) as "Total Tests", --total
    SUM(CASE WHEN TYPE = ''P1'' THEN 1 ELSE 0 END) as "P1",
    SUM(CASE WHEN TYPE = ''P2'' THEN 1 ELSE 0 END) as "P2",
    SUM(CASE WHEN TYPE = ''P3'' THEN 1 ELSE 0 END) as "P3",
    SUM(CASE WHEN TYPE = ''P4'' THEN 1 ELSE 0 END) as "P4"
    FROM ' || 
    SYS.DBMS_ASSERT.sql_object_name(:P1_TABLE_NAME) ||
  ' group by RELEASE 
  ORDER BY case
    when RELEASE = ''19.3''  then 1
    when RELEASE = ''18.11'' then 2
    when RELEASE = ''18.9''  then 3
    when RELEASE = ''18.7''  then 4
    when RELEASE = ''17.3''  then 5
    else 6
  end asc';

  • DBMS_ASSERT是为了防止 SQL 注入

  • 将“使用通用列名”设置为“是”
  • 页面项目的名称是:P1_TABLE_NAME（在我的示例中）；它应该是一个文本字段，按 Enter 时提交

就是这样，我想。

推荐阅读