当前位置: 首页 >> 技术文章 >> Oracle 20c 新特性详解:SQL Macro 宏 SCALAR / TABLE 模式带来的敏捷和便利
Oracle 20c 新特性详解:SQL Macro 宏 SCALAR / TABLE 模式带来的敏捷和便利
发布时间:2020-02-19 发布人:eygle 155


原文链接:https://www.modb.pro/db/7280 


在 Oracle 20c 中,SQL的宏支持 - SQL Macro 为 SQL 开发带来了进一步的敏捷和便利,在这篇文章中,我们来详细了解一下 SQL Macro 的特性用法。


以下是概要介绍:


宏的作用在于让SQL获得进一步的概括和抽象能力,允许开发者将复杂的处理逻辑通过宏进行定义,然后在后续程序处理中可以反复引用这一定义。


在 20c 中引入的 SQL Macro 支持两种宏类型,Scalar 和 Table 类型。


SCALR 表达式可以用于 SELECT 列表、WHERE/HAVING、GROUP BY/ORDER BY子句;


TABLE 表达式可以用于 FROM 语句。


image%2Feditor%2Ff1f0c52b-1bba-4088-95d8-671f55e229a0.jpg



对于 SCALAR 类型的宏,可以为复杂的SQL提供更灵活的语法来优化执行,这些灵活的方式包括:包和Schema 级别的函数,重载,命名参数和默认参数。



通过SCALAR宏,可以使可重用的SQL代码对SQL Optimizer透明,这将带来很大的收益,包括:


– SQL优化器可以转换代码以有效执行;


– PL / SQL内部的查询可以合并到外部查询中;


–在PL / SQL和SQL之间没有上下文切换;


–在与外部查询相同的快照下执行 PL / SQL 函数内部的查询;


–使用标量宏的WHERE子句谓词可被下推到Exadata中的存储侧谓词评估– 这对PL / SQL函数不适用


image%2Feditor%2Fd9499911-3d5c-418b-8ce9-5f6b5cbfe668.jpg


我们看一个简单的示例,例如需要一个函数,返回数据的下限和上限,举例而言,如果 x < lower (特定小值) 则返回 lower(作为下限);如果 x > upper (特定大值)则返回 upper(作为上限),否则返回 x 本身。


image%2Feditor%2F5bb51f14-0bb8-4a4a-b96d-99a25be79544.jpg


例如,如下创建一个 Clip 函数,返回一个 SCALAR 值:


create function clip(lo number, x number, hi number)

return varchar2 SQL_MACRO(SCALAR) is 

begin

    return ‘least(greatest(x, lo), hi)’;

end;

/



接下来就可以如下引用这个函数:


SELECT

 ename,

 CLIP (:lower, sal, :upper)

FROM emp;



Macro 函数被查询改写为如下执行:


SELECT

 ename,

 least(greatest(sal, :lower), :upper)

FROM emp;



这个查询返回的结果如下,可以看到 CLIP 的效果:


image%2Feditor%2F65535f11-1701-4e00-aa1c-5e8df9c7d1ac.jpg


TABLE 类型的 SQL Macro 有两种,分别是:


1. 参数化视图 - Parameterized Views


2. Polymorphic Views




什么是参数化视图?


• 查询中使用的表固定在宏的定义内;


• 传递参数以从那些表中选择行;


• 返回的查询的“形状”(通常)是固定的;


• 参数化视图的常见用法是,当使用标量参数选择行的子集然后进行聚合时


image%2Feditor%2F22507e13-fe3d-4f27-8467-59b30ef74b3a.jpg

参数化视图的示例:


CREATE FUNCTION budget (dept_no number DEFAULT 10)

RETURN varchar2 SQL_MACRO(TABLE)

IS BEGIN

 RETURN q’[

   SELECT

d.deptno,

SUM(e.sal) budget, 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;

/



通过传递 DEPT_NO,返回一个查询表宏,调用范例:


WITH east_coast as(SELECT deptno  FROM dept  WHERE loc = 'Boston')SELECT *FROM budget(east_coast);



什么是多态视图?


• 具有一个或多个表参数的表值宏


–还可以具有标量值的参数!


• 输入表在宏返回的查询中使用。


• 示例:反选择 where 条件...


–对于给定的表,返回一个查询,该查询会跳过给定名称或数据类型的列


–传递通用谓词(例如rownum <n),


–为现有语法提供功能语法


image%2Feditor%2F2ef32077-7e47-4889-861c-82c29d91396b.jpg


以下是一个实例:


CREATE FUNCTION sample(t DBMS_TF.Table_t, pct number DEFAULT 5)

RETURN VARCHAR2 SQL_MACRO(TABLE) AS

BEGIN

 RETURN q’[SELECT *

           FROM t

           WHERE dbms_random.value<=sample.pct/100]’;

END sample;

/



调用过程:


SELECT *

FROM sample(t=>sh.sales, pct=>15);



还可以使用SQL宏(TABLE)返回范围:


• 生成范围为[first,stop] 的行的算术级数。


–第一行的值优先


–每个后续行的值将比上一行的值多一步


• 这些宏的语义是在Python的内置range()函数之后建模的


– PL / SQL程序包


– PL / SQL程序包主体




以下是一个范围处理的范例:


create or replace 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);

end GEN; 

/

create or replace package body GEN is

function RANGE(stop number)

return varchar2 SQL_MACRO(TABLE) is

  begin

    return q’{

select * from (select level-1 n from dual connect by level<=stop) where stop>0 }’;

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;

end GEN; 

/



调用使用效果如下:


image%2Feditor%2F836b4604-193a-4ce7-a415-ce6a93566e83.jpg


数据字典更新,USER_PROCEDURES,ALL_PROCEDURES,DBA_PROCEDURES等视图都增加了 SQL_MACRO 字段。


对于 SQM 函数,增加了 SQL_MACRO 字段用于标识类型 - TABLE 或者 SCALAR:


image%2Feditor%2Fd33c6cd4-018d-41c9-a26c-f51dbe06aa24.jpg


最后总结一下 SQL Macro 的意义:


• SQL宏提供了一个基于SQL的简单框架,用于封装业务/技术逻辑


–无需在查询中调用自定义PL / SQL过程和函数


• 可以在SQL语句内的任何地方使用;


• 自动继承所有常用的数据库内查询优化;


• 可以建立参数化视图;


• 简化从非Oracle数据库迁移到Oracle数据库;


这就是 Oracle 20c 的 SQL Macro 的概要信息,相关的 OOW分享文档参考:


https://www.modb.pro/doc/997