Oracle Database 21c新特性讲座（2）——使用SQL宏作为标量表达式、位聚合函数

甲骨文云技术 2020-12-28

1337

这是Oracle Database 21c新特性的第二讲。在上一讲当中我们为大家介绍了如何搭建Oracle Database 21c数据库环境，以及新的set函数还有在初始化参数中使用表达式。在今天的内容将，将为大家介绍SQM标量表达式和Table表达式以及位聚合函数。

实验2-1:使用SQL宏作为标量表达式

在本实验中，我们使用HR用户进行操作，关于sample schema的安装，大家可以参考页面底部的“阅读原文”进行设定。

我们使用hr用户连接到pdb，然后创建一个SQL宏。这个宏的内容很简单，给出两个参数，一个是字符串str，另外一个是要重复的次数，然后返回varchar2类型的结果，将输入的内容重复几次并返回。

[oracle@henry ~]$ cat 21c-2.sql
CREATE OR REPLACE FUNCTION concat_self(str varchar2, cnt pls_integer)
            RETURN VARCHAR2 SQL_MACRO(SCALAR)
      IS BEGIN
            RETURN 'rpad(str, cnt * length(str), str)';
END;
/
[oracle@henry ~]$

Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

SQL> show user
USER is "HR"
SQL> @/home/oracle/21c-2.sql

Function created.

接下来我们在select语句当中使用我们刚刚定义的SQL宏。

SQL> SELECT first_name, concat_self(first_name,2) FROM hr.employees;

FIRST_NAME           CONCAT_SELF(FIRST_NAME,2)
-------------------- ----------------------------------------
Ellen                EllenEllen
Sundar               SundarSundar
Mozhe                MozheMozhe
David                DavidDavid
Hermann              HermannHermann
Shelli               ShelliShelli
Amit                 AmitAmit
Elizabeth            ElizabethElizabeth
Sarah                SarahSarah
David                DavidDavid
Laura                LauraLaura

SQL宏还可以作为Table表达式使用，比如我们现在想按照部门编号对employees表进行分组，然后求出每组的sum工资。之前我们可以创建一个view，现在我们可以通过SQL宏来完成。在下面的例子中，我们创建了一个宏叫做budget，然后在使用的时候，直接将这个宏写在了table name的位置，需要注意的是，使用宏的时候，记得在后面写上括号。

[oracle@henry ~]$ cat 21c-2-2.sql
CREATE OR REPLACE FUNCTION budget
return varchar2 SQL_MACRO
IS
BEGIN
  RETURN q'( select department_id, sum(salary) budget
             from hr.employees
             group by department_id )';
END;
/
[oracle@henry ~]$
SQL> @/home/oracle/21c-2-2.sql
Function created.

SQL> SELECT * FROM budget() WHERE department_id IN (10,50);

DEPARTMENT_ID     BUDGET
------------- ----------
           50     156400
           10       4400

刚才我们在调用SQL宏的时候，没有给出参数。接下来我们创建一个带有参数的SQL宏，用来显示特定job在各部门中的工资总和。请注意在宏主体部分当中，调用宏参数（本例中：job_id）的写法，要将宏的名字写在前面。在下面的查询语句中，我们在调用SQL宏的时候，在括号当中给出了参数SH_CLERK，只查询该种JOB的工资汇总情况，我们可以在SQL语句当中加上where做进一步的限定。

[oracle@henry ~]$ cat 21c-2-3.sql
CREATE OR REPLACE FUNCTION budget_per_job(job_id varchar2)
return varchar2 SQL_MACRO
IS
BEGIN
  RETURN q'( select department_id, sum(salary) budget
             from hr.employees
             where job_id = budget_per_job.job_id
             group by department_id )';
END;
/

SQL> @/home/oracle/21c-2-3.sql

Function created.

SQL> SELECT * FROM budget_per_job('SH_CLERK') WHERE department_id = 50;

DEPARTMENT_ID     BUDGET
------------- ----------
           50      64300

如果大家想了解上面的SQL语句在真正执行的时候，具体使用的完整SQL是什么。我们可以重写上面的SQL宏，将生成的SQL使用DBMS包打印出来。

[oracle@henry ~]$ cat 21c-2-4.sql
CREATE OR REPLACE function budget_per_job(job_id varchar2)
return varchar2 SQL_MACRO
is
  stmt varchar(2000) := q'(
   select department_id, sum(salary) budget
   from hr.employees
   where job_id = budget_per_job.job_id
   group by department_id )';
begin
  dbms_output.put_line('----------------------------------------------');
  dbms_output.put_line('SQM Text: ' );
  dbms_output.put_line('----------------------------------------------');
  dbms_output.put_line('  ' ||stmt);
  dbms_output.put_line('----------------------------------------------');
  return stmt;
end;
/

SQL> @/home/oracle/21c-2-4.sql

Function created.

SQL> SELECT * FROM budget_per_job('ST_CLERK') WHERE department_id = 50;

DEPARTMENT_ID     BUDGET
------------- ----------
           50      55700

----------------------------------------------
SQM Text:
----------------------------------------------

   select department_id, sum(salary) budget
   from hr.employees
   where
job_id = budget_per_job.job_id
   group by department_id
----------------------------------------------

可以通过user_procedures查询我们创建的SQL宏。

SQL>  SELECT object_name, sql_macro, object_type FROM user_procedures where object_type='FUNCTION';

OBJECT_NAME                    SQL_MA OBJECT_TYPE
------------------------------ ------ -------------
CONCAT_SELF                    SCALAR FUNCTION
BUDGET                         TABLE  FUNCTION
BUDGET_PER_JOB                 TABLE  FUNCTION

实验2-2:位聚合函数

按位聚合函数会进行位运算，以聚合整数列和可转换或舍入为整数值的列。比如十进制数4对应的二进制是100，十进制数5对应的二进制是101，如果按照二进制位，每一位都取and，那么结果是二进制100，对应十进制数4。可能在这里说比较复杂，大家自己拿出笔和纸，在纸上写出来就会变得非常容易理解了。位聚合运算在大数据分析当中的消费者行为分析中有较多应用。下面我们就看看位聚合and函数BIT_AND_AGG。

SQL> WITH x AS (SELECT 4 c1 FROM dual UNION ALL SELECT 5 FROM dual)
  2  SELECT BIT_AND_AGG(c1) FROM x;

BIT_AND_AGG(C1)
---------------
              4

按照相同的法则，如果我们还是以数字4和5举例，十进制数字4的二进制表示为100，十进制数字5的二进制表示为101，如果他们按位取or，得到的应该是101.也就是十进制数字5.

SQL> WITH x AS (SELECT 4 c1 FROM dual UNION ALL SELECT 5 FROM dual)
  2  SELECT BIT_OR_AGG(c1) FROM x;

BIT_OR_AGG(C1)
--------------
             5

最后我们看看逻辑异或XOR，下面是逻辑异或的运算规则。

我们依旧使用十进制数字4和5进行举例，4对应二进制100,5对应二进制101，按照上面的计算规则，4 XOR 5=001，也就是1.

SQL> WITH x AS (SELECT 4 c1 FROM dual UNION ALL SELECT 5 FROM dual)
  2  SELECT BIT_XOR_AGG(c1) FROM x;

BIT_XOR_AGG(C1)
---------------
              1

今天的内容就到这里，我们将在下次课当中为大家介绍Oracle Database 21c当中的自动化Zone映射和SecureFile LOBs管理，期待您的关注，谢谢。

文章被以下合辑收录

Oracle Database 21c 专栏（共73篇）

本专栏收录 Oracle 21c相关文章、文档。

Oracle Database 21c新特性讲座（2）——使用SQL宏作为标量表达式、位聚合函数

文章被以下合辑收录

评论