Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

Oracle Database 20c之SQL宏

您是否编写了一些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 Resources
    OE: 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_string
sqlplus 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.sql
GRANT 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 ViewsPolymorphic 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);


本例创建一个SQL宏,该宏生成范围[first, stop]内的等差数列。第一行首先从值开始,随后每一行的值都比前一行的值多一步。
/*  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.html
https://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
关键词:sqlmacro 20c oracle 

相关文章

基于PDB的Active Data Guard(Oracle 21.7+)
在Oracle数据库中使用REST
OGG from MySQL to Oracle
Oracle数据库容灾之两地三中心实践
低代码开发用Oracle Apex,看这篇就够了
Oracle Database 20c之SQL宏
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Oracle Database 19c在优化方面有哪些新特性
Top