探索 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工作流中保留。 -
實驗性行為：由于這些功能是新的，某些功能可能會在次要版本中發展——請頻繁查看補丁說明和文檔。
)
-工廠模式)
