附註：

探索 Oracle Database 23ai 的 SQL 功能

簡介

我們將在本自學課程中學習 10 項您需要瞭解的功能，以及這些功能如何與現有的對手方進行比較。這些功能包括：

目標

必要條件

功能 1：使用 FROM 子句

Oracle Database 23ai 中引進的有趣功能是 SELECT 敘述句中 FROM 子句的選擇性功能。FROM 子句必須是最新版本。

以下是 SELECT 在 Oracle Database 23ai 中沒有 FROM 功能的一些潛在優勢。

功能 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?Type

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.

選擇 DOMAIN_DISPLAY (person_birth)；


   ![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，email EmailAddress)；```

顯示如何使用輕量型物件類型與 SQL 網域的影像

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

一般限制

確認

其他學習資源

在 docs.oracle.com/learn 上探索其他實驗室，或在 Oracle Learning YouTube 頻道上存取更多免費學習內容。此外，請造訪 education.oracle.com/learning-explorer 以成為 Oracle Learning Explorer。

如需產品文件，請造訪 Oracle Help Center 。