Hello everyone. Today we wanted to discuss with you one of the new functionalities of Oracle 21C (although we have already started to see it from the 20th with limitations), the SQL macros in Oracle.
SQL macros improve reuse of the code when labeling expressions and common statements in components that we can reuse later.
The best way to see its use is through an example. For this, we need the following database objects:
Department table:
create table department ( department_id number (2) constraint pk_department primary key, name varchar2 (14), city varchar2 (13) );
Employee table:
create table employee ( employee_id number (4) constraint pk_emp primary key, employee varchar2 (10), put varchar2 (9), manager number (4), hiredate date, salary number (7,2), comm number (7,2), department_id number (2) constraint fk_department references department );
Once the tables are created. We fill them with data. Starting with “department”
insert into department values (10, 'ACCOUNTING', 'MADRID'); insert into department values (20, 'INFORMATICA', 'BARCELONA'); insert into department values (30, 'SALES', 'VALENCIA'); insert into department values (40, 'OPERATIONS', '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', 'VENDOR', 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', 'MANAGER', 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', 'VENDOR', 7698, to_date ('8-9-1981', 'dd-mm-yyyy'), 1500,0,30);
insert into employee values (7876, 'ADAMS', 'SCHEDULER', 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', 'ANALYST', 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 previous data, if for example we want calculate the taxes paid by each employee, traditionally we created a function that calculates the values for each record as follows:
create or replace function calculate_tax (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, calculate_tax (salary) as taxes from employee where department_id = 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 calculate_tax (p_value number)
return varchar2 sql_macro (scalar)
is
begin
return q '{
p_value * 0.4
} ';
end;
/
The call to our macro would be done as follows:
select salary, calculate_tax (salary) as taxes from employee where department_id = 10; SAL TAX ---------- ---------- 2450 490 5000 1000 1300 260
The result is the itself, so what does the new functionality bring?
Advantages of SQL Macros in Oracle
The most important advantage What offers us this functionality is performance. When running a select with a call to a PL / SQL, for each row to be evaluated, it is done a context switch between SQL mode and PL / SQL mode. These changes, although very fast, always add an overhead to the query that with this functionality does not it does. The optimizer integrates the macro function inside the select, making it solve in less time and only in SQL mode so we we save the previous context switches. When we treat few rows this The 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 it will be useful when you have your 21C mounted. If you don’t know how to assemble it, we can help you, contact us .
Greetings and see you in a next post. Don’t miss it by signing up for our newsletter. With one email a month you will be up to date with all our publications.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn
More information in: https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html