探索 Oracle Database 23ai 中的 SQL 功能
- 介绍
- 目标
- 前提条件
- 功能 1：使用 `FROM` 子句
- 功能 2：使用 `BOOLEAN` 数据类型
- 功能 3：使用 `IF NOT EXISTS` DDL 子句
- 功能 4：使用 `INSERT` 插入多行
- 功能 5：使用新的 `VALUE` 构造函数
- 功能 6：在 `GROUP BY` 子句中使用别名
- 功能 7：使用 `UPDATE` 和 `MERGE` 语句的 `RETURNING` 子句
- 功能 8：在 `UPDATE` 和 `DELETE` 中使用连接
- 功能 9：使用注释
- 功能 10：使用 SQL 域
- Oracle Database 23ai 功能的限制和限制
介绍
在这个教程中，我们将学习 10 个您需要知道的功能及其与现有对应功能的比较。这些功能包括：
FROM子句（可选）。- SQL 中的
BOOLEAN。 - 数据定义语言（DDL）中的
IF NOT EXISTS子句。 - 多值
INSERT。 - 新表
VALUE构造函数。 GROUP BY子句中的别名。UPDATE和MERGE语句的RETURNING子句。UPDATE和DELETE中的连接。- 注释，数据库对象的新元数据。
- 使用 SQL 域的轻量级对象类型。
目标
-
不使用
FROM子句的 SQL：通过删除在选择表达式或内置函数时使用FROM子句的要求，使查询更简单。 -
在 SQL 中实现原生
BOOLEAN数据类型：在 SQL 表、查询和条件中利用原生BOOLEAN数据类型，以便更直观地处理真/假逻辑。 -
在 DDL 语句中使用
IF NOT EXISTS：通过有条件地执行CREATE和DROP语句，简化对象创建和删除逻辑，无需额外的 PL/SQL 检查。 -
执行多值
INSERT操作：通过在单个INSERT语句中插入多行，提高代码可读性并减少与数据库的往返次数。 -
使用表值构造函数创建内联数据集：使用
VALUES构造函数直接在 SQL 中创建临时行集，支持MERGE、SELECT或比较等操作。 -
在
GROUP BY子句中引用列别名：通过允许在GROUP BY中使用SELECT别名而非重复表达式，增强查询的可读性。 -
在
UPDATE和MERGE中利用RETURNING子句：直接从UPDATE和MERGE语句中检索受影响的数据，无需后续查询。 -
在
UPDATE和DELETE语句中执行连接：直接在UPDATE和DELETE操作中使用JOIN逻辑，根据相关表的条件修改或删除记录。 -
使用元数据注释数据库对象：使用
ANNOTATION文档化数据库对象，以存储描述性元数据（例如所有者、用途），便于维护和内省。 -
使用 SQL 域定义轻量级对象类型：创建可重用的域类型和约束，以在多个表中强制执行一致性和强类型。
前提条件
-
基础 SQL 知识。
-
了解 SQL 语法：
SELECT、INSERT、UPDATE、DELETE、JOIN、GROUP BY等。 -
熟悉关系数据库概念和数据类型。
-
-
使用 Oracle Database 23ai 及其早期版本的经验。
-
了解 Oracle Database 19c、Oracle Database 21c 及更早版本中 DDL、数据操作语言（DML）和 PL/SQL 的工作方式。
-
了解 Oracle 特定功能，如
DUAL、MERGE、RETURNING INTO等。
-
-
访问 Oracle Database 23ai 环境。
-
访问 Oracle Database 23ai（本地设置、云实例或 Oracle Live SQL）。
-
某些功能（如 SQL 域或
BOOLEAN）仅在 Oracle Database 23ai 中可用。
-
-
SQL*Plus、SQLcl 或 GUI 工具（如 SQL Developer 或 DataGrip）。能够在兼容的界面中运行和测试 SQL 语句。
-
PL/SQL 基础（用于高级功能）。用于
RETURNING INTO、过程块和处理动态 SQL。 -
约束和数据完整性规则的知识。需要了解 SQL 域和表约束。
-
熟悉 Oracle 数据字典视图。用于查询注释或元数据。例如，
USER_TABLES、USER_ANNOTATIONS。 -
Oracle Database 中的角色和权限。创建/修改表、域和注释的能力需要适当的用户权限。
-
版本意识。确保您的工具和客户端支持 Oracle Database 23ai 功能（较旧的驱动程序或工具可能会失败）。
-
（可选）接触其他现代 SQL 方言（PostgreSQL、MySQL 等）。这将帮助您理解新功能（如
VALUES、BOOLEAN和IF EXISTS）的跨兼容性。
功能 1：使用 FROM 子句
Oracle Database 23ai 引入的一个有趣功能是 SELECT 语句中 FROM 子句的可选性。在此版本之前，FROM 子句是必需的。
以下是 Oracle Database 23ai 中不使用 FROM 子句功能的一些潜在好处。
-
选择当前日期以便于数据操作。
SELECT CURRENT_DATE;
-
无需涉及表数据的数学运算或计算。
SELECT 25.50*25.25; 25.50*25.25 ----------- 643.875 耗时: 00:00:00.002 1 行已选中。 -
不使用
FROM子句的 PL/SQL 块。CREATE SEQUENCE empno_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 1000; 序列 EMPNO_SEQ 已创建。 耗时: 00:00:00.005 declare v1 number; begin select empno_seq.nextval into v1; dbms_output.put_line ('v1= '||v1); end; / v1= 1 PL/SQL 过程已成功完成。 耗时: 00:00:00.009 -
使用内置或用户定义的函数执行操作或检索值。
SELECT DBMS_RANDOM.VALUE() as random_number; -
无需依赖表数据的字符串操作或转换。
SELECT UPPER('oracle') AS uppercase_text;
-
不使用表的条件或逻辑表达式。
SELECT CASE WHEN 10 > 5 THEN 'True' ELSE 'False' END AS result;
功能 2：使用 BOOLEAN 数据类型
Oracle Database 23ai 引入了新的 BOOLEAN 数据类型。这使得可以使用真正的布尔列/变量，而不是用数值或 Varchar 模拟它们。能够编写布尔谓词简化了 SQL 语句的语法。
-
创建一个名为
TEST_BOOLEAN的表。CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN); 表 TEST_BOOLEAN 已创建。 耗时: 00:00:00.004 -
向新表中输入数据。
IS_SLEEPING的值将是NOT NULL并默认为FALSE。ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);
ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE); 表 TEST_BOOLEAN 已修改。 耗时: 00:00:00.014在这里，您可以看到 Mick、Keith 和 Ron 的不同布尔输入。所有输入都是有效的。对于 Mick，使用默认的 FALSE 值 - Mick 没有睡觉。
INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Mick', default); 1 行已插入。 耗时: 00:00:00.006对于 Keith，我们使用 NO 值 - Keith 没有睡觉。
INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO'); 1 行已插入。 耗时: 00:00:00.002对于 Ron，我们使用 1 值 - Ron 正在睡觉。
INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Ron',1); 1 行已插入。 耗时: 00:00:00.002 -
查看基于我们布尔值的结果。
SELECT * FROM test_boolean;您不再需要记住您设置的布尔系统类型。如我们所示，使用 0/1、True/False、Yes/No 或任何其他常见输入将返回准确的表值。
功能 3：使用 IF NOT EXISTS DDL 子句
从 Oracle Database 23ai 开始，新的 IF NOT EXISTS DDL 子句允许决定如何处理 DDL 错误。这简化了 DDL 脚本编写，因为由于对象存在或不存在导致的潜在错误可以被脚本隐藏。
-
首先，测试不使用此新功能。运行以下语句。
DROP TABLE DEPT;由于没有现有的
DEPT表可删除，我们将看到错误：ORA-00942: 表或视图不存在。 -
然而，在 Oracle Database 23ai 中，我们可以使用
DROP IF EXISTS而不出现错误。这让我们在避免错误的同时心安理得。现在，运行相同的语句，但包含此新的IF EXISTS功能。DROP TABLE IF EXISTS DEPT; -
类似地，我们可以使用此功能在表不存在时创建表。创建
DEPT表。CREATE TABLE IF NOT EXISTS DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ;
-
使用此功能在本教程中创建更多示例表。在这里，我们将创建一个名为
EMP的员工表。CREATE TABLE IF NOT EXISTS EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); 表 EMP 已创建。 耗时: 00:00:00.006
功能 4：使用 INSERT 插入多行
另一个确保与其他常用数据库管理系统更好共存和兼容性的有趣功能是多值 INSERT 语句。
-
在 Oracle 数据库的早期版本中，例如，插入多行需要为每一行单独插入语句。
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES'); INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO'); INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');Oracle Database 23ai 引入了新的语法，允许在单个
INSERT语句中插入所有这些行，因此您可以在一个 DML 中插入几个元组。运行以下语句。INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'), (50,'HR','LOS ANGELES'), (60,'IT','SAN FRANCISCO'), (70,'MANUFACTURING','DETROIT');
除了与其他数据库更好的兼容性外，此语句还可以用于确保在自动提交模式下的一些插入操作的一致性。这对于使用此模式处理数据的 Oracle APEX 应用程序可能很重要。
-
运行以下语句以使用此功能为
EMP表填充值。INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20), (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30), (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30), (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30), (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30), (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10), (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20), (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10), (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30), (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20), (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30), (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20), (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
功能 5：使用新的 VALUE 构造函数
从 Oracle 数据库 23ai 开始，表值构造函数已扩展。现在可以在 INSERT 语句中使用，以便在单个命令中创建多行。它还可以在 SELECT 语句和视图因式分解语法中使用。在这种情况下，它简化了语句的语法，并避免使用 DUAL 表。
以下语句看起来像是一种即时的表函数。
SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);
功能 6：在 GROUP BY 子句中使用别名
Oracle Database 23ai 引入了在 SELECT 语句的 GROUP BY 子句中使用别名的能力。此功能简化了编写具有复杂表达式的查询，并确保与某些其他关系数据库（如 Teradata、MySQL 和 PostgreSQL）的更好兼容性。
例如：
SELECT to_char(hiredate,'YYYY') "Year", count(*) FROM emp GROUP BY to_char(hiredate,'YYYY');
在 Oracle Database 23ai 中，这可以以更简单的方式编写，如下图所示：
功能 7：使用 UPDATE 和 MERGE 语句的 RETURNING 子句
此子句曾作为 EXECUTE IMMEDIATE 语句的一部分实现。然而，在 Oracle Database 23ai 中，我们可以在传统的、静态的 DML 语句中找到它。
-
在这种情况下，它允许从处理的行获取列的旧值和新值。首先，让我们看看 King 的当前薪水。
SELECT ename, sal FROM emp WHERE ename = 'KING'; -
为了在 LiveSQL 中使用变量，我们将将我们的语句包装在 PL/SQL 中。运行此脚本。它首先为旧薪水和新薪水创建变量，然后使用
RETURNING子句更新 King 的薪水以设置我们的变量。然后我们将查看结果。BEGIN DECLAREold_salary NUMBER;new_salary NUMBER; BEGINUPDATE empSET sal = sal + 1000WHERE ename = 'KING'RETURNING OLD sal, NEW sal INTO old_salary, new_salary;DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary); END; END;
Old Salary: 6000 New Salary: 7000此示例使用了
UPDATE语句，但RETURNING子句可以类似地用于MERGE语句。
功能 8：在 UPDATE 和 DELETE 中使用连接
您可以使用基于外部表条件的连接更新表数据。无需子查询或 IN 子句。
-
运行以下语句以查看研究部门的员工薪资信息。
select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH'; -
在 Oracle Database 23ai 之前，我们需要使用嵌套语句来更新薪资信息。
UPDATE emp e set e.sal=e.sal*2 WHERE e.deptno in (SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno and d.dname='RESEARCH');在 Oracle Database 23ai 中，您可以这样使用它：
UPDATE emp e set e.sal=e.sal*2 FROM dept d WHERE e.deptno=d.deptno and d.dname='RESEARCH';
-
您可以看到薪资已成功更新。
select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';
功能 9：使用注释
注释是数据库对象的可选元数据。注释是名称-值对或仅名称。名称和可选值是自由格式的文本字段。注释表示为数据库对象的从属元素，该注释已添加。支持的模式对象包括表、视图、物化视图和索引。使用注释，您可以存储和检索有关数据库对象的元数据。您可以使用它来自定义业务逻辑、用户界面或将元数据提供给元数据存储库。它可以在表或列级别使用 CREATE 或 ALTER 语句添加。
使用注释，您可以存储和检索有关数据库对象的元数据。您可以使用它来自定义业务逻辑、用户界面或将元数据提供给元数据存储库。
-
创建带有列和表注释的注释表
EMP_ANNOTATED_NEW。CREATE TABLE emp_annotated_new (empno number annotations(identity, display 'person_identity', details 'person_info'), ename varchar2(50), salary number annotations (display 'person_salary', col_hidden)) annotations (display 'employee_table');
-
数据字典视图，如
USER_ANNOTATIONS和USER_ANNOTATIONS_USAGE，可以帮助监控使用情况。SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;
功能 10：使用 SQL 域
SQL 域是属于模式的字典对象，它封装了一组可选的属性和约束，并使用 CREATE DOMAIN 语句创建。域提供约束、显示、排序和注释属性。定义 SQL 域后，您可以定义表列与该域关联，从而将域的可选属性和约束显式应用于这些列。
SQL 域允许用户声明列的预期用途。它们是字典对象，因此可以轻松重用抽象的域特定知识。
-
创建名为
yearbirth的域和名为person的表。CREATE DOMAIN yearbirth as number(4) constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900)) display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100) order (yearbirth -1900) annotations (title 'yearformat');
CREATE TABLE person (id number(5), name varchar2(50), salary number, person_birth number(4) DOMAIN yearbirth ) annotations (display 'person_table');
desc person;Name Null? Type* * *ID NUMBER(5) NAME VARCHAR2(50) SALARY NUMBER PERSON_BIRTH NUMBER(4) DOMAIN YEARBIRTHINSERT INTO person values (1,’MARTIN’,3000, 1988); -
使用新函数
DOMAIN_DISPLAY可以显示属性。SELECT DOMAIN_DISPLAY(person_birth) FROM person; -
域的使用情况和注释可以通过数据字典视图监控。让我们查看
user_annotations_usage。SELECT * FROM user_annotations_usage;定义可重用的域类型（轻量级对象）。 CREATE DOMAIN EmailAddress AS VARCHAR2(100) CHECK (REGEXP_LIKE(VALUE, ‘^\[^@\]+@\[^@\]+.\[^@\]+$’)); CREATE TABLE users ( user_id NUMBER, email EmailAddress );
Oracle Database 23ai 功能的限制和限制
-
FROM子句。-
仅适用于简单表达式，如函数、字面量或变量。
-
不能用于涉及表、连接或子查询的查询。
-
不支持在 PL/SQL 上下文中使用游标循环，这些循环期望
FROM子句。
-
-
原生
BOOLEAN数据类型。-
可以用于表列和表达式。
-
不可索引，
BOOLEAN数据类型的列不能被索引。 -
并非所有客户端工具或报表工具都直接支持（可能需要将其转换为 0/1 或 Y/N）。
-
某些较旧的 API 或驱动程序不支持（JDBC/ODBC 客户端可能需要更新）。
-
-
DDL 中的
IF NOT EXISTS。-
简化了幂等 DDL 脚本。
-
仅适用于特定对象：
TABLE、INDEX、SEQUENCE、VIEW等。 -
并非所有对象类型都支持此功能（例如，
TRIGGER、SYNONYM可能仍需要手动检查）。 -
不支持较旧的 Oracle 版本。
-
-
多值
INSERT。-
批量插入的清晰语法。
-
仅限于显式值集，不能在相同的
VALUES子句中使用SELECT或子查询插入。 -
不能与
RETURNING子句结合使用，以便在一步中为所有插入的行返回值。
-
-
表值构造函数（
VALUES子句）。-
适用于小型、临时的行集。
-
有行限制（通常为 999 行或更少，具体取决于上下文）。
-
不适用于大规模加载，更好地使用临时表或暂存区域处理大型数据集。
-
-
GROUP BY子句中的别名。-
使查询更容易阅读和编写。
-
不支持所有分析函数或涉及子查询列的复杂查询。
-
可能在查询中引起混淆，其中别名被重复使用（例如，在内部查询中使用相同名称）。
-
-
UPDATE/MERGE中的RETURNING子句。-
减少了 DML 后的
SELECT需要。 -
仅能返回实际修改的行的值。
-
不能用于批量更新，除非使用
FORALL，必须使用 PL/SQL 并显式RETURNING BULK COLLECT。
-
-
UPDATE和DELETE中的连接。-
使多表逻辑更简单。
-
仅支持某些上下文中的
INNER JOIN和LEFT JOIN类型。 -
如果使用较旧的工具或需要与较早的 Oracle 版本兼容，可能需要重写
MERGE逻辑。
-
-
注释。
-
非常适合文档化。
-
注释仅是元数据，无法在运行时强制执行。
-
需要使用 Oracle 数据字典视图（
*_ANNOTATIONS）来检索。 -
尚未集成到 Oracle Data Pump 导出/导入或复制工具中。
-
-
使用 SQL 域的轻量级对象类型。
-
可重用，一致的类型强制。
-
仍然不如完整的对象类型强大（没有方法、属性）。
-
没有继承或组合，仅用于原始约束强制。
-
无法在域本身中定义默认值（默认值仍在列级别指定）。
-
一般限制
-
工具兼容性：许多 GUI 工具和较旧的 Oracle 客户端库可能尚未完全支持 Oracle Database 23ai 语法（特别是
VALUES、BOOLEAN、DOMAIN）。 -
导出/导入：某些功能（如注释或域）可能不会在较旧的
expdp/impdp工作流中保留。 -
实验性行为：由于这些功能是新的，某些功能可能会在次要版本中发展——请频繁查看补丁说明和文档。
)
-工厂模式)
