LLL的数据库培训-88-第五部分—Oracle11g-Oracle SQL对象管理—第15讲—视图/序列/同义词
一、视图
1、视图的概念
视图是一种虚拟表，由一个或多个表的列组成，它是从基本表中检索数据的查询结果集。视图并不是物理存储的表，而是在查询时动态生成的。
2、视图的功能
- 数据保护和安全性：视图可以隐藏基本表的某些数据，只提供部分数据给用户，从而实现数据的保护和安全性控制。 - 简化复杂的查询：视图可以将多个表的连接、过滤和聚合操作封装为一个视图查询，简化了复杂查询的编写和维护。 - 数据的逻辑组织：通过视图可以将数据在逻辑上组织成更为清晰和易于理解的结构，使数据的访问更加方便。 - 数据的数据独立性：视图可以对基本表的结构进行抽象，使得应用程序对基本表结构的变化不敏感，从而提高了数据的独立性。
3、视图的使用场景
- 数据访问控制：通过视图可以限制用户对数据的访问权限，只提供他们需要的部分数据，保护敏感数据的安全性。 - 简化复杂查询：通过创建视图将复杂的查询操作封装起来，提供给用户一个简单、易用的查询界面。 - 数据的逻辑组织和整合：通过视图可以将多个表的数据逻辑上整合和组织起来，提供给用户一个更清晰的数据展示方式。
4. 视图的创建语法：
视图可以通过以下语法在Oracle中创建：
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table1, table2, ... WHERE condition;
这里的`view_name`是视图的名称，`column1, column2, ...`是要查询和展示的列，`table1, table2, ...`是视图所基于的基本表，`condition`是条件用于筛选数据。
Oracle的默认设置是通过视图可以更新基础表(在拥有对象权限的情况下)。
5.检查更新数据
（1）功能
可以设置通过视图只能修改基础表中通过视图查看的数据。
（2）功能开启：
使用WITH CHECK OPTION子句可以启用该功能:
create or replace view sales_rockies as select sales_id, amnt, state from sales where state in ('CO',UT',WY',ID',AZ') with check option;
使用WITH CHECKOPTION子句，意味着通过视图只能插入和更新视图显示的行。
例如，下面的UPDATE语句可以生效，因为该语句更新的是视图能够显示的基础表数据:
SOL> update sales_rockies set state='ID' where sales_id=1;
（3）否则如下无法实现
下面的更新语句就无法成功执行，因为它尝试更新无法通过视图显示的STATE列的值:
SOL> update sales_rockies set state='CA' where sales_id=1;
这个例子会显示错误提示:
ORA-01402: view WITH CHECK OPTION where-clause violation。
生产中极少用到WITH CHECKOPTION子句的情况。话虽如此，如果你的业务需求规定通过视图只能更新视图显示的数据，使用该功能当然不会有问题。
6.创建只读视图
如果你不想赋予用户通过视图执行INSERT、UPDATE或DELETE操作的能力，就不要将视图上的这些对象权限赋予用户。
此外，如果你不想让用户通过视图修改基础表,创建视图时还应使用WITHREAD ONLY子句。Oracle的默认设置是通过视图可以更新基础表(在拥有对象权限的情况下)
下面的例子使用WITH READ ONLY子句创建了一个视图:
create or replace view sales_rockies as select sales_id, amnt, state from sales where state in ('CO','UT','WY' ,'ID' , 'Az')with read only;
即使用户(包括所有者)拥有删除、插人或更新基础表的权限，当他们尝试通过视图执行这些操作时，系统也会显示下列错误提示:
ORA-42399: cannot perform a DML operation on a read-only view
如果你使用视图报告数据，并且从不通过视图执行修改基础表的操作，那么就可以一直使用WITH READ ONLY子句创建视图。这样做可以避免无意中通过视图修改基础表。
7. 视图的维护：
视图是基于基本表的查询结果，因此在维护视图时需要注意以下几个方面：
- 当基本表变化时，视图可能需要更新以反映最新的数据。 - 视图可能会被其他视图依赖，所以在修改或删除视图时需要考虑它对其他对象的影响。 - 视图的性能依赖于其查询语句和基本表的索引等因素，因此在需要优化性能时，需要考虑重新设计或修改视图的查询语句。
在维护视图时，可以使用`CREATE OR REPLACE VIEW`语句来更新或重新定义视图的查询逻辑。另外，也可以使用`DROP VIEW`语句来删除不再需要的视图。
（1）修改视图定义
--方法1：使用前面介绍过的CREATE OR REPLACE语法直接修改
例如，向表SALES添加REGION列;
SOL> alter table sales add (region varchar2(30));
要向SALES_ROCKIES视图添加REGION列，可运行下面的命令替换已存在的视图定义:
create or replace view sales_rockies asselect sales_id,amnt, state, region from saleswhere state in ('CO'UT',WY','ID'Az')with read only;
CREATE OR REPLACE方法的优点是无需重新为用户赋予先前赋予过的访问视图权限。
（2）如果要修改创建视图的SQL查询代码，可以先删除该视图然后重建它
如果使用先删除后重建的方法，就必须重新为用户和角色赋予访问视图的权限。
因此，在更改视图结构时，我从来不使用先删除后重建的方法。
当从表删除列而且被删除的列正被相应视图引用时，会出现怎样的情况呢?
例如:
SOL> alter table sales drop (region);
如果你尝试通过视图选择数据，系统就会显示ORA-04063错误提示。在修改基础表时，可以通过编译视图，检查更改表是否会影响视图，
例如:
SOL> alter view sales_rockies compile; Warning: View altered with compilation errors.
这种方法能够确定更改表是否会影响依赖表的视图。遇到上述情况，你应该重新创建扫描删除列的视图:
create or replace view sales_rockies asselect sales_id, amnt, statefrom sales where state in ('Co','UT','WY', 'ID', 'Az')with read only;
（2）重命名视图
--重命名视图的理由有两个。
其一是使视图的名称更符合命名标准，需要更改视图的名称;
其二是在删除视图前需要更改视图的名称，以查明该视图是否正在被使用。使用RENAME语句可以更改视图的名称。
--语法：
rename sold_view to sold_view_new;
（3）删除视图
drop view sold_view;
二、序列
1.序列的概念
序列是用于生成唯一整数的数据库对象。
通常可以使用序列为主键和外键列生成整数。
通过SELECT、INSERT和UPDATE语句，能够以增量方式访问序列。在选择数据时Oracle会保证序列数值具有唯一性，两个用户会话无法同时选中一个序列号。
2.无法避免的情况-跳号
没有任何办法能够确保序列号不出现跳号现象。
通常，一些序列号会缓存在内存中，而出现实例失效事件(如掉电、死机)时，内存中未使用的序列值都会丢失。
即使不缓存序列，也无法阻止用户为执行事务而获取序列号，然后又回滚该事务(事务会回滚，但是序列号会被占用)。大多数应用都可以接受拥有跳号可能性很高的、唯一整数生成器。只需注意存在跳号现象即可。
在数据库中，序列是用来生成唯一数字序列的对象。
序列是一个计数器，它可以按照预定义规则生成唯一的整数值。序列生成的值可以被用作表的主键、唯一标识符或其他需要唯一值的场景。
3.序列的功能
- 生成唯一值：序列可以按照定义的规则自动产生唯一的整数值，确保数据的唯一性。 - 自增功能：序列可以在每次使用时自动递增，生成连续的整数值。 - 高并发性能：序列的生成是数据库中高效地处理并发请求的方式之一，避免了锁和冲突问题。
4.序列的使用场景
- 主键生成：序列常用于为数据库表的主键字段生成唯一的标识符，确保表中每条记录的唯一性。 - 唯一标识符：序列可以用来生成唯一的标识符，用于标识某个实体或资源。 - 随机数生成：序列可以根据需要生成特定规则或范围内的随机数。
5.序列的创建
在Oracle中，可以使用以下语法来创建一个序列：
CREATE SEQUENCE sequence_name [START WITH initial_value] [INCREMENT BY increment_value] [MINVALUE min_value] [MAXVALUE max_value] [CYCLE | NOCYCLE] [CACHE cache_value];
sequence_name 是序列的名称， initial_value 是序列的初始值（默认为1）， increment_value 是序列每次自增的值（默认为1）， min_value和max_value 是序列允许的最小值和最大值， CYCLE和NOCYCLE 指定序列在达到最大值（或最小值）时是否循环， cache_value 指定序列的缓存大小（默认为20）。
6.使用序列伪列
创建了序列后，可以使用两个伪列访问序列值:
NEXTVAL CURRVAL
可以在SELECT、INSERT或UPDATE语句中引用这些伪列。要从INV_SEO序列检索某个值，可访问NEXTVAL伪列值:
SQL> select inv_seq.nextval from dual;
上面的命令为会话检索了一个序列值，访问CURRVAL伪列值可以多次使用该序列值:
SQL> select inv_seq.currval from dual;
下面的例子为父表的主键赋予序列值，然后使用同一序列为子表的外键赋值。通过INSERT语句可以直接访问这个序列。第一次访问该序列时，应使用NEXTVAL伪列。
SQL> insert into inv(inv_id, inv_desc) values (inv_seq.nextval, 'Book')!
如果你想要再次使用相同的序列值，可以通过CURRVAL伪列引用它。下面向子表插人一条记该子表的外键列与父表的主键列具有相同的值:录，
insert into inv_lines(inv_line_id,inv_id,inv_item_desc) values(1,inv_seq.currval,'Tome1');
7.自动增长列
（1）12c的自动方式
从Oracle Database 12c开始，可以创建带自动增长序列值标识列的表。请参阅第7幸详细了解这方面的内容。
在12C之前版本，Oracle没有这种功能。可以让开发人员直接在INSERT语句中使用序列号(如上节序列伪列)，或将序列值赋予变量，然后根据需要引用这个变量。
（2）12c之前的触发器方式
如果你无法使用Oracle Database 12c中的标识列功能，可以通过触发器模拟这个功能。例如创建一个表和序列:
SOL> create table inv(inv_id number, inv_desc varchar2(30)); SOL> create sequence inv_seq;
在INV表上创建一个触发器，设置它通过序列自动为INV_ID列赋值:
create or replace trigger inv_bu_tr before insert on inv for each row begin select inv_seq.nextval into :new.inv_id from dual; end; /
--下面向INV表插入两条记录:
SQl> insert into inv (inv_desc) values( 'Book'); SQl> insert into inv (inv_desc) values( 'pen');
通过查询该表，验证INV_ID列是否被自动赋予了序列值:
SQL> select * from inv; INV_ID INV_DESC ------ --------------------
（3）建议方式
利用上节方式，在DML语句中直接调用序列（减少DBA工作量）。
6.序列的维护
- 重置序列：可以使用`ALTER SEQUENCE`语句来修改序列的定义，如修改初始值、自增值等。 - 删除序列：可以使用`DROP SEQUENCE`语句来删除不再需要的序列。
在维护序列时，需要考虑并发访问和数据一致性。当多个会话同时使用序列时，需要确保生成的序列值不会重复，并且需要避免并发访问引起的冲突。
可以使用序列的缓存机制来提高性能，在内存中缓存多个序列值，减少与序列对象的交互次数。
（1）重置序列
alter sequence myseq incremnet by 1000; alter sequence myseq incremnet by 1;
（2）删除序列
drop sequence inv_seq;
注：开始序列是不能修改的。
三、同义词
1. 同义词的概念：
同义词是数据库中的一个对象，它提供了一个替代表名或视图名的名称。
同义词实际上是对表名、视图名或远程对象名的别名，可以使得在查询中使用更简短、更易记的名称。
2. 同义词的功能：
- 简化和美化命名：同义词可以为表、视图或远程对象提供一个更简单、直观的名称，使查询和代码更易读、易理解。
- 隐藏底层对象：同义词可以隐藏底层数据库对象的名称和位置，提供封装和安全性。
- 简化数据库迁移：当需要将数据库迁移到另一个环境时，通过修改同义词的定义，可以轻松地更改引用的数据库对象。
3. 同义词的使用场景：
- 系统集成：在不同的数据库中，可以使用同义词来引用另一个数据库中的表或视图，简化跨数据库的查询和操作。
- 数据库重构：当需要对数据库进行重构或优化时，可以使用同义词来提供新的表或视图名称，使得应用程序的修改最小化。
- 数据访问控制：可以使用同义词来隐藏底层数据库对象，只暴露需要的对象，并进行权限控制。
4. 同义词的创建：
在Oracle中，可以使用以下语法来创建一个同义词：
CREATE or replace SYNONYM synonym_name FOR object_name;
这里的`synonym_name`是同义词的名称，`object_name`是同义词所引用的对象的名称。
5. 同义词的维护：
- 修改同义词：可以使用`ALTER SYNONYM`语句来修改同义词的定义，如修改引用的对象名称。 - 删除同义词：可以使用`DROP SYNONYM`语句来删除不再需要的同义词。 - 重命名同义词：可以使用`RENAME`语句来重命名同义词。
（1）修改同义词
CREATE or replace SYNONYM synonym_name FOR object_name;
（2）重命名同义词
rename inv_s to inv_st;
（3）删除同义词
drop synonym inv;
在维护同义词时，需要确保同义词引用的对象存在，并且权限设置正确。同时，需要注意同义词的命名规范，避免与其他对象命名发生冲突。
