Oracle基础和进阶笔记第二篇_oracle数据库进阶

六、索引

1、索引的特点

（1）索引是建立在表的一列或多个列上的辅助对象，是可选的，目的就是为了提高数据的访问效率，降低磁盘的IO，类似于书本的目录；
（2）索引的分类逻辑上分为unique（唯一索引）和 nonunique（非唯一索引），这里只介绍部分常用的分类，并非全部；
唯一索引：如身份证号码，就是每个值都不一样，可建唯一索引，默认主键非空且唯一就是唯一索引；
非唯一索引：如姓名，不可避免的可能存在重名的情况，这上面建的索引就只能是非唯一索引。
（3）物理上常用分类分为B-tree B树索引，Bitmap 位图索引，HASH 哈希索引，Oracle存储索引的默认数据结构是B-tree索引；
物理上面分类是指索引在底层寻找数据的时候的不同方式方法，理解索引在底层的原理更能理解需要在什么情况下创建什么类型的索引。
B树索引：Oracle的默认索引，就是像一颗倒立的树，如图所示，因为它是顺序存储的，所以比较适合范围查找，如销售表在日期字段添加B树索引，在计算某个时间段的范围时，效果明显；

位图索引：针对的是值很少的字段，且经常需要用这个字段来查询的情况下，如性别：只有男女两个选项，存储到数据库就是0和1两个值。当一个表含多个位图索引，并且在查表时联合使用，更能体会位图索引的真正威力，位图索引可以理解成是多个字段索引的按位与或，而不是根据范围或指针来查找数据的；
哈希索引：哈希索引就是将每个字映射成一个哈希值，每个相同的值映射的哈希值是一样的，且因为相邻的值，映射成哈希值后并不相近，所以哈希索引更适合单独的查找，如医院前台每次只需调一个人的信息出来查看，并不需要涉及到范围查询，在身份证号上面添加哈希索引，这样保证每次查找都只需转化一次哈希值，并只找一次即可。
总的来说，B树索引比较通用，各种唯一值和非唯一值都能使用，且更适合范围查询，如上图的节点10更容易找到9和11，因为底层是按照顺序去存储的。但查询单个值的话，如11，需要先找到10，才能找到11，花了2步，而且随着数据量的增加，节点也会增加，所以单独查一个值的速度慢于哈希索引；
位图索引更适合值比较少的，如性别这样的字段，且更适合or这样的查询；
哈希索引更适合每次都是一个值单独查询的，不太适合范围查询，因为相邻近的数据，他们的哈希索引并不临近，导致范围查询就是每个值都哈希查找，数据量大时范围查询不如B树索引。
（4）索引本身也是数据，也是需要消耗和占用存储空间，因此根据需要仅在需要经常查询的字段上面添加索引；
（5）数据库会自动的维护索引的内容，当对表进行的增、删、改的操作时，系统会自动的更新索引；
（6）索引可以提高数据的查找效率，但对表的增、删、改的操作时，因为系统也要维护索引，导致增、删、改的效率下降。所以表的索引不是越多越好，需要衡量两者的权重；当数据改动多，而查询少时，适当减少索引的创建。

2、索引的创建

创建索引的语法：

CREATE UNIUQE | BITMAP INDEX <index_name> 
	ON <table_name>(<column_name>,<column_name>,..)
1
2

UNIUQE 创建唯一性索引，BITMAP 为创建位图索引，省略都不写为非唯一索引；
<index_name> <table_name> <column_name>依次为索引名称，表名，列名，列名可以有多个，多个列时为创建组合索引；

6.2.1 在学生表的名字上面创建普通索引

create index idx_stu_name on student(name);
1

6.2.2 在分数表的scoid上面创建唯一索引

create unique index uni_score_scoid on score(scoid);
1

6.2.3 在学生表的性别列上，创建位图索引

create bitmap index bit_stu_sex on student(sex);
1

6.2.4 在学生表中创建名字和生日的组合索引

create index idx_stu_namebirthday on student(name,birthday);
1

6.2.5 索引重命名

-- alter index 索引名称 rename to 新的名称;
alter index idx_stu_name to idx_student_name;
1
2

6.2.7 重建索引
如果表到后期越来越大，新增了几千上万个节点，修改了里面的大部分数据，就会导致索引的叶子节点各种“支离破碎”，“缓慢膨胀”，且索引的顺序也会被打乱。这时索引已经不能按照预期发挥快速查找的功能，这时就需要重建索引，或者合并索引，类似于重新按顺序整理一份书本的目录。

-- 重建索引就是重新建立索引，但是重新建立索引的开销大，重建的过程会影响现在的查询效率，
-- 需在较空闲的时间综合情况谨慎用之
alter index 索引名称 rebuild;
-- 合并索引也是能重新整理索引
alter index 索引名称 coalesce;
1
2
3
4
5

推荐使用合并索引，相对于重建索引，合并索引有以下优势：
一是不需要占用磁盘存储索引两倍的空间，二是可以在线操作，三是无需重建索引结果，四是尽快合并索引页块，不失为一种可用的方法。

6.2.8 删除索引

drop index 索引名称;
drop index idx_stu_namebirthday;
1
2

6.2.9 索引失效
虽然创建了索引，但是查询的条件不遵循索引的规则，就相当于没用到索引，也即索引失效的情况。

6.2.9.1 字段类型不匹配
stuid是数值类型，但后面的条件’2’却是字符类型，类型不匹配，此时索引无效。虽然Oracle现在的版本会帮助优化此类索引查询，但还是养成类型匹配的习惯比较好。

-- 索引失效（但简单的Oracle会自动优化）
select * from student where stuid = '2'; 
-- 正确做法
select * from student where stuid = 2; 
1
2
3
4

6.2.9.2 字段不能加函数
因为字段的索引是建立在字段的值上的，加了函数之后，就不是原来的值了，所以索引失效，所以应该给需要查询的值添加函数，而不是给字段添加函数。

-- 索引失效查询
select * from student where to_char(stuid) = '2';
select * from student where trunc(birthday) = date'2022-01-01';
-- 正确做法
select * from student where stuid = to_number('2');
select * from student 
	where birthday >= date'2022-01-01' and birthday < date'2022-01-02';
1
2
3
4
5
6
7

6.2.9.3 对字段使用空或非空查询时
使用空或非空查询时，Oracle默认都是全表扫描，因为空值上面是不会加索引的，所以应该尽量减少此类非空查询。

-- 使用空值或非空查询
select * from student where name is null;
select * from student where name is not null;
-- 非空时，根据字段类型可以使用如下方式替代
select * from student where name > '0';
select * from student where stuid > 0;
1
2
3
4
5
6

查询为空时，只能使用全表扫描。

6.2.9.4 使用 <> , != 等操作
Oracle默认的B-tree索引是按照顺序排列的，直接使用 <> 或 != 会使索引失效，这种可以使用 > 或者 < 的操作来代替。

-- 使用 <> 或 != 查询
select * from student where name <> '小三';
select * from student where name != '小三';
-- 正确做法
select * from student where name > '小三' or name > '小三';
1
2
3
4
5

6.2.9.5 使用like时，拿开头作为匹配条件
索引是按照前面匹配的规则来查找数据的，所以左边的值要存在才能使用索引，也称为最右原则（匹配符号%,_等放在右边）。

-- 前面使用模糊匹配
select * from student where name like '%二';
-- 可用如下代替
select * from student where name like '小%';
1
2
3
4

当然并不能所有的模糊查询都能这样改，应该视情况而定，实在没办法就应该使用全表扫描。

6.2.9.6 使用组合索引时，没有按照组合规则使用
上面的name，birthday组合索引，只用name作为条件可以用到索引，用name和birthday一起使用也能使用索引，但单独使用birthday无法用到索引。同理，组合3个字段索引时（假设是字段1，字段2，字段3），字段1组合，字段1和字段2组合，字段1、字段2和字段3组合都能用索引，但是单独字段2，单独字段3，字段2和字段3组合时不能使用索引。就是创建组合索引时，应该将最常使用的字段放在前面。

-- 组合索引时，只用后面的字段
select * from student where birthday = date'2022-01-01';
-- 替代方案
select * from student where birthday = date'2022-01-01' and name = '小二';
1
2
3
4

6.2.9.7 使用not in 或 not exist的时候，不会使用索引

select * from student where  name not in ('小二','小甲','小乙');
1

6.2.9.8 Oracle自主分析
Oracle基于cost成本分析，查询小表，或者大表的条件查询返回值大概在10%以上时，Oracle分析认为全表扫描成本更小，会自动切换到全表扫描而使字段的索引失效，所以小表可以不用建索引，大表经常查询的需要建索引。因为我们测试的表数据都很小，所以Oracle基本都是全表扫描。

6.2.9.9 Oracle优化思路
Oracle的优化思路参考
1、减少磁盘的访问，减少数据的访问（合理有效的利用索引），仅返回需要的字段（减少使用select *等，直接select需要的字段等)
2、减少网络传输，批量处理数据，减少网络IO，可以将逻辑写成存储过程放在本地，远程调用等
3、减少系统开销，减少CPU的开销（较少排序，全表查询等），减少内存的开销等
4、充分利用资源，表按规则分区，表和索引分开不同区存放等

七、视图

视图是一种数据库对象，是从一个表或多个联合表中查询的虚表，视图查询的数据，并不是真正存储在视图中，而是存储在视图引用的数据表中。视图存储的只是一组查询语句，视图可分为普通视图和物化视图。
视图的优点有：
（1）简化数据操作，如可以将4,5张表的联合查询放在视图里面，简化使用人员的数据查询；
（2）着重于特定的数据，例如学生表中的添加了身份证和家长电话，这个属于比较隐私的数据，直接提供学生表供人查询就会不可避免的泄露隐私，但通过视图可以给数据加脱敏规则来解决；
（3）视图提供一个安全机制，可以针对不同的人提供不同的视图给到不同的数据，还可以设置不同的访问权限；
（4）视图也能提供向后兼容的接口，提供了视图后，若视图里面的定义发生变化修改，对视图的使用是没有任何感觉和影响的，后续便于更新。

1、普通视图

语法规则如下：

create [or replace] [force] view 视图名称	
	as 查询语句 [with check option] [with read only]
1
2

replace 覆盖，如果有同名的视图，则直接覆盖不报错；
force 查询语句的基表不存在时，也强制创建视图（一般基表不存在是会报错的，不建议加上force关键词，可以避免基表的表名写错）；
with check option 当对视图插入或修改数据的时候，必须满足视图的约束；
with read only 创建只读视图，这个一般可以加上，避免别人对通过视图对原表的数据进行操作。

7.1.1 创建学生成绩视图

create or replace view student_score
as 
	select a.name,b.subject,b.score
	from student a,score b
	where a.stuid = b.stuid
with read only;
1
2
3
4
5
6

7.1.2 创建带检查的约束视图

create or replace view v_student
as 
	select stuid,name from student where stuid > 14
	with check option;
1
2
3
4

加上约束条件，意思就是视图只能修改自己能查到的信息：

-- 查询视图的值
select * from v_student;
-- 修改stuid=15的值为16，能正常执行
update v_student set stuid = 16 where stuid = 15;
-- 修改stuid=15的值为13，不能正常执行
update v_student set stuid = 13 where stuid = 15;
1
2
3
4
5
6

因为视图 v_student 本身不能查出来 stuid=13 的值，所以无法将值修改成13，因为对stuid=13的值该视图没有查询权限，自然也就更没有修改权限。
关于with check option，总结的规律如下：
（1）对于update,有with check option，要保证update后，数据要被视图查询出来，就是更新后的数据也要满足查询语句的where条件；
（2）对于delete,有无with check option都一样；
（3）对于insert,有with check option，要保证insert后，数据要被视图查询出来，就是insert语句插入的值也要满足查询语句的where条件；
（4）对于没有where 子句的视图，使用with check option是多余的。

2、物化视图

物化视图可以理解为根据查询语句建立的一份数据副本，相对于普通视图（只存储查询语句不存储数据），物化视图需要占用存储空间存储数据副本。物化视图的优点是查询效率更高，但需要占用存储空间，普通视图的查询效率相对物化视图比较低，是因为普通视图存储的是查询语句，每次查询都是按照查询语句查询一遍。
当基表的数据量很大，或者多表联合查询的语句很耗时的时候，考虑使用物化视图，物化视图可以在本地建立远程的数据副本，优化查询效率。因此物化视图需要解决的点就是数据的同步问题，因为针对物化视图的数据新增修改删除，或者基表本身的数据新增和修改，都要同步回物化视图，物化视图才能查询显示出来。

create materialized view 物化视图名称
[build immediate | deferred] 			-- 同步数据 立即显示 | 延迟显示
refresh [ fast | complete | force ]   	-- 刷新方式 增量刷新 | 全量刷新 | oracle默认的刷新
[on [ demand | commit ] | start with (start_time) next (next_time)]
as 查询语句;
1
2
3
4
5

immediate 在创建的物化视图的时候，同步刷新数据，物化视图一创建成功，查询物化视图即有数据，Oracle默认的同步数据是立即显示；
deferred 延迟显示，在创建物化视图的时候，不会立即同步刷新数据，需要手动再执行刷新的命令，才会刷新数据，数据量大的时候可以先快速创建视图，然后再执行刷新操作；
fast 刷新的方式，增量刷新，自上次刷新以后进行的新增，修改，删除部分更新，比较快速；
complete 全量刷新的方式，重新再全部刷新一遍数据，比较慢但是是全部刷新；
force Oracle默认的刷新方式，先判断能否用快速刷新，可以则用fast，否则则用complete方式刷新（推荐使用）；
demand 刷新的触发方式，当需要刷新的时候才刷新，一般用在定时刷新，如每天凌晨3点更新，或每隔15分钟刷新一次等（Oracle默认）；
commit 基表一提交就立马刷新，这个能保证数据的每时每刻都是同步的，但当基表commit操作过多的时候，会给刷新造成很大压力，适合小表或数据同步性高的表；
start with 创建后第一次刷新的时间，next 为下一次刷新的时间，一般用sysdate+15/1440(一天1440分钟，这里为15分钟）之类的表示间隔；

7.2.1 创建手动刷新的物化视图

-- 创建物化视图
create materialized view mv_student
build immediate
as select stuid,name from student;
-- 查询物化视图，可以看到最后一个stuid是15
select * from mv_student;
-- 将上面修改的学生id改成20，改完再查select * from mv_student;学生ID还是15，因为物化视图没有更新
update student set stuid = 15 where stuid = 20;

-- 编写手动刷新的语句
begin
	-- DBMS_MVIEW.refresh是Oracle自带的刷新物化视图的存储过程，
	-- 'C'COMPLETE是刷新物化视图的参数，完全刷新
	DBMS_MVIEW.refresh('mv_student', 'C');
end;
-- 更新完再查询，stuid就会更新成20
select * from mv_student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

7.2.2 创建自动刷新的物化视图

create materialized view mv_student2
-- 刷新方式采用Oracle默认，刷新点在提交时自动刷新
refresh on commit
as select stuid,name from student;
-- 修改基表的数据
update student set stuid = 16 where stuid = 20;
-- 查询物化视图是否自动更新
select * from mv_student2;	
1
2
3
4
5
6
7
8

7.2.3 创建不立即显示数据的物化视图
这个适合在创建大表的时候使用，先创建视图，然后再更新数据。

create materialized view mv_student3
-- 数据延迟显示
build deferred
-- 刷新方式采用Oracle默认，刷新点在提交时自动刷新
refresh on commit	
as select stuid,name from student;
-- 此时查询无数据
select * from mv_student3;

-- 编写手动刷新的语句
begin
	DBMS_MVIEW.refresh('mv_student3', 'C');
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

7.2.4 创建增量更新的物化视图
增量更新的物化视图，必须首先创建物化视图的日志，物化视图的日志记录了基表发生了哪些变化，用日志去更新物化视图。关于增量的物化视图因为涉及到日志的处理方式，还有诸多增量物化视图的限制，就这一个能开一个专题，故这里不作过多介绍。
有兴趣研究的小伙伴可以先参考以下资料：
Oracle物化视图与物化视图日志 https://blog.csdn.net/cckevincyh/article/details/88982217
物化视图快速刷新限制条件 https://blog.csdn.net/aiyocxuan/article/details/78732567
以下代码仅供示例：

-- 创建日志
create materialized view mv_student_log on student with primary key including new values; 
create materialized view mv_classroom_log on classroom with primary key including new values;
-- 增量刷新
create meterialized view mv_stu_class
refresh fast on demand
as select stuid,name from student;
1
2
3
4
5
6
7

八、序列

项目开发过程中，数据库表中的主键值有时需要被设置为number类型且自增，MySql、Sql Server中使用工具创建表时能够很方便的设置自增。但是Oracle中没有设置自增的方法，一般情况下需要使用序列或触发器来实现主键自增功能。
序列是Oracle数据库中特有的，使用序列可以生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5… 的效果，序列也可以看成是自动增长的表。

1、序列创建语法

create sequence 序列名称
		start with 从几开始
		increment by 每次增长多少
		[maxvalue 最大值 | nomaxvalue 不设最大值]
		[minvalue 最小值 | nominvalue 不设最小值]
		cycle 自动循环| nocycle 不自动循环 --是否自动循环
		[cache 缓存数量] | nocache 不缓存;
1
2
3
4
5
6
7

8.1.1 创建一个序列auto_increment_seq，从1开始增长，每次增长1，无最大限制，最小为1，不自动循环，缓存100

create sequence auto_increment_seq
		start with 1
		increment by 1
		nomaxvalue
		minvalue 1
		nocycle
		cache 100;
1
2
3
4
5
6
7

序列有两个字段，分别为当前值 currval 和下一个值 nextval，当序列刚创建或者刚使用的时候，是没有当前值的，也就是直接查询 currval 的值是会报错的，因为此时序列还没定义，值为空，如下：

select auto_increment_seq.currval from dual;
-- ORA-08002: sequence AUTO_INCREMENT_SEQ.CURRVAL is not yet defined in this session
1
2

-- 先调用 nextval ，再调用 currval
select auto_increment_seq.nextval from dual;
-- 查询当前的序列值，刚创建的时候序列是没有定义的，当前值为空
select auto_increment_seq.currval from dual;
1
2
3
4

8.1.2 查找当前用户下的序列

select * from user_sequences;
1

8.1.3 删除序列

drop sequence auto_increment_seq;
1

如果当前没有创建序列的权限，则需使用管理员或有权限的账号赋予创建权限方可。

-- 将权限赋予某个用户
grant create sequence to username;
1
2

九、触发器

触发器就是对数据库中的对象操作时，触发运行另一组语句的操作。常用来约束数据库的完整性和约束数据库的操作，也能用来监视对数据库的各种操作。
触发器的分类：
（1）触发器可简单分为前置触发器（before）和后置触发器（after）；
（2）根据影响触发器还能再分行触发器（for each row）和语句触发器；
（3） 根据使用可以分为普通触发器（针对表），替代触发器（针对视图）和系统事件触发器（针对DDL操作或数据库系统）。

行触发器和语句触发器：
（1）行触发器当DML语句操作影响数据表中的多行时，针对每个数据行，均触发一次触发器；
（2）语句触发器当DML语句操作影响数据表时，将这个语句操作作为触发时间，仅触发一次。

触发器的触发次序：
（1）执行before语句级触发器；
（2）执行before行级触发器；
（3）执行DML语句；
（4）执行after行级触发器；
（5）执行after语句级触发器。

1、触发器的语法

create [or replace] trigger 触发器名
		before | after
		[delete] [[or]insert] [[or]update [of '列名']]
		on '表名'
		[for each row ][where ('条件')]
		declare
			-- ...声明变量，无需声明变量，则省略
		begin
			-- 需要执行PLSQL代码块
		End;
1
2
3
4
5
6
7
8
9
10

9.1.1 创建触发器，删除学生表之前，将需要删除的数据存在旧学生表进去

-- 创建触发器，在有同名的适合直接覆盖
create or replace trigger tri_del_student
	-- 在学生表上创建前置删除触发器，在删除之前需要做的操作
	before delete 
	on student
	-- 声明行级触发器，针对每一行都触发一次
	for each row
	-- 具体需要做的PLSQL代码块
	begin
		insert into student_old(stuid,name,birthday,sex,cid)
		values(:old.stuid,:old.name,:old.birthday,:old.sex,:old.cid);
	end;
1
2
3
4
5
6
7
8
9
10
11
12

关于上面， :new 和 :old 分别表示操作之后值定义为新值和操作之前的值定义为旧值。新旧针对的是语句而言，插入的话，插入之前是空的，插入之前的空值为旧值，插入之后的值为新值；更新的话，更新之前的为旧值，更新之后的为新值；删除的话，删除之前的值为旧值，删除之后的空值为新值。但在when子句设置条件的时候前面不用加冒号，where可看后面示例，具体可以看下表：

执行删除操作，查看结果：

-- 先查看学生表数据
select * from student;
select * from student_old;
-- 删除stuid = 14的数据 
delete from student where stuid = 14;
-- 再次查看学生表数据
select * from student;
select * from student_old;
-- 删除触发器，为了下面的演示不混淆，先删除
drop trigger tri_del_student;
1
2
3
4
5
6
7
8
9
10

9.1.2 限制对学生表的修改，对非工作日，不允许修改学生表，此处只是用MONDAY做演示，实际情况按照实际需求，这里应改成周六日

create or replace trigger tr_student_time
-- 在插入/更新/删除之前的前置触发器
	before insert or update or delete on student
	begin
		if (to_char(sysdate,'DAY') like 'MONDAY%')
			then RAISE_APPLICATION_ERROR(-2000,'非工作日禁止修改学生表');
		end if;
	end;
1
2
3
4
5
6
7
8

9.1.3 不能降低stuid=1的分数，也不能删除stuid=1的记录
只针对部分数据和部分字段的时候，创建如下触发器：

create or replace trigger tr_score_score
	before update of score or DELETE
	on score
	-- 此处的old前面不用加冒号，因为在when条件子句中
	for each row when (old.stuid = 1)
	begin
		case when updating('score') then
			if :new.score < :old.score then RAISE_APPLICATION_ERROR(-2000,'学号1的学生成绩不能降');
			end if;
		when deleting then 
			RAISE_APPLICATION_ERROR(-2001,'学生1的成绩不能删');
		end case;
	end;

-- 对score执行更新或删除stuid=1的命令是，将会有错误提醒
update score set score = 50 where stuid = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

其中inserting、updating、deleting分别当触发事件为insert、update、delete时，取值为true，否则为false。

9.1.4 在修改学生id的时候，分数表的stuid能一起修改，保持数据的统一性

create or replace trigger tr_stu_score_stuid
	after update of stuid
	on student for each row
	begin
		DBMS_OUTPUT.PUT_LINE('旧的stuid值是'||:old.stuid||'、新的stuid值是'||:new.stuid);	-- 将新旧的值打印出来，更加直观
		update score set stuid = :new.stuid where stuid = :old.stuid;
	end;
-- 更新之后，查询score表的数据即可
select * from student;
update student set stuid = 20 where stuid = 1;
select * from student;
select * from score;
1
2
3
4
5
6
7
8
9
10
11
12

触发器的执行语句也能调用存储过程，这里先不介绍。学完存储过程之后，在PLSQL代码块的地方调用存储过程即可。

2、替代触发器

替代触发器（instead of），替代触发器只针对视图或对象视图创建，只会使Oracle激活触发器，并执行触发器里面的操作，而不执行触发事件本身的操作。就是当对一个视图执行删除操作时，该操作会激活触发器，并执行触发器里面的动作执行，但不会再去执行删除操作本身。

替代触发器的特点：
（1）只能创建在视图上，并且该视图没有指定with check option的约束选项；
（2） 不能指定before或after选项；
（3） 替代触发器只能在行级上面触发，只能是行级触发器（for each row不加默认也是行级触发）；
（4）没有必要在一个表的视图上面创建替代触发器，一个表的创建DML触发器即可，所以一般都是在多表联合的视图上面创建。

替代触发器的语法：

create [or replace] trigger 触发器名称
	instead of
	{insert | delete | update [of 列名]}
	[or {insert | delete | update [of 列名]}...]
	on 视图名称  	-- 只能定义在视图上
	[referencing {old [as] old | new [as] new| parent as parent}]
	[for each row ] -- 因为instead of触发器只能在行级上触发,所以没有必要指定，此处可以不写
	[when 条件]
	执行的代码块 | call 调用的存储过程名称;
1
2
3
4
5
6
7
8
9

9.2.1 创建学生分数联合表视图，并在删除学生记录时，要求分数表的学生记录也一起删除

-- 创建学生分数视图
create or replace view v_stu_score
as 
select a.stuid,a.name,b.subject,b.score 
from student a,score b 
where a.stuid = b.stuid;
-- 删除学生表stuid=1的记录
delete from v_stu_score where stuid = 2;
-- 执行删除语句后，发现score的stuid=2记录删除了，但student的stuid=2的记录仍然存在，不符合我们预期
select * from score;
select * from student;
1
2
3
4
5
6
7
8
9
10
11

创建替代触发器：

create or replace trigger tri_v_stu_score instead of
	delete on v_stu_score for each row
	begin
		-- 分别删除学生表和分数表的记录
		delete from student where stuid = :old.stuid;		
		delete from score where stuid = :old.stuid;
	end;
-- 创建替代触发器之后，再执行下面语句
delete from v_stu_score where stuid = 3;
1
2
3
4
5
6
7
8
9

此时再查询student表，score表，会发现 stuid=3 的记录都会被删除，这里替代触发器只介绍了简单用法，具体很有很多深入的用法只能实际工作中学习摸索。
这里替代触发器触发时，会直接执行触发器里面的内容，而不会再执行delete from v_stu_score where stuid = 3这个语句，这个语句仅仅是触发替代触发器的执行。

3、系统触发器

需要用户具备ADMINISTER DATABASE TRIGGER权限才能创建，一般都是系统管理员的工作，其他人员只需了解即可。 系统事件触发器可以在DDL（数据定义语言，create，alter，drop等）或数据库系统（服务器启动或关闭，用户的登录登出，数据库服务错误等）上面被触发。

系统触发器语法：

create or replace trigger 触发器名
	{before|after} 
	-- 一个或多个ddl事件列表，用or隔开 | 一个或多个数据库事件列表，用or隔开
	{ddl_event_list | database_event_list}
	-- 建立在数据库database上面 或建立在模式schema上面
	on { database | schema }					
	[when 条件]
	执行的代码块 | call 调用的存储过程名称;
1
2
3
4
5
6
7
8

此处不作过多说明，有兴趣的小伙伴可以自行查找资料学习。

十、游标

游标可以看成是用来存储多条查询数据的一种数据结构（结果集），它有一个’指针’，能从上往下移动（fetch），从而能遍历每条记录。我们一般的数据都是存放在磁盘上的，因此读取和处理会比较慢，游标就是先将需要处理的数据先读到内存中，然后再处理，这个过程能有效的提高效率。
游标特点：因此，游标的特点就是提高sql的执行效率，但比较耗内存。
虽然比较耗内存，但执行效率才是我们考虑的，而且现在的内存都比较大，所以应该尽量使用游标优化我们的查询语句，特别是在千万级别以上的数据时，在内存允许的情况下，将会有很大的效率提升。

游标的四大属性：

游标的分类：
大类分成静态游标和动态游标；静态游标里面又有分隐式游标和显示游标。一般静态隐式游标都是系统自动创建，自动管理的（无需人工干预，自动声明，打开和关闭），所以只需了解即可。

1、一般游标创建

declare
	-- 1 声明游标，可以理解成将数据先查询放到内存中
	cursor cur_stu_info is select * from student;
	-- 2 声明一个游标数据类型的变量v_stu_info
	v_stu_info cur_stu_info%rowtype;
begin
	-- 3 开启游标
	open cur_stu_info;
	-- 4 fetch获取数据（每次获取一行），并存放到v_stu_info变量中
	fetch cur_stu_info into v_stu_info;
	-- 5 用Oracle的打印函数，将获取到的内容打印出来，正常这里就是处理数据的过程，这里演示就只是打印
	dbms_output.put_line(v_stu_info.stuid ||','||v_stu_info.name ||','||v_stu_info.sex);
	-- 6 关闭游标
	close cur_stu_info;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

rowtype 是取行的类型的关键字，由系统自动根据字段类型创建，cur_stu_info 因为将数据全部查询并存入内存，可以看成是存在内存中的一张表，cur_stu_info%rowtype 取 cur_stu_info 表的行的类型，用来存储一行的数据，变量的名称为v_stu_info。
fetch into 将 cur_stu_info 表里面的值的一行赋给 v_stu_info，因为每次只能获取一行，所以这里最后只打印一行的数据，如果要想取全部的值，这里应循环取值。

2、静态隐式游标

在平时的删除，更新，插入或者计数时，其实系统都会创建游标，就是将数据存储到内存中，再执行操作，但整个过程都是系统自动执行的，所以我们没有察觉到。

declare
	v_count number;		-- 声明一个number类型的变量v_count
	 begin
	-- 插入模拟
	insert into student(stuid,name,birthday,sex,cid) values(17,'十七',date'2022-01-01','男',4);
	-- 判断游标是否获取到值，这里是系统获取，默认游标名为sql
	if sql%found then
		dbms_output.put_line('插入成功！');
	end if;
	-- 更新模拟
	update student set sex = '女' where stuid = 17;
	if sql%found then
		dbms_output.put_line('更新成功！');
	end if;
	-- 删除模拟
	delete from student where stuid = 17;
	if sql%found then
		dbms_output.put_line('删除成功！');
	end if;
	-- 计数查询模拟
	select count(1) into v_count from student;
	if sql%found then
		dbms_output.put_line('总记录数为：'||v_count);
	end if;
	if sql%isopen then							-- 判断游标是否开启
		dbms_output.put_line('不可能到这一步，系统已经自动关闭游标');
	else
		dbms_output.put_line('系统已自动关闭游标');
	end if;
	 end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

sql 是系统默认的游标名称，系统自动创建的游标都叫这个名字，是系统默认的。
sql%found 判断该游标是否获取到值，有值时，说明系统创建了游标；sql%isopen 系统自动创建的会自动关闭，这里判断的系统游标一定是关闭的。

3、静态显示游标

由关键字cursor声明，可带参数，也可不带参数，当不带参数时，可以参考上面一般游标创建示范；
带参数时可看下面示例：
%type是获取表字段的类型，%rowtype是获取一整行的类型。

declare
	-- 1.声明游标
	-- 参数为学生表stuid字段的类型的参数，参数名为s_id
	cursor cur_student(s_id student.stuid%type) is
		select * from student t where t.stuid = s_id;
	-- 2.声明一个学生表行数据的数据类型的变量
	v_student student%rowtype;
	begin
	-- 3.打开游标并传递参数
	open cur_student(1);
	-- 4.从游标提取数据，并存放到v_student变量中
	fetch cur_student into v_student;
	-- 打印变量中的stuid和name
	dbms_output.put_line(v_student.stuid||','||v_student.name);
	-- 5.关闭游标
	close cur_student;
  end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这里演示的都是游标最简单的用法，更多用法还需在实际工作中继续学习摸索。

4、动态游标(ref cursor)

动态游标顾名思义就是能动态的加载需要的数据，相对于静态游标的创建 cursor cur_stu_info is select * from student 在创建游标的时候就已经指定了游标的内容为student表的全体数据，虽然可以通过参数设置条件where stuid=s_id来获取部分数据，但这个也算是在游标创建的时候就已经固定下来数据了。
动态游标类型的创建则不同，type cur_stu_type is ref cursor return student%rowtype 创建动态游标，并指定该动态游标只能存储student表数据，此时并没有固定获取哪部分的数据，可在执行的过程中再动态加载。
更有甚者，连表的数据类型都不指定，只声明一个动态游标，可以存储任何的数据，type ref_cursor is ref cursor 并可以在执行过程中再动态加载。
强类型和弱类型：
上面说的 student%rowtype 即为强类型，就是指定了游标存储的数据类型；
上面说的 ref cursor 即为弱类型，就是不指定游标存储的数据类型，更加的常用。

11.4.1 获取每个有分数的学生的分数，需要先知道学生的stuid，再通过stuid动态找到学生的分数

declare
	-- 创建学生表静态游标
	cursor cur_student is select * from student;
	-- 定义分数表动态游标，也可以定义为type cur_sco_type is ref cursor return score%rowtype;
	type cur_sco_type is ref cursor;
	-- 用上面动态游标的类型定义一个变量cur_sco_info	
	cur_sco_info cur_sco_type;
	
	v_stu_info student%rowtype;		-- 声明存放学生表行信息的变量
	v_sco_info score%rowtype;		-- 声明存放分数表行信息的变量
  begin
  	-- 开启静态游标
	open cur_student;
	-- 开启循环
	loop
		-- fetch into .. 获取学生表游标的数据，并存放在v_stu_info里面
		fetch cur_student into v_stu_info;
		-- 游标没有找到时，数据为空，退出循环
		exit when cur_student%notfound;
		dbms_output.put_line('学生ID：'||v_stu_info.stuid||'，学生姓名：'||v_stu_info.name);
		-- 打开动态游标，因为v_stu_info.stuid的值一直在变，每次循环都不同，因此这里动态获取分数表的数据
		-- 动态游标的打开方式跟静态游标是不一样的
		open cur_sco_info for select * from score where stuid = v_stu_info.stuid;		
		loop 
			-- fetch into .. 获取分数表游标的数据，并存放在v_sco_info里面
			fetch cur_sco_info into v_sco_info;
			exit when cur_sco_info%notfound;
			dbms_output.put_line('     学生科目：'||v_sco_info.subject||'，学生分数：'||v_sco_info.score);
		end loop;		-- 退出动态游标循环
	end loop;			-- 退出静态游标循环循环
	close cur_student;	-- 关闭静态游标
  end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

关于强类型：
（1）字段数量，字段类型，字段顺序均完全同return一样
（2）此时open…for 后面只能用sql语句，而不能是字符串
关于弱类型：
上面两步的写法：
type cur_sco_type is ref cursor;
cur_sco_info cur_sco_type;
其实含义就是定义一个动态游标，然后用动态游标定义一个变量 cur_sco_info ，系统有给了我们更简单的定义方式：
cur_sco_info sys_refcursor;
就是直接定义一个变量cur_sco_info，该变量为动态游标的类型，跟无return类型的两步写法效果是一样的。也建议这样写，比较省步骤。

十一、函数

函数就是按照自己需求把复杂且不轻易变更的功能或业务逻辑封装进PL/SQL函数中，给使用者提供一个返回值，之前介绍的都是系统自带的函数，如length，replace等都是系统自带的函数。 一般Oracle已经提供了足够多的函数供日常使用，我们可以通过编写自己的函数实现跟业务的结合。

1、函数语法

create or replace function 函数名(参数名 in|out 参数类型,..)
	  return 返回值类型
	  is | as 
	  -- 声明部分
	  begin
	  -- PL/SQL功能代码块部分
	  end
1
2
3
4
5
6
7

11.1.1 编写简单函数，给出学生id，获取学生姓名

-- 设置入参pstuid，类型为student.stuid%type
create or replace function getStudentName(pstuid in student.stuid%type)
  return varchar				-- 设置返回值类型为 varchar
  is 
  pname student.name%type;		-- 声明返回值变量pname
  begin
  	-- 查询姓名，并赋值给pname
	select name into pname from student where stuid = pstuid;
	-- 返回pname
	return pname;
  -- 异常处理
  exception 					
	when no_data_found then 
		DBMS_OUTPUT.PUT_LINE('你需要的数据不存在! ');
	when others then  
		DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
  end;
-- 使用如下，可以跟函数一样使用，传入int型，返回varchar型
select getStudentName(1) from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

关于函数，其实还是要根据实际的业务去编写自己的函数，会发现效率提高很多。

十二、存储过程

存储过程（Stored Procedure）就是在大型数据库系统中，一组为了完成特定功能的PL/SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户可以通过存储过程的名字并给出参数的形式来调用。
存储过程的特点：
（1）存储过程一般效率高（创建后只需编译一次），可降低网络流量（服务器上创建存储过程后，本地只需发送存储过程名字即可调用）；
（2）复用性高（存储过程一般都是常用的特定的业务逻辑，变化性小，复用性高），可维护性和安全性也更好（只有特定的用户才能使用）；
（3） 存储过程一般的创建框架，跟函数类似，但是不一定需要返回值，而函数一定要有返回值，所以存储过程的使用比函数更加广泛。

1、存储过程语法

create or replace procedure 存储过程名(参数名 in|out 数据类型) 
	as | is			-- is 和 as的效果是一样的，用哪个就看个人习惯
		声明语句
	begin
		事务处理语句
	exception
		异常处理语句
	end;
1
2
3
4
5
6
7
8

12.1.1 将每个科目分数最高的学生信息插入到score_top表里面
假设是每次考试都要统计单科最高分，可以按照考试时间设置定时任务，或在score设置触发器等多种手段，将该存储过程设置成自动的模式，也可以远程的手动调用，以下演示该案例：

-- 创建score_top空表
create table score_top as
	select a.stuid,a.name,b.subject,b.examinationtime,b.score
	from student a,score b
	where a.stuid = b.stuid and 1=2;
1
2
3
4
5

-- 创建存储过程
create or replace procedure pro_getTopscore		-- 创建不带参数的存储过程
as 
begin 
	merge into score_top t0 using(				-- 有则更新，无则插入
		select a.stuid,a.name,b.subject,b.examinationtime,b.score
		from student a
		join
		(select subject,examinationtime,stuid,score
		from (
			select subject,examinationtime,stuid,score,
			-- 最高分
			max(score) over(partition by subject,trunc(examinationtime)) as maxscore
			from score)
		-- 取最高分的值，并且科目不能为空
		where score = maxscore and subject > '0') b 
		on a.stuid = b.stuid) t1
	on (t0.stuid = t1.stuid)
	when not matched then 
		insert(t0.stuid,t0.name,t0.subject,t0.examinationtime,t0.score) 
		values(t1.stuid,t1.name,t1.subject,t1.examinationtime,t1.score);
	commit;
exception 
	when others then  
	DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 调用存储过程
call pro_getTopscore;
-- 查询数据
select * from score_top;
1
2
3
4

2、record和table of

前面介绍了想要获取表某一行的数据类型时可为：student%rowtype，想要获取某一个字段的类型时可为：student.stuid%type。但如果只想获取表里面一行的的两个字段或者多个字段的类型的时候呢，这里就需要用到自定义的类型。
（1）record 关键字，可以自定义单行多列的数据类型，就是行类型，具体如下：

declare type stuid_name is record(
	v_stuid student.stuid%type,
	v_name student.name%type);
1
2
3

将 stuid 和 name 两个字段组合在一起，形成一个stuid+name的类型。

（2）record 定义的类型只能存储单行多列的数据，此时如果想要多行的数据的话呢，这时用到了table of创建一个多行的集合，也可以理解为表，就是表类型，但是这个表里面的字段都是可以自定义的。

-- 用行类型定义表类型
type tab_stuid_name is table of stuid_name;
-- 注意上面定义的都是类型，象varchar一样，具体使用还需要用类型定义变量才能使用
v_stuid_name stuid_name;
v_stu_table tab_stuid_name;	
1
2
3
4
5

（3）不过一般不像上面那样直接写，因为这样创建的表类型 tab_stuid_name 并不会自动增长的，每往里面插入一个值，都要先手动调用 v_stu_table.extend 去增长空间表的空间，这在平时使用中是不太现实的。集合自增长一般使用如下实现数值或字符串的自增长（字符串可以自定义长度）。

type tab_stuid_name 
is table of stuid_name index of binary_integer | pls_integer | varchar2(10);
1
2

其中 binary_integer 和 pls_integer 都是整型类型，binary_integer 类型的变量值都是由Oracle来执行计算的，当出现数据溢出时，就是数值很大，超出了 binary_integer 的范围，系统会被自动指派一个number型，而不会出现溢出的错误。
pls_integer 类型变量值是由硬件即CPU直接运算的，范围是-2147483648到2147483647，因为会出现溢出的情况，但硬件直接执行的速度导致比 binary_integer 快，因而效率更高。
所以一般正常的写法是直接使用pls_integer，因为效率更高，而且一般情况下很难出现越界溢出的情况，因为范围本身已经很大，如果确实存在溢出风险，再选择使用 binary_integer。

-- 最终的写法如下
-- 先定义行类型
declare type stuid_name is record(
	v_stuid student.stuid%type,
	v_name student.name%type);
-- 再定义自增长的表类型
type tab_stuid_name is table of stuid_name index by pls_integer;
-- 用表类型创建表类型的变量
v_stu_table tab_stuid_name;
1
2
3
4
5
6
7
8
9

3、存储过程进阶

其实SQL的执行都是要交给SQL引擎去执行的，并且都是一条一条的交给SQL引擎执行，当遇到千万级别甚至更多的数据处理时，再一条一条的交给SQL引擎执行，就会花费很多时间在跟SQL引擎的交互上面。我们把这种SQL语句跟SQL引擎的通信称之为上写文的切换，当数据量过大时，上下文切换的时间会明显大于SQL引擎处理数据的时间，会带来过量的性能负载。
这时我们就在想了，能不能不要一条一条交给SQL引擎处理，能不能批量一次性提交多条处理呢。其实是可以的，批量获取数据的关键字为 BULK COLLECT。

12.3.1 从表获取批量数据
语法结构为：

select 字段名 BULK COLLECT into 变量名 from 表名 where 条件;
1

可以有一个或多个字段，存到集合变量里面，集合变量的字段需要跟前面的字段名一一对应。

12.3.2 从游标获取批量数据
语法结构为：

fetch 游标名 BULK CONNECT into 变量 [limit rows]
1

从游标中获取数据存到变量中，limit每次获取多少行，不写的时候，每次默认是1行数据。

12.3.3 returning into
在存储过程、PL/SQL块需要返回insert、delete、update等DML语句后的信息时使用，合理使用能够简化程序逻辑（少一次select into），提高程序的性能。

12.3.3.1 DML语句（insert、update、delete）获取单行记录
其中，insert、update 是提取操作后的数据，delete是提取操作前的数据。

declare 
	pname student.name%type;
 begin
insert into student(stuid,name) values(16,'十六') returning name into pname;	-- 插入后保存name到pname
dbms_output.put_line('插入后的学生姓名：' || pname);
pname := null;		-- 将pname清空
update student set name = '十七' where stuid = 16 returning name into pname;	-- 更新后保存name到pname
dbms_output.put_line('更新后的学生姓名：' || pname);
pname := null;		-- 将pname清空
delete from student where stuid = 16 returning name into pname;				-- 删除前保存name到pname
dbms_output.put_line('删除前的学生姓名：' || pname);
 exception 
when others then 
	dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
 end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

12.3.3.2 动态SQL中获取单行记录
动态SQL是指可以在程序过程编写的时候再确定要取具体的数据retuining into在动态SQL内部和外面都要写上，且外面的returning 后面不加字段直接into。

declare 
	pstuid student.stuid%type;
	pname student.name%type;
	p_sql varchar(200);
begin
	p_sql := 'update student set name = ''十八'' where stuid = :b1 returning stuid,name into :b2,:b3';
execute immediate p_sql using 15 returning into pstuid,pname;
dbms_output.put_line('学生id：' || pstuid ||',学生姓名：'|| pname);
 exception 
when others then 
	dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
end;
1
2
3
4
5
6
7
8
9
10
11
12

上面的 p_sql 中的:b1,:b2,:b3可以简单理解为占位符，因为还不确定参数具体的内容，所以先按照顺序占位，等后续在语句中再将需要的内容补上；
execute immediate 立即执行的意思，立即执行后面的 p_sql 语句，using后面带参数，15，pstuid，pname分别代替上面语句的b1,b2,b3占位的变量，即传入15的参数值，将查询到的值返回赋值给 pstuid 和 pname；

12.3.3.3 使用record+table修改多行数据，并获取多行数据

declare 
	type stuid_name is record(
		v_stuid student.stuid%type,
		v_name student.name%type);
	type tab_stuid_name is table of stuid_name index by binary_integer;
	v_stu_table tab_stuid_name;
  begin
	-- 这里因为是批量处理，需要加上 bulk collect
	update student t set t.sex = '女' where 1=1 returning t.stuid,t.name bulk collect into v_stu_table;
	rollback;		-- 处理完之后，数据已经存到v_stu_table集合里面了，这时防止原来的数据发生更改的话，可以回退，也可以不回退
	for i in v_stu_table.first .. v_stu_table.last loop			-- first last获取该集合的第一行数据，到最后一行数据
		dbms_output.put_line(v_stu_table(i).v_stuid||' , '||v_stu_table(i).v_name);
	end loop;
  exception
	when others then 
		dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
  end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

12.3.3.4 动态SQL修改多行数据，并返回多行数据

  declare
	type stu_stuid_table is table of student.stuid%type;
	type stu_name_table is table of student.name%type;
	v_stuid stu_stuid_table;
	v_name stu_name_table;
	v_sql varchar(2000);
  begin
	v_sql := 'update student t set t.name = ''二十'' where t.stuid <= :b1 returning t.stuid,t.name into :b2,:b3';
	execute immediate v_sql using 15 returning bulk collect into v_stuid,v_name;
	rollback;
	for i in v_stuid.first .. v_stuid.last loop
		dbms_output.put_line('学生学号：'||v_stuid(i));
	end loop;
	for i in v_name.first .. v_name.last loop
		dbms_output.put_line('学生姓名：'||v_name(i));
	end loop;
  exception
	when others then 
		dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
  end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

注意returning不支持返回record类型，上面的returning into后面接的是table集合类型，当这里into后面接的是record定义的类型时，Oracle会报错因为returning不支持返回record类型的数据。

十三、oracle里面的控制语句

13.1.1 赋值语句

select name into pname from student where stuid = 1; 
1

Oracle中是采用select into 的方式给变量赋值的。

13.1.2 判断选择语句
语法：

-- 单 if 判断
if <布尔表达式> then
	pl/sql 和 sql 语句;
end if;
-- if else 判断
if <布尔表达式> then 
	pl/sql 和 sql 语句;
else
	其它语句;
end if;
-- if elsif 判断
if <布尔表达式> then 
	pl/sql和sql语句;
elsif < 其它布尔表达式> then
	其它语句;
elsif < 其它布尔表达式> then
	其它语句;
else
	其它语句;
end if;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

注意 if elsif else 判断时，elsif 不要写错了。

-- 上面代码示例
if sql%found then
	dbms_output.put_line('删除成功！');
end if;
1
2
3
4

13.1.3 循环语句 Loop
语法：

loop
	要执行的语句;
	exit when <条件语句> ;	/*条件满足，退出循环语句*/
end loop;
1
2
3
4

-- 上面示例
loop 
	fetch cur_sco_info into v_sco_info;
	exit when cur_sco_info%notfound;
	dbms_output.put_line('     学生科目：'||v_sco_info.subject||'，学生分数：'||v_sco_info.score);
end loop;
1
2
3
4
5
6

loop 可以使用 if 判断来退出，也可以直接 exit when 来退出。

13.1.4 循环语句 while

-- 当表达式不满足时，退出循环
while 表达式 loop 			
	-- 执行PL/SQL代码块
end loop;
1
2
3
4

13.1.5 循环语句 for
语法：

for 循环计数器 in [ reverse ] 下限 .. 上限 loop
要执行的语句;
end loop;
1
2
3

-- 示例，a在10-20时，循环
for a in 10..20 loop
	-- 执行PL/SQL代码块
end loop;
1
2
3
4

每循环一次，循环变量自动加 1；使用关键字 reverse ，循环变量自动减 1。跟在 in reverse 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。也可以使用 exit 退出循环。

十四、常用的用法和简单的数据恢复

1、一些常用的语句介绍

14.1.1 merge into 的用法

-- 基本语法
merge into 目标表 b
	using 源表 a
	on (b.col_name1 = a.col_name1 and b.col_name2 = a.col_name2..) 		-- 就算只用一个字段匹配也必须带 '()'
	when matched then 													-- 整体扫描，匹配时，执行更新操作
	   update set b.col_name3 = a.col_name3, b.col_name4 = a.col_name5	-- 同时更新多个值时，用逗号隔开
	when not matched then 												-- 整体扫描，不匹配时，执行插入操作
	   insert (b.col_name3,b.col_name2,b.col_name1) values (a.col_name3,a.col_name2,a.col_name1);
1
2
3
4
5
6
7
8

14.1.2 把student_old的信息更新插入到学生表中，存在相同的stuid，就更新姓名和出生年月，不存在相同的stuid，就直接将student_old的数据插入到student中。
简单概括就是存在就更新，不存在就插入。

-- 先插入student_old一条信息
insert into student_old(stuid,name,birthday,sex,cid) 
	values(16,'十六',date'2022-01-01','男',3);
commit;
-- 存在就更新，不存在就插入
merge into student b
using student_old a
	on (b.stuid = a.stuid)
when matched then
  update set b.name = a.name, b.birthday = a.birthday
when not matched then
  insert (b.stuid,b.name,b.birthday,b.sex,b.cid) values (a.stuid,a.name,a.birthday,a.sex,a.cid);
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13

2、简单的数据恢复和回滚

当我们误操作删除了表的数据或者部分数据之后，发现删错了，这时就需要将数据恢复回来，可以采取下面的方式。

-- 1、先查询数据库当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-- 2、通过当前时间往前推时间，选择想要恢复的时间点
select * from 表名 as of 
	timestamp to_timestamp('2022-04-11 12:00:19','yyyy-mm-dd hh24:mi:ss');
-- 3、开启修改分区键
alter table 表名 enable row movement;
-- 4、将数据回滚到某个时间点，假设 12:00:19 时数据是好的，就将数据恢复到这个时间点
flashback table 表名 to 
	timestamp to_timestamp('2022-04-11 12:00:19','yyyy-mm-dd hh24:mi:ss');
1
2
3
4
5
6
7
8
9
10