oracle（三）

一、视图

1.1什么是视图

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视

图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图

的结构和数据是对数据表进行查询的结果。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多

个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使

用。

使用视图的优点：

1. 简化数据操作：视图可以简化用户处理数据的方式。

2. 着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

3. 视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问

权限。

4. 提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接

口。

1.2创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name

AS subquery

[WITH CHECK OPTION ]

[WITH READ ONLY]

选项解释：

OR REPLACE ：若所创建的试图已经存在， ORACLE 自动重建该视图；

FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；

subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ ONLY ：该视图上不能进行任何 DML 操作。

1.3删除视图语法

DROP VIEW view_name

1.4案例

1. 简单视图的创建与使用

什么是简单视图？如果视图中的语句只是单表查询，并且没有聚合函数，我们就

称之为简单视图。

需求：创建视图 ：业主类型为 1 的业主信息

语句：

create or replace view view_owners1 as

select * from T_OWNERS where ownertypeid= 1

利用该视图进行查询

select * from view_owners1 where addressid= 1 ;

就像使用表一样去使用视图就可以了。

查询结果：

对于简单视图，我们不仅可以用查询，还可以增删改记录。

我们下面写一条更新的语句，试一下：

update view_owners1 set name = ' 王刚 ' where id = 2 ;

再次查询：

select * from view_owners1

查询结果如下：

结果已经更改成功。

我们再次查询表数据

发现表的数据也跟着更改了。由此我们得出结论：视图其实是一个虚拟的表，它

的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了

表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL

语句。

2. 带检查约束的视图

需求：根据地址表（ T_ADDRESS ）创建视图 VIEW_ADDRESS2 , 内容为区域 ID

为 2 的记录。

语句：

create or replace view view_address2 as

select * from T_ADDRESS where areaid= 2

with check option

查询结果如下：

执行下列更新语句：

update view_address2 set areaid= 1 where id = 4

系统提示如下错误信息：

3. 只读视图的创建与使用---就是只能查询，不能修改，删除，添加

如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视

图时指定 WITH READ ONLY 选项，这样创建的视图就是一个只读视图。

需求：将上边的视图修改为只读视图

语句：

create or replace view view_owners1 as

select * from T_OWNERS where ownertypeid= 1

with read only

修改后，再次执行 update 语句，会出现如下错误提示

4. 创建带错误的视图

我们创建一个视图，如果视图的 SQL 语句所设计的表并不存在，如下

create or replace view view_TEMP as

select * from T_TEMP

T_TEMP 表并不存在，此时系统会给出错误提示

有的时候，我们创建视图时的表可能并不存在，但是以后可能会存在，我们如果

此时需要创建这样的视图，需要添加 FORCE 选项， SQL 语句如下：

create or replace FORCE view view_TEMP as

select * from T_TEMP

此时视图创建成功。

5. 复杂视图的创建与使用--多表联查

所谓复杂视图，就是视图的 SQL 语句中，有聚合函数或多表关联查询。

我们看下面的例子：

（ 1 ）多表关联查询的例子

需求：

创建视图，查询显示业主编号，业主名称，业主类型名称

语句：

create or replace view view_owners as

select o.id 业主编号 ,o.name 业主名称 ,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot

where o.ownertypeid=ot.id

使用该视图进行查询

select * from view_owners

那这个视图能不能去修改数据呢？

我们试一下下面的语句：

update view_owners set 业主名称 = ' 范小冰 ' where 业主编号 = 1 ;

可以修改成功。

我们再试一下下面的语句：

update view_owners set 业主类型 = ' 普通居民 ' where 业主编号 = 1 ;

这次我们会发现，系统弹出错误提示：key-preserved table 键保留表

这个是什么意思？是说我们所需改的列不属于键保留表的列。

什么叫键保留表呢？

键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视

图中 , 并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连

接视图中也是键值，那么就称这个表为键保留表。

在我们这个例子中，视图中存在两个表，业主表（ T_OWNERS ）和业主类型表

（ T_OWNERTYPE ） , 其中 T_OWNERS 表就是键保留表，因为 T_OWNERS 的

主键也是作为视图的主键。键保留表的字段是可以更新的，而非键保留表是不能

更新的。

键保留表说白了就是把键保留的那个表。

视图可以作为主键爱你的那个字段，所属的表就是键保留表。

6.分组聚合统计查询的例子

需求：创建视图，按年月统计水费金额，效果如下

语句：

create view view_accountsum as

select year , month , sum (money) moneysum

from T_ACCOUNT

group by year , month

order by year , month

此例用到聚合函数，没有键保留表，所以无法执行 update 。

因为这个视图是聚合统计的数，聚合统计是没有键保留表的，所有的列都不能修改。

二、物化视图

2.1什么是物化视图

视图是一张虚拟的表，他并不是真实存在，他只是一个sql语句，物化视图是一张真实存在的表，区别在于，物化视图是要建立副本的，它是把数据查出来放到一个副本里。物化视图更像一张表。视图他只是一个查询，她不需要建立副本。物化视图查询效率更高，但是占用存储空间。

物化视图相当于查询单表，不需要关联。视图相当于多表关联，需要关联查询。物化视图就是提高查询效率，但是占用存储空间。

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语

句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次 访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化

视图）。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张

表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样

的。

2.2创建物化视图语法

CREATE METERIALIZED VIEW view_name

[BUILD IMMEDIATE | BUILD DEFERRED ]

REFRESH [FAST|COMPLETE|FORCE]

[

ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT

(next_time)

]

AS

subquery（查询语句的意思）

BUILD IMMEDIATE（立即创建）  是在创建物化视图的时候就生成数据

BUILD DEFERRED （延迟创建） 则在创建时不生成数据，以后根据需要再生成数据。

默认为 BUILD IMMEDIATE 。

刷新（ REFRESH ）：指当基表发生了 DML 操作后，物化视图何时采用哪种

方式和基表进行同步。

REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE 。 FAST

刷新采用增量刷新，只刷新自上次刷新以后进行的修改。 COMPLETE 刷新对整

个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判

断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE

的方式。 FORCE 是默认的方式。

刷新的模式有两种： ON DEMAND 和 ON COMMIT 。 ON DEMAND 指需要

手动刷新物化视图（默认）。 ON COMMIT 指在基表发生 COMMIT 操作时自动

刷新。

2.3案例

1.创建手动刷新的物化视图

手动刷新就是你提交基表的时候他不会立刻更新数据，手动刷新物化视图就是不加选项，on demand 默认参数可以不写的。

需求：查询地址 ID, 地址名称和所属区域名称 , 结果如下：

语句：

create materialized view mv_address

as

select ad.id,ad.name adname,ar.name ar_name

from t_address ad,t_area ar

where ad.areaid=ar.id

执行上边的语句后查询

select * from mv_address;

查询结果如下：

基表：就是你的物化视图来自于哪张表，这个表就是基表。

这时，我们向地址表（ T_ADDRESS ）中插入一条新记录，

insert into t_address values ( 8 , ' 宏福苑小区 ' , 1 , 1 );

再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。

我们需要通过下面的语句（ PL/SQL ），手动刷新物化视图：

begin

DBMS_MVIEW.refresh( 'MV_ADDRESS' , 'C' );

end ;

或者通过下面的命令手动刷新物化视图：

EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');

注意：此语句需要在命令窗口中执行（新建--》选择command window选项）。

执行此命令后再次查询物化视图，就可以查询到最新的数据了。

DBMS_MVIEW.refresh 实际上是系统内置的存储过程，关于存储过程我们在第 4

章会详细讲解。

2.创建自动刷新的物化视图，和上例一样的结果集

自动刷新的物化视图就是加上refresh on commit关键字，就是让你的基表发生变更的时候，去自动的刷新物化视图。

语句如下：

create materialized view mv_address2

refresh

on commit

as

select ad.id,ad.name adname,ar.name ar_name

from t_address ad,t_area ar

where ad.areaid=ar.id

创建此物化视图后，当 T_ADDRESS 表发生变化时， MV_ADDRESS2 自动跟着

改变。

查询结果：

3.创建时不生成数据的物化视图

create materialized view mv_address3

build deferred

refresh

on commit

as

select ad.id,ad.name adname,ar.name ar_name

from t_address ad,t_area ar

where ad.areaid=ar.id;

创建后执行下列语句查询物化视图

select * from mv_address3

查询结果：

执行下列语句生成数据

begin

DBMS_MVIEW.refresh( 'MV_ADDRESS3' , 'C' );

end ;

再次查询，得到结果：

由于我们创建时指定的 on commit , 所以在修改数据后能立刻看到最新数据，无须

再次执行 refresh

4.创建增量刷新的物化视图

增量刷新就是加上refresh fast的关键字，

如果创建增量刷新的物化视图，必须首先创建物化视图日志

create materialized view log on t_address with rowid ；

create materialized view log on t_area with rowid

创建的物化视图日志名称为 MLOG$_ 表名称

创建物化视图

create materialized view mv_address4

refresh fast

as

select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name

adname,ar.name ar_name

from t_address ad,t_area ar

where ad.areaid=ar.id;

注意：创建增量刷新的物化视图，必须：

1. 创建物化视图中涉及表的物化视图日志。

2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

当我们向地址表插入数据后，物化视图日志的内容：

 

SNAPTIME$$ ：用于表示刷新时间。

DMLTYPE$$ ：用于表示 DML 操作类型，

I 表示 INSERT ， D 表示 DELETE ， U表示 UPDATE 。

OLD_NEW$$ ：用于表示这个值是新值还是旧值。 N （ EW ）表示新值， O （ LD ）

表示旧值， U 表示 UPDATE 操作。

CHANGE_VECTOR$$ ：表示修改矢量，用来表示被修改的是哪个或哪几个字段。

此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。

插入操作显示为： FE, 删除显示为： OO 更新操作则根据更新字段的位置而显示

不同的值。

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

begin

DBMS_MVIEW.refresh( 'MV_ADDRESS4' , 'C' );

end ;

三、序列--用的多的是创建简单序列，和序列的2个伪列

3.1什么是序列

序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。

序列是产生连续数字的数据库对象。我们插入语句的时候，第一个值是主键值，主键值是直接写死的，在mysql当中有一个自动增长的特性，自动增长的话这个值就不需要指定了。oracle没有提供表中的字段有自动增长的特性，但是他提供了序列，序列是独立于表的数据库对象。序列是专门产生这些数字的，表可以使用这些数字。

3.2创建与使用简单序列

我们知道表有2个伪列，rowid，rownum，序列也有2个伪列，nextavl（下一个值）和currval（当前值）。

创建序列语法：

create sequence 序列名称

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值---就是下一个值

CURRVAL 返回序列的当前值----就是当前值

注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次

提取当前值。

提取下一个值

select 序列名称.nextval from dual

提取当前值

select 序列名称.currval from dual

查询结果：

oracle每执行一次序列自动往下走，先是1，在执行一次就是2，再执行一次是3，以此类推。。

 

3.3创建复杂序列

有时候我们产生的序列不是连续的值，比如第一次查出来是1，第二次是3，第三次是5，每次增长是2.也就是偶数和奇数这样的。

语法：

CREATE SEQUENCE sequence // 创建序列名称

[INCREMENT BY n ] // 递增的序列值是 n 如果 n 是正数就递增 , 如果是负数就递减 默

认是 1

[START WITH n ] // 开始的值 , 递增默认是 minvalue 递减是 maxvalue

[{MAXVALUE n | NOMAXVALUE}] // 最大值--单个方向

[{MINVALUE n | NOMINVALUE}] //最小值--单个方向

[{CYCLE | NOCYCLE}] // 循环 / 不循环---绕圈

[{CACHE n | NOCACHE}];// 分配并存入到内存中

cycle循环就是周而复始。

cache的意思就是缓存（就是内存），序列是存储在磁盘上的，缓存就是吧这个值放到内存当中，下次再去访问从缓存中取值。

3.4案例

1. 有最大值的非循环序列

创建序列的语句：

create sequence seq_test1

increment by 10  //每次增长10

start with 10  //从10开始

maxvalue 300

minvalue 20

意思：从10开始，每次增长10个，最大值是300，最小值是20，到了最大值300的话，在执行的话就会报错了。

以上的错误，是由于我们的开始值小于最小值 。开始值不能小于最小值，修改

以上语句：

create sequence seq_test1

increment by 10

start with 10

maxvalue 300

minvalue 5

我们执行下列语句提取序列值，当序列值为 300 （最大值）的时候再次提取值，

系统会报异常信息。

2. 有最大值的循环序列---类似于for循环，循环默认是20，也可以自己设定

create sequence seq_test2

increment by 10

start with 10

maxvalue 300

minvalue 5

cycle ;

当序列当前值为 300 （最大值），再次提取序列的值

select seq_test2.nextval from dual

提取的值为：

由此我们得出结论，循环的序列，第一次循环是从开始值开始循环，而第二次循

环是从最小值开始循环。

思考问题：

下列语句是否会报错？为什么？

create sequence seq_test3

increment by 10

start with 10

minvalue 5

cycle ;

答：此为错误的语句。因为你创建的是一个循环的序列，所以必须指定最大值，

否则会报错。

3. 带缓存的序列

我们执行下列语句：

create sequence seq_test3

increment by 10

start with 10

maxvalue 300

minvalue 5

cycle

cache 50 ;

我们执行上边语句的意思是每次取出 50 个缓存值，但是执行会提示错误

上边错误提示的意思是：缓存设置的数必须小于每次循环的数。

我们缓存设定的值是 50 ，而最大值是 300 ，那么为什么还会提示这样的信息呢？

其实我们的 cache 虽然是 50 ，但是我们每次增长值是 10 。这样 50 次缓存提取出

的数是 500 （ 50*10 ）

我们更改为下列的语句：

create sequence seq_test4

increment by 10

start with 10

maxvalue 500

minvalue 10

cycle

cache 50 ;

下列语句依然会提示上边的错误，这是因为还存在一个 minvalue ， minvalue 和

maxvalue 之间是 490 个数，也就是一次循环可以提取 490 ，但是我们的缓存是

500 。

我们再次修改语句：

create sequence seq_test5

increment by 10

start with 10

maxvalue 500

minvalue 9

cycle

cache 50 ;

把最小值减 1 ，或把最大值加 1 ，都可以通过。

每次缓存多少根据你设置的数字来，他只会缓存那几个数，相当于设置10，就缓存10、20、30.。。超过了这个设置的100就报错了，所以是100设置小了点。

3.5修改和删除序列

注意：起始参数不能修改，其他的参数都能改。

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START

WITH 参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列：

DROP SEQUENCE 序列名称;

四、同义词

4.1什么是同义词--就是别名

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以

及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全

性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义

词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊

用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，

只允许特定用户或者有基对象访问权限的用户进行访问。

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给

同义词的基对象赋予权限，同义词只是基对象的一个别名。

4.2创建与使用同义词

创建同义词的具体语法是：

create [public] SYNONYM synooym for object;

其中 synonym 表示要创建的同义词的名称， object 表示表， 视图 ，序列等我们要

创建同义词的对象的名称。

你加上public就是共有同义词，不加上public就是私有同义词，就只能当前用户可用该别名。

4.3案例

1. 私有同义词

需求：为表 T_OWNERS 创建 ( 私有 ) 同义词 名称为 OWNERS

语句：

create synonym OWNERS for T_OWNERS;

使用同义词：

select * from OWNERS ;

查询结果如下：

2. 公有同义词

需求：为表 T_OWNERS 创建 ( 公有 ) 同义词 名称为 OWNERS2 ：

create public synonym OWNERS2 for T_OWNERS;

以另外的用户登陆，也可以使用公有同义词：

select * from OWNERS2 ;

查询结果如下：

五、索引

5.1什么是索引

索引就是一本书的目录，就是找东西快。你要查某条记录，你要加上where条件，它的原理就是把整个表扫描一遍，吧符合结果的挑选出来。也就是逐行扫描，找到你想要的结果。

上百万级别的时候在考虑索引。索引不是逐行扫描，而是通过索引找到这条记录的通过物理地址，它会吧这些数据整成索引的数据结构，索引就是一个树，通过树快速找到符合条件的物理地址，也就是rowid。

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次

数 , 从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于

下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提

到的伪列（ ROWID ）

5.2普通索引

语法：

create index 索引名称 on 表名 ( 列名 );

需求：我们经常要根据业主名称搜索业主信息，所以我们基于业主表的 name 字

段来建立索引。语句如下：

create index index_owners_name on T_OWNERS( name )

索引性能测试：

创建一个两个字段的表

create table T_INDEXTEST (

ID NUMBER ,

NAME VARCHAR2 ( 30 )

);

编写 PL/SQL 插入 100 万条记录（关于 PL/SQL 我们在第四章会学到）

BEGIN

FOR i in 1 ..1000000

loop

INSERT INTO T_INDEXTEST VALUES (i, 'AA' ||i);

end loop ;

commit ;

END ;

创建完数据后，根据 name 列创建索引

CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST( name )

执行下面两句 SQL 执行

SELECT * from T_INDEXTEST where ID = 765432 ;

SELECT * from T_INDEXTEST where NAME = 'AA765432' ;

我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短

5.3唯一索引--加上unique关键字

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可

以创建唯一索引。

语法：

create unique index 索引名称 on 表名 ( 列名 );

需求：在业主表的水表编号一列创建唯一索引

语句：

create

unique

index

index_owners_watermeter

on

T_OWNERS(watermeter);

5.4复合索引

我们经常要对某几列进行查询，就要用复合索引。

我们经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜

索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。那

如何建立索引呢？我们可以建立复合索引，也就是基于两个以上的列建立一个索

引 。

语法：

create index 索引名称 on 表名 ( 列名 , 列名 .....);

根据地址和门牌号对学员表创建索引，语句如下：

create index owners_index_ah

on T_OWNERS(addressid,housenumber);

5.5反向键索引--加上reverse关键字

当某个字段的值为连续增长的值,就会建立反向键索引。反就是二进制反。

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子

树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变

得不规则，从而使索引树能够均匀分布。

语法：

create index 索引名称 on 表名 ( 列名 ) reverse ;

5.6位图索引--bitmap关键字

前面的索引都是BTtree*索引，位图就是一张图片。每一个rowid存在节点上，位图索引就是每一个rowid存在像素节点上。他们之间没有先后顺序的。位图索引只能用等于号，不能用大于号，小于号啥的。

范围、模糊查询都会造成索引失效。

使用场景：位图索引适合创建在低基数列上

位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射

优点：减少响应时间，节省空间占用

语法：

create bitmap index 索引名称 on 表名 ( 列名 );

需求：我们在 T_owners 表的 ownertypeid 列上建立位图索引，语句：

create bitmap index index_owners_typeid

on T_OWNERS(ownertypeid)