注意：

了解 Oracle Database 23ai 中的 SQL 特性

简介

我们将在本教程中学习 10 个你需要知道的功能，以及它们如何与现有功能进行比较。这些功能包括：

目标

先决条件

功能 1：使用 FROM 子句

Oracle Database 23ai 中引入的一个有趣功能是在 SELECT 语句中可选使用 FROM 子句。在此版本之前，需要 FROM 子句。

以下是 Oracle Database 23ai 中没有 FROM 功能的 SELECT 的一些潜在优势。

功能 2：使用 BOOLEAN 数据类型

Oracle Database 23ai 引入了新的 BOOLEAN 数据类型。这使用真正的布尔值列/变量，而不是用数字值或 Varchar 模拟它们。编写布尔谓词的功能简化了 SQL 语句的语法。

  1. 创建一个名为 TEST_BOOLEAN 的表。

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. 将数据输入新表中。值 IS_SLEEPING 将 NOT NULL 设置为 FALSE 作为默认值。

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    图中显示了如何将布尔值用于 SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    在这里，您可以看到 Mick、Keith 和 Ron 的不同类型的布尔值输入。所有项都有效。对于 Mick，使用缺省的 FALSE 值 - Mick 未休眠。

    1 row inserted.

Elapsed: 00:00:00.006

    对于 Keith，我们使用 NO 值 -Keith 没有休眠。

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    对于 Ron，我们使用 1 值 - Ron 正在休眠。

  3. 根据布尔值查看一些结果。

    SELECT * FROM test_boolean;

    您不再需要记住您设置了什么类型的布尔系统。如前所述，使用 0/1、True/False、Yes/No 或任何其他公用输入将返回准确的表值。

功能 3：使用 IF NOT EXISTS DDL 子句

从 Oracle Database 23ai 开始，新的 IF NOT EXISTS DDL 子句允许您决定如何处理 DDL 错误。这简化了 DDL 脚本编写过程，因为脚本可以隐藏由于对象存在或不存在而导致的潜在错误。

  1. 首先，在不使用此新功能的情况下进行测试。运行以下语句。

    DROP TABLE DEPT;

    由于没有要删除的现有 DEPT 表，我们将看到一个错误：ORA-00942: table or view does not exist。

  2. 但是，在 Oracle Database 23ai 中，我们能够毫无错误地使用 DROP IF EXISTS。这使我们安心，同时避免错误。现在，运行相同的语句，但包括此新的 IF EXISTS 功能。

    DROP TABLE IF EXISTS DEPT;

  3. 同样，如果表尚不存在，也可以使用此功能来创建表。创建该 DEPT 表。

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    图中显示了如何使用 IF [NOT] EXISTS DDL 子句

  4. 使用此功能可以在本教程中创建更多样例表。这里，我们将创建一个名为 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);

Table EMP created.

Elapsed: 00:00:00.006

功能 4：对多个行使用 INSERT

确保与其他常用数据库管理系统更好地共存和兼容的另一个有趣功能是多值 INSERT 语句。

  1. 例如，在以前版本的 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');

    图中显示了如何使用多值 INSERT

    除了与其他数据库更好的兼容性外，此语句还可用于确保某些插入操作在自动提交模式下的一致性。例如，对于使用此模式处理数据的 Oracle APEX 应用程序，这可能很重要。

  2. 运行以下语句以使用此功能填写 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 中，可以采用更简单的方式编写，如下图所示：

图中显示了如何在 GROUP BY 子句中使用别名

功能 7：使用 UPDATE 和 MERGE 语句的 RETURNING 子句

此子句早先已作为 EXECUTE IMMEDIATE 语句的一部分实施。但是，在 Oracle Database 23ai 中，我们可以将其作为传统静态 DML 语句的一部分进行查找。

  1. 在这种情况下，它允许从处理的行获取列的旧值和新值。首先，让我们看看王的工资。

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. 要在 LiveSQL 中使用变量，我们将总结 PL/SQL 中的语句。运行此脚本。它首先为旧薪金和新薪金创建变量，然后使用 RETURNING 子句更新 King 的薪金以设置变量。然后，我们将查看结果。

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE 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;

    图中显示了如何使用 UPDATE 和 MERGE 语句的 RETURNING 子句 

    Old Salary: 6000
New Salary: 7000

    此示例使用了 UPDATE 语句，但是 RETURNING 子句可以与 MERGE 语句类似使用。

功能 8：在 UPDATE 和 DELETE 中使用联接

您可以使用基于外部表条件的联接来更新表数据。无需子选择或 IN 子句。

  1. 运行以下语句以查看研究部门的员工薪金信息。

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. 要更新薪金信息，在 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';

    图中显示了如何在 UPDATE 和 DELETE 中使用联接

  3. 您可以看到工资已成功更新。

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    图中显示了如何在 UPDATE 和 DELETE 中使用联接

功能 9：使用注释

注释是数据库对象的可选元数据。注释可以是名称 - 值对，也可以是名称本身。名称和可选值是自由格式文本字段。注释表示为已添加注释的数据库对象的从属元素。支持的方案对象包括表、视图、实体化视图和索引。使用注释，您可以存储和检索有关数据库对象的元数据。您可以使用它来定制业务逻辑、用户界面或向元数据资料档案库提供元数据。可以在表级别或列级别上添加 CREATE 或 ALTER 语句。

通过注释，您可以存储和检索有关数据库对象的元数据。您可以使用它来定制业务逻辑、用户界面或向元数据资料档案库提供元数据。

  1. 创建带列和表注释的带注释的表 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');

    图中显示了如何使用注释和数据库对象的新元数据

  2. 数据字典视图（如 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 域允许用户声明列的预期用法。它们是数据字典对象，因此可以轻松重用特定于抽象域的知识。

  1. 创建名为 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');

    图中显示了如何将轻量级对象类型与 SQL 域结合使用

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    图中显示了如何将轻量级对象类型用于 SQL 域

    ``` 说明人；

    Name Null?类型

ID 编号 (5) 名称 VARCHAR2(50) 薪金编号 PERSON_BIRTH 编号 (4) 域名出生年份

INSERT INTO 人员值 (1，'MARTIN'，3000，1988)；

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SELECT DOMAIN_DISPLAY(person_birth) FROM 人员；


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

从 user_annotations_usage 选择 *；


   Define reusable domain types (lightweight objects).

创建域 EmailAddress 作为 VARCHAR2(100) 检查（REGEXP_LIKE（值，‘ ^[^@]+@[^@]+.[^@]+$ ’））；

CREATE TABLE 用户（ user_id NUMBER，电子邮件 EmailAddress ）；```

图中显示了如何将轻量级对象类型用于 SQL 域

Oracle Database 23ai 功能的限制和限制

一般限制

确认

更多学习资源

通过 docs.oracle.com/learn 浏览其他实验室，或者通过 Oracle Learning YouTube 频道访问更多免费学习内容。此外，请访问 education.oracle.com/learning-explorer 以成为 Oracle Learning Explorer。

有关产品文档，请访问 Oracle 帮助中心。