可能具有可变数量的绑定的Oracle EXECUTE IMMEDIATE吗? [英] Oracle EXECUTE IMMEDIATE with variable number of binds possible?

问题描述

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

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.

是否可以通过某种方式在对EXECUTE IMMEDIATE的调用中使用可变数量的绑定变量?

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

更具体地说，我需要将一个参数传递到未知的SQL中，但我不知道该参数将在其中多久使用一次.

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

我尝试过类似的事情

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

但是它又被ORA-01008: not all variables bound.

推荐答案

您不能使用EXECUTE IMMEDIATE执行此操作.但是，您可以通过使用Oracle的DBMS_SQL软件包来执行此操作. 《数据库应用程序开发人员指南》 具有您熟悉的EXECUTE IMMEDIATE与dbms_sql方法之间的比较. 此页面文档DBMS_SQL ，但其中有一些示例(上面已链接)可以帮助您入门(示例1是运行一条可以具有任意数量的绑定变量的语句的简单示例).从编码的角度来看，DBMS_SQL繁琐得多，但是它使您可以做几乎可以想到的任何事情.

You can't do this with EXECUTE IMMEDIATE. However, you can do this by using Oracle's DBMS_SQL package. The Database Application Developer's Guide has a comparison between the EXECUTE IMMEDIATE you're familiar with and dbms_sql methods. This page documents 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_SQL is a lot more cumbersome from a coding perspective, but it will allow you to do just about anything you can conceive.

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

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.

这篇关于可能具有可变数量的绑定的Oracle EXECUTE IMMEDIATE吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！