创建其他架构对象

创建其他架构对象

除了表，其他基本模式对象是视图、序列、索引和同义词。视图是逻辑或虚拟表。同义词只是数据库对象的别名。同义词还通过伪装数据库对象的实际名称来简化查询编写并提供系统安全元素。序列是支持整数值自动生成的特殊数据库对象，通常用于为表生成主键值。在表列上创建索引以方便从表中快速检索信息。

观看次数

数据库视图是基于查询的逻辑或虚拟表。视图就像查询表一样。这意味着从您作为开发人员的角度或从数据库系统用户的角度来看，视图看起来像一个表。视图的定义作为对象存储在数据库的数据字典中；然而，视图本身不存储数据。数据库还存储创建视图的执行计划——这意味着即使没有存储视图的 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 查询，没有函数或组子句，只是从表中选择列，没有任何转换。如果在视图上执行 DML，它会直接反映在基表中。

一个Complex视图是使用joins在多个表上创建的。它可以包含SQL函数，按函数分组。但是由于视图是在多个数据上的，而且列的选择也不简单，所以不允许对其进行DML操作。

插图

简单视图：下面的简单视图为 JOB 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 不能在 CREATE TABLE 或 ALTER TABLE 语句中使用 DISTINCT 关键字、GROUP BY、HAVING 或 ORDER BY 子句以及 DEFAULT 表达式在 VIEW 选择列表中。

修改顺序

序列所有者可以修改序列以更改属性，例如 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-Tree 索引。

复合索引（也称为串联索引）是在表的多个列上创建的索引。复合索引中的列可以以任何顺序出现，并且不必是表中的相邻列。对于 WHERE 子句引用复合索引中所有列或列的前导部分的查询，复合索引提高了行检索速度。一个索引最多可以包含 32 列。

例如，用户在 EMPLOYEES 表的 HIRE_DATE 列上创建索引 IDX_EMP。索引使用将通过遍历索引路径扫描并找到在 HIRE_DATE 列上过滤的数据来减少磁盘 I/O。

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： name 已被现有对象错误消息使用。

插图

考虑两个用户 U1 和 U2。U1 有权访问 EMPLOYEES 表。因此，要启用 EMPLOYEES 表对 U2 的访问，可以在 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;

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁