Macros de SQL en Oracle Database 21c

Hola a todos. Hoy queríamos analizar con vosotros una de las nuevas funcionalidades de Oracle 21C (aunque ya empezamos a verla desde la 20 con limitaciones), las macros de SQL en Oracle.

Las macros SQL mejoran la reutilización del código al etiquetar expresiones y sentencias comunes en componentes que podemos reutilizar posteriormente.

macros sql en oracle gpsos

La mejor forma de ver su utilización es mediante un ejemplo. Para ello, necesitamos los siguientes objetos de base de datos:

Tabla de departamento:

create table departamento (
  id_departamento number(2) constraint pk_departamento primary key,
  nombre varchar2(14),
  ciudad varchar2(13)
) ; 

Tabla de empleado:

create table empleado (
  id_empleado number(4) constraint pk_emp primary key,
  empleado varchar2(10),
  puesto varchar2(9),
  manager number(4),
  hiredate date,
  salario number(7,2),
  comm number(7,2),
  id_departamento number(2) constraint fk_departamento references departamento
); 

Una vez creadas las tablas. Las rellenamos con datos. Empezando con «departamento»

insert into departamento values (10,'CONTABILIDAD','MADRID');
insert into departamento values (20,'INFORMATICA','BARCELONA');
insert into departamento values (30,'VENTAS','VALENCIA');
insert into departamento values (40,'OPERACIONES','SEVILLA'); 

Y seguimos con «empleado»

insert into empleado values (7369,'LOPEZ','PROGRAMADOR',7902,to_date('17-12-1980','dd-mm-yyyy'),1200,NULL,20);
insert into empleado values (7499,'ALLEN','VENDEDOR',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into empleado values (7521,'MARTINEZ','VENDEDOR',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into empleado values (7566,'RODRIGUEZ','GESTOR',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into empleado values (7654,'CANO','VENDEDOR',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into empleado values (7698,'GARRIDO','GESTOR',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into empleado values (7782,'SANCHEZ','GESTOR',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into empleado values (7788,'SCOTT','ANALISTA',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into empleado values (7839,'MILLAN','PRESIDENTE',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into empleado values (7844,'GUTIERREZ','VENDEDOR',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into empleado values (7876,'ADAMS','PROGRAMADOR',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into empleado values (7900,'PEREZ','PROGRAMADOR',7698,to_date('3-12-1981','dd-mm-yyyy'),1950,NULL,30);
insert into empleado values (7902,'IBAÑEZ','ANALISTA',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into empleado values (7934,'DIAZ','PROGRAMADOR',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit; 

Con los datos anteriores, si por ejemplo queremos calcular los impuestos que se paga por cada empleado, tradicionalmente creábamos una función que calcula los valores para cada registro de la siguiente forma:

create or replace function calcula_impuestos(p_value  number)
  return number
is
begin
  return p_value * 0.4;
end;
/ 

Para utilizar la nueva función en nuestra select haríamos:

select salario, calcula_impuestos(salario) as impuestos from empleado where id_departamento = 10;
 
   SALARIO  IMPUESTOS
---------- ----------
      2450        980
      5000       2000
      1300        520 

Esta es la forma tradicional, con la nueva utilidad la forma de realizar este cálculo sería:

create or replace function calcula_impuestos(p_value  number)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{
    p_value * 0.4
  }';
end;
/ 

La llamada a nuestra macro se haría de la siguiente manera:

select salario, calcula_impuestos(salario) as impuestos from empleado where id_departamento = 10;
 
       SAL        TAX
---------- ----------
      2450        490
      5000       1000
      1300        260  

El resultado es el mismo, entonces ¿que aporta la nueva funcionalidad?

Ventajas de las Macros SQL en Oracle

La más importante ventaja que nos ofrece esta funcionalidad es el rendimiento. Cuando se ejecuta una select con una llamada a un PL/SQL, por cada fila que hay que evaluar, se hace un cambio de contexto entre modo SQL y modo PL/SQL. Estos cambios, aunque muy rápidos, siempre añaden una sobrecarga a la query que con esta funcionalidad no se hace. El optimizador, integra la función de la macro dentro de la select, haciendo que se resuelva en menos tiempo y solo en modo SQL por lo que nos ahorramos los cambios de contexto anteriores. Cuando tratemos pocas filas esta diferencia no será muy importante, pero según suban el número de filas será mucho más visible.

Esto es todo en la entrada, esperamos que os sea de utilidad cuando tengáis vuestra 21C montada. Si no sabes cómo montarla, podemos ayudarte, contáctanos.

Un saludo y nos vemos en una próxima entrada. No te la pierdas apuntándote a nuestra newsletter. Con un email al mes estarás al día de todas nuestras publicaciones.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn

Más información en: https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html