Então, estou responsável por adaptar algumas views de um banco de dados Oracle, e possui mais de 20 views que basicamente, tem a mesma consulta em todas elas, mudando apenas alguns valores usados no WHERE da consulta. Basicamente seria algo como, mas muito mais complexo do que o exemplo que passo:
View 1:
SELECT * FROM cliente WHERE cidade = 'Joinville' AND bairro IN ('Bom Retiro', 'América', 'Floresta')
View 2:
SELECT * FROM cliente WHERE cidade = 'Curitiba' AND bairro IN ('Centro', 'Sítio Cercado', 'Santa Felicidade')
E assim por diante.
É uma boa oportunidade para fazer uma refatoração, né? Minha intenção é criar uma função e criar um macro para facilitar. Para criar um macro, podemos fazer o seguinte:
CREATE OR REPLACE FUNCTION clientesPorRegiao (tx_cidade VARCHAR2)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN 'SELECT * FROM cliente WHERE cidade = tx_cidade';
END;
Se nós fazermos um
SELECT * from clientesPorRegiao('Joinville')
e
SELECT * from clientesPorRegiao('Curitiba')
Temos o mesmo efeito do que se tivessemos feito as views, só que sem o filtro do bairro, ou seja, o primeiro vai vir os clientes de Joinville e o segundo, os clientes de Curitiba. Mas e o Bairro? Você me pergunta... digamos que a cláusula IN não é muito amigável nas funções e não existe no Oracle, nenhuma forma direta de passar uma lista como é feito no exemplo da View 1 e View 2, ou seja, não existe uma maneira direta de termos um clientesPorRegiao('Joinville',('Bom Retiro', 'América', 'Floresta')). Entretanto, se estou postando algo, é que achei uma solução que seja prático, correto? Correto!
Então, vamos por partes, a criação da função, o parâmetro para bairro será um texto VARCHAR2 que nem o primeiro parâmetro:
CREATE OR REPLACE FUNCTION clientesPorRegiao (tx_cidade VARCHAR2, tx_bairro VARCHAR2)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN 'SELECT * FROM cliente WHERE cidade = tx_cidade AND bairro IN (tx_bairro)';
END;
Intuitivamente, a vontade é colocar o parâmetro como coloquei acima, mas já aviso que não vai dar certo. Ao invés disso, vamos substituir o "bairro IN (tx_bairro)" pela função instr, desta forma:
CREATE OR REPLACE FUNCTION clientesPorRegiao (tx_cidade VARCHAR2, tx_bairro VARCHAR2)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN 'SELECT * FROM cliente WHERE cidade = tx_cidade AND instr(tx_bairro, '',''||bairro||'','')>0';
END;
E o que faz essa função instr? Segundo a documentação da Oracle, da forma que usamos, ele vai procurar no primeiro parâmetro se há alguma ocorrência do segundo parâmetro. No nosso contexto, ele irá procurar no parametro tx_bairro, se existe alguma substring que tenha o valor do campo bairro e retornar a quantidade desta ocorrência, e sendo essa quantidade maior que zero, então está incluso na lista. Entretanto, temos uma pegadinha, repare que nós concatenamos ',' na frente e na parte de trás do bairro, fiz isso para garantir que não ocorra problemas como nomes de bairro tipo 'Santa' e acabar validando 'Santana'. Mas isso, vai afetar a forma que vamos passar esta lista, que será desta forma:
SELECT * from clientesPorRegiao('Joinville',',Bom Retiro,América,Floresta,');
Repare que a lista ele começa com vírgulas e termina com vírgulas, justamente para fazer com que as palavras sejam reconhecida como um todo e não parte. Então, esta solução não serve para lista de textos que contenham vírgulas.
Então, é isso, se conhecer alguma solução melhor, estou bem aberto para ouvir.
