在Oracle PL/SQL中重新使用绑定变量

我有一个庞大的SQL语句，其中包含不断重复使用代码的联合。我希望知道是否有一种方法可以重复使用单个绑定变量，而无需多次将变量重复为"USING"。

下面的代码返回"并非所有变量都绑定"，直到我将"USING"行更改为"USING VAR1，VAR2，VAR1；"

我希望避免这种情况，因为我指的是：1在这两种情况下——有什么想法吗？

declare
var1 number :=1;
var2 number :=2;
begin
execute immediate '
select * from user_objects 
where 
rownum = :1
OR rownum = :2  
OR rownum = :1 '
using var1,var2;
end;
/

编辑：为了获得更多信息，我正在使用动态SQL，因为我还生成了一个where条件的捆绑包。

我不太擅长SQL数组（我在代码中使用光标，但我认为这会使问题过于复杂），但伪代码是：

v_where varchar2(100) :='';
FOR i in ('CAT','HAT','MAT') LOOP
  v_where := v_where || ' OR OBJECT_NAME LIKE ''%' || i.string ||'%''
END;
  v_where := ltrim(v_where, ' OR');

然后将上面的SQL修改为：

execute immediate '
select * from user_objects 
where 
rownum = :1
OR rownum = :2  
OR rownum = :1 AND ('||V_WHERE||')'
using var1,var2;

您可能会考虑一些选项，尽管它们可能需要更改执行SQL语句的方式或SQL语句本身。

使用DBMS_SQL而不是EXECUTE IMMEDIATE--DBMS_SQL（请参阅http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm)比EXECUTE IMMEDIATE更难使用，但可以让您更好地控制流程，包括（通过DBMS_SQL.BIND_VARIABLE和DBMS_SQL.BIND_ARRAY）按名称而不是按位置绑定的能力
将EXECUTE IMMEDIATE与WITH子句一起使用--您可以将查询重组为使用WITH子句，该子句在开始时收集子查询中的绑定变量，然后在需要时联接到子查询（而不是直接引用绑定变量）。它可能看起来像这样

with your_parameters as 
    (select :1 as p1, :2 as p2 from dual) 
select * 
from your_table, your_parameters 
where your_table.some_column1 = your_parameters.p1 
  and your_table.some_column2 <= your_parameters.p1 
  and your_table.some_column3 = your_parameters.p2

这可能会影响查询的性能，但这可能是一个可以接受的折衷方案。

不要使用动态SQL--当然，如果不需要动态SQL，也不需要使用EXECUTE IMMEDIATE，因此"仅按位置绑定"限制不适用。你确定你真的需要使用动态SQL吗

编辑：如果你使用动态SQL是因为你有一个可变数量的OR条件，就像你在编辑中发布的那样，你可以通过执行以下操作之一来避免使用动态SQL：

如果OR条件来自表（或查询）--加入该表（或询问），而不是使用OR条件列表。例如，如果CAT、HAT和MAT列在名为YOUR_CRITERIA_TABLE的表中名为YOUR_CRITERIA的列中，则可以将YOUR_CRITERIA_TABLE添加到FROM子句中，并将WHERE子句中的OBJECT_NAME LIKE '%CAT% OR OBJECT_NAME LIKE '%MAT% OR OBJECT_NAME LIKE '%HAT% OR OBJECT_NAME LIKE '%MAT%替换为类似OBJECT_NAME LIKE '%' || YOUR_CRITERIA_TABLE.YOUR_CRITERIA || '%'.的内容
否则，您可能会将条件放在全局临时表中--如果您的条件不是来自表（或查询），您可以（在设计时，而不是在运行时）创建一个全局临时表来保存这些条件，然后在运行时，将条件插入全局临时表，然后如第1项所述连接到它
或者，您可以将条件放在嵌套表中--这与项2类似，只是使用嵌套表（使用CREATE TYPE...IS TABLE OF创建的表）而不是全局临时表。您可以创建或拥有嵌套表类型，也可以使用像SYS.ODCIVARCHAR2LIST这样的内置表类型。在PL/SQL中，您将填充这种类型的变量，然后像第1项中的"真实"表一样使用它

项目3的一个例子可能看起来像：

DECLARE
    tblCriteria SYS.ODCIVARCHAR2LIST;
BEGIN
    tblCriteria := SYS.ODCIVARCHAR2LIST();
    -- In "real" code you might populate the nested table in a loop.
    -- This example populates it explicitly so that it will compile.  For the
    -- purpose of the example, we could have populated the nested table in 
    -- a single statement:
    -- tblCriteria := SYS.ODCIVARCHAR2LIST('CAT', 'HAT', 'MAT');
    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'CAT';
    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'HAT';
    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'MAT';
    FOR rec IN
    (
        SELECT
            USER_OBJECTS.*
        FROM
            USER_OBJECTS,
            TABLE(tblCriteria) YOUR_NESTED_TABLE
        WHERE
            USER_OBJECTS.OBJECT_NAME LIKE '%' || YOUR_NESTED_TABLE.COLUMN_VALUE || '%'
    )
    LOOP
        -- Do something.  For example, print out the object name.
        DBMS_OUTPUT.PUT_LINE(rec.OBJECT_NAME);
    END LOOP;
END;

否，遗憾的是，EXECUTE IMMEDIATE的绑定变量必须按照它们在语句中出现的顺序提供，并且绑定变量名称将被忽略。所以你只需要在你的陈述中有：1、：2和：3。

相关内容

最新更新

热门标签：