This article describes nice new feature SQL_MACRO that allows to simplify coding in PL/SQL for table expressions.
It’s a new, simpler way to encapsulate complex processing logic directly within SQL. SQL Macros allow developers to encapsulate complex processing within a new structure called a “macro” which can then be used within SQL statement. Essentially there two types of SQL Macros: SCALAR and TABLE. What’s the difference:
- SCALAR expressions can be used in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clauses
- TABLE expressions used in a FROM-clause
Let’s prepare test data first
CREATE TABLE test_tbl
(
id NUMBER,
name VARCHAR2(30)
);
INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'CHRIS');
INSERT INTO test_tbl VALUES(1, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');
COMMIT;
SELECT * FROM test_tbl;
ID NAME
-- -------
1 TOMASZ
1 TOMASZ
1 GREG
1 GREG
1 CHRIS
1 ADAM
2 ADAM
2 ADAM
SQL_MACRO Table
Then we build simple function fn_group_by with parameter p_name that returns type VARCHAR2 SQL_MACRO
CREATE OR REPLACE FUNCTION fn_group_by
(
p_name VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
RETURN q'{SELECT name, SUM(id) id
FROM test_tbl
WHERE name = fn_group_by.p_name
GROUP BY name}';
END;
/
When the function is executed query inside function fn_group_by is executed dynamically and data is returned. It’s very simple nice way to build dynamic parametrized queries !!!
select * from fn_group_by('TOMASZ');
NAME ID
------------------------------ ----------
TOMASZ 2
select * from fn_group_by('GREG');
NAME ID
------------------------------ ----------
GREG 2
You can easily use it inside PL/SQL
BEGIN
FOR I IN (SELECT * FROM fn_group_by('GREG'))
LOOP
NULL;
END LOOP;
FOR I IN (SELECT * FROM fn_group_by('TOMASZ'))
LOOP
NULL;
END LOOP;
END;
/
or build views on top of that
create view test_tbl_vw
as
SELECT * FROM fn_group_by('GREG');
desc test_tbl_vw
Name Null? Type
---- ----- ------------
NAME VARCHAR2(30)
ID NUMBER
It’s transparent in explain plan
explain plan for
select * from fn_group_by('GREG');
select *
from table(dbms_xplan.display);
Plan hash value: 148626839
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 2 | 60 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST_TBL | 2 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='GREG')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL_MACRO Scalar
it works a little bit different than SQL_MACRO Table. It can transform part of your query when you execute it.
CREATE OR REPLACE FUNCTION fn_scalar(p_name VARCHAR2)
RETURN VARCHAR2 SQL_MACRO(scalar)
IS
BEGIN
RETURN q'{dump(fn_scalar.p_name)}';
END;
/
and use it in query
select id, fn_scalar(name) from test_tbl;
ID FN_SCALAR(NAME)
1 Typ=1 Len=6: 84,79,77,65,83,90
1 Typ=1 Len=6: 84,79,77,65,83,90
1 Typ=1 Len=4: 71,82,69,71
1 Typ=1 Len=4: 71,82,69,71
1 Typ=1 Len=5: 67,72,82,73,83
1 Typ=1 Len=4: 65,68,65,77
2 Typ=1 Len=4: 65,68,65,77
2 Typ=1 Len=4: 65,68,65,77
you can display what SQL_MACRO(scalar) returns
set serveroutput on
exec dbms_output.put_line(fn_scalar('test text'));
dump(fn_scalar.p_name)
it could be useful everywhere where you want to hide complex transformation.
nice feature of SQL_MACRO(scalar) is possibility to pass as argument dynamic list of columns and manipulate them
CREATE OR REPLACE FUNCTION fn_gen_csv
(
p_cols dbms_tf.columns_t
)
RETURN CLOB SQL_MACRO(SCALAR)
IS
l_cols CLOB;
BEGIN
FOR i IN 1 .. p_cols.COUNT
LOOP
l_cols := l_cols || '||'',''||' || p_cols(I);
END LOOP;
l_cols := LTRIM(l_cols, '||'',''||');
dbms_output.put_line(l_cols);
RETURN l_cols;
END;
/
select FN_GEN_CSV(columns(id, name)) from test_tbl a;
FN_GEN_CSV(COLUMNS(ID,NAME))
1,TOMASZ
1,TOMASZ
1,GREG
1,GREG
1,CHRIS
1,ADAM
2,ADAM
2,ADAM
Features of SQL_MACRO
- The SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINED.
- Although the DETERMINISTIC property cannot be specified, a SQL macro is always implicitly deterministic.
- SQL macros can be used in any SQL statement, including DML, DDL, and DCL statements.
- SQL macros can be nested.
- SQL macros can be used to dynamically generate SQL statements.
- SQL macros can be used to improve performance by caching the results of expensive queries.
Have fun 🙂
Tomasz