最新!Oracle 20c 新特性详解（oracle 2021）

原题目丨Oracle 20c 新特性详解：SQL Macro 宏 SCALAR / TABLE 模式带来的敏捷和便利

作者丨盖国强

出处丨墨天轮（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 语句。

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

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

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

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

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

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

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

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

例如，如下创建一个 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 的效果：

TABLE 类型的 SQL Macro 有两种，分别是：

1. 参数化视图 - Parameterized Views

2. Polymorphic Views

什么是参数化视图？

? 查询中使用的表固定在宏的定义内；

? 传递参数以从那些表中选择行；

? 返回的查询的“形状”（通常）是固定的；

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

参数化视图的示例：

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），

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

以下是一个实例:

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; 
/

调用使用效果如下：

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

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

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

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

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

? 可以在SQL语句内的任何地方使用；

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

? 可以建立参数化视图;

? 简化从非Oracle数据库迁移到Oracle数据库；

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

https://www.modb.pro/doc/997（复制到网页中，即可打开)

想了解更多关于数据库、云技术的内容吗？

快来关注“数据和云"、"云和恩墨"公众号及"云和恩墨"官方网站，我们期待大家一同学习与进步！

墨天轮小程序”DBASK“在线问答，随时解惑，欢迎了解和关注！