Menu de navegação

[Carlos Henrique Paiffer de SouzaParticipante]

Boa tarde.

Alguém sabe uma maneira de passar uma “lista/collection” de bind variable ao trabalhar com sql dinâmico utilizando cursor?

Exemplificando meu problema, tenho a procedure abaixo, onde há comentários para exemplificar e questionar melhor o problema.

create or replace procedure pr_teste(
pAtivo number := 1,
pCampo2 number,
pCampo3 varchar2,
pCampo4 varchar2,
pCursor in out SYS_REFCURSOR,
) is
vQuery varchar2(1000);
vWhere varchar2(1000) := ”;
begin

vQuery := ‘select * from clientes where ativo = :ativo ‘;

if pCampo2 > 0 then
vWhere := vWhere || ‘ and campo2 = :campo2 ‘;
end if;

if pCampo3 is not null then
vWhere := vWhere || ‘ and campo3 = :campo3 ‘;
end if;

if pCampo4 is not null then
vWhere := vWhere || ‘ and campo4 = :campo4 ‘;
end if;

— Se nenhum parâmetro for informado, com exceção do primeiro, que é obrigatório, eu tenho o seguinte comando para me retornar o cursor:
if pCampo2 = 0 and pCampo3 is null and pCampo4 is null then
open pCursor for vQuery || vWhere
using pAtivo;
— Se APENAS o campo2 for informado, o comando para retornar o cursor precisa de 2 “itens” no “using”:
elsif pCampo2 > 0 and pCampo3 is null and pCampo4 is null then
open pCursor for vQuery || vWhere
using pAtivo, pCampo2;
— Se o campo2 + campo3 forem informados, o comando para retornar o cursor precisa de 3 “itens” no “using”:
elsif pCampo2 > 0 and pCampo3 is not null and pCampo4 is null then
open pCursor for vQuery || vWhere
using pAtivo, pCampo2, pCampo3;
— Se o campo2 + campo3 + campo 4 forem informados, o comando para retornar o cursor precisa de 4 “itens” no “using”:
elsif pCampo2 > 0 and pCampo3 is not null and pCampo4 not is null then
open pCursor for vQuery || vWhere
using pAtivo, pCampo2, pCampo3 , pCampo4;
— continuando os IF´s, seria necessário combinar as outras possibilidades ainda, como, “pcampo2 = 0 and pcampo3 is not null and pcampo 4 is null” OU
— “pcampo2 = 0 and pcampo3 is not null and pcampo 4 is not null” OU “pcampo2 = 0 and pcampo3 is null and pcampo 4 is not null”
end if;

— … agora, o meu caso real, eu tenho OITO parâmetros opcionais que podem ser combinados. é inviável trabalhar com tantas condições.
— … Gostaria de alguma opção que eu pudesse ir adicionado os possíveis “binds” já em uma collection e depois, ao abrir o cursor, usar esta collection na clausula using.
— … algo tipo:

if pCampo2 > 0 then
vWhere := vWhere || ‘ and campo2 = :campo2 ‘;
<> := pCampo2;
end if;

if pCampo3 is not null then
vWhere := vWhere || ‘ and campo3 = :campo3 ‘;
<> := pCampo3;
end if;

if pCampo4 is not null then
vWhere := vWhere || ‘ and campo4 = :campo4 ‘;
<> := pCampo4;
end if;

open pCursor for vQuery || vWhere
using <>

end;
/

Pesquisei muito a respeito e não encontrei nada.

Agradeço a ajuda.

abs,

[José Laurindo ChiappaModerador]

Blz, colega ? Então, respondendo diretamente à sua pergunta, até onde sei usando SQL dinâmico via OPEN CURSOR (como é o seu caso) ou usando via EXECUTE IMMEDIATE, rigorosamente Não Tem como vc fazer bind de um array : iirc a única rotina de SQL dinâmico que tem essa capacidade (e muitas outras!!) é a package DBMS_SQL… Ela é mais complexa de se usar mas tem muuuuuuitas capacidades e recursos extras que nem EXECUTE IMMEDIATE nem OPEN CURSOR/REF CURSOR te dão…
Aqui ro seu caso eu Acredito que vc poderia usar ou a DBMS_SQL.BIND_ARRAY ou a DBMS_SQL.BIND_VARIABLE, onde vc criaria uma bind variable para cada posição do array, tipo… Eu não tenho um exemplo direto aqui comigo mas dá uma estudada na Documentação (online em http://docs.oracle.com : se for versão 10g é o link direto, mas nessa URL vc encontra pra todas elas) e veja alguns exemplinhos em https://stackoverflow.com/questions/24568038/array-binding-for-select-statement-in-dbms-sql-oracle-11g, http://www.java2s.com/Code/Oracle/System-Packages/Usedbmssqlbindarraytobindarraytoacursor.htm, https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7804873400346193917 e https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3687642838607 ….

===>>> Porém, é MUITO IMPORTANTE frisar que a resposta acima é a resposta para a sua pergunta sobre SQL dinâmico : **** DE FORMA ALGUMA **** usar SQL dinâmico , porém (que fique CLARO!!) é a melhor e mais performática técnica…..
INCLUSIVE, neste seu caso ao que entendi vc só vai ter diferença no WHERE , cfrme um ou outro valor dos oito possíveis seja preenchido , né ?? Sendo isso, é uma ***** ASNICE SEM TAMANHO ******* vc querer abrir cursores diferentes com WHERE diferentes – além de ficar Extremamente mais complexo de programar, eu IMAGINO que vc saiba que o RDBMS Oracle faz cache dos seus SQLs baseados no TEXTO EXATO que vc enviou ao banco…
Sendo assim ,na sua lógica, ao que entendi se numa dada execução se preencher valor pro parãmetro de busca 1 o texto que vai ser enviado pro banco vai ser :

select * from clientes where ativo = :ativo and campo1 = :campo1;

Se depois alguém executar essa mesma rotina preenchendo o argumento de pesquisa 2 aí o texto a ser enviado pro banco vai ser :

select * from clientes where ativo = :ativo and campo2 = :campo2;

===> VEJA que os dois textos são COMPLETAMENTE DIFERENTES, aí necessariamnte CADA TEXTO vai ocupar uma posição diferente no cache, vai ser feito PARSE, vai ser gerado um Plano de Execução potencialmente diferente para CADA TEXTO….. Isso tem SIM um Custo em termos de performance, sim sim sim ???? Tá LONGE de ser uma solução Escalável e performática por causa de vc estar gerando múltiplos textos de SQL diferentes entre si….

Sorry, mas se EU tivesse essa necessidade E SE vc sabe a quantidade máxima de argumentos de pesquisa (8 pelo que vc falou), E o argumento 1 é pra coluna 1, o argumento 2 é pra coluna 2, etc, assim por diante, eu faria uma *** PROCEDURE *** que aceitaria 8 parâmetros de entrada e codificaria a query tipo (PSEUDOCÓDIGO aqui, nem pense em compilar – é Um Exemplinho apenas) :

CREATE OR REPLACE PROCEDURE PROC_CONSULTA_CLIENTES (P_ATIVO IN NUMBER,
P_WHERE1 IN CLIENTES.COLUNA1%TYPE,
P_WHERE2 IN CLIENTES.COLUNA2%TYPE,
P_WHERE3 IN CLIENTES.COLUNA3%TYPE,
P_WHERE4 IN CLIENTES.COLUNA4%TYPE,
P_WHERE5 IN CLIENTES.COLUNA5%TYPE,
P_WHERE6 IN CLIENTES.COLUNA6%TYPE,
P_WHERE7 IN CLIENTES.COLUNA7%TYPE,
P_WHERE8 IN CLIENTES.COLUNA8%TYPE,
P_ROW_CLIENTE IN CLIENTES%TYPE,
P_RECORDSET OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_recordset FOR
SELECT * FROM CLIENTES
WHERE ATIVO = :P_ATIVO
AND COLUNA1 = NVL(:P_WHERE1, COLUNA1)
AND COLUNA2 = NVL(:P_WHERE2, COLUNA2)
AND COLUNA3 = NVL(:P_WHERE3, COLUNA3)
AND COLUNA4 = NVL(:P_WHERE4, COLUNA4)
AND COLUNA5 = NVL(:P_WHERE5, COLUNA5)
AND COLUNA6 = NVL(:P_WHERE6, COLUNA6)
AND COLUNA7 = NVL(:P_WHERE7, COLUNA7)
AND COLUNA8 = NVL(:P_WHERE8, COLUNA8)
ORDER BY condiçãodeordenação;
END PROC_CONSULTA_CLIENTES;
/

===>> Com iso eu consigo o resultado desejado (ie, pesquiso filtrando por qualquer uma das 8 colunas possíveis) MAS ** EVITEI ** as complexidades do SQL dinâmico (compara o código acima com o emaranhado de IFs que vc estava fazendo, isso NÂO FAZ SENTIDO!!), , E *** AINDA POR CIMA ***, como Bônus Adicional, o texto que vai ser enviado pro banco é sempre o mesmo, então ele VAI SER CACHEADO de boa….

Blz ???

[]s

Chiappa

[Autor]

Posts