专栏首页数据和云Oracle 20c 新特性详解:SQL Macro 宏 SCALAR / TABLE 模式带来的敏捷和便利

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

墨墨导读:在 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(复制到网页中,即可打开)

出处:墨天轮(https://www.modb.pro/db/7280,复制到网页中打开或者点击“阅读原文”)

DBASK,DBA的即时问答平台

本文分享自微信公众号 - 数据和云(OraNews),作者:盖国强

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 为什么用尽了办法你的系统性能还是不见改善

    随着业务数据的增长,以及新业务的推出,很多企业都面临着系统性能的问题,并且日益凸显。我们曾遇到很多这样的用户,似乎用尽了所有招数,但性能就是不见改善,问题到底出...

    数据和云
  • SQL 审核 - z3 产品理念与功能介绍

    我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的,一个开发环境中,众多的程序员难免引入一个又一个的或初级或高端的...

    数据和云
  • 执行计划:如何手工创建Profile维持SQL计划的稳定性

    在上一篇:《执行计划:Oracle的Profile特性与SQL执行计划的稳定性》,向大家介绍了什么是 SQL Profiles 及其作用,如何使用 SQL Tu...

    数据和云
  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    SQLplusDB
  • 【DB笔试面试688】在Oracle中,跟踪会话执行语句的方法有哪几种?

    因为TRACE的目标范围不同,所以导致必须使用不同的方法。若作用于数据库全局的,则改初始化参数。若只作用于当前会话的,则就用ALTER SESSION命令。若作...

    小麦苗DBA宝典
  • 在线就能用的 SQL 练习平台我给你找好了!

    在看完了某 《xxx 必知必会》以后,我觉得我膨胀了,立马某度 "xxx SQL 面试 100 题"、”SQL 岗位 xxx 个面试题“,准备大展手脚...

    帅地
  • 【每日一摩斯】-Troubleshooting: High CPU Utilization (164768.1) - 系列6

    如果问题是一个正运行的缓慢的查询SQL,那么就应该对该查询进行调优,避免它耗费过高的CPU资源。如果它做了许多的hash连接和全表扫描,那么就应该添加索引以提高...

    bisal
  • SQL Server从入门到精通.pdf

    《SQL Server从入门到精通》从初学者的角度出发,通过通俗易懂的语言、丰富多彩的实例,详细地介绍了SQL Server 2008开发应该掌握的各方面技术...

    用户3157710
  • 谷歌又傻X之BigQuery ML

    最近工作忙,又努力在写干活,没怎么关注互联网行业的发展。周末好不容易补补课,就发现了谷歌在其非常成功的云产品BigQuery上发布了BigQuery ML。说白...

    用户1564362
  • 【DB笔试面试670】在Oracle中,什么是SQL实时监控?

    在Oracle 11g中,V$SESSION视图增加了一些新的字段,这其中包括SQL_EXEC_START和SQL_EXEC_ID,这两个字段实际上代表了Ora...

    小麦苗DBA宝典

扫码关注云+社区

领取腾讯云代金券