您是否编写了一些C程序,用Macro替换Function?您的代码就可以像使用模块一样易于阅读和维护。比如下方的MAX和MIN。
/*宏定义的方式*/
#define MAX(x,y) (((x)>(y)) ? (x):(y)) #define MIN(x,y) (((x)<(y)) ? (x):(y))/*函数的方式*/
int max(int x,int y) { return (x>y?x:y); } int min(int x,int y) { return (x<y?x:y); }
现在在Oracle 20c数据库中,引入了SQL宏。您可以创建SQL宏(SQM),将常见的SQL表达式和语句分解成可在其他SQL语句中使用的可重用、参数化的构造。SQL_MACRO语句将函数标记为SQL宏,可以用作scalar表达式或table表达式。通过重用可用减少递归调用(recursive call),提供系统的性能,也可以通过该方式,将业务逻辑在数据库中实现,更方便维护和管理。
SQL宏分为两种:Scalar和Table
Scalar:通常用于SELECT列表、WHERE、GROUP BY和HAVING子句,以封装计算和业务逻辑,定义为SCALAR类型的函数Table: 通常用于FROM子句,以充当一种多态(参数化)视图,定义为TABLE类型
SQL宏函数的返回类型必须是VARCHAR2、CHAR或CLOB。如果没有指定返回类型,默认是TABLE类型。
SQL宏提高了开发人员的工作效率,简化了协作开发,并提高了代码质量。
SQL宏使用限制:
Table Macro只能出现在查询表表达式的FROM子句中
Scalar Macro不能出现在查询表表达式的FROM子句中。它可以出现在select的列中或WHERE子句中。该类型宏不能有表参数。
SQL宏不能出现在虚拟列表达式、函数索引、编辑视图或物化视图中。
准备测试数据
下面让我们来看几个例子,在练习之前,可以先安装样本数据,可参考链接https://github.com/oracle/db-sample-schemas
HR: Human ResourcesOE: Order Entry
PM: Product Media
IX: Information Exchange
SH: Sales History
BI: Business Intelligence
下载并解压sample schema数据
[oracle@xd08dbadm01 ~]$ wget https://github.com/oracle/db-sample-schemas/archive/v19.2.zip
[oracle@xd08dbadm01 ~]$ ls -l v19.2.zip-rw-r--r--. 1 oracle dba 28972417 Mar 21 07:24 v19.2.zip
[oracle@xd08dbadm01 ~]$ unzip v19.2.zip
[oracle@xd08dbadm01 ~]$ cd db-sample-schemas-19.2/
替换脚本中路径
[oracle@xd08dbadm01 db-sample-schemas-19.2]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
开始安装sample schema
安装的命令格式如下
sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_stringsqlplus system/oracle12@localhost/pdb20c
@mksample oracle12 oracle12 oracle12 oracle12 oracle12 oracle12 oracle12 oracle12 users temp /tmp/ localhost/pdb20c
安装scott数据
[oracle@xd08dbadm01 ~]$ wget https://raw.githubusercontent.com/oracle/dotnet-db-samples/master/schemas/scott.sqlGRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
运行里面的create和insert脚本,即可完成安装
Scalar Marco例子:Emp_doc:使用Scalar宏将列转换为JSON或XML文档,使用的是hr.employee和scott.emp
emp_doc()
==> emp_json()
==> name_string()
==> email_string()
==> name_string()
==> date_string()
==> emp_xml
==> name_string()
==> email_string()
==> name_string()
==> date_string()
CREATE FUNCTION date_string(dat DATE)
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
TO_CHAR(dat, 'YYYY-MM-DD')
}';
END;
/
CREATE FUNCTION name_string(first_name VARCHAR2,
last_name VARCHAR2)
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
TRIM(INITCAP(first_name) || ' ' || INITCAP(last_name))
}';
END;
/
CREATE FUNCTION email_string(first_name VARCHAR2,
last_name VARCHAR2,
host_name VARCHAR2 DEFAULT 'example.com')
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
REPLACE(LOWER(name_string(first_name, last_name)),' ','.') || '@' || host_name
}';
END;
/
CREATE FUNCTION emp_json(first_name VARCHAR2 DEFAULT NULL,
last_name VARCHAR2 DEFAULT NULL,
hire_date DATE DEFAULT NULL,
phone_num VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
JSON_OBJECT(
'name' : name_string(first_name, last_name),
'email' : email_string(first_name, last_name),
'phone' : phone_num,
'hire_date' : date_string(hire_date)
ABSENT ON NULL)
}';
END;
/
CREATE FUNCTION emp_xml(first_name VARCHAR2 DEFAULT NULL,
last_name VARCHAR2 DEFAULT NULL,
hire_date DATE DEFAULT NULL,
phone_num VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
XMLELEMENT("xml",
CASE WHEN first_name || last_name IS NOT NULL THEN
XMLELEMENT("name", name_string(first_name, last_name))
END,
CASE WHEN first_name || last_name IS NOT NULL THEN
XMLELEMENT("email", email_string(first_name, last_name))
END,
CASE WHEN hire_date IS NOT NULL THEN
XMLELEMENT("hire_date", date_string(hire_date))
END,
CASE WHEN phone_num IS NOT NULL THEN
XMLELEMENT("phone", phone_num)
END)
}';
END;
/
CREATE FUNCTION emp_doc(first_name VARCHAR2 DEFAULT NULL,
last_name VARCHAR2 DEFAULT NULL,
hire_date DATE DEFAULT NULL,
phone_num VARCHAR2 DEFAULT NULL,
doc_type VARCHAR2 DEFAULT 'json')
RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{
DECODE(LOWER(doc_type),
'json', emp_json(first_name, last_name, hire_date, phone_num),
'xml', emp_xml(first_name, last_name, hire_date, phone_num))
}';
END;
/
SELECT department_id,
emp_doc(first_name => e.first_name, hire_date => e.hire_date) doc
FROM hr.employees e
WHERE department_id = 30
ORDER BY last_name;
SELECT deptno,
emp_doc(first_name => ename, hire_date => hiredate, doc_type => 'xml') doc
FROM scott.emp
ORDER BY ename;
VARIABLE surname VARCHAR2(100)
EXEC :surname := 'ellison'
WITH e AS (SELECT emp.*, :surname lname FROM scott.emp WHERE deptno IN (10,20))
SELECT deptno,
emp_doc(first_name => ename, last_name => lname, hire_date => hiredate) doc
FROM e
ORDER BY ename;
env和SQL宏为与描述当前会话的上下文名称空间USERENV关联的参数的值提供了包装
CREATE PACKAGE env AS
FUNCTION current_user RETURN VARCHAR2 SQL_MACRO(SCALAR);
FUNCTION current_edition_name RETURN VARCHAR2 SQL_MACRO(SCALAR);
FUNCTION module RETURN VARCHAR2 SQL_MACRO(SCALAR);
FUNCTION action RETURN VARCHAR2 SQL_MACRO(SCALAR);
END;
/
CREATE PACKAGE BODY env AS
FUNCTION current_user RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{SYS_CONTEXT('userenv','SESSION_USER')}';
END;
FUNCTION current_edition_name RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{SYS_CONTEXT('userenv','CURRENT_EDITION_NAME')}';
END;
FUNCTION module RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{SYS_CONTEXT('userenv','MODULE')}';
END;
FUNCTION action RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{SYS_CONTEXT('userenv','ACTION')}';
END;
END;
/
SELECT env.current_user, env.module, env.action FROM DUAL;
Table Marco分为两种Parameterized Views和Polymorphic Views:
Parameterized Views:带入输入参数的表/视图。有一下特征
查询中使用的表在宏的定义中是固定的
通过传入参数来筛选行
返回的查询格式,通常是固定的
这些参数化视图的常见用法是,使用标量参数选择然后聚合的行的子集
这个例子显示了一个表表达式中使用的名为budget的SQL宏,它用于返回给定职位的每个部门员工的工资总额。
CREATE FUNCTION budget(job VARCHAR2) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN q'{SELECT deptno, SUM(sal) budget
FROM emp
WHERE job = budget.job
GROUP BY deptno}';
END;
/
SELECT * FROM budget('MANAGER');
CREATE OR REPLACE FUNCTION BUDGET (DEPT_NO NUMBER DEFAULT 10)RETURN VARCHAR2 SQL_MACRO(TABLE)
IS BEGIN
RETURN Q'[
SELECT
D.DEPTNO, SUM(E.SAL)
ANY_VALUE(D.DNAME) DEPARTMENT, COUNT(E.EMPNO) HEADCOUNT, COUNT(E.MGR) MGR_HEADCOUNT
FROM EMP E, DEPT D
WHERE D.DEPTNO = :DEPT_NO
AND E.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO]';
END BUDGET;
/
with east_coast as (select deptno from dept where loc='boston')
select * from bugdet(east_coast);
使用Table Marco with Polymorphic View(多态视图)
Polymorphic Views:多态视图,简单的理解就是这个视图/表名字作为参数来使用
具有一个或多个表参数的表值宏
输入表用于宏返回的查询中
这个例子创建了一个名为take的Table Macro,它从表t返回前n行
CREATE FUNCTION take (n NUMBER, t DBMS_TF.table_t)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN 'SELECT * FROM t FETCH FIRST take.n ROWS ONLY';
END;
/
SELECT * FROM take(2, dept);
VAR row_count NUMBER
EXEC :row_count := 5
WITH t AS (SELECT * FROM emp NATURAL JOIN dept ORDER BY ename)
SELECT ename, dname FROM take(:row_count, t);
/* PACKAGE NAME: GEN
* SQL TABLE MACROS:
* range(stop : number to generate starting from zero)
* range(first : starting number of the sequence (default=0),
* stop : generate numbers up to, but not including this number,
* step : difference between each number in the sequence (default=1) )
*/
CREATE PACKAGE gen IS
FUNCTION range(stop NUMBER)
RETURN VARCHAR2 SQL_MACRO(TABLE);
FUNCTION range(first NUMBER DEFAULT 0, stop NUMBER, step NUMBER DEFAULT 1)
RETURN VARCHAR2 SQL_MACRO(TABLE);
FUNCTION tab(tab TABLE, replication_factor NATURAL)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING gen;
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, replication_factor NATURAL)
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE fetch_rows(replication_factor NATURALN);
END gen;
/
CREATE PACKAGE BODY gen IS
FUNCTION range(stop NUMBER)
RETURN VARCHAR2 SQL_MACRO(TABLE) IS
BEGIN
RETURN q'{SELECT ROWNUM-1 n FROM gen.tab(DUAL, stop)}';
END;
FUNCTION range(first NUMBER DEFAULT 0, stop NUMBER, step NUMBER DEFAULT 1)
RETURN VARCHAR2 SQL_MACRO(TABLE) IS
BEGIN
RETURN q'{
SELECT first+n*step n FROM gen.range(ROUND((stop-first)/NULLIF(step,0)))
}';
END;
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, replication_factor NATURAL)
RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
RETURN DBMS_TF.DESCRIBE_T(row_replication => true);
END;
PROCEDURE fetch_rows(replication_factor NATURALN) as
BEGIN
DBMS_TF.ROW_REPLICATION(replication_factor);
END;
END gen;
/
SELECT * FROM gen.range(5);
SELECT * FROM gen.range(5, 10);
SELECT * FROM gen.range(0, 1, step=>0.1);
SELECT * FROM gen.range(+5,-6,-2);
查看SQL Macro的对象
我们可以通过[user|all|dba|cdb]_procedures来查看这些对象
SQL> desc dba_procedures Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) PROCEDURE_NAME VARCHAR2(128) OBJECT_ID NUMBER SUBPROGRAM_ID NUMBER OVERLOAD VARCHAR2(40) OBJECT_TYPE VARCHAR2(13) AGGREGATE VARCHAR2(3) PIPELINED VARCHAR2(3) IMPLTYPEOWNER VARCHAR2(128) IMPLTYPENAME VARCHAR2(128) PARALLEL VARCHAR2(3) INTERFACE VARCHAR2(3) DETERMINISTIC VARCHAR2(3) AUTHID VARCHAR2(12) RESULT_CACHE VARCHAR2(3) ORIGIN_CON_ID NUMBER POLYMORPHIC VARCHAR2(5) SQL_MACRO VARCHAR2(6) BLOCKCHAIN VARCHAR2(3) BLOCKCHAIN_MANDATORY_VOTES VARCHAR2(4000)
col object_name for a30
set lines 156 pages 100
select object_name,object_type,sql_macro from dba_procedures where sql_macro in ('SCALAR','TABLE');
Reference
https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/sql-macros.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/release-changes.html#GUID-0A638FCA-89C2-44E0-A5D5-0D09800D920D
https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/CREATE-FUNCTION-statement.html#GUID-B71BC5BD-B87C-4054-AAA5-213E856651F2
https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/plsql-optimization-and-tuning.html#GUID-981102A8-5204-4931-B10A-93486304B184
https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/plsql-language-elements.html#GUID-292C3A17-2A4B-4EFB-AD38-68DF6380E5F7
https://static.rainfocus.com/oracle/oow19/sess/1553765929286001lSFW/PF/SQL-Macros-Overview_1569084990942001SWly.pdf