Início / dba / Perguntas / 291616
Accepted
Pantea
Asked: 2021-05-16 03:35:24 +0800 CST

Como resolver o erro "ORA:01-006 :bind variable not exist" ao trabalhar com variáveis ​​bind?

  • 772

Eu tenho uma tabela com estrutura e dados abaixo:

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

Dados de amostra:

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

Este é o meu procedimento de exemplo com o qual estou tentando usar o 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;

O problema é que quando todos os parâmetros de entrada têm um valor, o procedimento funciona corretamente, sem nenhum erro, mas quando apenas um ou dois parâmetros têm valor, recebo este erro: ORA-01006: bind variable does not exist,. Como posso resolver este problema? Alguns parâmetros podem ter valor e outros não.

desde já, obrigado

oracle oracle-11g-r2

3 respostas

  1. Best Answer

    Você pode evitar completamente o SQL dinâmico (você também nunca deve nomear as variáveis ​​PL/SQL da mesma forma que as colunas da tabela):

    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. Certifique-se de levar em conta a precedência do operador, ao contrário de mim.

    • 5

  2. Você não pode enviar variáveis ​​de associação dinamicamente para a consulta dinâmica. Em vez disso, você terá que codificar as variáveis ​​de ligação na mesma ordem, não importa o que aconteça, você pode usar um truque para que elas sejam ignoradas.

    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;

    Isso funciona porque

    (1=1 or :1 is null)

    Sempre retornará true ( 1=1), e ainda permite que você passe uma variável de ligação no local correto. A avaliação de curto-circuito será acionada para que o otimizador possa ignorar isso com segurança ao elaborar o melhor plano.

    • 2

  3. Você não deve poder chamar esse procedimento sem fornecer todos os parâmetros, pois não possui padrões.

    • -1

relate perguntas