Oracle: SQL_MACRO - Adicionando condicionais ao gerar o macro

 Estou continuando meus estudos sobre SQL_MACRO no Oracle. Ainda não consegui achar uma solução elegante para o IN sem que envolva gambiarra. Mas isso deixo para atualizar o referido post. Hoje quero registrar como é possível tratar os parâmetros para gerar o macro.

Neste primeiro caso, só para ilustrar, vou mostrar um parâmetro que se for 0, o WHERE será diferente:

CREATE OR REPLACE FUNCTION teste_function(obj_chave number) RETURN VARCHAR2 SQL_MACRO IS BEGIN RETURN 'SELECT * FROM tabela WHERE '|| CASE WHEN obj_chave=0 THEN 'FOTR_CD_CHAVE=''X''' ELSE 'FOTR_CD_CHAVE=''Y''' END; END;

Basicamente concatenamos a expressão com uma estrutura condicional. Também podemos usar funções que faça condicional, como o NVL (é até mais recomendável, já que fica mais limpo o código)

Oracle: Função, Macro e Gambiarra para passar uma lista de palavras por parâmetro em uma função

 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.