SQL Macros in Oracle Database 21c

Hello everyone. Today we wanted to discuss with you one of the new features of Oracle 21C (although we already started to see it from the 20 with limitations), the SQL macros in Oracle.

SQL macros improve the reuse of code when tagging common expressions and statements in components that we can reuse later.

sql macros in oracle gpsos

The best way to see its use is through an example. To do this, we need the following database objects:

Department table:

create table department (
 id_departamento number(2) constraint pk_departamento primary key,
 name varchar2(14),
 varchar2 city(13)
) ; 

Employee table:

create table employee (
 id_empleado number(4) constraint pk_emp primary key,
 employee varchar2(10),
 post varchar2(9),
 manager number(4),
 hiredate date,
 salary number(7,2),
 comm number(7,2),
 id_departamento number(2) constraint fk_departamento references department
); 

Once the tables are created. We fill them with data. Starting with “department”

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

And we continue with “employee”

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

With the above data, if for example we want calculate the taxes that are paid for each employee, traditionally we created a function that calculates the values for each record as follows:

create or replace function calcula_impuestos(p_value number)
 return number
Is
Begin
 return p_value * 0.4;
end;
/  

To use the new function in our select we would do:

select salary, calcula_impuestos(salary) as taxes from employee where id_departamento = 10;
 
 SALARY TAXES
---------- ----------
  2450 980
  5000 2000
  1300 520  

This is the traditional way, with the new utility the way to perform this calculation would be:

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

The call to our macro would be made as follows:

select salary, calcula_impuestos(salary) as taxes from employee where id_departamento = 10;
 
 SALT TAX
---------- ----------
  2450 490
  5000 1000
  1300 260  

The result is the So what does the new functionality bring?

Advantages of SQL Macros in Oracle

The most important advantage what this functionality offers us is performance. When you run a select with a call to a PL/SQL, for each row to be evaluated, is made a context switch between SQL mode and PL/SQL mode. These changes, although very fast, always add an overload to the query that with this functionality does not it is done. The optimizer, integrates the function of the macro within the select, making it solve in less time and only in SQL mode so we we save the previous context changes. When we try few rows this difference will not be very important, but as they go up the number of rows will be much more visible.

This is all at the entrance, we hope you find it useful when you have your 21C mounted. If you do not know how to assemble it, we can help you, contact us.

Greetings and see you in a next post. Do not miss it by signing up for our newsletter. With one email per month you will be up to date with all our publications.

More information at: https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html

Comments are closed.