Oracle 20c - SQL Macros

Imagen de https://blog.sqlora.com/en/

Las macros SQL son nuevas en la base de datos 20c, y permiten a los desarrolladores de SQL encapsular ciertos procesamientos complejos dentro de una macro que luego se usará en cualquier lugar dentro de instrucciones SQL.

Existen dos tipos de macros SQL, de tipo ESCALAR y de tipo TABLA

1. Expresiones ESCALARES utilizadas en SELECT o en clausulas WHERE, HAVING, GROUP BY u ORDER BY
2. Expresiones de TABLA utilizadas en una cláusula FROM

Las macros de SQL se crean mediante funciones PL/SQL, como veremos en los ejemplos posteriores.

Macros de tipo ESCALAR

Proporcionan una sintaxis más flexible generando código SQL reutilizable y transparente para el optimizador de SQL, lo cual trae las siguientes ventajas:

• El optimizador de SQL puede transformar el código para una ejecución eficiente.
• La consulta dentro de la macro puede combinarse con una consulta externa, a diferencia de una función PL/SQL que debe ejecutarse en el contexto de PL/SQL.
• Sin cambio de contexto entre PL/SQL y SQL.
• La consulta dentro de la función PL/SQL ahora se ejecuta bajo la misma snapshot que la consulta externa, evitando inconsistencias.
• Los predicados de la cláusula WHERE que usan macros escalares pueden ser "empujados" para la evaluación de predicados de almacenamiento en Exadata - no es posible con las funciones PL/SQL.

Ejemplo

A continuación vamos a crear una macro mediante la definición de la funcion "fnControlRango", la cual verifica si un valor X pertenece a un rango dado. De ser así, devuelve el valor de X, en caso contrario devuelve el valor mínimo o máximo del rango:

CREATE FUNCTION fnControlRango (minval number, x number, maxval number)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS BEGIN
    RETURN ‘least(greatest(x, minval), maxval)’;
END;

Hay tres cosas que pueden llamar la atención

1. La clausula "SQL_MACRO(SCALAR)" le indica que la función PL/SQL es una Macro, y que es de tipo Escalar.
2. El tipo de datos devuelto es "VARCHAR2", ya que la función no devuelve el resultado de la operación sino el texto SQL a ejecutar.
3. Lo que se devuelve con la sentencia "RETURN" es una cadena de caracteres que representa el texto a usar al momento de ejecutar la sentencia que usa la Macro.

Una vez creada la función, la podemos usar en sentencias SQL de la siguiente manera:

SELECT Nombre, 
       Apellido, 
       LimiteCredito, 
       fnControlRango (10000, LimiteCredito, 20000) AS NuevoLimite1,
       fnControlRango (12000, LimiteCredito, 22000) AS NuevoLimite2,
       fnControlRango (15000, LimiteCredito, 25000) AS NuevoLimite3
FROM Clientes;

Suponiendo que tenemos los siguientes datos:

El resultado que veremos es el siguiente:

La ventaja de usar una Macro en vez de una función PL/SQL, radica en que la misma se "traduce" a código SQL y se ejecuta en el contexto SQL, sin necesidad de hacer un context switch con PL/SQL en cada ejecución. La sentencia que le llega al optimizador es la siguiente:

SELECT Nombre, 
       Apellido, 
       LimiteCredito, 
       least(greatest(LimiteCredito, 10000), 20000) AS NuevoLimite1,
       least(greatest(LimiteCredito, 12000), 22000) AS NuevoLimite2,
       least(greatest(LimiteCredito, 15000), 25000) AS NuevoLimite3
FROM Clientes;

El uso de la macro reduce y simplifica el código que tenemos que escribir, facilita la corrección de la misma (ya que alcanza con modificar la función que define la macro y no todos los lugares donde se emplea) sin perjudicar la performance.

Macros de tipo TABLA

Las macros de tipo Tabla permiten generar:

• Vistas parametrizadas. En las mismas las tablas utilizadas en las consultas están fijas se arreglan dentro de la definición de la macro, y se pasan argumentos para seleccionar filas de esas tablas. El uso común de estas vistas parametrizadas es cuando los argumentos escalares se usan para seleccionar un subconjunto de las filas que luego se agregan

• Vistas polimórficas; En las mismas, los argumentos representan uno o mas nombres de tabla (también puede tener argumentos adicionales de tipo escalar). Las tablas de entrada se utilizan dentro de la consulta devuelta por macro.

Ejemplo de Macro de Tabla usada para vista Parametrizada

A continuación, creamos una función que define una macro de tipo Tabla, que nos devuelve una sentencia SELECT sobre las tablas Regiones y Clientes, filtrando por el ID de la region y a la vez agrupando por el mismo.

CREATE FUNCTION fnLimitePromedio (pID_Region number)
RETURN varchar2 SQL_MACRO(TABLE)
IS 
BEGIN
    RETURN q’[SELECT r.ID_Region, ANY_VALUE(r.Region) AS Region,
                     AVG(c.LimiteCredito) AS CreditoPromedio,
                     COUNT(c.ID_Cliente) AS NroClientes
              FROM Regiones r JOIN Clientes c ON c.ID_Region = r.ID_Region
              WHERE r.ID_Region = :pID_Region
              GROUP BY r.ID_Region]’;
END fnLimitePromedio;

El paso siguiente es usar esta macro como si fuera una vista que acepta un parámetro:

WITH Norte AS
    (SELECT ID_Region
     FROM Regiones
     WHERE ID_Pais = 1)
SELECT *
FROM fnLimitePromedio(Norte);

Ejemplo de Macro de Tabla usada para vista Polimórfica

A continuación, creamos una función que define una macro de tipo Tabla, que nos devuelve una sentencia SELECT sobre una tabla que pasamos como parámetro, limitando la cantidad de filas devueltas mediante el uso de otro parámetro.

CREATE FUNCTION fnPedirNFilas
(
    pTabla DBMS_TF.Table_t,
 pFilas NUMBER DEFAULT 10
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
AS
BEGIN
RETURN q’[SELECT *
          FROM pTabla
          WHERE rownum <= pFilas’;
END fnPedirNFilas;

El paso siguiente es usar esta macro como si fuera una vista normal, pasando el nombre de la tabla y la cantidad de filas como parámetros:

SELECT * 
FROM fnPedirNFilas(pTabla=>Clientes, pFilas=> 20);

En este caso, al optimizador le llega una consulta de la siguiente forma:

SELECT * 
FROM (SELECT * FROM Clientes WHERE rownum <= 20);