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

Getting results in a result set from dynamic SQL in Oracle

提问by Michael Sorens

This question is similar to a couple others I have found on StackOverflow, but the differences are signficant enough to me to warrant a new question, so here it is:

这个问题类似于我在 StackOverflow 上发现的其他几个问题，但差异对我来说足够重要，需要提出一个新问题，所以这里是：

I want to obtain a result set from dynamic SQL in Oracle and then display it as a result set in a SqlDeveloper-like tool, just as if I had executed the dynamic SQL statement directly. This is straightforward in SQL Server, so to be concrete, here is an example from SQL Server that returns a result set in SQL Server Management Studio or Query Explorer:

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

EXEC sp_executesql N'select * from countries'

Or more properly:

或者更准确地说：

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

The question "How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?"addresses the first half of the problem--executing dynamic SQL into a cursor. The question "How to make Oracle procedure return result sets"provides a similar answer. Web search has revealed many variations of the same theme, all addressing just the first half of my question. I found this postexplaining how to do it in SqlDeveloper, but that uses a bit of functionality of SqlDeveloper. I am actually using a custom query tool so I need the solution to be self-contained in the SQL code. This custom query tool similarly does not have the capability to show output of print (dbms_output.put_line) statements; it only displays result sets. Here is yet one more possible avenueusing 'execute immediate...bulk collect', but this example again renders the results with a loop of dbms_output.put_line statements. This linkattempts to address the topic but the question never quite got answered there either.

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

Assuming this is possible, I will add one more condition: I would like to do this without having to define a function or procedure (due to limited DB permissions). That is, I would like to execute a self-contained PL/SQL block containing dynamic SQL and return a result set in SqlDeveloper or a similar tool.

假设这是可能的，我将再添加一个条件：我想这样做而不必定义函数或过程（由于 DB 权限有限）。也就是说，我想执行一个包含动态 SQL 的自包含 PL/SQL 块，并在 SqlDeveloper 或类似工具中返回一个结果集。

---

---

So to summarize:

所以总结一下：

• I want to execute an arbitrary SQL statement (hence dynamic SQL).
• The platform is Oracle.
• The solution must be a PL/SQL block with no procedures or functions.
• The output must be generated as a canonical result set; no print statements.
• The output must render as a result set in SqlDeveloper without using any SqlDeveloper special functionality.

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

Any suggestions?

有什么建议？

采纳答案by Gary Myers

You seem to be asking for a chunk of PL/SQL code that will take an arbitrary query returning result set of undetermined structure and 'forward/restructure' that result set in some way such that is can easily be rendered by some "custom GUI tool".

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

If so, look into the DBMS_SQL for dynamic SQL. It has a DESCRIBE_COLUMNS procedure which returns the columns from a dynamic SELECT statement. The steps you would need are,

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

1. Parse the statement
2. Describe the result set (column names and data types)
3. Fetch each row, and for each column, call the datatype dependent function to return that value into a local variable
4. Place those local variables into a defined structure to return to the calling environment (eg consistent column names [such as col_1, col_2] probably all of VARCHAR2)

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

As an alternative, you could try building the query into an XMLFORESTstatement, and parse the results out of the XML.

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

---

---

Added : Unlike SQL Server, an Oracle PL/SQL call will not 'naturally' return a single result set. It can open up one or more ref cursors and pass them back to the client. It then becomes the client's responsibility to fetch records and columns from those ref cursors. If your client doesn't/can't deal with that, then you cannot use a PL/SQL call. A stored function can return a pre-defined collection type, which can allow you to do something like "select * from table(func_name('select * from countries'))". However the function cannot do DML (update/delete/insert/merge) because it blows away any concept of consistency for that query. Plus the structure being returned is fixed so that

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

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

must return the same set of columns (column names and data types) as

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

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

It is possible, using DBMS_SQL or XMLFOREST, for such a function to take a dynamic query and restructure it into a pre-defined set of columns (col_1, col_2, etc) so that it can be returned in a consistent manner. But I can't see what the point of it would be.

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

回答by Kuberchaun

Try try these.

试试这些。

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;

Find more examples here. http://forums.oracle.com/forums/thread.jspa?threadID=886365&tstart=0

在此处查找更多示例。http://forums.oracle.com/forums/thread.jspa?threadID=886365&tstart=0

回答by Brian

In TOAD when executing the script below you will be prompted for the type of v_result. From the pick list of types select cursor, the results are then displayed in Toad's data grid (the excel spreadsheet like result). That said, when working with cursors as results you should always write two programs (the client and the server). In this case 'TOAD' will be the client.

在 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;

There may be a similar mechanism in Oracle's SQL Developer to prompt for the binding as well.

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

回答by Kaushik Nayak

The closest thing I could think of is to create a dynamic view for which permission is required. This will certainly involve using a PL/SQL block anda SQL query and no procedure/function. But, any dynamic query can be converted and viewed from the Result Grid as it's going to be run as a select query.

我能想到的最接近的事情是创建一个需要许可的动态视图。这肯定会涉及使用 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;