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;

打印页面