从 Oracle 中的动态 SQL 获取结果集中的结果

Question

这个问题与我在 StackOverflow 上找到的其他几个问题类似，但这些差异对我来说足够重要，足以保证提出一个新问题，所以这里是：

我想从动态 SQL 中获取结果集在Oracle中，然后在类似SqlDeveloper的工具中将其显示为结果集，就像我直接执行动态SQL语句一样。这在 SQL Server 中很简单，所以具体来说，这里是一个来自 SQL Server 的示例，它在 SQL Server Management Studio 或查询资源管理器中返回结果集：

EXEC sp_executesql N'select * from countries'

或更准确地说：

DECLARE @stmt nvarchar(100)
SET @stmt = N'select * from countries'
EXEC sp_executesql @stmt

问题 "如何从 Oracle PL/ 返回结果集/游标执行动态 SQL 的 SQL 匿名块？” 解决了问题的前半部分——在游标中执行动态 SQL。问题“如何使Oracle过程返回结果集”提供了类似的答案。网络搜索揭示了同一主题的许多变体，所有这些都只解决了我问题的前半部分。我发现这篇文章解释了如何在 SqlDeveloper 中执行此操作，但这使用了 SqlDeveloper 的一些功能。我实际上使用的是自定义查询工具，因此我需要将解决方案独立于 SQL 代码中。该自定义查询工具同样不具备显示 print (dbms_output.put_line) 语句输出的功能；它只显示结果集。这是又一个可能的途径使用“立即执行...批量收集”，但此示例再次使用 dbms_output.put_line 语句循环呈现结果。 此链接尝试解决了这个话题，但问题也从未得到完全解答。

假设这是可能的，我将添加一个条件：我想在不必定义函数或过程的情况下执行此操作（由于数据库权限有限）。也就是说，我想执行一个包含动态 SQL 的独立 PL/SQL 块，并在 SqlDeveloper 或类似工具中返回结果集。

---

总结一下：

• 我想执行任意 SQL 语句（因此是动态 SQL）。
• 该平台是Oracle。
• 解决方案必须是没有过程或函数的 PL/SQL 块。
• 输出必须生成为规范结果集；没有打印语句。
• 输出必须在 SqlDeveloper 中呈现为结果集，而不使用任何 SqlDeveloper 特殊功能。

有什么建议吗？

Answer 1

我能想到的最接近的事情是创建一个需要权限的动态视图。这肯定会涉及使用 PL/SQL 块和 SQL 查询，而不涉及过程/函数。但是，任何动态查询都可以从结果网格中进行转换和查看，因为它将作为选择查询运行。

DEFINE view_name = 'my_results_view';
SET FEEDBACK OFF
SET ECHO OFF
DECLARE
  l_view_name VARCHAR2(40)     := '&view_name';
  l_query     VARCHAR2(4000)   := 'SELECT 1+level as id,
                                  ''TEXT''||level as text  FROM DUAL ';
  l_where_clause VARCHAR2(4000):= 
                           ' WHERE TRUNC(1.0) =  1 CONNECT BY LEVEL < 10';
BEGIN
     EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '
                       || l_view_name
                       || ' AS '
                       || l_query
                       || l_where_clause;
END;
/
 select * from &view_name;

Answer 2

您似乎要求一大块 PL/SQL 代码，该代码将采用任意查询返回未确定结构的结果集，并以某种方式“转发/重组”该结果集，以便可以通过某些“自定义 GUI 工具轻松呈现” ”。

如果是这样，请查看 DBMS_SQL 中的动态 SQL。它有一个 DESCRIBE_COLUMNS 过程，该过程从动态 SELECT 语句返回列。您需要的步骤是：

1. 解析语句
2. 描述结果集（列名和数据类型）
3. 获取每一行，并为每一列调用数据类型相关函数以将该值返回到局部变量
4. 将这些局部变量放入已定义的变量中返回调用环境的结构（例如，一致的列名称 [例如 col_1、col_2] 可能都是 VARCHAR2）

作为替代方案，您可以尝试将查询构建到 XMLFOREST 语句，并从 XML 中解析出结果。

---

补充：
与 SQL Server 不同，Oracle PL/SQL 调用不会“自然”返回单个结果集。它可以打开一个或多个引用游标并将它们传回客户端。然后，客户端有责任从这些引用游标中获取记录和列。如果您的客户端不/无法处理该问题，则您无法使用 PL/SQL 调用。
存储函数可以返回预定义的集合类型，这可以让您执行诸如“select * from table(func_name('select * from states'))”之类的操作。但是，该函数无法执行 DML（更新/删除/插入/合并），因为它破坏了该查询的任何一致性概念。另外，返回的结构是固定的，因此

select * from table(func_name('select * from countries'))

必须返回相同的列集（列名和数据类型），因为

select * from table(func_name('select * from persons'))

使用 DBMS_SQL 或 XMLFOREST，这样的函数可以采用动态查询并将其重组为预定义的结构。列集（col_1、col_2 等），以便可以以一致的方式返回。但我不明白这有什么意义。

Answer 3

尝试一下这些。

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind argument in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;


declare
  v_rc    sys_refcursor;
begin
   v_rc := get_dept_emps(10);  -- This returns an open cursor
   dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
   close v_rc;
end;

在这里找到更多例子。 http://forums.oracle.com/forums/thread。 jspa?threadID=886365&tstart=0

Answer 4

在 TOAD 中，执行下面的脚本时，系统将提示您输入 v_result 的类型。从类型选择列表中选择光标，结果将显示在 Toad 的数据网格中（类似于 Excel 电子表格的结果）。也就是说，当使用游标作为结果时，您应该始终编写两个程序（客户端和服务器）。在这种情况下，“TOAD”将是客户端。

DECLARE
   v_result      sys_refcursor;
   v_dynamic_sql   VARCHAR2 (4000);
BEGIN
   v_dynamic_sql := 'SELECT * FROM user_objects where ' || ' 1 = 1';

   OPEN :v_result FOR (v_dynamic_sql);
END;

Oracle 的 SQL Developer 中也可能有类似的机制来提示绑定。