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 

相关文章

Oracle Database 20c之SQL宏
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Oracle Database 19c在优化方面有哪些新特性
Oracle Database 20c云端预览版发布了
Oracle 19c新特性之RAC Automatic Failback Service
Install Oracle RAC Database 19c Step by Step
保障业务连续性的神器
Oracle DataGuard feature and workshop
Top