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:
Right now (Oct 2020) only table macros are available in LiveSQL but don't panic because scalar macros will be arriving shortly.
In the past, if you wanted to extend the capabilities of SQL, then PL/SQL was the usually the best way to do add additional, more complex processing logic. However, by using PL/SQL this created some underlying complexities at execution time since we needed to keep swapping between the SQL context and the PL/SQL context. This tended to have an impact on performance and most people tried to avoid doing it whenever possible.
SQL Macros have an important advantage over ordinary PL/SQL functions in that they make the reusable SQL code completely transparent to the Optimizer – and that brings big benefits! It makes it possible for the optimizer to transform the original code for efficient execution because the underlying query inside the macro function can be merged into outer query. That means there is no context switching between PL/SQL and SQL and the query inside the macro function is now executed under same snapshot as outer query. So we get both simplicity and faster execution.
Suppose I want to have a simple way for my developers and users to view total sales revenue from the fact table SALES for a specific zip code in the CUSTOMERS table. In effect, we need to create a paramterized view using a table macro.
This requires a join for the two tables SALES and CUSTOMERS, then we need to find the matching rows for the zip code and finally sum the result. The table macro will look like this:
CREATE OR REPLACE FUNCTION total_sales(zip_code varchar2) return varchar2 SQL_MACRO is
BEGIN
RETURN q'{
SELECT cust.cust_postal_code as zip_code,
SUM(amount_sold) as revenue
FROM sh.customers cust, sh.sales s
WHERE cust.cust_postal_code = total_sales.zip_code
AND s.cust_id = cust.cust_id
GROUP BY cust.cust_postal_code
ORDER BY cust.cust_postal_code
}';
END;
/
Let's extend the above macro to take two input parameters and also allow the user to not provide any parameters....
Note the references in the WHERE clause that specify the input parameters via the function name which is total_sales:
Total_Sales.country
Total_Sales.region
CREATE OR REPLACE FUNCTION Total_Sales(country VARCHAR2 default null,
region VARCHAR2 default null)
RETURN clob SQL_MACRO is
BEGIN
RETURN q'{
SELECT
r.country_name name,
r.country_region region,
ROUND(SUM(s.amount_sold)) total_sales
FROM sh. countries r, sh.customers c, sh.sales s
WHERE r.country_id = c.country_id
AND c.cust_id = s.cust_id
AND r.country_name = NVL(INITCAP(Total_Sales.country), r.country_name)
AND r.country_region = NVL(INITCAP(Total_Sales.region), r.country_region)
GROUP BY r.country_id, r.country_name, r.country_region
}';
END;
/
Table macros are just like any ordinary database table so we can extend our SQL statement to include analytic functions such as RATIO_TO_REPORT to add more value to our SQL statement as follows:
SELECT
region,
name,
total_sales,
TRUNC(RATIO_TO_REPORT(total_sales) OVER (PARTITION BY region), 4) contribution
FROM (select *
FROM total_sales(region => 'europe')
ORDER BY 3 desc);
what happens if we don't pass any region name or country name into the macro? Will it simply return an error or return a list of all regions and their counties?
SELECT
region,
name,
total_sales,
TRUNC(RATIO_TO_REPORT(total_sales) OVER (PARTITION BY region), 4) contribution
from (SELECT *
FROM total_sales()
ORDER BY 1, 3 desc);
This should return a list of all regions with their corresponding countries which means our SQL macro can deal with the times when the user doesn't provide any parameters.