Oracle研学-对象

学自B站黑马程序员

1.视图 (物化视图序列同义词oracle特有）

  1.对一个SQL语句的封装，一个虚拟的表。-简化开发

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

使用视图的优点：

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

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

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

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

  2.创建或修改视图语法

  //基本subquery是查询语句
  CREATE VIEW view_name AS subquery 
  //[]可选
  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 操作。(加上后变为只读)
  //删除视图
  DROP VIEW view_name
  

  3.简单视图

  //视图可以在views文件夹中找到,视图是虚拟表，修改视图数据相当于修改表数据，改表的数据则视图也跟着变，视图在数据库中只占一条sql语句的大小，视图的表也叫做基表
  //创建视图：业主类型为1 的业主信息
  create or replace view view_owners1 as
  select * from T_OWNERS where ownertypeid=1
  //通过视图修改数据
  update view_owners1 set name='周也' where id=4    
  

  4.带检查约束的视图with check option

  //根据地址表（T_ADDRESS）创建视图VIEW_ADDRESS2,内容为区域ID为2的记录
  create or replace view view_address2 as
  select * from T_ADDRESS where areaid=2
  with check option
  //执行更新语句会报错ORA-01402 视图的条件是areaid=2 这个条件不能改
  update view_address2 set areaid=1 where id=4
  

  5.只读视图的创建与使用（只能查不能改）WITH READ ONLY

  //将上边的视图修改为只读视图
  create or replace view view_owners1 as
  select * from T_OWNERS where ownertypeid=1
  with read only
  //执行更新语句会报错ORA-01733，只读视图不允许修改
  update view_owners1 set name='邢道荣' where id=4 
  

  6.创建带错误的视图-工具的视图上会有个小红叉

  //创建视图但视图的SQL语句所设计的表并不存在，ORA-00942
  create or replace view view_TEMP as
  select * from T_TEMP (错误语句)
  //有时我们创建视图时的表可能并不存在，但是以后可能会存在 FORCE
  create or replace FORCE view view_TEMP as
  select * from T_TEMP
  

  7.复杂视图-视图的 SQL 语句中，有聚合函数或多表关联查询

  多表关联复杂视图-视图的每一列都是要有名的

  //创建视图，查询显示业主编号，业主名称，业主类型名称
  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;
  //所需改的列不属于键保留表的列ORA-01779
  update view_owners set 业主类型='普通居民' where 业主编号=1;
  //多表关联的事务中存在键保留表，多表关联的视图中存在某一个或多个基表是键保留表，键保留表的数据是可以修改的(若字段属于键保留表就可以修改，不是则不能修改)，键保留表就是把主键保留下来的那个表(视图的主键来自于哪一张表，该表就是键保留表)，只读视图没有键保留表的概念(因为不管是否为键保留表都不能修改)
  该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。在我们这个例子中，视图中存在两个表，业主表（T_OWNERS）和业主类型（T_OWNERTYPE）, 其中 T_OWNERS 表就是键保留表，因为 T_OWNERS 的主键也是作为视图的主键。键保留表的字段是可以更新的，而非键保留表是不能更新的。
  

  分组聚合统计复杂视图-没有键保留表(不能修改数据)

  //创建视图，按年月统计水费金额 sum(money)不算列名，需要起别名ORA-00998
  create view view_accountsum as
  select year,month,sum(money) moneysum 
  from T_ACCOUNT 
  group by year,month
  order by year,month
  //聚合函数，没有键保留表，所以无法执行 update ORA-01732
  update view_accountsum set money=100 where year='2012' and month='03'
  

2.物化视图-真实存在的表建立副本-提高性能

  视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图可以理解为一张表）。

  物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的，物化视图相当于查单表(比需要多表查询的视图速度快)提高查询性能，但占用空间。基表就是物化视图所涉及的表

  要求数据实时性强设置自动刷新，若只是想每天抓两个快照，不需要最准确的数据可以使用手动刷新(日报表，每晚12点刷一下)

//物化视图在Materialized views文件夹中(定义) 物化视图一般用MV_开头
//建立物化视图后会在tables中形成一张对应的表(定义对应的表)，两处都可通过工具查询数据,删除物化视图，对应的表也会被删除，删除物化视图语句如下
drop materialized view MV_address1
//创建语法 METERIALIZED-实体化 IMMEDIATE-立即 DEFERRED-延迟
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 操作(提交操作)时自动刷新物化视图。

1.创建手动刷新的物化视图-提交基表的时候不会自动更新数据

//查询地址 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）,手动刷新物化视图,因为默认ON DEMAND故需要手动刷新(执行下列语句进行刷新)
select * from mv_address;
//引号中是物化视图名，DBMS_MVIEW.refresh是oracle内置的存储过程，C是完全刷新，F是快速刷新
begin
DBMS_MVIEW.refresh('MV_ADDRESS','C');
end;
//或者通过下面的命令手动刷新物化视图 注意：此语句需要在命令窗口中执行。工具中的Command Window窗口
EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');
//DBMS_MVIEW.refresh 实际上是系统内置的存储过程

2.创建自动刷新的物化视图-每次基表刷新自动更新物化视图

//查地址ID地址名称和所属区域名称 多出refresh on commit字段
//基表发生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
//插入基表
insert into t_address values(8,'宏福苑小区',1,1);
//查询物化视图，自动更新
select * from mv_address;

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

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
//对基表插数据，视图中仍没有数据，这种视图第一次必须通过PLSQL生成数据，之后可以自动刷新
insert into t_address values(8,'宏福苑小区',1,1);
//生成数据PLSQL
begin
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;
//查询视图，由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须再次执行 refresh
select * from mv_address3

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

//创建增量刷新的物化视图，必须首先创建物化视图日志
//物化视图日志相当于一张表，记录基表发生了那些变化(增删改)，用这些记录去更新物化视图 on后面是需要创建物化视图日志的表名 with后面是根据什么进行刷新，可以是主键，rowid或其他
create materialized view log on t_address with rowid；
create materialized view log on t_area with rowid
//创建的物化视图日志名称为 MLOG$_表名称(开头)在tables中可以看到，当基表发生增删改时，表中出现数据，基于两个表创建的物化视图，两张表都要创建日志
//创建物化视图的语句中，必须有基表的rowid，两张表的都要
//创建物化视图
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;
//此时可以查询出物化视图数据
select * from mv_address4
//插入一条数据
insert into t_address values(9,'男士小区',1,1);
commit;
//此时物化视图中没有数据，因为设置了手动刷新，这样可以看到物化视图中的内容，若设置自动刷新，物化视图是看不到内容的(增加日志内容后马上就刷新掉看不到过程)
//此时可以看到物化视图日志
M_ROWSS:表示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;
注意：创建增量刷新的物化视图，必须：
1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

3.序列

  序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象(产生连续数字的数据库对象)oracle表的字段没有自动增长的特性，对应的oracle提供了序列，独立于表的一个数据库对象，序列产生数字，表可以使用这个数字

1.简单序列

//简单序列 sequence-序列 名字最好用seq_开头 在Sequences文件夹中找
create sequence 序列名称
//通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
注意：在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。
//查询序列的下一个值，每执行一次序列走一步，取一次就走一次
select 序列名称.nextval from dual
//查询序列的当前值,序列数字不会继续往下走
select 序列名称.currval from dual

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}];//分配并存入到内存中(序列实际存在磁盘上)，开启缓存后，可在内存中获取序列，减少访问磁盘次数，cache 10一次拿10个值内存用完再去磁盘拿，NOCACHE表示不走内存，每次都从磁盘拿，(默认值cache 20)，若拿20后，序列号走到10时服务器重启，此时再抓序列应为21，此时缓存中的空间浪费了，内存会重新抓20个，此刻会出现断号(重启服务器和oracle都可能出现断号)

3.案例

//语句需要加分号不然打点调用不出来
//有最大值20的非循环序列
create sequence seq_test1 
maxvalue 20
//执行查询时，序列号不断自增,当超过最大值时会报错ORA-08004
select seq_test1.nextval from dual;

//增长值为10,从10开始，最大值100
create sequence seq_test2
increment by 10
start with 10
maxvalue 100;

//最小值5
create sequence seq_test3
increment by 10
start with 10
minvalue 5
maxvalue 100;

//循环序列,ORA-04013(缓存必须小于一次循环)，该限制只在循环下有
//缓存默认20次，在该序列中会取200个(10*20)增长值是10，起始10最大为210
create sequence seq_test4
increment by 10
start with 10
minvalue 10
maxvalue 210    //多一个数也能通过201，但少于等于都不行
cycle;   //一次缓存数为缓存值*增长值

//非循环序列可以缓存数超出，oracle会自动适配
create sequence seq_test5
increment by 10
start with 10
minvalue 10
maxvalue 100    //多一个数也能通过201，但少于等于都不行
cache 20;   //一次缓存数为缓存值*增长值

4.修改和删除序列

//使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数
//修改序列最大值为5000，循环序列(非循环用no cycle)，起始参数不能改
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
//删除序列
DROP SEQUENCE 序列名称;

4.同义词-别名-可做兼容性

1.创建同义词的具体语法 SYNONYM-同义词，在Synonyms文件夹中看

  create [public] SYNONYM synooym for object;

  其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要对哪个对象起别名。加public是公有同义词(大家都可以用)，不加是私有同义词(只有当前用户能使用该同义词，system不能直接使用(dba权限)，需要通过用户打点调用)

2.案例-varchar是varchar2的同义词(内置类型)，类型也是对象的一种

//为表T_OWNERS 创建(私有)同义词 名称为OWNERS(相当于起别名)
create synonym OWNERS for T_OWNERS;
//与表的使用方式相同
select * from OWNERS
//为表 T_OWNERS 创建( 公有 )同义词 名称为 OWNERS2
create public synonym OWNERS2 for T_OWNERS;
//以另外的用户登陆，也可以使用公有同义词
select * from OWNERS2

5.索引-为查询服务-b树

  索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。(索引查询有0秒的时候)，索引实际上也是通过寻找物理地址查询数据(但是他找的快)

  索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID），根据rowid查比主键更快(跟索引差不多)，oracle内部有缓存机制，所以有时查询时长不同(可多次查询取时长)

1.普通索引-建表指定主键索引自动生成(基于主键)

//语法 在Indexes文件夹中可看到索引基于何表何列创建
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 万条记录
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 查询所用的时间要短

2.唯一索引-unique（某一列不能重复时可创建，同时创建唯一约束）

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

//语法 性能高于普通索引 唯一约束-无法插入相同的值 列有重复记录则无法创建
create unique index 索引名称 on 表名(列名);
//在业主表的水表编号一列创建唯一索引
create unique index index_owners_watermeter on
T_OWNERS(watermeter);

3.复合索引-索引的字段顺序与查询的字段顺序要一致

  要对多列进行查询，例对这两列建立两个索引，要查两棵树，查询性能不一定高，故建立复合索引，也就是基于两个以上的列建立一个索引

//语法
create index 索引名称 on 表名(列名,列名.....);
//根据地址和门牌号对学员表创建索引
create index owners_index_ah
on T_OWNERS(addressid,housenumber);

4.反向键索引-reverse

  应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布，将原来10进制数据转二进制后进行反向键运算(0100变0010)再转回10进制数，此时数值就会变得随机一些方便分布降低层数

//语法 建立索引后加reverse
create index 索引名称 on 表名(列名) reverse;

5.位图索引-非b树，bitmap，将对应位图上的点取出进行查找(比树快)

  使用场景：位图索引适合创建在低基数列上 (可能性少，例:男女每个可能性都要建立一张位图，存入对应id，会占用很大空间)

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

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

//语法 只能用等号(等于某张位图)不能范围查询，不能走索引
create bitmap index 索引名称 on 表名(列名);
//我们在 T_owners 表的 ownertypeid 列上建立位图索引
create bitmap index index_owners_typeid
on T_OWNERS(ownertypeid)