主页 / dba / 问题 / 291616
Accepted
Pantea
Asked: 2021-05-16 03:35:24 +0800 CST

使用绑定变量时如何解决错误“ORA：01-006：绑定变量不存在”？

  • 772

我有一个具有以下结构和数据的表：

create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

样本数据：

item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

这是我尝试使用的示例程序bind variables。

create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

问题是当所有输入参数都有值时，程序正常工作，没有任何错误，但是当只有一个或两个参数有值时，我收到这个错误：ORA-01006: bind variable does not exist,。我怎么解决这个问题？有些参数可能有值，有些可能没有。

提前致谢

oracle oracle-11g-r2

3 个回答

  1. Best Answer

    您可以完全避免使用动态 SQL（您也不应该将 PL/SQL 变量命名为与表列相同的名称）：

    create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               p_item_number varchar2,
                                               p_item_name   varchar2,
                                               p_item_col    varchar2,
                                               p_item_pol    varchar2) is

  qry varchar2(8000);

begin

  open res for select * from test_table where
    (p_item_number is null or p_item_number = item_number) and
    (p_item_name is null or p_item_name = item_name) and
    (p_item_col is null or p_item_col = item_col) and
    (p_item_pol is null or p_item_pol = item_pol)

end;

    PS。与我不同，确保您考虑运算符的优先级。

    • 5

  2. 您不能将绑定变量动态发送到动态查询。相反，无论如何，您都必须以相同的顺序在绑定变量中编码，您可以使用一个技巧，以便它们被忽略。

    create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  else
    qry := qry || ' and (1=1 or :1 is null) ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  else
    qry := qry || ' and (1=1 or :2 is null) ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  else
    qry := qry || ' and (1=1 or :3 is null) ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  else
    qry := qry || ' and (1=1 or :4 is null) ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

    这有效，因为

    (1=1 or :1 is null)

    将始终返回 true ( 1=1)，它仍然允许您在正确的位置传递绑定变量。将触发短路评估，以便优化器在制定最佳计划时可以安全地忽略它。

    • 2

  3. 如果没有提供所有参数，则不应调用该过程，因为您没有默认值。

    • -1

相关问题