SQL Macros improve code reuse by factoring out common expressions and statements into reusable components. SQL Macros for scalar and table expressions were introduced in the Oracle database 20c preview release, and were made generally available in Oracle 21c. SQL Macros for table expressions were backport ported to 19c in the 19.6 release update.
◉ Setup
Some of the examples in this article use the following tables.
-- drop table emp purge;
-- drop table dept purge;
create table dept (
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
) ;
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno references dept
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
◉ The Problem
We have several queries in an application that need to calculate tax. If the tax rate changes, that means making code changes in each query. The typical solution to this issue is to create a function to calculate tax. This function can be used in all the queries and therefore centralises the tax calculation. In the example below we create a function that calculates the tax at 20%.
create or replace function calculate_tax(p_value number)
return number
is
begin
return p_value * 0.2;
end;
/
select sal, calculate_tax(sal) as tax from emp where deptno = 10;
SAL TAX
---------- ----------
2450 490
5000 1000
1300 260
SQL>
That works fine, but calling a function from SQL adds an overhead. Each time we switch between the SQL engine to the PL/SQL engine and back we burn some CPU. Depending on the nature of the query, this overhead could be significant.
◉ The Solution (SQL Macros)
SQL Macros are one of several possible solutions. I'll mention some others in the Considerations section.
SQL macros look similar to conventional functions, but rather than performing an action during query execution, their action occurs during the optimization of the query. They explain how to rewrite the macro call with the expression returned by the macro. The optimizer does this substitution before executing the query.
The following SQL macro returns a scalar expression that calculates tax at 20% of the input parameter.
create or replace function calculate_tax(p_value number)
return varchar2 sql_macro(scalar)
is
begin
return q'{
p_value * 0.2
}';
end;
/
When we use the table macro it works as expected.
select sal, calculate_tax(sal) as tax from emp where deptno = 10;
SAL TAX
---------- ----------
2450 490
5000 1000
1300 260
SQL>
At first sight this looks like we've created a regular function, and the shared pool seems to suggest this also.
select sql_text
from v$sqlarea
where sql_text like '%tax%'
and sql_text not like '%sqlarea%';
SQL_TEXT
--------------------------------------------------------------------------------
select sal, calculate_tax(sal) as tax from emp where deptno = 10
SQL>
To see what is really happening, we need to do a 10053 trace on the statement. We flush the shared pool and reconnect to our test user, then check the name of the trace file for the current session.
conn sys@pdb1 as sysdba
alter system flush shared_pool;
conn testuser1@pdb1
select value
from v$diag_info
where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1_lhr12p/cdb1/trace/cdb1_ora_26425.trc
SQL>
We turn on the 10053 trace, run the query and turn the trace off again.
alter session set events '10053 trace name context forever';
select sal, calculate_tax(sal) as tax from emp where deptno = 10;
alter session set events '10053 trace name context off';
We check the section of the trace file beginning with "Final query after transformations" and we see the following. Notice there is no function call present in the statement. Instead it has the calculation substituted into the statement.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."SAL" "SAL","EMP"."SAL"*0.2 "TAX" FROM "TESTUSER1"."EMP" "EMP" WHERE "EMP"."DEPTNO"=10
So we still have the centralised approach to calculating tax, but we're not adding the overhead of making a function call. We have the same performance as doing the calculation directly in the query.
If we call the SQL macro directly from PL/SQL, we see the macro string is produced. There is no macro expansion.
SQL> exec dbms_output.put_line(calculate_tax(100));
p_value * 0.2
PL/SQL procedure successfully completed.
SQL>
◉ Basics
SQL macros look similar to normal functions, but they must return a string (VARCHAR2, CHAR, or CLOB). Remember, this macro call is substituted into the SQL statement.
SQL macros come in two forms.
- Scalar Expressions.
- Table Expressions.
There are some points to consider about SQL macros, including restrictions.
- They must return a string (VARCHAR2, CHAR, or CLOB).
- The SQL_MACRO clause can't be combined with DETERMINISTIC, RESULT_CACHE, PARALLEL_ENABLE or PIPELINED clauses.
- Despite the DETERMINISTIC clause not being allowed, all SQL macros are deterministic.
- The AUTHID property can't be specified, and the SQL macro always runs with invoker rights when used directed in a SQL statement.
- SQL macros in views are always execute with the privileges of the view owner.
- SQL macros can't be used in virtual column expression, function-based indexes, editioning views or materialized views.
- SQL macros can't be used in type methods.
- Scalar SQL macros can appear in a SELECT list, WHERE clause, or HAVING clause.
- Table SQL macros can appear in a FROM clause.
- SQL scalar SQL macro can't have table arguments.
- When a SQL macro is called directly from PL/SQL a string is returned. There is no macro expansion.
SQL macros are just producing text, so it's easy to enter incorrect syntax and the macro will compile. It's only at runtime you will see an error, so test your macros carefully.
◉ SQL Macros : Scalar Expressions
SQL macros for scalar expressions return an expression that is substituted into the SQL Statement in place of the SQL macro call. These can be used in the SELECT list, WHERE clause, and HAVING clause.
We've already seen an example of a SQL macros using a scalar expression in the section above, but lets try a few more examples.
The following example allows us to define a standard format to output dates in our application.
create or replace function show_date(p_value date)
return varchar2 sql_macro(scalar)
is
begin
return q'{ to_char(p_value, 'YYYY-MM-DD') }';
end;
/
select show_date(sysdate) as my_date from dual;
MY_DATE
----------
2020-12-26
SQL>
We can incorporate SQL macro definitions into a package. The following example creates a package containing macros to display dates, datetimes and timestamps.
create or replace package date_macros as
function show_date(p_value date)
return varchar2 sql_macro(scalar);
function show_datetime(p_value date)
return varchar2 sql_macro(scalar);
function show_timestamp(p_value timestamp)
return varchar2 sql_macro(scalar);
end;
/
create or replace package body date_macros as
function show_date(p_value date)
return varchar2 sql_macro(scalar)
is
begin
return q'{ to_char(p_value, 'YYYY-MM-DD') }';
end;
function show_datetime(p_value date)
return varchar2 sql_macro(scalar)
is
begin
return q'{ to_char(p_value, 'YYYY-MM-DD HH24:MI:SS') }';
end;
function show_timestamp(p_value timestamp)
return varchar2 sql_macro(scalar)
is
begin
return q'{ to_char(p_value, 'YYYY-MM-DD HH24:MI:SS.FF') }';
end;
end;
/
We use the SQL macros in the normal way, remembering to prefix them with the package name.
select date_macros.show_date(sysdate) as my_date from dual;
MY_DATE
----------
2020-12-26
SQL>
select date_macros.show_datetime(sysdate) as my_datetime from dual;
MY_DATETIME
-------------------
2020-12-26 21:06:41
SQL>
select date_macros.show_timestamp(systimestamp) as my_timestamp from dual;
MY_TIMESTAMP
-----------------------------
2020-12-26 21:07:19.438043
SQL>
The following example prepares a name in a standard format for our application.
create or replace function show_full_name(
p_first_name varchar2,
p_middle_names varchar2,
p_last_name varchar2
)
return varchar2 sql_macro(scalar)
is
l_sql varchar2(32767);
begin
l_sql := q'{ p_first_name || ' ' || nvl2(p_middle_names, p_middle_names||' ', '') || p_last_name }';
return l_sql;
end;
/
select show_full_name('Wonder', null, 'Woman') as full_name from dual;
FULL_NAME
------------
Wonder Woman
SQL>
select show_full_name('Conan', 'the', 'Barbarian') as full_name from dual;
FULL_NAME
-------------------
Conan the Barbarian
SQL>
When you are coding scalar macros, remember you don't have access to parameter values. The expression you build is done at optimization time, not runtime, so your expression needs to cope with all scenarios in advance of knowing the values.
A scalar SQL macro can accept column attributes, which allows us to do some interesting things. The following scalar SQL macro accepts column arguments, and uses those to return the correct JSON_OBJECT call to display those columns as a JSON fragment.
create or replace function get_json (p_cols dbms_tf.columns_t)
return clob sql_macro(scalar)
is
l_columns varchar2(32767);
begin
-- Get comma-separated list of column names in lower case. Remove quotes.
for i in 1 .. p_cols.count loop
l_columns := l_columns || trim(both '"' from lower(p_cols(i))) || ',';
end loop;
l_columns := rtrim(l_columns, ',');
return 'json_object(' || l_columns || ')';
end;
/
We can now use the macro in a select list, passing a list of columns we want to be included in the JSON document. Below are two examples.
column json_data format a50
select empno, get_json(columns(ename, job, sal)) as json_data from emp where deptno = 20;
EMPNO JSON_DATA
---------- --------------------------------------------------
7369 {"ename":"SMITH","job":"CLERK","sal":800}
7566 {"ename":"JONES","job":"MANAGER","sal":2975}
7788 {"ename":"SCOTT","job":"ANALYST","sal":3000}
7876 {"ename":"ADAMS","job":"CLERK","sal":1100}
7902 {"ename":"FORD","job":"ANALYST","sal":3000}
SQL>
select empno, get_json(columns(empno, ename)) as json_data from emp where deptno = 20;
EMPNO JSON_DATA
---------- --------------------------------------------------
7369 {"empno":7369,"ename":"SMITH"}
7566 {"empno":7566,"ename":"JONES"}
7788 {"empno":7788,"ename":"SCOTT"}
7876 {"empno":7876,"ename":"ADAMS"}
7902 {"empno":7902,"ename":"FORD"}
SQL>
◉ SQL Macros : Table Expressions
SQL macros for table expressions return a table expression that is substituted into the SQL statement in place of the SQL macro. These are used in the FROM clause. We can think of this like building a query for an inline view.
The following example creates a table macro that returns the total salaries in each department.
create or replace function sal_by_dept
return varchar2 sql_macro(table)
is
begin
return q'{
select deptno, sum(sal) as sal_tot
from emp
group by deptno
}';
end;
/
We can add this macro to a FROM clause, just like we would a table function.
select * from sal_by_dept();
DEPTNO SAL_TOT
---------- ----------
20 10875
30 9400
10 8750
SQL>
The following example adds a parameter to the table macro. We can now limit the output by the department number.
create or replace function sal_by_dept (p_deptno number)
return varchar2 sql_macro(table)
is
begin
return q'{
select deptno, sum(sal) as sal_tot
from emp
where deptno = p_deptno
group by deptno
}';
end;
/
select * from sal_by_dept(10);
DEPTNO SAL_TOT
---------- ----------
10 8750
SQL>
We can include joins in the table macro. The following example joins the departments table, to pull back the department name.
create or replace function sal_by_dept (p_deptno number)
return varchar2 sql_macro(table)
is
begin
return q'{
select e.deptno, d.dname, sum(e.sal) as sal_tot
from emp e
join dept d on e.deptno = d.deptno
where e.deptno = p_deptno
group by e.deptno, d.dname
}';
end;
/
select * from sal_by_dept(10);
DEPTNO DNAME SAL_TOT
---------- -------------- ----------
10 ACCOUNTING 8750
SQL>
A table macro can accept table arguments, so a single SQL macro can perform an action against multiple tables.
create or replace function row_count (p_tab dbms_tf.table_t)
return varchar2 sql_macro(table)
is
begin
return q'{
select count(*) as row_count from p_tab
}';
end;
/
select * from row_count(emp);
ROW_COUNT
----------
14
SQL>
select * from row_count(dept);
ROW_COUNT
----------
4
SQL>
Within the table macro we have access to information about the table using the DBMS_TF.TABLE_T type. In the following example we use the table column names to build a query using the JSON_OBJECT function, which returns a JSON fragment for each row.
create or replace function get_json (p_tab dbms_tf.table_t)
return clob sql_macro(table)
is
l_columns varchar2(32767);
l_sql varchar2(32767);
begin
for i in 1 .. p_tab.column.count loop
l_columns := l_columns || trim(both '"' from lower(p_tab.column(i).description.name)) || ',';
end loop;
l_columns := rtrim(l_columns, ',');
l_sql := 'select json_object(' || l_columns || ') as json_data from p_tab';
return l_sql;
end;
/
select * from get_json(dept);
JSON_DATA
--------------------------------------------------------------------------------
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}
SQL>
select * from get_json(emp);
JSON_DATA
----------------------------------------------------------------------------------------------------------------
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00","sal":800,"comm":null,"deptno":20}
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00","sal":1600,"comm":300,"deptno":30}
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00","sal":1250,"comm":500,"deptno":30}
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-02T00:00:00","sal":2975,"comm":null,"deptno":20}
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00","sal":1250,"comm":1400,"deptno":30}
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-05-01T00:00:00","sal":2850,"comm":null,"deptno":30}
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-09T00:00:00","sal":2450,"comm":null,"deptno":10}
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00","sal":3000,"comm":null,"deptno":20}
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00","sal":5000,"comm":null,"deptno":10}
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00","sal":1500,"comm":0,"deptno":30}
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1987-05-23T00:00:00","sal":1100,"comm":null,"deptno":20}
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03T00:00:00","sal":950,"comm":null,"deptno":30}
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03T00:00:00","sal":3000,"comm":null,"deptno":20}
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00","sal":1300,"comm":null,"deptno":10}
14 rows selected.
SQL>
If we didn't care about the case of the elements, we could have done the following, but uppercase element names are ugly, and it wouldn't demonstrate the use of the DBMS_TF.TABLE_T type.
create or replace function get_json (p_tab dbms_tf.table_t)
return clob sql_macro(table)
is
begin
return 'select json_object(*) from p_tab';
end;
/
set linesize 150
select * from get_json(dept);
JSON_OBJECT(*)
--------------------------------------------------------------------------------
{"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"}
{"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"}
{"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"}
{"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"}
SQL>
◉ Views
The SQL_MACRO column in the {CDB|DBA|ALL|USER}_PROCEDURES views can be used to display SQL macros. The column contains the values "TABLE" or "SCALAR" depending on the type of macro, or the string "NULL" if the object is not a SQL macro.
set linesize 120 pagesize 20
column object_name format a30
column procedure_name format a30
column sql_macroformat a9
select uo.object_type,
up.sql_macro,
up.object_name,
up.procedure_name
from user_procedures up
join user_objects uo on up.object_id = uo.object_id
where up.sql_macro != 'NULL'
order by uo.object_type, up.sql_macro, up.object_name, up.procedure_name;
OBJECT_TYPE SQL_MA OBJECT_NAME PROCEDURE_NAME
----------------------- ------ ------------------------------ ------------------------------
FUNCTION SCALAR CALCULATE_TAX
FUNCTION SCALAR SHOW_DATE
FUNCTION SCALAR SHOW_FULL_NAME
FUNCTION TABLE GET_JSON
FUNCTION TABLE ROW_COUNT
FUNCTION TABLE SAL_BY_DEPT
PACKAGE SCALAR DATE_MACROS SHOW_DATE
PACKAGE SCALAR DATE_MACROS SHOW_DATETIME
PACKAGE SCALAR DATE_MACROS SHOW_TIMESTAMP
9 rows selected.
SQL>
The SQL macro source is present in the {CDB|DBA|ALL|USER}_SOURCE views, like any other code-based object.
column text format a50
select line, text
from user_source
where name = 'CALCULATE_TAX'
order by line;
LINE TEXT
---------- --------------------------------------------------
1 function calculate_tax(
2 p_value number
3 )
4 return varchar2 sql_macro(scalar)
5 is
6 begin
7 return q'{
8 p_value * 0.2
9 }';
10 end;
10 rows selected.
SQL>
They can also be displayed using the DBMS_METADATA package, like most other objects.
set long 20000
select dbms_metadata.get_ddl('FUNCTION', 'CALCULATE_TAX') as function_ddl
from dual;
FUNCTION_DDL
--------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE FUNCTION "TESTUSER1"."CALCULATE_TAX" (
p_value number
)
return varchar2 sql_macro(scalar)
is
begin
return q'{
p_value * 0.2
}';
end;
SQL>
Considerations
SQL macros are one of several solutions for improving the performance of function calls from SQL. It's important you don't think of them in isolation.
◉ If you have a piece of functionality that is required from both SQL and PL/SQL, you may prefer to use a regular function over a SQL macro. The SQL macro could only be used in PL/SQL as part of a query. For example you might do a SELECT macro INTO variable FROM dual to make an assignment. This is better than having duplicate functionality for SQL and PL/SQL, but some people may not find it acceptable.
◉ There are alternatives to reduce the impact of context switches. In the example of the scalar SQL macro, the CALCULATE_TAX function could have been implemented using PRAGMA UDF to reduce the impact of the context switches.
◉ There are also a number of caching methods that can increase the performance of function calls in SQL, and may be more appropriate than a scalar SQL macro in some circumstances.
Posts
0 comments:
Post a Comment