ORACLE 第 3 章

ORACLE 数据库

第 3 章

ORACLE 对象

一、视图

（一）什么是视图

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。

使用视图的优点：

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

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

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

权限。

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

口。

（二）创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH READ ONLY]

选项解释：

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

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

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

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

（三）删除视图语法

DROP VIEW view_name

（四）例子

需求：分页逻辑过于复杂，创建一个分页视图

-- 创建视图
create view t_user_page as 
select * from (select id,addTime,userName,password,rownum r from (select id,addTime,userName,password from t_user order by id desc) where rownum <=20) t where t.r>10;

-- 查询视图
select * from  t_user_page;

-- 创建或替换视图
create or REPLACE FORCE VIEW t_user_page as
select id,addTime,userName,password from t_user;

二、序列

（一）什么是序列

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

（二）创建与使用简单序列

创建序列语法：

create sequence 序列名称

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

NEXTVAL 返回序列的下一个值

CURRVAL 返回序列的当前值

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

提取下一个值

select 序列名称.nextval from dual

提取当前值

select 序列名称.currval from dual

create sequence t_user_id_seq;

select t_user_id_seq.nextval from dual;

select t_user_id_seq.currval from dual;

（三）创建复杂序列

语法：

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}];//分配并存入到内存中

1) INCREMENT BY：用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

2) START WITH： 定义序列的初始值(即产生的第一个值)，默认为1。

3) MAXVALUE： 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4) MINVALUE：定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

5) CYCLE和NOCYCLE ：表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

6) CACHE：(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。

7) NEXTVAL ：返回序列中下一个有效的值，任何用户都可以引用。

8) CURRVAL ：中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。

（四）修改和删除序列

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

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列：

DROP SEQUENCE 序列名称;

（五）查看序列

select * from user_sequences ;

可以加上查询条件

（六）插入语句使用序列

-- 使用当前序列
insert into t_user(id,addTime,userName,password) 
values(t_user_id_seq.currval+100,sysdate,'张三','123456');

-- 使用下一个序列
insert into t_user(id,addTime,userName,password) 
values(t_user_id_seq.nextval+100,sysdate,'张三','123456');

四、索引

（一）什么是索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于 下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提 到的伪列（ROWID）

（二）普通索引

语法：

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

drop index 索引名称;

需求：给t_user表的userName创建索引

create index index_t_user_userName on t_user(userName);

索引性能测试：

创建一个两个字段的表

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 查询所用的时间要短

（三）唯一索引

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

语法：

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

需求：建唯一索引

语句：

create unique index INDEX_T_INDEXTEST_ID on
T_INDEXTEST(id);

唯一索引执行搜索效率更高

（四）索引分类

适当的使用索引可以提高数据检索速度，那Oracle有哪些类型的索引呢？

1、b-tree索引：Oracle数据中最常见的索引，就是b-tree索引，create index创建的normal就是b-tree索引，没有特殊的必须应用在哪些数据上。

2、bitmap位图索引：位图索引经常应用于列数据只有几个枚举值的情况，比如上面说到过的性别字段，或者我们经常开发中应用的代码字段。这个时候使用bitmap位图索引，查询效率将会最快。

3、函数索引：比如经常对某个字段做查询的时候经常是带函数操作的，那么此时建一个函数索引就有价值了。例如：trim（列名）或者substr(列名)等等字符串操作函数，这个时候可以建立函数索引来提升这种查询效率。

4、hash索引：hash索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。创建hash索引必须使用hash集群，相当于定义了一个hash集群键，通过这个集群键来告诉oracle来存储表。因此，需要在创建HASH集群的时候指定这个值。存储数据时，所有相关集群键的行都存储在一个数据块当中，所以只要定位到hash键，就能快速定位查询到数据的物理位置。

5、reverse反向索引：这个索引不经常使用到，但是在特定的情况下，是使用该索引可以达到意想不到的效果。如：某一列的值为{10000,10001,10021,10121,11000,....}，假如通过b-tree索引，大部分都密集分布在某一个叶子节点上，但是通过反向处理后的值将变成{00001,10001,12001,12101,00011,...}，很明显的发现他们的值变得比较随机，可以比较平均的分部在各个叶子节点上，而不是之前全部集中在某一个叶子节点上，这样子就可大大提高检索的效率。

6、分区索引和分区表的全局索引：这两个索引是应用在分区表上面的，前者的分区索引是对分区表内的单个分区进行数据索引，后者是对分区表的全表进行全局索引。分区表的介绍，可以后期再做单独详解，这里就不累述了。