手把手教你21c新特性(1)：软件安装与应用程序开发

甲骨文云技术 2022-04-07

1334

在2020年12月8日，Oracle首先在OCI云端为大家提供了Oracle Database 21c，并且在2021年8月13日发布了运行在Linux上的On-Premises版本。在21c版本中，提供了215个（截至目前）新特性，我们将通过系列文章，为大家介绍这些新特性。我们按照主题，将这些新特性分为如下几个主题：

软件安装
应用程序开发
大数据和数据仓库
高性能与高可用
数据库工具
数据库安全
数据库管理

在今天的内容中，我们将为您介绍数据库安装与应用程序开发新特性。从19c开始，大家已经喜欢使用rpm的方式安装数据库，因为通过这种方式，您只需执行几个简单的命令，就可以迅速完成数据库软件的安装及数据库的创建。接下来就为您介绍Oracle Database 21c的安装，今天我们使用的操作系统是Oracle Enterprise Linux，您也可以使用您所熟悉的其他Linux操作系统。

1、软件下载

通过如下地址下载Oracle Database 21c的RPM文件。

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

2、操作系统配置

在Linux系统上安装Oracle Database往往需要经过一个比较复杂的系统配置过程，如果配置出错将导致安装失败。在十多年前，比如在OEL5的时候，我们就为大家提供了操作系统预处理包，在Linux 7和8也不例外，您可以根据自己的操作系统，来执行下面的预安装操作。

Oracle Linux 7

# yum install oracle-database-preinstall-21c

Oracle Linux 8

# dnf install oracle-database-preinstall-21c

预安装的执行界面如下所示，一般经过几分钟的时间，就可以看到命令提示配置成功。

3、安装数据库软件

# yum -y localinstall oracle-database-ee-21c-1.0-1.ol8.x86_64.rpm

4、创建数据库

您可以根据提示，使用root身份执行如下脚本。

/etc/init.d/oracledb_ORCLCDB-21c configure

5、设定环境变量并访问数据库

关于环境变量的设置，想必大家都会非常熟悉，我们首先需要设定ORACLE_HOME，然后在PATH当中将ORACLE_HOME/bin添加进去，这样我们就可以非常方便地使用sqlplus等工具。

接下来，我们将为您介绍在Oracle Database 21c中，关于应用程序开发的新特性。在最新版本的 Oracle 数据库 21c 中提供了新运算符、参数、表达式和 SQL 宏。Oracle 21c 引入了三个新的运算符，EXCEPT、EXCEPT ALL 和 INTEREST ALL。SQL 集合运算符现在支持 ANSI SQL 中定义的所有关键字。新的运算符 EXCEPT [ALL] 在功能上等同于 MINUS [ALL]。运算符 MINUS 和 INTERSECT 现在都支持关键字 ALL。您可以在初始化参数中使用表达式，这些表达式在数据库启动期间进行评估。您现在可以指定一个考虑到当前系统配置和环境的表达式。这在 Oracle 自治数据库环境中特别有用。您可以创建 SQL 宏 (SQM)，将常见的 SQL 表达式和语句分解为可在其他 SQL 语句中使用的可重用、参数化构造。SQL 宏可以是标量表达式，通常用于 SELECT 列表、WHERE、GROUP BY 和 HAVING 子句，从而封装计算和业务逻辑，它也可以是Table表达式，通常用于 FROM 子句。

实验1：新的集合操作符

集合运算符对我们来说并不陌生，在21c之前，我们已经使用了如下这些集合运算符。

UNION
UNION ALL
INTERSECT
MINUS

在21c中，新添加了如下三个集合运算符 EXCEPT、EXCEPT ALL和INTERSECT ALL。接下来我们就通过实验的方式为您介绍。

在这个实验中，我们将使用到两张表，inventories和order_items分别表示库存的商品和客户订购商品，首先让我们查询这两张表中的记录数。

SQL> SELECT count(distinct product_id) FROM inventories;

COUNT(DISTINCTPRODUCT_ID)
-------------------------
                      208
SQL> SELECT count(distinct product_id) FROM order_items;

COUNT(DISTINCTPRODUCT_ID)
-------------------------
                      185

EXCEPT运算符

我们现在想知道，有多少商品是在库存中，但是从来没有被过买过。我们可以通过except运算符来计算。

SELECT count(*) FROM
        (SELECT product_id FROM inventories
EXCEPT
SELECT product_id FROM order_items);
  COUNT(*)
----------
        84

接下来，我们想了解有多少商品被订购过，但是现在已经与在库存当中。

SQL> SELECT count(*) FROM
        (SELECT product_id FROM order_items
            EXCEPT
        SELECT product_id FROM inventories);  

  COUNT(*)
----------
        61

EXCEPT ALL运算符

还是上面的两个语句，我们将EXCEPT换成EXCEPT ALL，看看会有什么效果？

SQL> SELECT count(*) FROM
(SELECT product_id FROM inventories
EXCEPT ALL
SELECT product_id FROM order_items);  

COUNT(*)
----------
826

SQL> SELECT count(*) FROM
(SELECT product_id FROM order_items
EXCEPT ALL
SELECT product_id FROM inventories);  

COUNT(*)
----------
       379

加上ALL之后，如果有重复的记录，也不会合并成一条记录进行计算，所以在上面结果中，两条语句得到的结果数都要比之前EXCEPT要多。

INTERSECT与INTERSECT ALL

接下来，我们通过具体SQL语句来比较INTERSECT和INTERSECT ALL的区别，是否使用ALL，结果与之前的EXCEPT一样，如果使用ALL，多条重复的记录，不会当做一条记录进行计算，其实这与我们所熟识的UNION和UNION ALL一样。

SQL>  SELECT count(*) FROM
    (SELECT product_id FROM inventories
    INTERSECT
    SELECT product_id FROM order_items);  

  COUNT(*)
----------
       124

SQL> SELECT count(*) FROM
    (SELECT product_id FROM order_items
    INTERSECT
    SELECT product_id FROM inventories);  

  COUNT(*)
----------
       124

SQL>  SELECT count(*) FROM
    (SELECT product_id FROM order_items
    INTERSECT ALL
    SELECT product_id FROM inventories); 

  COUNT(*)
----------
       286

实验2：在初始化参数中使用表达式

关于初始化参数，我想大家都不陌生，但有时候我们在设置这些参数的时候，难免出错，比如SGA_MAX_SIZE为10GB，而将SGA_TARGET设置为16GB，然后重启数据库，发现数据库无法启动。上过OU课程的朋友应该记得，我们当时为大家介绍了去哪里找到上一次正确的配置文件，以及如何通过导入导出的方式解决错误初始化参数配置导致数据库无法启动的问题。在21c当中，我们可以在初始化参数当中使用表达式，比如将SGA_TARGET设置为SGA_MAX_SIZE的80%，我们就可以通过如下方式完成：

SQL> ALTER SYSTEM SET sga_target = 'sga_max_size*80/100';

System altered.

SQL> SHOW PARAMETER sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 1360M
sga_min_size                         big integer 0
sga_target                           big integer 1088M

同理，我们可以将job_queue_processes设定为 processes的10%，如下所示：

SQL> ALTER SYSTEM SET job_queue_processes='processes*10/100' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     64
log_archive_max_processes            integer     4
processes                            integer     640

在这是初始化参数时，不只可以使用上面的算术式，也可以使用函数，比如下面语句中的min，用来取得两个参数中较小的那个。

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 'MIN(40, PROCESSES * .1)' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     40
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     64
log_archive_max_processes            integer     4
processes                            integer     640

如果我们对某个参数进行了修改，比如上面的processes，那么和它相关的参数也将自动修改。

SQL> ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE;
SQL> shutdown immediate
SQL> startup
SQL> SHOW PARAMETER processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     40
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     50
log_archive_max_processes            integer     4
processes                            integer     500

实验3：使用 SQM 标量和table表达式

SQL 宏是一种直接在 SQL 中封装复杂处理逻辑的新方法。SQL 宏允许开发人员将复杂的处理封装在称为“宏”的新结构中，然后可以在 SQL 语句中使用。Oracle Database 21c提供两种类型的 SQL 宏：SCALAR 和 TABLE。SCALAR 表达式可用于 SELECT 列表、WHERE/HAVING、GROUP BY/ORDER BY 子句。TABLE 表达式可用在 FROM 子句中。

SQL 宏与普通 PL/SQL 函数相比有一个重要优势，因为它们使可重用的 SQL 代码对优化器完全透明，这带来了巨大的好处。它使优化器可以转换原始代码以高效执行效率，因为宏函数内部的底层查询可以合并到外部查询中。这意味着 PL/SQL 和 SQL 之间没有上下文切换，并且宏函数内部的查询，现在在与外部查询相同的快照下执行。所以我们得到了更快的执行效率。

首先我们创建一个scalar的SQM，下面的宏只有一个用于处理字符串的主要语句：

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

Function created.

SQL>

接下来，我们在SQL语句中我们刚刚创建的宏，我们将看到，Last_Name被输出两遍。

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

LAST_NAME                 CONCAT_SELF(LAST_NAME,2)
------------------------- ----------------------------------------
Abel                      AbelAbel
Ande                      AndeAnde
Atkinson                  AtkinsonAtkinson
Austin                    AustinAustin
Baer                      BaerBaer
Baida                     BaidaBaida
Banda                     BandaBanda
Bates                     BatesBates
Bell                      BellBell
Bernstein                 BernsteinBernstein
Bissot                    BissotBissot

如果您愿意，您可以在查询中，修改宏中的参数，比如将它修改为3：

SQL> SELECT last_name, concat_self(last_name,3) FROM hr.employees;

LAST_NAME                 CONCAT_SELF(LAST_NAME,3)
------------------------- ----------------------------------------
Abel                      AbelAbelAbel
Ande                      AndeAndeAnde
Atkinson                  AtkinsonAtkinsonAtkinson
Austin                    AustinAustinAustin
Baer                      BaerBaerBaer
Baida                     BaidaBaidaBaida
Banda                     BandaBandaBanda
Bates                     BatesBatesBates
Bell                      BellBellBell
Bernstein                 BernsteinBernsteinBernstein
Bissot                    BissotBissotBissot

接下来，我们将使用TABLE表达式宏，来实现多态视图。首先我们创建一个普通的视图：

SQL> CREATE VIEW v_budget
 AS SELECT department_id, sum(salary) v_budget
    FROM hr.employees
    GROUP BY department_id;  

View created.
SQL> SELECT * FROM v_budget WHERE department_id IN (10,50);

DEPARTMENT_ID   V_BUDGET
------------- ----------
           50     156400
           10       4400

接下来，我们可以使用宏来实现相同的效果：

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

Function created.

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

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

如果觉得上面的宏太简单，那么我们可以创建一个稍微复杂的宏，下面的宏，在使用时需要给出参数，因为宏内部的逻辑十分简单，所以在这里不再赘述，请注意宏中的“job_id = budget_per_job.job_id”：

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

Function created.

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

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

为了弄清宏是如何运行的，我们可以重写上面的宏，然后通过DBMS_OUTPUT打印出具体执行的查询语句，通过观察数据结果，您就明白为什么使用宏，可以提高查询效率了。这次我们将上面宏中的参数由SH_CLERK修改为ST_CLERK。

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

Function created.

SQL> SET serveroutput on
SQL> SET LONG 20000
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_MACRO列显示了宏的类型。

SQL> SELECT object_name, sql_macro, object_type FROM user_procedures;

OBJECT_NAME                    SQL_MA OBJECT_TYPE
------------------------------ ------ -------------
SECURE_DML                     NULL   PROCEDURE
ADD_JOB_HISTORY                NULL   PROCEDURE
CONCAT_SELF                    SCALAR FUNCTION
BUDGET                         TABLE  FUNCTION
BUDGET_PER_JOB                 TABLE  FUNCTION
SECURE_EMPLOYEES                      TRIGGER
UPDATE_JOB_HISTORY                    TRIGGER