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.

Oracle: Query para pesquisar nomes de tabela

 Para listar todas as colunas criadas e existente no Oracle, pode-se utilizar a seguinte query:

SELECT * FROM ALL_TAB_COLUMNS

Essa tabela possui as seguintes colunas:

• OWNER: O usuário "dono" da tabela
• TABLE_NAME: Nome da tabela em que a coluna pertence
• COLUMN_NAME: Nome da Coluna
• DATA_TYPE:
• DATA_TYPE_MOD:
• DATA_TYPE_OWNER:
• DATA_LENGTH:
• DATA_PRECISION:
• DATA_SCALE:
• NULLABLE:
• COLUMN_ID:
• DEFAULT_LENGTH:
• DATA_DEFAULT:
• NUM_DISTINCT:
• LOW_VALUE:
• HIGH_VALUE:
• DENSITY:
• NUM_NULLS:
• NUM_BUCKETS:
• LAST_ANALYZED:
• SAMPLE_SIZE:
• CHARACTER_SET_NAME:
• CHAR_COL_DECL_LENGTH:
• GLOBAL_STATS:
• USER_STATS:
• AVG_COL_LEN:
• CHAR_LENGTH:
• CHAR_USED:
• V80_FMT_IMAGE:
• DATA_UPGRADED:
• HISTOGRAM:
• DEFAULT_ON_NULL:
• IDENTITY_COLUMN:
• EVALUATION_EDITION:

Oracle: Tratando valores nulos: COALESCE e NVL

 Estava eu trabalhando com a seguinte situação: precisava que um dado filtrasse por um campo data comparado com outros dois campos data. No clássico SQL ficaria algo assim:

WHERE campo1 BETWEEN campo2 AND campo3

Só que o campo3 tem um detalhe, ele é nulo se não tem uma data definida, o que significaria no contexto, uma data futura indefinida. Como tratar isso?

Temos duas funções que faz a mesma coisa, o COALESCE e NVL, ele avalia o primeiro parâmetro, se for nulo, passa para o próximo. Exemplo:

WHERE campo1 BETWEEN campo2 AND COALESCE(campo3, SYSDATE)

WHERE campo1 BETWEEN campo2 AND NVL(campo3, SYSDATE)

Existe alguma diferença entre as duas? Sim, COALESCE permite mais de dois parâmetros, então, vamos supor que tenha um campo4, faríamos:

WHERE campo1 BETWEEN campo2 AND COALESCE(campo3, campo4, SYSDATE)

Neste último, se campo3 é nulo, é verificado o campo4, e se for também nulo, irá para SYSDATE. E se quisermos colocarmos um campo5, campo6, campo7, ..., campoN, podemos.

Já o NVL tem uma outra função chamado NVL2 que realiza a mesma função, mas para 3 parâmetros.

WHERE campo1 BETWEEN campo2 AND NVL2(campo3, campo4, SYSDATE)

E é isso por hoje.