Oracle EXECUTE IMMEDIATE 可以使用可变数量的绑定吗？

Oracle EXECUTE IMMEDIATE with variable number of binds possible?

提问by Kosi2801

I need to use dynamic SQL execution on Oracle where I do not know the exact number of bind variables used in the SQL before runtime.

我需要在 Oracle 上使用动态 SQL 执行，我不知道运行前 SQL 中使用的绑定变量的确切数量。

Is there a way to use a variable number of bind variables in the call to EXECUTE IMMEDIATEsomehow?

有没有办法在调用中使用可变数量的绑定变量EXECUTE IMMEDIATE？

More specifically, I need to pass oneparameter into the unknown SQL but I do not know how often it will be used there.

更具体地说，我需要将一个参数传递给未知的 SQL，但我不知道它在那里使用的频率。

I tried something like

我试过类似的东西

EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL WHERE :var = :var' USING 1;

But it threw back with ORA-01008: not all variables bound.

但它退回了 ORA-01008: not all variables bound.

采纳答案by Steve Broberg

You can't do this with EXECUTE IMMEDIATE. However, you can do this by using Oracle's DBMS_SQLpackage. The Database Application Developer's Guidehas a comparison between the EXECUTE IMMEDIATEyou're familiar with and dbms_sqlmethods. This pagedocuments DBMS_SQL, but has some examples (linked above) that should get you started (example 1 is a simple case of running a statement that could have an arbitrary number of bind variables). DBMS_SQLis a lot more cumbersome from a coding perspective, but it will allow you to do just about anything you can conceive.

你不能用EXECUTE IMMEDIATE. 但是，您可以使用 Oracle 的DBMS_SQL包来完成此操作。该数据库应用开发者指南之间有着比较EXECUTE IMMEDIATE你熟悉和dbms_sql方法。 此页面记录DBMS_SQL，但有一些示例（上面链接）应该可以帮助您入门（示例 1 是运行可能具有任意数量绑定变量的语句的简单案例）。 DBMS_SQL从编码的角度来看要麻烦得多，但它可以让你做任何你能想到的事情。

Multiple instances of the bind variable occurring in the SQL are allowed. However, you will have to know the name being used as the bind variable (e.g. :var in your case) in order to pass it into DBMS_SQL.BIND_VARIABLE.

允许在 SQL 中出现绑定变量的多个实例。但是，您必须知道用作绑定变量的名称（例如 :var 在您的情况下）才能将其传递到DBMS_SQL.BIND_VARIABLE.

回答by Karl Bartel

You could also work around this problem by using a WITHstatement. Generally using DBMS_SQLis better, but sometimes this is a simpler way:

您还可以通过使用WITH语句来解决此问题。通常使用DBMS_SQL更好，但有时这是一种更简单的方法：

BEGIN
    EXECUTE IMMEDIATE 'WITH var AS (SELECT :var FROM dual) SELECT SYSDATE FROM DUAL WHERE (SELECT * FROM var) = (SELECT * FROM var)' USING 1;
END;

回答by Vincent Malgrat

This Thread on AskTomcovers the subject in details.

AskTom 上的此主题详细介绍了该主题。

In your case if you want to pass one parameter or none, you could build two queries that have a single parameter and in one of these query it is not used (i-e the predicate is always true) like this:

在您的情况下，如果您想传递一个参数或不传递一个参数，您可以构建两个具有单个参数的查询，并且在其中一个查询中不使用它（即谓词始终为真），如下所示：

-- query1
SELECT * FROM DUAL WHERE dummy = :x;

-- query2
SELECT * FROM DUAL WHERE nvl(:x, 1) IS NOT NULL;

You can probably refine the predicate so that the optimizer will understand that it is always true.

您可能可以细化谓词，以便优化器了解它始终为真。

回答by Theo

One can use dbms_sqllike Steve Broberg explained but the resulting cursor can't be consumed (read) in a lot of clients. Oracle 11 has added a conversion function (dbms_sql.to_refcursor) that makes it possible to convert a dbms_sqlcursor to a ref cursor but for some reason one can't consume this converted ref cursor in a .Net application. One can consume a normal ref cursor in .net but not a ref cursor that used to be dbms_sqlcursor.

可以dbms_sql像 Steve Broberg 解释的那样使用，但是在很多客户端中无法使用（读取）生成的游标。Oracle 11 添加了一个转换函数 ( dbms_sql.to_refcursor)，可以将dbms_sql游标转换为引用游标，但由于某种原因，无法在 .Net 应用程序中使用此转换后的引用游标。在 .net 中可以使用普通的 ref 游标，但不能使用曾经是dbms_sql游标的 ref游标。

So what kind of client will be consuming this cursor?

那么什么样的客户端会消耗这个游标呢？

回答by John

More specifically, I need to pass one parameter into the unknown SQL but I do not know how often it will be used there.

更具体地说，我需要将一个参数传递给未知的 SQL，但我不知道它在那里使用的频率。

I actually ran into this exact same issue a couple of days ago, and a friend shared with me a way to do exactly that with EXECUTE IMMEDIATE.

几天前我实际上遇到了这个完全相同的问题，一位朋友与我分享了一种使用EXECUTE IMMEDIATE.

It involves generating a PLSQL block as opposed to the SQL block itself. When using EXECUTE IMMEDIATEwith a block of PLSQL code, you can bind variables by name as opposed to just by position.

它涉及生成一个 PLSQL 块而不是 SQL 块本身。EXECUTE IMMEDIATE与 PLSQL 代码块一起使用时，您可以按名称而不是仅按位置绑定变量。

Check out my example/code and on my own similar question/answer thread:

查看我的示例/代码和我自己的类似问题/答案线程：

• Assign one bind variable a varying number of times to dynamically generated SQL

• 为动态生成的 SQL 分配不同次数的绑定变量