SQL - 建立其他模式物件

除了表之外，其他重要的模式物件還有檢視、序列、索引和同義詞。檢視是邏輯表或虛擬表。同義詞只是資料庫物件的別名。同義詞還可以簡化查詢編寫，並透過隱藏資料庫物件的實際名稱來提供一定的系統安全性。序列是支援自動生成整數值的特殊資料庫物件，通常用於生成表的 primary key 值。索引是在表列上建立的，用於加快從表中檢索資訊的速度。

檢視

資料庫檢視是基於查詢的邏輯表或虛擬表。檢視的查詢方式與表相同。這意味著，從開發人員的角度或資料庫系統使用者的角度來看，檢視看起來像一個表。檢視作為物件的定義儲存在資料庫的資料字典中；但是，檢視本身不儲存資料。資料庫還儲存建立檢視的執行計劃——這意味著即使透過檢視的 SELECT 查詢呈現的實際資料未儲存為檢視的一部分，也可以透過使用檢視快速檢索資料。相反，每次從檢視定義的資料庫表（稱為基表）查詢檢視時，“收集”資料。

通用語法如下所示。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

從語法中，

FORCE 選項允許即使檢視引用的基表尚不存在也能建立檢視。此選項用於在實際建立基表和相關資料之前建立檢視。

NOFORCE 選項與 FORCE 相反，允許系統使用者在擁有建立檢視的必要許可權以及檢視建立的表已存在的情況下建立檢視。這是預設選項。

WITH READ ONLY 選項允許建立只讀檢視。您不能使用 DELETE、INSERT 或 UPDATE 命令來修改只讀檢視的資料。

WITH CHECK OPTION 子句允許更新可以透過檢視選擇的行。它還允許您指定對值的約束。CONSTRAINT 子句與 WITH CHECK OPTION 子句一起使用，使資料庫管理員能夠為 CHECK OPTION 指定唯一名稱。如果資料庫管理員省略 CONSTRAINT 子句，Oracle 將自動為約束分配一個系統生成的名稱，該名稱意義不大。

檢視型別

簡單檢視僅在一個表之上建立。它是一個簡單的 SELECT 查詢，沒有函式或 group 子句，只是從表中選擇列而無需任何轉換。如果在檢視上執行 DML，它會直接反映在基表中。

複雜檢視使用連線在多個表上建立。它可以包含 SQL 函式、Group by 函式。但是由於檢視基於多個數據，並且列的選擇也不是簡單的，因此不允許在其上進行 DML 操作。

示例

簡單檢視：下面的簡單檢視選擇職位 ID 為 DEV 的員工的員工姓名、部門 ID 和薪水。

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

複雜檢視：下面的示例顯示部門名稱、部門中獲得的平均工資以及在該部門工作的員工人數。

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [檢視名稱] 描述檢視結構。列按檢視定義中的相同順序列出。

在檢視上進行 DML 操作

DML 操作可以輕鬆地在簡單檢視上執行。如前所述，插入、更新和刪除操作實際上發生在基表上。

當您在檢視上執行 UPDATE、DELETE 或 INSERT DML 語句時，您實際上是在操作定義檢視的基表的資料行。使用 UPDATE、DELETE 和 INSERT 語句與檢視一起使用時存在一些限制。首先，要將 UPDATE、DELETE 或 INSERT 語句與檢視一起使用，檢視必須是可更新的。如果 SELECT 子句未在 SELECT 列表中指定任何聚合函式，則檢視是可更新的。此外，檢視不能透過使用 GROUP BY、DISTINCT 或 UNION 子句建立。允許在 FROM 子句中的 SELECT 子查詢中使用聚合函式。此外，檢視在 SELECT 列表中不能有任何派生列。接下來，如果檢視是作為 JOIN 操作的結果建立的（連線檢視），則 UPDATE 和 INSERT 語句一次只能修改或插入一個基表中的行。您不能使用單個數據操作語言 (DML) 語句修改來自兩個或多個表的行。最後，只有當 FROM 子句中引用表時，DELETE 語句才能針對檢視執行。這僅僅意味著您不能刪除未指定的表的行。

WITH CHECK OPTION 子句

WITH CHECK OPTION 是一個可選子句，用於指定透過檢視插入或更新資料時要執行的檢查級別。如果使用 WITH CHECK OPTION 子句建立檢視，則透過檢視插入或更新到基表中的每一行都必須符合檢視定義。請注意，如果檢視被建立為只讀的，則不能指定該選項。

例如，為開發人員 (JOB_ID=DEV) 建立了員工檢視 V_EMP_DEV。

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

使用者嘗試透過檢視更新 HR 員工的薪水，但遇到異常。這是因為檢視是使用 WITH CHECK OPTION 建立的。

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

如果它是一個簡單的檢視，則 UPDATE 語句不會引發任何異常。

刪除檢視

資料庫管理員 (DBA) 或檢視所有者可以使用 DROP VIEW 語句刪除檢視。如果檢視已定義約束，則在刪除檢視時需要指定 CASCADE CONSTRAINTS 子句；否則，DROP VIEW 語句將無法處理。如果另一個檢視或其他資料庫物件（例如同義詞或物化檢視（本章稍後將討論這兩個物件））引用已刪除的檢視，Oracle 不會刪除這些資料庫物件；相反，Oracle 將它們標記為無效。您可以刪除這些無效物件或重新定義它們以使它們再次有效。

下面的 DROP VIEW 命令從資料庫中刪除檢視 EMP_VU。

DROP VIEW EMP_VU;

序列

Oracle 提供了生成此類用途的唯一數字序列的功能，它們稱為序列。通常，序列用於生成用作資料庫表中主鍵值的唯一、連續的整數值。可以按升序或降序生成數字序列。請注意，序列生成的數字一旦生成就不能回滾。

語法

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

從語法中，

CREATE SEQUENCE 語句必須指定唯一的序列名稱。這是語句中唯一必需的子句。如果您沒有指定任何其他子句，則生成的所有序列號都將遵循 Oracle 預設設定。

INCREMENT BY 子句確定在生成每個數字時序列如何遞增。預設增量為 1；但是，如果您有充分的理由讓序列跳過數字，您可以指定不同的增量。正數值增量將生成升序序列號，其間隔等於您選擇的間隔。負數值增量將生成降序序列號。

START WITH 子句指定序列的起始數值——預設起始數字為 1。此外，如果您已經在將儲存序列值的列中有一些帶有資料的資料行，則必須指定起始值。

MAXVALUE 子句指定序列可以遞增到的最大值。如果沒有 MAXVALUE，則可以為序列生成的允許最大值非常大，為 10 的 27 次方 - 1。預設為 NOMAXVALUE。

MINVALUE 子句為遞減序列（按降序生成數字的序列）指定序列的最小值。預設為 NOMINVALUE。

CYCLE 子句指定如果序列達到指定的 MAXVALUE，則可以重複使用序列值。如果序列迴圈，則從 START WITH 值重新開始生成數字。

CACHE 子句可以透過啟用 Oracle 生成要儲存在快取記憶體記憶體中的指定批次的序列號來提高系統性能。

如果您指定 CACHE 而沒有指定數字，則預設快取大小為 20 個序列號。或者，您可以指定 NOCACHE 以防止快取序列號。

ORDER 子句指定序列號按請求的精確時間順序分配。

NEXTVAL 和 CURRVAL

序列值是透過使用名為 currval 和 nextval 的兩個偽列生成的。偽列的行為類似於表列，但偽列實際上並未儲存在表中。第一次選擇 nextval 偽列時，將返回序列中的初始值。隨後選擇 nextval 偽列會導致序列按 INCREMENT BY 子句中指定的遞增，並返回新生成的序列值。currval 偽列返回序列的當前值，這是對 nextval 的最後一次引用的返回值。

在一個會話中，NEXTVAL 而不是 CURRVAL 必須是序列上的第一個操作。這是因為在一個會話中，當 NEXTVAL 從序列生成會話的第一個數字時，Oracle 會將當前值儲存在 CURRVAL 中。

語法

Sequence.NEXTVAL
Sequence.CURRVAL

需要注意的幾點 -

CURRVAL 和 NEXTVAL 只能用於 select 語句的外部 SQL。
CURRVAL 和 NEXTVAL 可用於 INSERT 語句以替換列主鍵。它既可以用作子查詢子句，也可以用在 VALUES 子句中。
CURRVAL 和 NEXTVAL 可用於更新表中的值。
CURRVAL 和 NEXTVAL 不能在 VIEW select 列表中使用，不能與 DISTINCT 關鍵字、GROUP BY、HAVING 或 ORDER BY 子句一起使用，也不能在 CREATE TABLE 或 ALTER TABLE 語句中使用 DEFAULT 表示式。

修改序列

序列所有者可以修改序列以更改屬性，例如 INCREMENT BY 值、MINVALUE、MAXVALUE、CYCLE 或 CACHE 子句。請注意，所做的更改將反映在即將到來的數字中。

語法

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

刪除序列

DROP SEQUENCE 命令刪除需要重新建立或不再需要的序列。

DROP SEQUENCE [sequence name]

索引

索引是用於調整 SELECT 查詢效能的資料庫物件。有不同型別的索引，包括用於強制主鍵約束、唯一索引、非唯一索引和連線索引等的索引。如果沒有索引，查詢將需要 Oracle 掃描表中的所有行才能返回結果表所需的行的。索引是在表列上建立的，然後在索引段下儲存列的所有值。與序列不同，索引是特定於表的。一旦表被刪除，它們就會被自動刪除。

索引可以自動建立或手動建立。當您指定 PRIMARY KEY 約束或 UNIQUE 約束時，Oracle 將自動建立一個唯一索引來支援指定表的快速資料檢索。

或者，使用者可以手動建立索引以最佳化查詢效能。手動建立的索引可以是唯一的或非唯一的。非唯一索引可以是 B 樹、點陣圖或基於函式的索引。預設情況下，Oracle 在列上建立 B 樹索引。以下是語法

語法

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

注意，UNIQUE 和 BITMAP 索引型別只能分別用於唯一索引和點陣圖索引。預設情況下，Oracle 為普通索引建立 B 樹索引。

複合索引（也稱為連線索引）是在表的多個列上建立的索引。複合索引中的列可以按任何順序出現，不必是表中相鄰的列。對於 WHERE 子句引用複合索引中所有列或前導部分列的查詢，複合索引可以提高行檢索速度。一個索引最多可以包含 32 列。

例如，使用者在 EMPLOYEES 表的 HIRE_DATE 列上建立索引 IDX_EMP。索引的使用將透過遍歷索引路徑掃描來減少磁碟 I/O，並找到在 HIRE_DATE 列上過濾的資料。

CREATE INDEX IDX_EMP ON employees(hire_date);

刪除索引

索引無法修改，但可以為了分析、重建或統計計算的目的而更改。如果必須修改索引定義，則必須刪除並重新建立它。DROP INDEX 命令的語法很簡單。

DROP INDEX index_name;

同義詞

同義詞是別名，即一種簡寫形式，用於簡化引用資料庫物件的任務。這個概念類似於使用暱稱來稱呼朋友和熟人。引用另一個使用者擁有的物件需要在其前面加上模式名。藉助同義詞，您可以減少引用物件及其模式名的工作量。這樣，同義詞提供了位置透明性，因為同義詞名稱隱藏了實際的物件名稱及其所有者。

同義詞分為兩類：公共同義詞和私有同義詞。公共同義詞可用於允許所有系統使用者輕鬆訪問物件。事實上，建立公共同義詞的個人並不擁有該同義詞——相反，它將屬於 Oracle 中存在的 PUBLIC 使用者組。另一方面，私有同義詞屬於建立它們的系統使用者，並駐留在該使用者的模式中。

語法

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

系統使用者可以授予其他系統使用者使用其擁有的私有同義詞的許可權。要建立同義詞，您需要具有 CREATE SYNONYM 許可權。此外，您必須具有 CREATE PUBLIC SYNONYM 許可權才能建立公共同義詞。如果同義詞宣告為公共同義詞，則同義詞名稱不能已被用作公共同義詞。嘗試建立已存在的公共同義詞將導致 CREATE PUBLIC SYNONYM 命令失敗，Oracle 將返回 ORA-00955：名稱已被現有物件使用的錯誤訊息。

示例

考慮兩個使用者 U1 和 U2。U1 可以訪問 EMPLOYEES 表。因此，為了允許 U2 也訪問 EMPLOYEES 表，可以在 U2 模式中建立一個同義詞。U1 必須向 U2 授予訪問許可權。

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

刪除同義詞

使用者可以刪除其擁有的同義詞。要刪除公共同義詞，您必須具有 DROP PUBLIC SYNONYM 許可權。

DROP SYNONYM EMP_SYN;

列印頁面