大家好！我是只谈技术不剪发的 Tony 老师。

今天给大家介绍一下 Oracle Databse 21c SQL 语句的一些功能增强和新特性。如果你觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

SQL 集合运算符

SQL 集合运算符现在支持 ANSI SQL 标准中定义的所有关键字。新的运算符 EXCEPT [ALL] 等价于 MINUS [ALL]，MINUS 和 INTERSECT 运算符现在可以支持 ALL 选项。完全兼容 ANSI 表示使得 Oracle 更加兼容其他数据库产品，而且使得从其他数据库产品迁移到 Oracle 数据库更加容易。

示例中的第一个和第二个语句使用 EXCEPT 运算符将两个查询语句的结果合并成一个结果，只返回了属于第一个查询但不属于第二个查询的数据（等价于 MINUS）。

第三个和第四个语句使用 EXCEPT ALL 运算符将两个查询语句的结果合并成一个结果，只返回了属于第一个查询但不属于第二个查询的数据（等价于 MINUS ALL），执行操作时不会排除重复值。

第五个和第六个语句使用 INTERSECT 运算符将两个查询语句的结果合并成一个结果，返回了同时属于第一个查询和第二个查询的数据。

初始化参数表达式

Oracle 21c 允许在设置初始化参数时使用表达式，表达式中可以引用当前系统配置和环境变量；而在之前的版本中，用户只能指定字面值。这一功能对于 Oracle 自治数据库非常有用。

首先，我们使用 SYSTEM 账号登录一个容器数据库：

$ sqlplus system
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Enter password:Last Successful login time: Mon Mar 16 2020 08:49:41 +00:00
Connected to:

将 SGA_TARGET 设置为 2G。

SQL> ALTER SYSTEM SET sga_target = 15G;ALTER SYSTEM SET sga_target = 15G*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-00823: Specified value of sga_target greater than sga_max_size

以上命令执行失败，我们将 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     FALSElock_sga                             boolean     FALSEpre_page_sga                         boolean     TRUEsga_max_size                         big integer 13824Msga_min_size                         big integer 0sga_target                           big integer 11072M

然后，我们将 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     1db_writer_processes                  integer     1gcs_server_processes                 integer     0global_txn_processes                 integer     1job_queue_processes                  integer     40log_archive_max_processes            integer     4processes                            integer     400

接着将 AQ_TM_PROCESSES 设置为 40 和 processes 参数的 10% 之间的最小值。

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     40db_writer_processes                  integer     1gcs_server_processes                 integer     0global_txn_processes                 integer     1job_queue_processes                  integer     40log_archive_max_processes            integer     4processes                            integer     400

想一想，如果我们此时修改 processes 参数的值会发生什么？

将 SPFILE 文件中的 processes 参数设置为 500。

SQL> ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE;
System altered.

SQL>Restart the CDB instance.

SQL> CONNECT / AS SYSDBAConnected.SQL> SHUTDOWN IMMEDIATEDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUPORACLE instance started.

Total System Global Area 1140848912 bytesFixed Size                  9566480 bytesVariable Size             352321536 bytesDatabase Buffers          771751936 bytesRedo Buffers                7208960 bytesDatabase mounted.Database opened.SQL>

查看 processes 和 aq_tm_processes 参数的设置。

SQL> SHOW PARAMETER processes

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

40 和 processes 参数的 10% 之间的最小值变成了 40（因为 500 的 10% 等于 50）。用于设置 aq_tm_processes 参数的表达式在数据库实例重启之后仍然有效。

我们将 db_recovery_file_dest 的值设置为 $HOME 变量。

SQL> ALTER SYSTEM SET db_recovery_file_dest='$HOME' SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER db_recovery_file_dest

NAME                                 TYPE        VALUE------------------------------------ ----------- ----------------------------db_recovery_file_dest                string      $HOMEdb_recovery_file_dest_size           big integer 250GSQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> HOST$ cd $HOME$ ls -ltR | more.:total 20./CDB21_FRA1XN:total 4drwxr-x--- 3 oracle oinstall 4096 Dec 14 10:21 archivelog

./CDB21_FRA1XN/archivelog:total 4drwxr-x--- 2 oracle oinstall 4096 Dec 14 10:21 2020_12_14

./CDB21_FRA1XN/archivelog/2020_12_14:total 499832-rw-r----- 1 oracle oinstall 511804416 Dec 14 10:21 o1_mf_1_4_hxgh534q_.arc-rw-r----- 1 oracle oinstall     11264 Dec 14 10:21 o1_mf_1_6_hxgh575g_.arc-rw-r----- 1 oracle oinstall      2560 Dec 14 10:21 o1_mf_1_5_hxgh54jb_.arc...$ exitSQL> EXIT$

DDL 语句占位符

SQL DDL 语句中现在可以包含占位符，用于替代某些硬编码的内容。例如，CREATE USER 语句中额用户名或者密码可以使用占位符表示。OCI 程序可以在发生语句到 Oracle 数据库之前替换这些占位符的数值。这种方式类似于数据绑定，但是操作发生在客户端。

这种占位符可以提高应用安全，因为不再需要在 DDL 语句中硬编码敏感信息。

OCIStmtPlaceholderSubstitute() 函数负责替换 SQL 语句中的占位符。包含占位符的语句中不能出现绑定变量，OCI 占位符和绑定变量的实现不同。

以下语句不能包含 OCI 占位符：SELECT、UPDATE、DELETE、INSERT、BEGIN\DECLARE、RETURNING、CALL、MERGE、ROLLBACK、COMMIT 以及 FLASHBACK 等，因为它们支持绑定变量。其他 SQL 语句可以包含 OCI 占位符，例如 CREATE、DROP、ALTER 以及 EXPLAIN 等。

SQL 宏

用户可以创建 SQL 宏（SQM），将常用的 SQL 表达式和语句分解成可重用的参数化结构，这些结构可以在其他 SQL 语句中使用。SQL 宏可以是标量表达式（通常用于 SELECT 列表、WHERE、GROUP BY 以及 HAVING 子句）或者类似于参数化视图的表表达式（通常用于 FROM 子句）。

SQL 宏提高了开发人员的工作效率，简化了协作开发，提高了代码质量。

以下示例创建了一个 SQL 宏标量表达式，并且用于查询语句的 SELECT 列表。

以下示例创建了一个 SQL 宏标量表达式，并且用于查询语句的 SELECT 列表和 WHERE 子句。

以下示例创建了一个 SQL 宏表表达式，并且用于查询语句的 FROM 子句。

ITPub博客

Oracle Databse 21c SQL 新特性

最新文章