SQL Macros: Introdução, Funcionalidades e Exemplos

SQL Macros são uma nova funcionalidade introduzida no Oracle Database 19c, versão 19.7, que permite criar expressões e instruções SQL reutilizáveis e parametrizáveis que podem ser usadas em outras instruções SQL. SQL Macros são funções PL/SQL anotadas com a cláusula SQL_MACRO, que devem retornar um tipo VARCHAR2, CHAR ou CLOB contendo um fragmento de código SQL. SQL Macros podem ser de dois tipos: macros de tabela e macros de escalar.

Macros de tabela são expressões que podem ser usadas em uma cláusula FROM de uma consulta, como se fossem uma espécie de visão polimórfica (parametrizada). Macros de tabela podem receber como parâmetros valores escalares ou tipos de tabela definidos pelo pacote DBMS_TF. Macros de tabela permitem encapsular lógicas complexas ou comuns em uma função que pode ser invocada em diferentes contextos, aumentando a produtividade, a colaboração e a qualidade do código .

Macros de escalar são expressões que podem ser usadas em qualquer lugar onde uma expressão SQL é permitida, como em uma cláusula SELECT, WHERE, GROUP BY, HAVING, ORDER BY, etc. Macros de escalar podem receber como parâmetros valores escalares ou tipos de coleção definidos pelo pacote DBMS_TF. Macros de escalar permitem simplificar expressões longas ou repetitivas em uma função que pode ser invocada em diferentes contextos, facilitando a leitura e a manutenção do código.

Exemplos de SQL Macros

A seguir, são apresentados alguns exemplos de SQL Macros e como usá-los em consultas SQL.

Exemplo 1: Macro de tabela para retornar os primeiros n registros de uma tabela

Este exemplo cria uma macro de tabela chamada take, que recebe como parâmetros um número n e um tipo de tabela t, e retorna os primeiros n registros da tabela t. A macro de tabela usa a cláusula FETCH FIRST para limitar o número de registros retornados.

CREATE FUNCTION take (n NUMBER, t DBMS_TF.TABLE_T) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'SELECT * FROM t FETCH FIRST take.n ROWS ONLY';
END;
/

Esta consulta mostra como usar a macro de tabela take para obter os primeiros dois registros da tabela dept.

SELECT * FROM take (2, dept);

Resultado

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS

Exemplo 2: Macro de escalar para calcular o fatorial de um número

Este exemplo cria uma macro de escalar chamada fact, que recebe como parâmetro um número n e retorna o fatorial de n. A macro de escalar usa uma expressão recursiva comum para calcular o fatorial.

CREATE FUNCTION fact (n NUMBER) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'WITH fact (n, f) AS (
            SELECT 1, 1 FROM DUAL
            UNION ALL
            SELECT n + 1, (n + 1) * f FROM fact WHERE n < fact.n
          )
          SELECT f FROM fact WHERE n = fact.n';
END;
/

Esta consulta mostra como usar a macro de escalar fact para obter o fatorial de 5.

SELECT fact (5) FROM DUAL;

Resultado

FACT(5)
120

Exemplo 3: Macro de tabela para retornar os registros de uma tabela que contêm uma palavra-chave

Este exemplo cria uma macro de tabela chamada search, que recebe como parâmetros uma palavra-chave k e um tipo de tabela t, e retorna os registros da tabela t que contêm a palavra-chave k em alguma coluna. A macro de tabela usa a função DBMS_TF.COLUMN_NAMES para obter os nomes das colunas da tabela t, e a função DBMS_TF.UTIL.FORMAT_FILTER_PREDICATE para construir uma condição de filtro dinâmica.

CREATE FUNCTION search (k VARCHAR2, t DBMS_TF.TABLE_T) RETURN VARCHAR2 SQL_MACRO IS
  v_cols DBMS_TF.COLUMN_ARRAY;
  v_filter VARCHAR2(4000);
BEGIN
  v_cols := DBMS_TF.COLUMN_NAMES(t);
  FOR i IN 1 .. v_cols.COUNT LOOP
    v_filter := v_filter || 'UPPER(' || v_cols(i) || ') LIKE ''%' || UPPER(k) || '%'' OR ';
  END LOOP;
  v_filter := RTRIM(v_filter, ' OR ');
  RETURN 'SELECT * FROM t WHERE ' || DBMS_TF.UTIL.FORMAT_FILTER_PREDICATE(v_filter);
END;
/

Esta consulta mostra como usar a macro de tabela search para obter os registros da tabela emp que contêm a palavra-chave ‘MANAGER’ em alguma coluna.

SELECT * FROM search ('MANAGER', emp);

Resultado

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO
7566	JONES	MANAGER	7839	1981-04-02	2975	20
7698	BLAKE	MANAGER	7839	1981-05-01	2850	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	10
7839	KING	PRESIDENT		1981-11-17	5000

Exemplo 4: Macro de escalar para formatar um número como moeda

Este exemplo cria uma macro de escalar chamada currency, que recebe como parâmetro um número n e retorna uma string formatada como moeda, usando o símbolo R$ e duas casas decimais. A macro de escalar usa a função TO_CHAR para converter o número em uma string com o formato desejado.

CREATE FUNCTION currency (n NUMBER) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'TO_CHAR(n, ''R$FM999G999D99'')';
END;
/

Esta consulta mostra como usar a macro de escalar currency para obter o salário dos empregados formatado como moeda.

SELECT ename, currency (sal) AS sal FROM emp;

Resultado

ENAME	SAL
SMITH	R$800,00
ALLEN	R$1.600,00
WARD	R$1.250,00
JONES	R$2.975,00
MARTIN	R$1.250,00
BLAKE	R$2.850,00
CLARK	R$2.450,00
SCOTT	R$3.000,00
KING	R$5.000,00
TURNER	R$1.500,00
ADAMS	R$1.100,00
JAMES	R$950,00
FORD	R$3.000,00
MILLER	R$1.300,00

Conclusão

SQL Macros são uma poderosa ferramenta para criar expressões e instruções SQL dinâmicas, reutilizáveis e parametrizáveis, que podem melhorar a produtividade, a colaboração e a qualidade do código dos desenvolvedores Oracle PL/SQL.

Espero que este artigo tenha sido útil e interessante para você.

Abs

Referências

SQL Macros: Introdução, Funcionalidades e Exemplos