Oracle
一、SQL
注意： 数据操纵语言(DML)包括insert（插入）命令、update（更新）命令、delete（删除）命令、select … for update（查询）等。只有提交（commit）后才能持久化到数据库。
1. create
2. select
语法：
select *|列名|表达式 from 表名 where 条件 order by 列名;
备份数据
create table 表名 as select 语句;
3. insert into
语法：
insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);
insert插入一个select的结果集：
insert into 表名 select 字句;
4. update
语法：
update 表名 set 列名1=值1,列名2=值2,列名3=值3...where 条件;
update利用另外一张表关联更新本表数据的命令结构：
update 表1 set 列名=(select 列名 from 表2 where 表1.列名 = 表2.列名 )
where exists ( select 1 from 表2 where 表1.列名 = 表2.列名 );
例如：
利用备份表stuinfo_2018更新回学生“张三”的年龄和身份证
update student.stuinfo t
set (age, idnumber) =
(select age, idnumber from student.stuinfo_2018 b where b.stuid = t.stuid)
where exists (select 1
from student.stuinfo_2018 b
where b.stuid = t.stuid
and b.stuname = '张三');
5. delete
语法：
delete from 表名 where 条件; --删除一条数据
或：
truncate table 表名;--清空表
truncate和delete的区别：
-
truncate是DDL命令，命令执行完就提交，删除的数据不能恢复；delete命令是DML命令，命令提交完才能生效，删除后的数据可以通过日志文件恢复。
-
如果表中的数据量比较大的话，truncate的速度比delete的速度快很多。
-
truncate删除将重新设置表索引的初始大小，而delete不能。
-
delete能够触发表上相关的delete触发器，而truncate则不会触发。
-
delete删除原理是一次一条从表中删除数据，并将删除操作当做事物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进行数据页的删除，因此执行速度快，但不能回滚。
注意：truncate慎用
6. Oracle运算符
算数运算符：+、-、*、/
关系运算符：=、>、<、>=、<=、!=/<>
逻辑运算符：and、or、not
7. 字符串连接符||
例：
select '姓名：' || c.stuname || ', 课程：' || b.coursename || ', 成绩：' || a.score || '分。' as sxcj
from score a, course b, stuinfo c
where a.courseid = b.courseid
and a.stuid = c.stuid;
结果：
姓名：张三，课程：数学，成绩：85分。
8. distinct
去重
9. 条件查询
=、in、between…and、
like模糊查询：
- %：表示零个或多个任意字符
- _：代表一个任意字符
- \：转义字符
10. 集合运算
-
intersect(交集)：返回两个查询共有的记录。
-
union all(并集重复)：返回歌个查询的所有记录，包括重复。
-
union(并集不重复)：返回各个查询的所有记录，不包括重复记录(重复只记录一条)
-
minus(补集)：返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
当使用集合运算时，要注意每个独立查询的字段名的列名尽量保持一致（列名不同时，取第一个查询的列名）、列的数据类型、列的个数要一致，不然会报错。
11. 连接查询
inner join、left join、right join、full join
12. 伪列
Oracle的伪列是Oracle表在存储的过程中或查询的过程中，表会有一些附加列，称为伪列。伪列就像表中的字段一样，但表中并不存储（有点像hive中的分区）。伪列只能查询，不能增删改。Oracle的伪列有rowid和rownum。
-
rowid：Oracle表中的每一行在数据文件中都有一个物理地址，rowid返回的就是这行数据的物理地址。使用rowid可以快速定位表中的一行数据。rowid可以唯一的标识表中的一行数据。
-
rownum： 表示的是Oracle查询的结果集的顺序，rownum表示的是结果集每行数据的行号，第一行返回1，第二行返回2，依次顺序递增。（与hive中的row_number有点类似）
总结： rowid是插入数据生成的，而rownum是查询数据生成的，所以有所不同。
13. 函数
Oracle中的dual表是一个单行单列的虚拟表，Oracle有内部逻辑保证dual表中永远只有一条记录。dual表主要用来选择系统变量或求一个表达式的值，常用在没有目标表的select语句中。因为Oracle的select语法限制，没表明就没办法查询，所以才有了dual虚拟表的概念。
1) 单行函数
一行数据一个结果
- 字符型函数
| 函数 | 说明 | 案例 | 结果 |
|---|---|---|---|
| ascii(x) | 求字符x的ASCII码 | select ascii(‘A’) from dual; | 65 |
| chr(x) | 求ASCII码对应的字符 | select chr(65) from dual; | ‘A’ |
| length(x) | 求字符串x的长度 | select length(‘oracle’) from dual; | 6 |
| concata(x, y) | 拼接字符串 | select concata(‘ora’, ‘cle’) from dual; | oracle |
| instr(x, y[, start]) | 查找字符串y在x中的位置，可以指定从哪个位置开始，不指定默认从头开始 | select instr(‘oracle’, ‘a’) from dual; | 3 |
| lower(x) | 字母大写转小写 | select lower(‘Oracle’) from dual; | oracle |
| upper(x) | 字母小写转大写 | select lower(‘Oracle’) from dual; | ORACLE |
| initcap(x) | 将x中单词的首字母改为大写，其余全为小写 | select initcap(‘oracle is good’) from dual; | Oracle Is Good |
| ltrim(x[, y]) | 去掉字符串x左边的y字符串，y不填，默认去掉左边的空格 | select ltrim(’–oracle’, ‘-’) from dual; | oracle |
| rtrim(x[, y]) | 去掉字符串x右边的y字符串，y不填，默认去掉右边的空格 | select ltrim(‘oracle–’, ‘-’) from dual; | oracle |
| trim(x[, y]) | 去掉字符串x左右两边的y字符串，y不填，默认去掉左右两边的空格 | select ltrim(’–oracle–’, ‘-’) from dual; | oracle |
| replace(x, old, new) | 用new字符替换x中的old字符 | select replace(‘oracld’, ‘d’, ‘e’) from dual; | oracle |
| substr(x, start[, length]) | 截取字符串从start位置开始（0/1都是从第一位开始），截取长度为length，不填默认到末尾。 start：> 0时从前往后截取，< 0时，从后往前截取 | select substr(‘oracle’, 2, 2) from dual; | ra |
| lpad(x, length[, y]) | 对字符串x进行左补齐，补齐到长度为length，有y时用字符串y补齐 | select lpad(‘oracle’, 8, ‘-’) from dual; | –oracle |
| rpad(x, length[, y]) | 对字符串x进行右补齐，补齐到长度为length，有y时用字符串y补齐 | select rpad(‘oracle’, 8, ‘-’) from dual; | oracle |
- 日期型函数
| 函数 | 说明 | 案例 | 结果 |
|---|---|---|---|
- 数值型函数
Oracle数值型函数可以是输入一个数值，并返回一个数值的函数
| 函数 | 说明 | 案例 | 结果 |
|---|---|---|---|
| abs(x) | 求x的绝对值 | select abs(-9) from dual; | 9 |
| cos(x) | 求数值x的余弦 | select cos(1) from dual; | 0.540302 |
| acos(x) | 求数值x的反余弦 | select acos(1) from dual; | 0 |
| ceil(x) | 求大于或等于数值x的最小值 | select ceil(7.8) from dual; | 8 |
| floor(x) | 求小于或等于数值X的最大值 | select floor(7.8) from dual; | 7 |
| log(x,y) | 求x为底y的对数 | select log(2,8) from dual; | 3 |
| mod(x,y) | 求x除以y的余数 | select mod(13,4) from dual; | 1 |
| power(x,y) | 求x的y次幂 | select power(2,4) from dual; | 16 |
| sqrt(x) | 求x的平方根 | select sqrt(16) from dual; | 4 |
| round(x[,y]) | 求数值x在y位进行四舍五入。y不填时，默认为y=0;当y>0时，是四舍五入到小数点右边y位。当y<0时，是四舍五入到小数点左边|y|位。 | select round(7.816, 2), round(7.816), round(76.816, -1) from dual; | 7.82 / 8 / 80 |
| trunc(x[,y]) | 求数值x在y位进行直接截取y不填时，默认为y=0;当y>0时，是截取到小数点右边y位。当y<0时，是截取到小数点左边|y|位。 | select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual; | 7.81 / 7 / 70 |
-
转换函数
Oracle转换函数是进行不同数据类型转换的函数，常用的函数有to_char()、to_number()、to_date()等。
| 函数 | 说明 | 案例 | 结果 |
|---|---|---|---|
| asciistr(x) | 把字符串x转换为数据库字符集对应的ASCII值 | select asciistr(‘Oracle技术’) from dual; | Oracle\6280\672F |
| bin_to_num(x1[, x2…]) | 把二进制数值转换为对应的十进制数值 | select bin_to_num(1,0,0) from dual; | 4 |
| cast(x as type) | 数据类型转换函数，该函数可以把x转换为对应的type的数据类型，基本上用于数字，字符，时间类型安装数据库规则进行互转 | select cast(‘123’ as number) num, cast(123 as varchar2(3)) as ch, cast(to_date(‘20181112’,‘yyyymmdd’) as varchar2(12)) as time from dual; | 123 ’123’ 12-11月-18 |
| convert(x,d_chset[, r_chset]) | 字符串在字符集间的转换函数，对字符串x按照原字符集r_chset转换为目标字符集d_chset，当r_chset不填时，默认选择数据库服务器字符集。 | select CONVERT(‘oracle技术圈’,‘US7ASCII’,‘ZHS16GBK’) from dual; | oracle??? |
| to_char(x[, f]) | 把字符串或时间类型x按格式f进行格式化转换为字符串。 | select to_char(123.46,‘999.9’) from dual; select to_char(sysdate,‘yyyy-mm-dd’) from dual; | 123.5 2018-11-13 |
| to_date(x[, f]) | 可以把字符串x按照格式f进行格式化转换为时间类型结果。 | select to_date(‘2018-11-13’,‘yyyy-mm-dd’) from dual; | 2018/11/13 |
| to_number(x[, f]) | 可以把字符串x按照格式f进行格式化转换为数值类型结果。 | select to_number(‘123.74’,‘999.99’) from dual | 123.74 |
2)聚合函数
多行数据一个结果
max()，min()，avg()，sum()，count()
14. Oracle子查询
Oracle的子查询就是嵌套查询，它把select查询的结果作为另一个select、updat或delete语句的条件，本质就是一个where条件查询中的一个条件表达式(也可作为一个表，即from后面使用子查询，把子查询当做一个新表)。可分单行子查询和多行子查询。
- 单行子查询
向外部返回的结果为空或者返回一行。Oracle单行子查询是利用where条件中的"="关联查询的结果，如果子查询返回多行会报错。
例：查询工资比名字为ADAMS高的员工的名字和工资：
select ename, sal
from emp where sal > (select sal from emp where ename = 'ADAMS');
/*虽然有时候利用内关联也可以查出结果，而且效率更好，但是在一些没有关联关系的时候可以利用子查询*/
- 多行子查询
向外部返回的结果为空、一行或者多行。
Oracle多行子查询则需要利用in关键字来接收子查询的多行结果。也可以用量化关键字any、all和关系运算符>、>=、=、<、<=来组合使用。
in关键字： 表示等于子查询里面的所有结果
--查询班级表中所有班级的学生信息：
select * from stuinfo t where t.classno in (select b.classno from class b);
any关键字： 表示子查询结果当中的任意一个。假如：>any(子查询)，表示：只要大于子查询当中的任意一个值，这个条件就满足，即表示大于其中最小值。
--年龄只要大于当中子查询的最小值26岁即可
select * from stuinfo t where t.age > any(26,27,28);
all关键字： 表示子查询中的所有结果。假如：>all(子查询)，表示：必须大于子查询当中的所有结果才能满足这个条件，即表示大于其中的最大值。
--年龄必须大于子查询当中的最大值28岁才可以
select * from stuinfo t where t.age > all(26,27,28);
15. Oracle synonym 同义词
Oracle synonym 同义词是数据库当前用户通过给另外一个用户的对象创建一个别名，然后可以通过对别名进行查询和操作，等价于直接操作该数据库对象。Oracle同义词常常是给表、视图、函数、过程、包等制定别名，可以通过CREATE 命令进行创建、ALTER 命令进行修改、DROP 命令执行删除操作。
Oracle synonym 同义词按照访问权限分为私有同义词、公有同义词。
私有同义词：私有同义词只能当前用户可以访问，前提：当前用户具有create synonym 权限。
公有同义词：公有同义词只能具有DBA用户才能进行创建，所有用户都可以访问的。
同义词创建
语法结构：
CREATE [OR REPLACE] [PUBLIC] SYSNONYM [当前用户.]synonym_name
FOR [其他用户.]object_name;
解析：
-
create [or replace] 命令create建表命令一样，当当前用户下同义词对象名已经存在的时候，就会删除原来的同义词，用新的同义词替代上。
-
[public]：创建的是公有同义词，在实际开发过程中比较少用，因为创建就代表着任何用户都可以通过自己用户访问操作该对象，一般我们访问其他用户对象时，需要该用户进行授权给我们。
-
用户名.object_name：oralce用户对象的权限都是自己用户进行管理的，需要其他用户的某个对象的操作权限，只能通过对象拥有者（用户）进行授权给当前用户。或者当前用户具有系统管理员权限（DBA），即可通过用户名.object_name操作该对象。
同义词删除
同义词删除只能通过同义词拥有者的用户或者具有DBA权限的用户才能删除。
语法结构：
DROP [PUBLIC] SYNONYM [用户.]sysnonym_name;
16. Oracle序列
Oracle序列Sequence是用来生成连续的整数数据的对象，它经常用来作为业务中无规则的主键。Oracle序列可以是升序列也可以是降序列。
创建Oracle序列的语法结构如下：
CREATE SEQUENCE sequence_name
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[START WITH num]
[INCREMENT BY increment]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
语法解析：
- 1、maxvalue/minvalue：指定的是序列的最大值和最小值。
- 2、nomaxvalue/nominvalue：不指定序列的最大值和最小值，使用系统的默认选项，升序的最大值：1027次方，降序是-1。升序最小值：1，降序的最小值：-1026。
- 3、start with：指定从某一个整数开始，升序默认是1，降序默认是-1。
- 4、cycle | nocycle:表示序列达到最大值或者最小值的时候，是否重新开始。cycle：重新开始，nocycle：不重新开始。
- 5、cache：使用 cache选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率 。
- 6、nocache：不预先在内存中生成序列号。
17. Oracle视图
oracle视图可以理解为数据库中一张虚拟的表，他是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。查询视图，本质上是对表进行关联查询。
视图的本身是不包含任何数据，只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。我们经常在实际开发过程中遇到的视图可以大概分为三种：单表视图、多表关联视图、视图中含有子视图。
视图的作用和优势：
既然视图在实际开发过程当中被广泛使用到，它到底有哪些作用和优势呢？
-
使数据简单化： 可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系。这样对视图的使用人员来说，就简化了数据的，屏蔽了数据的复杂性。
-
表结构设计的补充： 系统刚刚开始设计时，大部分程序是直接访问表结构的数据的，但是随着业务的变化、系统的更新等，造成了某些表结构的不适用，这时候去修改表结构对系统的影响太大，开发成本较高，这个时候可以创建视图来对表结构的设计进行补充，降低开发成本。程序可以直接通过查询视图得到想要的数据。
-
增加安全性： 视图可以把表中指定的字段展示给用户，而不必把表中所有字段一起展示给用户。在实际开发中，视图经常作为数据的提供方式，设置为只读权限提供给第三方人员进行查询使用。
创建视图：
语法：
CREATE [OR REPLACE] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]
解释：
1、or replace：如果视图已经存在，则替换旧视图。
2、with read only：默认不填的，用户是可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作，或者基表设置了某些约束，这时候插入视图或者修改视图的值，有可能会报错）， with read only说明视图是只读视图，不能通过该视图进行增删改操作。但是在现实开发中，基本上不通过视图对表中的数据进行增删改操作。
例子：利用学生信息表（stuinfo）、班级表（class）关联创建视图，只提供一些学生基本信息和班级信息（剔除学生一些敏感信息：如身份证，家庭地址等）。
create view vw_stuinfo as
select a.stuid,--学号
a.stuname,--学生姓名
a.grade,--年级
a.sex,--性别（1:男、2:女）
a.age,--年龄
b.classname,--班级
b.monitorname,--班长
b.headmastername--班主任
from stuinfo a, class b
where a.classno = b.classno;
select * from vw_stuinfo;
18. Oracle索引
Oracle索引（index）最大的作用是用来优化数据库查询的效率，提升数据库的查询性能。就好比书的目录一样，可以通过目录来直接定位所需内容存在的页数，大大提高检索效率。
Oracle数据库中如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据进行排序或归类，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle 会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于 ROWID 是记录的物理地址，因此可以根据 ROWID 快速的定位到具体的记录，当表中的数据非常多时，引用索引带来的查询效率非常可观 。
何时建立索引：
既然我们都知道建立索引有利于查询速率的提升，那是不是所有字段都可以加上索引。这是万万不行的，建立索引不仅仅要浪费空间来存储索引表，当数据量较少时，直接查询数据比经过查询索引表再定位到表数据的速度更快。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。所以要根据实际情况， 考虑好再建立索引。
那何时建立索引，下面大概介绍几点，其余的得在实际应用和开发过程中，酌情考虑：
- 1、Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引，所以在建立唯一约束时，可以考虑该列是否必要建立。是否经常要作为查询条件。
- 2、如果某个表的数据量较大（十几二十万以上），某列经常作为where的查询条件，并且检索的出来的行数经常是小于总表的5%，那该列可以考虑建立索引。
- 3、对于两表连接的字段，应该考虑建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。
- 4、不应该在小表上建立索引。上面也说过，小表之间查询的数据会比建立索引的查询速度更快，但是在某些字段，如性别：只有男、女和未知三种数据时，可以考虑位图索引，可以增加查询效率。
- 5、经常进行DML操作，即经常进行增删改的操作的表，创建表索引时就要权衡一下，因为建索引会导致进行DML操作时速度变慢。所以可以根据实际情况，选择某些字段建立索引，而不能盲目乱建。
索引的类别：
适当的使用索引可以提高数据检索速度，那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、分区索引和分区表的全局索引： 这两个索引是应用在分区表上面的，前者的分区索引是对分区表内的单个分区进行数据索引，后者是对分区表的全表进行全局索引。分区表的介绍，可以后期再做单独详解，这里就不累述了。
索引的创建：
语法结构：
create[unique]|[bitmap] index index_name --UNIQUE表示唯一索引、BITMAP位图索引
on table_name(column1,column2...|[express])--express表示函数索引
[tablespace tab_name] --tablespace表示索引存储的表空间
[pctfree n1] --索引块的空闲空间n1
[storage --存储块的空间
(
initial 64K --初始64k
next 1M
minextents 1
maxextents unlimited
)];
语法解析：
1、UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引，BITMAP表示位图索引。
2、index_name：指定索引名。
3、tabl_name：指定要为哪个表创建索引。
4、column_name：指定要对哪个列创建索引。我们也可以对多列创建索引，这种索引称为组合索引。也可以是函数表达式，这种就是函数索引。
修改索引：
1、重命名索引：
alter index index_old rename to index_new;--重新命名索引
2、合并索引、重新构造索引：我们索引建好后，经过很长一段时间的使用，索引表中存储的空间会产生一些碎片，导致索引的查询效率会有所下降，这个时候可以合并索引，原理是按照索引规则重新分类存储一下，或者也可以选择删除索引重新构造索引。
alter index index_name coalesce;--合并索引
alter index index_name rebuild;--重新构造
删除索引：
drop index index_name;
查看索引：
select t.INDEX_NAME,--索引名字
t.index_type,--索引类型
t.TABLESPACE_NAME,--表空间
t.status,--状态
t.UNIQUENESS--是否唯一索引
from all_indexes T
where t.INDEX_NAME='index_name';
案例分析：
案例1、 学生信息表（stuinfo）创建的时候就对学号（stuid）设置了主键（PK_STUINFO），当我们学生信息表数据量大的情况下，我们明显发现班号（classno）需要一个索引，不仅仅是用来关联班级信息表（class）、而且经常作为查询条件，因此创建脚本如下：
create index STUDENT.IDX_STUINFO_CLASSNO on STUDENT.STUINFO (CLASSNO)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
案例2、 对于学生信息我们经常用性别作为统计条件进行对学生信息进行统计，因此我们可以在性别（sex）建立一个位图索引进行查询优化。代码如下：
create bitmap index STUDENT.IDX_STUINFO_SEX on STUDENT.STUINFO (SEX)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
查询一下三种索引的状态：
select t.INDEX_NAME,
t.index_type,
t.TABLESPACE_NAME,
t.status,
t.UNIQUENESS
from all_indexes T
where t.TABLE_NAME='STUINFO'
AND T.OWNER='STUDENT';
19. Oracle分区详解和创建
Oracle在实际业务生产环境中，经常会遇到随着业务量的逐渐增加，表中的数据行数的增多，Oracle对表的管理和性能的影响也随之增大。对表中数据的查询、表的备份的时间将大大提高，以及遇到特定情况下，要对表中数据进行恢复，也随之数据量的增大而花费更多的时间。这个时候，Oracle数据库提供了分区这个机制，通过把一个表中的行进行划分，归为几部分。可以减少大数据量表的管理和性能问题。利用这种分区方式把表数据进行划分的机制称为表分区，各个分区称为分区表。
Oracle分区对于大型表（大数据量）非常有用，分区的作用主要有：
-
1、改善大型表的查询性能，因为可以通过查询对应分区表中对应的数据，而不需要查询整个表。
-
2、表更容易管理，因为分区表的数据存储在各个分区中，所以可以按照分区建，来管理对应分区当中的数据，可以按照分区加载和删除其中的数据，比在不分区情况下，更容易管理数据。以及在特定的事故情况下，通过备份好的分区，可以快速恢复对应分区当中的数据，也不需要对全表数据进行恢复。Oracle在实际业务生产环境中，经常会遇到随着业务量的逐渐增加，表中的数据行数的增多，Oracle对表的管理和性能的影响也随之增大。对表中数据的查询、表的备份的时间将大大提高，以及遇到特定情况下，要对表中数据进行恢复，也随之数据量的增大而花费更多的时间。这个时候，Oracle数据库提供了分区这个机制，通过把一个表中的行进行划分，归为几部分。可以减少大数据量表的管理和性能问题。利用这种分区方式把表数据进行划分的机制称为表分区，各个分区称为分区表。
Oracle创建分区
既然，Oracle分区有如此好处，我们在这里通过一个例子来讲解如何创建分区。在我们的学生信息系统案例当中，学生成绩表（SCORE）会随着学生的增多和课程的增多，表中的数据量会越来越大，所以可以考虑创建分区表来解决这个问题。
Oracle分区也是通过create table命令组成，但是对表进行分区时，得考虑一个字段作为分区建，通常按值的范围来划分分区，所以这里考虑使用成绩的录入时间进行分区。具体代码如下：
-- Create table
create table STUDENT.SCORE
(
scoreid VARCHAR2(18) not null,
stuid VARCHAR2(11),
courseid VARCHAR2(9),
score NUMBER,
scdate DATE
)
partition by range(scdate)(
partition p_score_2018 values less than (TO_DATE('2019-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
TABLESPACE TS_2018,
partition p_score_2019 values less than (TO_DATE('2020-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
TABLESPACE TS_2019,
partition p_score_2020 values less than (MAXVALUE)
TABLESPACE TS_2020
);
-- Add comments to the table
comment on table STUDENT.SCORE
is '学生成绩表';
-- Add comments to the columns
comment on column STUDENT.SCORE.scoreid
is '学生成绩id';
comment on column STUDENT.SCORE.stuid
is '学生学号';
comment on column STUDENT.SCORE.courseid
is '课程id(年度+上下学期+课程序列)';
comment on column STUDENT.SCORE.score
is '成绩';
comment on column STUDENT.SCORE.scdate
is '成绩录入时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table STUDENT.SCORE
add constraint PK_SCORE primary key (SCOREID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
这里使用命令partition by range对成绩的录入日期（scdate）进行分区，如录入日期小于2019年的会被放入分区p_score_2018当中，2019年数据会被放入p_score_2019这个分区当中，大于2019年数据都会被放入到p_score_2020这个分区当中。
这里不必为最后一个分区指定最大值，maxvalue关键字会告诉Oracle使用这个分区来存储前面几个分区当中不能存储的数据。
上面实例展示的是Oracle按照值的范围进行分区，Oracle还支出散列分区，通过某一个字段，把表中的数据散列在各个分区中。可以通过关键字partition by hash，可以把分区散列到不同的表空间当中。
Oracle还支持列表分区（partition by list），它是通过按照指定分区建的值归并到各个分区，其实这里学生成绩表也可以考虑按照课程进行列表分区。
总结：Oracle分区对大型表（数据量大）有重大的性能提升，所以在表结构设计时，需要提前按照相关业务需求进行相应的改进。
20. Oracle如何在分区表上创建索引
通过分区的详解，我们知道了Oracle分区的优势以及如何进行创建。在实际生产环境中，为了进一步的优化大型表（大数据量集）的查询效率，这个时候得考虑在分区表上某个字段创建索引。分区表的索引和普通表的索引本质上是一样的，都是利用空间换取时间的方式，通过存储索引块来增加查询效率。但是有不同地方是，Oracle分区表索引可以分为局部（分区）索引和全局索引之分。
分区索引
所为的分区索引指的是在子分区当中按照某个字段建立索引，例如，上一章创建的学生成绩表中（score）,可以对学生学号创建local索引，即分区索引。代码如下：
create index idx_score_stuid on student.score(stuid)
local
(
partition idx_score_stuid_1 tablespace TS_2018,
partition idx_score_stuid_2 tablespace TS_2019,
partition idx_score_stuid_3 tablespace TS_2020
);
请注意local关键字。在这个create index命令中没有指定范围，而是由local 关键字告诉Oracle为score表的每一个分区创建一个单独的索引，因此，每一个表分区对应着一个索引分区。每一个索引分区存储在不同的表空间上，可以大大提高I/O和查询效率。
通过查询数据字典dba_ind_partitions可以查看刚刚创建的分区索引：
select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = upper('idx_score_stuid');
全局索引
Oracle分区表也可以创建全局索引，全局索引和普通表的索引一样，是对整表的数据进行创建索引。例如，可以对学生成绩表的（score）的课程ID（COURSEID）创建全局索引，具体代码如下：
create index STUDENT.IDX_SCORE_COURSEID
on STUDENT.SCORE (courseid)
global;
这里，虽然分区索引比全局索引更容易管理，而且在分区当中查询效率更高，但是全局索引在全表进行唯一性检索时的速度可能会比局部索引更快，因为全局检索唯一性时，需要跨区。
注意： 不能为子分区创建全局索引。
21. Oracle merge into命令
Oracle merge into命令，顾名思义就是“有则更新，无则插入”，这个也是merge into 命令的核心思想，在实际开发过程中，我们会经常遇到这种通过两表互相关联匹配更新其中一个表的某些字段的业务，有时还要处理不匹配的情况下的业务。这个时候你会发现随着表的数据量增加，类似这种业务场景的执行效率会比较慢，那是因为你需要多次重复查询两表中的数据，而通过merge into命令，只需要一次关联即可完成“有则更新，无则插入”的业务场景，大大提高语句的执行效率。
merge into命令的语法结构如下：
merge into A
using B
on (A.id = B.id)
when matched then
update set A.col=B.col
when not matched then
insert 语句;
语法解析： 利用B表通过A.id=B.id的条件来匹配A表，当满足条件时，可以对A表进行更新，当不满足条件时，可以利用inert语句插入相关数据。
例子： 利用merge into命令从学生表（stuinfo）更新学生备份表（stuinfo_2018）的年龄，当备份表中找不到学生信息时插入新的学生信息，先看下两张表的数据：
merge into student.stuinfo_2018 A
using student.stuinfo B
on (A.stuid = B.stuid)
when matched then
update set A.age=B.age
when not matched then
insert (a.STUID,a.STUNAME,a.SEX,a.AGE,a.CLASSNO,a.STUADDRESS,a.GRADE,a.ENROLDATE,a.IDNUMBER
) values(b.STUID,b.STUNAME,b.SEX,b.AGE,b.CLASSNO,b.STUADDRESS,b.GRADE,b.ENROLDATE,b.IDNUMBER);
22. oracle物化视图
Oracle物化视图可以理解为用来存储数据表的副本或者聚集，物化视图可以用来复制一个表的全部数据或者部分数据，也可以复制多表关联查询的结果集。然后按照指定的时间间隔内自动更新副本数据的刷新。本文将介绍物化视图常用的场景和创建方法，以及刷新策略。
物化视图是基于select查询结果的数据副本，简单来讲就是数据的复制表，但是它可以按照特定的刷新策略刷新复制表中的数据。常用来：
-
1、物化视图经常用来当做远程数据表的本地化，可以用来做数据同步用。
-
2、也经常用来做本地单表或多表数据的汇总，这样子，可以定时把汇总数据更新到复制表，用来做决策分析用。
-
3、也可以用来做复杂视图的物化作用，把这种数据不经常实时更新的视图，物化成物理表，然后在物化表上建相应的索引，大大提高查询效率。
物化视图在创建时，可以指定刷新（refresh interval）的间隔来执行定时刷新物化表的数据。也可以利用基于事务的刷新，通过主表变化的行数实时来更新物化表。
23. Oracle分析函数_开窗函数详解
Oracle分析函数是Oracle系统自带函数中的一种，是Oracle专门用来解决具有复杂统计需求的函数，它可以对数据进行分组然后基于组中数据进行分析统计，最后在每组数据集中的每一行中返回这个统计值。
Oracle分析函数不同于分组统计（group by），group by只能按照分组字段返回一个固定的统计值，但是不能在原来的数据行上带上这个统计值，而Oracle分析函数正是Oracle专门解决这类统计需求所开发出来的函数。
Oracle分析函数都会带上一个开窗函数over()，所以常把两者结合一起讲解。
Oracle分析函数的语法结构：
select table.column,
Analysis_function() OVER([partition by 字段] [order by 字段 [windos]]) as 统计值
from table;
语法解析：
-
1、Analysis_function：指定分析函数名，常用的分析函数有sum、max、first_value、last_value、rank、row_number等等。
- first_value()：返回组中数据窗口的第一个值。
- last_value()：返回组中数据窗口的最后一个值。
- max()：返回组中的最大值
- min()：返回组中的最小值
- row_number()：从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
- RANK() ：生成数据项在分组中的排名，排名相等会在名次中留下空位
- DENSE_RANK()： 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
-
2、over()：开窗函数名，partition by指定进行数据分组的字段，order by指定进行排序的字段，windos指定数据窗口（即指定分析函数要操作的行数），使用的语法形式大概如下：
over(partition by xxx order by yyy rows between zzz)
window子句：
-
preceding：往前
-
following：往后
-
current row：当前行
-
unbounded：起点
-
unbounded preceding：表示从前面的起点开始
-
unbounded following：表示到后面的终点结束
例子：
select name, orderdate, cost,
sum(cost) over() s1, --无分组，求cost总和
sum(cost) over(partition by name) s2, --按name分组，求各个人cost总和
sum(cost) over(partition by name order by orderdate) s3, --按name分组，求各个cost总和，不过是order by是对起始cost到当前行的累加
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) s4, --起始行到当前行的累加，同s3
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) s5, --前一行和当前行的累加
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) s6, --前一行，当前行和后一行的累加
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) s7 --当前行到最后一行的累加
from business;
24. Oracle行转列（PIVOT）
Oracle行转列就是把某一个字段的值作为唯一值，然后另外一个字段的行值转换成它的列值。这里依然利用我们系统的学生成绩表作为例子，成绩表记录（行）当中对应着每一个科目的每一个学生的成绩，那我们要做出一个报表是每个学生的所有科目作为一列展示出学生的成绩信息。案例数据如下：
- 1、 首先我们肯定想到的是利用Oracle分组（group by）应该可以实现，实现代码如下：
先对decode()函数介绍：
1) decode(条件, 值1, 返回值1, 值2, 返回值2, …值n, 返回值n, 缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
2) decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3；当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多。
使用方法：
--比较大小
select decode(sign(变量1 - 变量2), -1, 变量1, 变量2) from dual; --取较小值
/*
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
*/
行转列实现：
select c.stuname,
--利用分组聚合函数
sum(decode(b.coursename, '英语(2018上学期)', t.score, 0)) as "英语(2018上学期)",
sum(decode(b.coursename, '数学(2018上学期)', t.score, 0)) as "数学(2018上学期)",
sum(decode(b.coursename, '语文(2018上学期)', t.score, 0)) as "语文(2018上学期)"
from STUDENT.SCORE t, student.course b, student.stuinfo c
where t.courseid = b.courseid
and t.stuid = c.stuid
group by c.stuname
我们利用group by对学生进行分组，然后利用decode对对应的课程的成绩值进行转换，然后再求和即可得到该门成绩的结果值
- 2、Oracle11g之后提供了自带函数PIVOT可以完美解决这个行转列的需求，具体语法结构如下：
SELECT * FROM （数据查询集）
PIVOT
(
SUM(Score/*行转列后 列的值*/) FOR
coursename/*需要行转列的列*/ IN (转换后列的值)
)
具体代码如下：
select * from (select c.stuname,
b.coursename,
t.score
from STUDENT.SCORE t, student.course b, student.stuinfo c
where t.courseid = b.courseid
and t.stuid = c.stuid ) /*数据源*/
PIVOT
(
SUM(score/*行转列后 列的值*/)
FOR coursename/*需要行转列的列*/ IN ('英语(2018上学期)' as 英语,'数学(2018上学期)' as 数学,'语文(2018上学期)' as 语文 )
) ;
- 3、case when
上例还可改为：
select c.stuname,
max(case when b.coursename = '英语(2018上学期)' then t.score else 0 end) as '英语',
max(case when b.coursename = '数学(2018上学期)' then t.score else 0 end) as '数学',
max(case when b.coursename = '语文(2018上学期)' then t.score else 0 end) as '语文'
from STUDENT.SCORE t, student.course b, student.stuinfo c
where t.courseid = b.courseid
and t.stuid = c.stuid
group by c.stuname
25. Oracle列转行unpivot
- 1、利用union all 进行拼接，可以完美的把对应的列转为行记录，具体代码如下：
select t.stuname, '英语' as coursename ,t.英语 as score from SCORE_COPY t
union all
select t.stuname, '数学' as coursename ,t.数学 as score from SCORE_COPY t
union all
select t.stuname, '语文' as coursename ,t.语文 as score from SCORE_COPY t;
- 2、利用Oracle自带的列转行函数unpivot也可以完美解决该问题，具体语法结构如下：
select 字段 from 数据集
unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））
实现代码如下：
select stuname, coursename ,score from
score_copy t
unpivot
(score for coursename in (英语,数学,语文));
26. Oracle创建物化视图
创建物化视图语法：
create materialized view view_name
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
AS select 查询语句;
语法解析：
-
1、create materialized view是创建物化视图的命令关键字。view_name指的是要创建物化视图的名字。
-
2、refresh 是指定物化视图数据刷新的模式：
force： 这个是默认的刷新方式，Oracle会自动判断该如何进行数据刷新，如果满足快速刷新的条件，就进行fast刷新，不满足就进行全量刷新。
complete： 这个是全量刷新数据，当要刷新数据时，会删除物化表中的所有数据，然后物化视图中的查询语句重新生成数据。
fast： 采用增量的方式进行数据的刷新。通过主表的视图日志和上次刷新数据进行比对，然后增量更新物化后的数据表。
-
3、on[comit|demand]，指的是选择物化视图数据刷新的模式。
on commit：指的是基表的数据有提交的时候触发刷新物化视图，使得物化视图中的数据和基本的数据是一致性的。
on demand：指定是物化视图数据刷新的另外一种方式，仅在需要更新数据时才刷新物化视图中的数据。一般配合 start with 参数按照特定的时间计划，按计划更新数据，比如每天晚上12点或每周更新一次物化视图中的数据。
实例：
创建物化视图，创建脚本如下：
--创建物化视图需要权限
grant create materialized view to student;
create materialized view student.mv_stuinfo
refresh force
on demand
start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'01:00:00'),'dd-mm-yyyy hh24:mi:ss')
as
select t1.stuid, t1.stuname, t1.sex, t1.age, t2.classno, t2.classname
from student.stuinfo t1, student.class t2
where t1.classno = t2.classno;
创建好后，直接查询物化视图，可以发现已经有数据了，这时你可以随意在物化视图上加索引，进一步提高查询效率。
select * from mv_stuinfo;
二、PL/SQL
Oracle PL/SQL 语言（Procedural Language/SQL）是结合了结构化查询与 Oracle 自身过程控制为一体的强大语言， PL/SQL 不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与 SQL 数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包等。
PL/SQL语言的应用基本贯彻了Oracle学习和开发的全过程，它是作为一个Oracle开发程序员必备的知识。只有认真掌握、理解和应用才能算真正入门。
1. PL/SQL简介
它是Oracle公司在标准SQL语言的基础上进行扩展，可以在数据库上进行设计编程的一种过程化的语言，类似程序语言JAVA一样可以实现逻辑判断、条件循环、异常处理等细节操作，可以处理复杂性问题。
上面学习过SQL语句是用来访问和操作关系型数据库的一种通用语言，不仅仅是在Oracle数据库可以使用，在其它关系型数据库也适用。但是这个SQL语言有一个弊端就是只能查询既得结果，不能做过程化的开发，因此，有时候SQL语言满足不了程序上复杂化的开发。所以，Oracle中的PL/SQL语言正是为了解决这一问题。
PL/SQL语言属于第三代语言（3GL），它是Oracle公司在标准SQL语言的基础上进行扩展，可以在数据库上进行设计编程的一种过程化的语言，类似程序语言JAVA一样可以实现逻辑判断、条件循环、异常处理等细节操作。因此，它和SQL语言有很多的关联性，又可以实现SQL语句一些很难办到的复杂性问题。总体来说它具有以下特点：
-
1、支持SQL语句命令和操作。
-
2、支持SQL中的数据类型，还扩展了一些自己特有的类型，如：type类型。
-
3、支持SQL中的内置函数和运算符。
-
4、PL/SQL支持事物，对写好的PL/SQL程序（过程、函数、包）可以进行权限的控制。
-
5、PL/SQL编写的数据库脚本是保留在Oracle服务器中的。
PL/SQL的优势
PL/SQL 是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL 块发送给服务器后，先被编译然后执行， PL/SQL引擎分析收到 PL/SQL 语句块中的内容， 把其中的过程控制语句由 PL/SQL 引擎自身去执行，把 PL/SQL 块中的 SQL 语句交给服务器的 SQL 语句执行器执行。如图所示：
虽然PL/SQL语言是从SQL语言发展而来的，但是相对于SQL语言，它还是具有以下几个明显的优势：
-
1、支持面向对象的编程： PL/SQL 支持面向对象的编程，在 PL/SQL 中可以创建类型，可以对类型进行声明、实例化、继承和带入到子程序中进行调用重载。
-
2、获得更好的程序性能： PL/SQL 把一个 PL/SQL 语句块统一进行编译后执行，同时还可以把编译好的 PL/SQL 块存储在Oracle服务器上，可以重复调用使用。而SQL 是非过程语言，只能一条一条执行，假如一个业务需要十几条SQL语句来完成，那就需要访问十几的数据库，大大提高了网络资源和访问数据库的时间。而PL/SQL减少了应用程序和服务器之间的通信时间， PL/SQL 是快速而高效的。
-
3、使程序模块化： 在PL/SQL程序块的编写过程中，可以把某一个业务模块的相关业务抽取成一个程序块，这样子只需要提供相关的出入参即可，PL/SQL程序开发者只需要开发好业务模块，而作为使用者则不需要关心内在的SQL语句和操作的相关表。这样子，使程序更具有模块化。
-
4、良好的移植性： 使用 PL/SQL 编写的应用程序，可以移植到任何操作系统平台上的 Oracle 服务器，同时还可以编写可移植程序库，在不同环境中重用。
-
5、安全性： 可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle 数据库的访问，数据库还可以授权和撤销其他用户访问的能力。
-
6、丰富的逻辑关系、顺序关系、错误信息的处理： PL/SQL语言是过程化的语言，可以来处理SQL语句处理不了的一些逻辑关系、顺序关系。还提供了丰富的错误信息提示，也可以自定义错误信息的抛出，大大丰富了程序的可用性和友好性。
2. PL/SQL块
PL/SQL块是PL/SQL程序的最基本的单位，它由声明部分、执行部分和异常处理三部分组成。其中声明部分由declare开始，执行部分由begin开始，异常处理部分由exception开始。其中执行部分是必须，即begin…end之间必须要有执行部分。其他两部分可以根据实际情况选择。
PL/SQL块语法结构：
[DECLARE]
声明语句...
BEGIN
执行语句...
[EXCEPTION]
异常处理语句...
END;
/
在PLSQL程序中：；号表示每条语句的结束，/表示整个PLSQL程序结束
语法解析：
-
1、声明部分是可选部分，由declare开始，声明执行部分所需的变量或者常量。假如，没有用到变量或者常量可以省略。
-
2、执行部分是由begin开始，end结束。是PL/SQL块语句的核心部分，所有的可执行的PL/SQL语句和操作变量都放在该部分。是必选的部分，不能省略。
-
3、异常部分是由exception开始。主要处理执行部分过程中的执行语句或者赋值操作出现错误时，就会进入该部分。是PL/SQL程序的异常处理部分，不是必须的。
例：我们通过简单的PL/SQL语句块输出学生信息表中的某一位学生的基本信息。代码如下：
declare
str varchar2(50);
degin
select '姓名：' || t.stuname || '，学号：' || t.stuid || '，年龄：' || t.age
into str
from stuinfo t
where t.stuname = '张三';
dbms_output.put_line(str);
exception
when no_data_found then
dbms_output.put_line('该学生在学生信息表中找不到');
end;
/
注释：
-
1、其中set serveroutput on 命令是打开COMMAND命令窗口中的输出流。设置显示PLSQL程序的执行结果，默认情况下，不显示PLSQL程序的执行结果，语法：set serveroutput on/off;
-
2、select … into是PL/SQL程序中对SQL查询语句给变量赋值方法。是PL/SQL程序特有的赋值语句，该赋值语句只能要求SQL语句查询出来的值只有一个，假如多个或者一个都没有会抛出异常。
-
3、dbms_output.put_line是Oracle系统自带的包中的过程，用来做输出流打印，经常可以用来开发PL/SQL程序时做测试用。
dbms_output是oracle中的一个输出对象。
put_line是上述对象的一个方法，用于输出一个字符串自动换行 。
3. PL/SQL数据类型
PL/SQL数据类型不但支持Oracle SQL中的数据类型，还有自身自带的数据类型。其中，包括标量数据类型，引用数据类型。
标量数据类型/基本数据类型
标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是 Oracle SQL 中有的数据类型，有的是 PL/SQL 自身附加的数据类型。下面我们来详细介绍一下常用的的标量数据类型：
-
1、数值类型： 主要用来存储数值类型的数据。常用的有number、pls_integer、binary_integer和simple_integer类型。
number：可以存储小数和整数类型数据，格式为number(p, s)，其中p表示的是精度（既是位数的长度），s表示的是小数点后的位数。例如：number(3, 2)，表示的范围是-9.99-9.99。
pls_integer、binary_integer、simple_integer：主要用来存储整数类型，它们存储整数的范围都是-(231)…(231-1)。但是BINARY_INTEGER发生内存溢出的时候会给它分配一个number类型的数据，而PLS_INTEGER会直接抛异常报错，SIMPLE_INTEGER是PLS_INTEGER的一个子类型，它不允许存在NULL值数据。
-
2、字符类型： 用来存储单个字符或字符串。主要有的类型有如下类型：
char类型：固定长度的字符串，char(n)：默认长度是1，当字符串长度小于n时，会自动右补空格，所以在取数据的时候要注意补空格。
varchar2类型：该类型存储可变长度的字符串varchar2(n)，最大存储的长度为4000个字节。当字符串长度小于n时，不会补齐空格。
long类型：该类型存储可变长度的字符串，不同于VARCAHR2类型，它对于字段的存储长度可达2G，但是作为PL/SQL变量，和VARCHAR2一样，只能存储最大32767字节。
-
3、时间类型： 主要和SQL中的时间类型一致，有date和timestamp两种时间类型。
-
4、布尔类型： PL/SQL程序中的逻辑判断的值有：TRUE、FALSE、NULL值。
引用数据类型
引用数据类型是PL/SQL程序语言特有的数据类型，是用来引用数据库当中的某一行或者某个字段作为数据类型的声明。其中有两种引用类型：%type 和 %rowtype。
%TYPE类型：
引用数据库中表的某列的类型作为某变量的数据类型，也可以直接引用PL/SQL程序中某个变量作为新变量的数据类型。下面我们通过一个案例做下测试：
--查询学生信息表(stuinfo)中学号“SC201801006”的姓名数据记录赋值给ls_stuname（%type）变量
declare
ls_stuname stuinfo.stuname%type;--通过学生姓名字段声明ls_stuname
begin
select t.stuname into ls_stuname
from student.stuinfo t
where t.stuid = 'SC201801006';
dbms_output.put_line(ls_stuname);
exception
when no_data_found then
dbms_output.put_line('该学生在学生信息表中找不到');
end;
/
% ROWTYPE 类型：
%rowtype 类型是 PL/SQL 程序引用数据库表中的一行作为数据类型，即 record 类型（记录类型）表示一条数据记录。类似java程序当中的对象的实例。可以使用“.”来访问记录中的属性。下面我们通过实例来做下测试：
--查询学生信息表(stuinfo)中学号“SC201801006”的数据记录赋值给ls_stuinfo（%rowtype）变量
declare
ls_stuinfo stuinfo%rowtype;
str varchar2(50);
begin
select t.* into ls_stuinfo
from stuinfo t
where t.stuid = 'SC201801006';
str := '姓名:' || ls_stuinfo.stuname || ' ，学号:' || ls_stuinfo.stuid || '，年龄：' || ls_stuinfo.age;
dbms_output.put_line(str);
exception
when no_data_found then
dbms_output.put_line('该学生在学生信息表中找不到');
end;
/
何时使用 %type，何时使用 %rowtype？
- 当定义变量时，该变量的类型与表中某字段的类型相同时，可以使用 %type
- 当定义变量时，该变量与整个表结构完全相同时，可以使用 %rowtype，此时通过变量名.字段名，可以取值变量中对应的值
- 项目中，常用 %type
4. PL/SQL控制结构
PL/SQL是既然是过程语言，那么就有PL/SQL的逻辑控制结构，拥有PL/SQL顺序结构、PL/SQL条件结构、PL/SQL循环结构。
PL/SQL顺序结构
在PL/SQL程序中，顺序结构我们经常使用到GOTO的关键字进行程序的跳转。GOTO的跳转有点类似脚本语言中的GOTO跳转到指定的标签位置，然后继续顺着标签往下走。标签是用双尖括号括起来的标示符，在 PL/SQL 块中具有唯一的id名，标签后必须紧跟可执行语句或者 PL/SQL 块。GOTO 不能跳转到 IF 语句、 CASE 语句、 LOOP 语句、或者子块中，因此，不在非不得已的情况下，不使用GOTO语句，因为这种逻辑控制经常有点僵硬的跳出业务逻辑，容易造成代码编写出错问题。下面通过案例详解GOTO关键字的使用：
declare
ls_stuinfo stuinfo%rowtype;
str varchar2(50);
begin
select t.* into ls_stuinfo
from stuinfo t
where t.stuid = 'SC201801006';
str := '姓名:' || ls_stuinfo.stuname || ' ，学号:' || ls_stuinfo.stuid || '，年龄：' || ls_stuinfo.age;
dbms_output.put_line(str);
if ls_stuinfo.age > 25 then
goto flag1;
else
goto flag2;
end if;
<<flag1>>
dbms_output.put_line(str ||'，年龄大于25岁');
<<flag2>>
null;
exception
when no_data_found then
dbms_output.put_line('该学生在学生信息表中找不到');
end;
/
/*
姓名：张三丰，学号：SC201801006，年龄：26
姓名：张三丰，学号：SC201801006，年龄：26，年龄大于25岁
*/
解释说明：
-
1、其中通过判断学生“张三丰”的年龄是否大于25岁，大于就跳转到flag1标志位开始继续顺序执行程序。
-
2、NULL在PL/SQL程序中是顺序结构当中的一种，表示不执行任何内容，直接跳过，因此，当年龄小于等于25岁，该学生年龄大于25岁的信息将不会被打印出来。
-
3、标签后面不能直接跟exception这种关键字类的语句，要用NULL把标签跟关键字隔开。类似的关键字还有end loop之类的，等等。
PL/SQL条件控制
PL/SQL 程序中关于条件控制的关键字有 if-then-end if、if-then-else- end if、 if-then-elsif-then-else-end if 和多分枝条件 case，下面我们通过案例进行讲解分析。
-
if-then-end if
语法结构如下：
if 条件 then --满足条件执行体; end if;案例： 查询学生信息表（stuinfo）中男生的数量。代码如下：
declare ls_stuinfo stuinfo%rowtype;--学生信息表 ls_num number := 0;--计数器 begin --对学生信息表进行全表循环 for ls_stuinfo in (select * from stuinfo) loop if ls_stuinfo.sex = '1' then --性别编码为1的是男生 ls_num := ls_num = 1;--计数器加1 end if; end loop; dbms_output.put_line('男生的数量是：' || ls_num); end; / -
if-then-else-end if
语法结构如下：
if 条件 then --条件满足执行体; else --条件不满足执行体; end if;案例： 分别计算学生信息表中男生和女生的数量各多少。代码如下：
declare ls_stuinfo stuinfo%rowtype;--学生信息表 num_boy num := 0;--男生计数器 num_girl num := 0;--女生计数器 begin --对学生信息表进行全表循环 for ls_stuinfo in (select * from stuinfo) loop if ls_stuinfo.sex = '1' then --性别编码为1的是男生 num_boy := num_boy + 1; else --性别编码为2(不为1)的是女生 num_girl := num_girl + 1; end if; end loop; dbms_output.put_line('男生的数量是：' || num_boy || '，女生的数量是：' || num_girl); end; / -
if-then-elsif-then-else-end if
语法结构如下：
if 条件1 then --条件1成立执行体; elsif 条件2 then --条件1不成立,条件2成立执行体; else --条件都不成立执行体; end if；解释说明：
elsif是对条件控制中前提条件（即上面的条件1）不成立时，再做条件判断，满足的情况下，执行then之后的执行块。这样子就可以做逻辑中条件判断的细分。
案例： 判断学生“张三丰”年龄所属范围。代码如下：
declare ls_age stuinfo.age%type; begin select age into ls_age from stuinfo where stuname = '张三丰'; if ls_age < 45 then dbms_output.put_line('青年'); elsif ls_age < 60 then dbms_output.put_line('中年'); else dbms_output.put_line('老年'); end if; exception when no_data_found then dbms_output.put_line('该学生在学生信息表中找不到'); end; / -
case when then
case是一种选择结构的控制语句，可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用，返回一个值。
语法结构如下：
--第一种形式是获取一个选择器值，然后将其与每个when字句进行比较 case 选择体 when 表达式1 then 执行体; when 表达式2 then 执行体; when 表达式3 then 执行体; ... else 表达式n then 执行体; end case; --另一种形式是不使用选择器，而是判断每个when字句中的条件 case when 表达式 then valueA else valueB end;案例： 通过选择体CASE WHEN 区分出学生信息表中学生的各个年龄的人数。代码如下：
declare ls_age stuinfo.age%type; num_26 number := 0; num_27 number := 0; num_other number := 0; begin for ls_age in (select age from stuinfo) loop case ls_age when 26 then num_26 := num_26 + 1; when 27 then num_27 := num_27 + 1; else num_other := num_other + 1; end case; end loop; dnms_output.put_line('26岁：'||ls_number_26||'人，27岁：'||ls_number_27||'人,其它岁数：'||ls_number||'人'); end; /
PL/SQL循环结构
PL/SQL 提供了丰富的循环结构来进行循环操作表中数据。 Oracle 提供的循环类型有：for循环语句、while循环语句和loop循环语句。
-
FOR循环
for循环可以根据循环体直接进行 loop循环，也可以通过对循环变量进行循环。
语法结构如下：
--通过循环体直接进行loop循环 for 循环体别名 in (SELECT 条件查询数据) loop --循环执行体； end loop; --通过循环变量进行循环 for 循环变量 in 循环下限...循环上限 loop --循环执行体； end loop; --通过循环变量进行循环 for i in 1...4 --循环的递增只能是1，不能自定义步长 loop --循环执行体； end loop;案例： 批量查询项目资金账户号为 "320001054663"的房屋账户信息并把它们打印出来，代码如下：
declare type acct_table_type is table of my_acct%rowtype index by binary_integer; v_acct_table acct_table_type; --声明一个集合变量 begin select * bulk collect into v_acct_table from my_acct where parent_fund='320001054663'; for i in 1...v_acct_table.COUNT loop ---循环打印 dbms_output.put_line('ACCT:' || v_acct_table(i).fund || ','|| v_acct_table(i).bal || ',' || v_acct_table(i).real_nmbr); end loop; end; /语法解析：
-
type
声明是类型 acct_table_type 类型的名字
-
index by binary_integer
指索引组织类型，加了”index by binary_integer ”后，类型的下标就是自增长，在增加元素时，不需要初始化，不需要每次extend，而如果没有这句“index by binary_integer”，那就得要显示对初始化，且每增加一个元素时，都需要先extend，如下所示：
declare type emp_table_type is table of emp%rowtype index by binary_integer;--这是类型声明 type ename_table_type is table of emp.ename%type;--这是类型声明 emp_table emp_table_type;--这里是定义变量 ename_table ename_table_type;--这里是定义变量 begin --没有index by需要初始化才能使用,否则会报错:变量未初始化 ename_table:= ename_table_type(); for v_ind in 1..20 loop ename_table.extend;--每次添加元素都要extend ename_table.(v_ind):='Ename' || v_ind; --index by只要定义就可以直接用,无需初始化 emp_table(v_ind).ename:='Ename' || v_ind; emp_table(v_ind).sal:=1000* v_ind; end loop; end; / -
is table of
指定是一个集合的表的数组类型, 简单的来说就是一个可以存储一列多行的数据类型 , my_acct指出在哪个表上( 存在的表 ) %rowtype指在表上的行的数据类型。
-
v_acct_table
定义一个变量来存储集合数据类型。
-
bulk collect
通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率。
Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。
采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意： 在使用bulk collect时，所有的into变量都必须是collections 。
-
-
while循环
while循环和其它编程语言的while循环一模一样，当满足 while条件时候，执行循环体，当不满足while条件时，跳出循环，结束循环。语法结构如下：
while 条件 loop --循环执行体 end loop;案例： 一个简单1加到n（n=4）的代码的while循环代码：
declare sum number(2) := 0; i number := 1; begin while i <= 4 loop sum := sum + i; i := i + 1; end loop; dbms_output.put_line(sum); end; /
-
loop循环
exit后面的条件成立了才退出循环【有点绕】
loop exit [when 条件成立]; --循环执行体 end loop;案例： 使用loop循环显示1-10：
declare i number(2) := 1; begin loop --输出i的值 exit when > 10; --输出i的值 dbms_output.put_line(i); --输出i的值 i := i + 1; end loop; end; /
5. PL/SQL动态执行DDL语句
PL/SQL程序中可以执行DML语句和事物控制等语句，如经常用到select into进行但赋值语句，但是直接DDL（create table 等操作）是不可以的，但是可以通过动态SQL语句执行，间接到达执行DDL操作的目的。
PL/SQL程序是通过PL/SQL执行时，把SQL语句当做字符串的形式传给动态SQL执行语句执行。动态SQL语句的写法如下：
execute immediate 动态SQL语句
[ into 变量列表 ]
[ using 参数列表 ]
语法解析：
如果动态SQL语句是 select语句，可以把查询的结果保存到 into后面的变量中。如果动态语句中存在参数， using为SQL语句中的参数传值。动态 SQL 中的参数格式是： [:参数名]，参数在运行时需要使用 using传值。下面我们通过案例代码分析动态SQL语句的写法。
案例1： 利用动态语句创建学生信息表的备份表（stuinfo_201812）:
declare
sql_yj varchar(500);--动态SQL执行的语句
begin
sql_yj := 'create table STUINFO_201812
(
stuid VARCHAR2(11) ,
stuname VARCHAR2(50) ,
sex CHAR(1) ,
age NUMBER(2) ,
classno VARCHAR2(7) ,
stuaddress VARCHAR2(100),
grade CHAR(4) ,
enroldate DATE,
idnumber VARCHAR2(18)
)' ;
--利用动态语句创建学生备份表（stuinfo_201812）
execate immediate sql_yj;
end;
/
案例2： 给备份表插入一个学生信息，代码如下：
declare
sql_yj varchar(500);--动态SQL执行的语句
ls_stuid varchar(11);
ls_stuname varchar(50);
ls_sex char(1);
ls_age number(2);
begin
--查询出学生信息表中学生"张三丰"的基本信息
select t.stuid, t.stuname, t.sex, t.age
into ls_stuid, ls_stuname, ls_sex, ls_age
from stuinfo t where t.stuid = 'SC201801006';
--利用动态语句执行插入操作，插入“张三丰”的信息
sql_yj := 'inster into stuinfo_201812 values(:1, :2, :3, :4, null, null, null, null, null)';
execute immediate sql_yj using ls_stuid, ls_stuname, ls_sex, ls_age;
end;
/
案例3、 利用动态SQL语句查询出刚刚插入的学生信息：
declare
sql_yj varchar(500);--动态SQL执行的语句
ls_stuinfo stuinfo%rowtype;
ls_stuid varchar(11);
ls_stuname varchar(50);
ls_sex char(1);
ls_age number(2);
begin
--查询出学生信息表中学生"张三丰"的基本信息
select t.stuid, t.stuname, t.sex, t.age
into ls_stuid, ls_stuname, ls_sex, ls_age
from stuinfo t
where t.stuid = 'SC201801006';
--利用动态语句查询获取"张三丰"的信息
sql_yj := 'select * from stuinfo_201812 where stuid = :1';
execute immediate sql_yj into ls_stuinfo using ls_stuid;
dbms_output.put_line('学号：' || ls_stuinfo.stuid ||'姓名：' || ls_stuinfo.stuname ||'性别：' || ls_stuinfo.sex || '年龄：' || ls_stuinfo.age);
end;
/
总结： PL/SQL动态执行SQL语句，是先进行解析编译后，执行后再传入参数进行执行，因此，动态SQL有一个优势就是绑定变量，只需一次解析，假如是一个SQL操作where条件后的值经常变换，而且经常用到，可以考虑使用Oracle动态执行SQL。因为，Oracle中SQL语句是通过SGA共享池进行缓存的，下次再次执行该SQL语句，直接从缓存当中取出，提高执行效率，减少Oracle数据库负担，不然，随着每次变量值的不同，Oracle把他当作不同的SQL语句，进行再次预解析，会大大加大数据库负担。
6. PL/SQL异常处理
PL/SQL异常处理是PL/SQL块中对执行部分出现异常进行处理的部分。PL/SQL采用的是统一异常处理机制，当异常发生时，程序会自动跳转到异常处理部分，交给异常处理程序进行异常匹配，再调用对应的处理方法。如果程序出现异常，而没有找到对应的异常处理程序，则程序会直接中断抛出异常。PL/SQL异常可以分为预定义异常、非预定义异常、自定义异常三种。
PL/SQL异常处理都在PL/SQL块的最下方，以exception开始，其语句结构如下：
declare
--声明部分
begin
--执行部分
exception
--异常部分
when exception1 then
--异常1处理程序
[when exception2 then
--异常2处理程序 ]
[when others then
--其它异常处理程序 ]
end;
/
语法解析：
1、exception是异常处理部分开始的标志。
2、when后面是跟着异常的名称， then后面是对应异常处理程序。也就是当异常exception1出现时，执行的是异常1处理程序。其它异常程序不会进入。
3、when others then 指的是异常再前面异常捕获中未捕获到对应的异常处理程序，则全部进入其它异常处理程序进行异常处理。
预定义异常
非预定义异常
自定义异常
7. Oracle创建函数
Oracle创建函数是通过PL/SQL自定义编写的，通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。这样使用者就不需要去理解业务逻辑，把PL/SQL函数中的业务逻辑交给专门的开发人员进行编写。
Oracle创建函数语法
PL/SQL函数主要有下面几部分组成：
- 1、输入部分：PL/SQL函数可以有输入参数，在调用函数时，必须给输入参数赋值。
- 2、逻辑计算部分：逻辑计算部分是由PL/SQL块组成业务逻辑计算部分。这部分主要是通过输入参数、表数据、SQL计算函数等进行逻辑计算得到想要的结果。
- 3、输出部分：通过逻辑计算部分，我们会得到一个函数的唯一返回值进行返回（函数必须要有返回值）。
语法结构如下：
create [or replace] function 函数名[(
p1 in|out datatype,
p2 in|out datatype,
...
pn in|out datatype)]
return datatype --返回值类型
as|is
--声明部分
begin
--PL/SQL程序块
end;
/
语法解析：(无论是过程还是函数，as关键字都代替了declare关键字)
-
1、function 是创建函数的关键字。
-
2、p1,p2…pn 是函数的参数，Oracle创建的函数也可以不需要参数。
-
3、in|out ：存储过程具有入参和出参两种参数选择，in表示的是入参，out表示的是出参，在使用过程的时候，入参必须得有对应的变量传入，出参得有对应的变量接收。不写in|out时，默认为in。
**注意：**PL/SQL有三种参数模式in|in out|out，以后细研究
-
4、return datatype：是函数的返回值的类型，一定要有。
-
5、通过is承接着PL/SQL程序块。这部分是函数的计算内容。
-
6、注意：return返回的值和out输出的值不一定是同一个，如下所示。
案例： 获取部门员工总数和工资
create or replace function (v_deptno number,total_num out number) return number as sumsal number(10):=0; cursor emp_sal_cursor is select sal from emp where deptno =v_deptno; begin total_num :=0;//注意赋值 for c in emp_sal_cursor loop sumsal := c.sal + sumsal;--工资总额 total_num := total_num + 1;--员工人数 end loop; return sumsal; end;函数调用：
declare v_num number(5):=0; begin dbms_output.put_line(get_deptnum_sal(20,v_num)); --工资总额 dbms_output.put_line(v_num); --员工人数 end; /
函数的作用是用来计算数据，并返回结果。因此，Oracle创建的函数必须得有return值，使用方式和Oracle内置函数使用方式一致。
例子：编写一个函数计算学生某一门课程在班级内的排名。代码如下：
create or replace function sf_score_rk(
in_stuid in varchar2,--学号
in_courseid in varchar2 --课程ID
)
return number
as
ls_rk number(2) := 0;--排名
ls_score number(3) := 0;--分数
begin
--获取该学生的成绩
select score into ls_score from stuinfo
where stuid = in_stuid and courseid = in_courseid;
--获取成绩比该学生高的人数
select count(1) into ls_rk from stuinfo
where courseid = in_courseid and score > ls_score;
--得到该学生的成绩排名
ls_rk := ls_rk + 1;
--返回值，一定要有
return ls_rk;
exception
when no_data_found then
dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');
end;
/
调用：
declare
ls_rk number(2);--排名
begin
ls_rk := sf_score_rk('SC201801001', 'R20180101')
dbms_output.put_line('学号：SC201801001,课程号：R20180101 的成绩排名是：' || ls_rk);
end;
/
Oracle函数查看
Oracle函数一旦创建成功就会存储在Oracle服务器当中，等待随时调用。可以通过pl/sql developer工具直接点击编辑查看，也可以通过Oracle自带的视图进行查看，代码如下：
select * from user_source where name = 'SF_SCORE_RK';
Oracle函数修改和删除
Oracle函数的修改是通过or replace关键词对原有的函数进行编辑覆盖，因此可以通过pl/sql developer工具直接点击右键进行编辑修改，修改完点击执行，即可完成覆盖。
Oracle函数的删除是通过drop命令进行删除的，语法结构如下：
drop function 函数名;
8. Oracle存储过程
Oracle存储过程在实际数据库开发过程当中会经常使用到，作为一个数据库开发者必备的技能，它有着SQL语句不可替代的作用。所谓存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。
创建Oracle存储过程语法
create [or replace] procedure 过程名 [(
p1 in|out datatype,
p2 in|out datatype,
...
pn in|out datatype
)]
as|is
--声明部分
begin
--PL/SQL执行部分
end;
/
语法解析：
-
1、procedure 关键字是创建存储过程的命令。
-
2、create [or replace]：如果存储过程已经存在则覆盖替代原有的过程。
-
3、in|out ：存储过程具有入参和出参两种参数选择，in表示的是入参，out表示的是出参，在使用过程的时候，入参必须得有对应的变量传入，出参得有对应的变量接收。不写in|out时，默认为in。
**注意：**PL/SQL有三种参数模式in|in out|out，以后细研究。
-
4、datatype表示出参数变量对应的数据类型。
-
5、as|is后面跟着的是过程当中使用到的声明变量。
-
6、begin…end 中间编写的就是存储过程的具体操作。
案例： 编写一个函数计算学生某一门课程在班级内的排名。代码如下：
create or replace procedure sf_score_rk(
in_stuid in varchar2,--学号
in_courseid in varchar2, --课程ID
out_rk out number(2) --排名
)
as
ls_rk number(2) := 0;--排名
ls_score number(3) := 0;--分数
begin
--获取该学生的成绩
select score into ls_score from stuinfo
where stuid = in_stuid and courseid = in_courseid;
--获取成绩比该学生高的人数
select count(1) into ls_rk from stuinfo
where courseid = in_courseid and score > ls_score;
--得到该学生的成绩排名
out_rk := ls_rk + 1;
exception
when no_data_found then
dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');
end;
/
调用：
declare
ls_rknum number(2);
begin
--SC201801001
sp_score_rk('SC201801001','R20180101',ls_pm);
dbms_output.put_line('学号：SC201801001,课程号：R20180101 的成绩排名是：'||ls_rknum);
end;
/
存储过程调用
-
方式一：使用execute，exec是sqlplus命令，只能在sqlplus中使用，使用时，exec可以直接跟过程名（可以省略括号）；
exec raisesalary(7369); -
方式二：使用call， 使用call时，要带上括号（无参也要带括号）；call为SQL命令使用时，对场景没有限制。
call sayHello(); -
方法三： 使用PL/SQL语句调用：
begin raisesalary(7369); end; /
存储过程的作用
存储过程的编写相对比较复杂，但是在实际系统开发过程中，还是利用过程来处理一些特定的业务功能，把业务逻辑编写在过程当中。它有哪些优势呢？
-
1、降低总体开发成本。存储过程把实际执行的业务逻辑PL/SQL块和多条SQL语句封装到存储过程当中，其它开发者只需要调用写好的过程，获取想要的结果，不需要重新理解业务。把业务抽取出来由专门的人来编写。
-
2、增加数据的独立性。它的作用和视图的作用类似，假如表的基础数据发生变化，我们只需要修改过程当中的代码，而不需要修改调用程序。使得用户程序不需要直接面对基础数据进行编写代码。使得代码内聚程度更高，耦合度更低。
-
3、提高性能。实际开发过程中，一个业务模块功能的开发可能需要用到多个SQL语句，多个PL/SQL程序块才能解决问题。把它编写进过程，Oracle只需要一次编译，以后随时可以调用。如果不使用过程，直接把许多SQL语句写进程序当中，需要多次编译，而且需要多次连接数据库，大大的降低了性能。
存储过程和存储函数的区别
(1) 一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。
(2) 过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。
- a. 存储过程和存储函数都可以有out参数；
- b. 存储过程和存储函数都可以有多个out参数；
- c. 存储过程可以通过out参数来实现返回值；
(3) 什么时候用存储过程/存储函数？
- 非必要原则：如果只有一个返回值，用存储函数；否则，就用存储过程通过out参数返回。
注意：
1、由于通过out参数，存储过程也可以返回函数值，所以存储过程和存储函数已经没有太大的区别了。而存储函数仍然存在，是由于oracle不断升级，需要实现向下兼容，所以存储函数就一直存留着。
2、一般不在存储过程或者存储函数中使用commit和rollback，谁调用谁来做提交commit 或者回滚，这样保证了在一个事务里。
3、使用存储过程和使用存储函数的一条简单非必要原则：如果只有一个返回值，使用存储函数的return子句返回；如果有多个返回值，则使用存储过程通过out参数返回。
问题思考：
a)如果查询某人的所有字段信息（并且字段比较多），该如何解决？
答：可以使用%rowtype？
b)如何返回多条符合条件的结果集，out参数可以返回结果集吗？
答：可以用到游标
9. Oracle游标
Oracle游标是通过关键字cursor的来定义一组Oracle查询出来的数据集，类似数组一样，把查询的数据集存储在内存当中，然后通过游标指向其中一条记录，通过循环游标达到循环数据集的目的。
通俗点说游标就类似一个指针。
游标的种类
Oracle游标可以分为显式游标和隐式游标两种之分。
-
显式游标： 指的是游标使用之前必须得先声明定义，一般是对查询语句的结果事进行定义游标，然后通过打开游标循环获取结果集内的记录，或者可以根据业务需求跳出循环结束游标的获取。循环完成后，可以通过关闭游标，结果集就不能再获取了。全部操作完全由开发者自己编写完成，自己控制。
-
隐式游标： 指的是PL/SQL自己管理的游标，开发者不能自己控制操作，只能获得它的属性信息。
显式游标
显式游标在实际开发中经常使用到，可以丰富PL/SQL的开发程序的编写，实现一些循环类的复杂业务。游标的使用步骤如下：
-
1、声明游标：
声明游标是给游标命名并给游标关联一个查询结果集，具体声明语法如下：
declare cursor cursor_name/*游标名*/ is select_statement/*查询语句*/ 即 cursor 游标名 [(参数名 数据类型[, 参数名 数据类型]...)] is select 语句； -
2、打开游标：
游标声明完，可以通过打开游标打开命令，初始化游标指针，游标一旦打开后，游标对应的结果集就是静态不会再变了，不管查询的表的基础数据发生了变化。打开游标的命令如下：
open cursor_name; -
3、读取游标中数据：
读取游标中的数据是通过fetch into语句完成，把当前游标指针指向的数据行读取到对应的变量中（record 变量）。游标读取一般和循环LOOP一起使用，用于循环获取数据集中的记录。
fetch cursor_name into record变量 -
4、关闭游标：
游标使用完，一定要关闭游标释放资源。关闭后，该游标关联的结果集就释放了，不能够再操作了，命令如下：
close cursor_name;
案例： 创建一个游标循环打印学生信息表中学生基本信息，代码如下：
declare
--定义游标
cursor cur_stuinfo is
select * from stuinfo order by stuid;
--定义记录变量
ls_str cur_stuinfo%rowtype;
begin
open cur_stuinfo;--打开游标
loop
fetch cur_stuinfo into ls_str;
exit when cur_stuinfo%notfound
dbms_output.put_line('学号：' || ls_str.stuid || ',姓名：' ||
ls_str.stuname);
end loop;
close cur_stuinfo;
end;
/
代码解析： 通常我们使用显式游标都是用来循环取数据集的，因此会经常如案例1一样，使用LOOP控制结果来搭配使用，通过游标的属性变量%notfound来获取游标的结束，跳出LOOP循环。
显式游标的属性
我们利用显式游标的属性值来获取游标所处的状态，然后对应做相应的处理，常用的属性有四个：
1、%notfound：表示游标获取数据的时候是否有数据提取出来，没有数据返回true，有数据返回false。经常用来判断游标是否全部循环完毕，如案例1%notfound为true的时候，说明循环完毕，跳出LOOP循环。
2、%found：正好和%notfound相反，当游标提取数据值时有值，返回true，否则返回false。
3、%isopen：用来判断游标是否打开。
4、%rowcount：表示当前游标fetch into获取了多少行的记录值，用来做计数用的。
案例： 我们经常用到%rowcount作为游标的计数器，我们修改一下上述案例的代码，用上%rowcount，代码如下：
declare
--定义游标
cursor cur_stuinfo is
select * from stuinfo order by stuinfo;
--定义记录变量
ls_stuinfo cur_stuinfo%rowtype;
begin
open cur_stuinfo;--打开游标
loop
fetch cur_stuinfo into ls_stuinfo;--获取记录值
exit when cur_stuinfo%notfound;
--利用游标计数器打印学生个数
dbms_output.put_line('%rowcount计数器，第' || cur_stuinfo%rowcount || '位学生， ');
dbms_output.put_line('学号：' || ls_curinfo.stuid || ', 姓名：' || ls_curinfo.stuname);
end loop;
close cur_stuinfo;--关闭游标
end;
/
隐式游标
隐式游标虽然不能像显式游标一样具有操作性，但是在实际开发过程当中还是经常使用到它的属性值，隐式游标主要是用在select语句活DML语句时，PL/SQL程序会自动打开隐式游标，这个隐式游标是不受开发者控制的。下面看个例子。
案例： 隐式游标使用，代码如下：
declare
ls_stuinfo stuinfo%rowtype;
begin
--查询学生信息
select * into ls_stuinfo from stuinfo where stuid = 'SC201801001';
if sql%found then
dbms_output.put_line('学号：' || ls_stuinfo.stuid || '， 姓名：' || ls_stuinfo.stuname);
end if;
--查询学生信息(不存在的学生)
select * into ls_stuinfo from stuinfo where stuid = 'SC201901001';
if sql%found then
dbms_output.put_line('学号：' || ls_stuinfo.stuid || '， 姓名：' || ls_stuinfo.stuname);
end if;
exception
when no_data_found then
dbms_output.put_line('该学生SC201901001不存在');
end;
/
代码解析： 案例中我们可以发现，Oracle隐式游标没有像显式游标一样声明游标名，直接采用SQL名称作为隐式游标的名称。然后可以利用游标的属性，做一些逻辑判断，隐式游标的属性值和显式的一样，有%notfound、%found、%rowcount、%isopen。显式游标表示的属性值都是对结果集行数的一些判断，而隐式游标对应的就是DML语句影响的行数。
- sql%rowcount：sql语句执行影响的行数，数值类型，默认值为0。
- sql%found：sql语句是否成功执行，布尔类型，默认值为null。
- sql%notfound：sql语句是否成功执行，布尔类型，默认值为null。
- sql%isopen：游标是否打开，布尔类型，默认值为null。
10. Oracle触发器
Oracle触发器是开发者对Oracle数据库的对象做特定的操作时，触发的一段PL/SQL程序代码，叫做触发器。触发的事件包括对表的DML操作，用户的DDL操作以及数据库事件等，通过关键字trigger进行创建的。
触发器的作用
Oracle触发器可以根据不同的数据库事件进行特定的调用触发器程序块，因此，它可以帮助开发者完成一些PL/SQL存储过程完成不了的问题，比如操作日志的记录、防止一些无效的操作、校验数据的正确性、限制一些对数据库对象的操作、提供数据同步的可行性。但是不推荐在触发器当中写业务逻辑程序，因为这样对后期数据的维护将大大提高成本。
触发器的类型
触发器按照用户具体的操作事件的类型，可以分为5种触发器。大致如下：
- 1、数据操作（DML）触发器：此触发器是定义在 Oracle 表上的，当对标执行 insert、update、delete 操作时可以触发该触发器。如果按照对表中行级数据进行触发或语句级触发，又可以分为行级（row）触发器，语句级触发器，按照修改数据的前后触发触发器，又可以分为 after 触发器和 before 触发器之分。
- 2、数据定义操作（DDL）触发器：当对数据库对象进行 create、alter、drop 操作时，触发触发器进行一些操作记录保存或者限定操作。
- 3、用户和系统时间触发器：该类型的触发器是作用在 Oracle 数据库系统上，当进行数据库事件时，触发触发器，一般用来记录登录的相关信息。
- instead of 触发器：此类型的触发器是作用在视图上，当用户对视图进行操作时，触发该触发器把相关的操作转换为对表进行操作。
- 复合触发器：指的是对数据操作（DML）触发器当中的多种类型触发器进行复合，比如：一个触发器当中包含着after（或before）的行级触发器和after（或before）的语句级触发器，来完成一些更为复杂的操作。
案例： 创建一个简单的触发器来校验学生基本信息的正确性，代码如下：
create or replace trigger tr_stuinfo_insert
before insert on stuinfo
for each row
begin
--对性别的数据进行校验
if :new.sex not in ('1', '2') then
raise_application_error(-20001, '性别错误，请正确选择。')
end if;
end;
insert into stuinfo
(STUID,
STUNAME,
SEX,
AGE,
CLASSNO,
STUADDRESS,
GRADE,
ENROLDATE,
IDNUMBER)
values
('SC201801006',
'张三丰',
'3',
26,
'C201801',
'福建省厦门市XXX号',
'2018',
to_date('01-09-2018', 'dd-mm-yyyy'),
'3503021992XXXXXXXX');
代码解析：
-
1、这是一个DML触发器，是对学生信息表(stuinfo)学生数据插入(insert)之前做的一个性别的校验，当性别的值不符合规范的时候报数据错误。
-
2、raise_application_error：
--语法 raise_application_error(error_number_in in number, error_msg_in in varchar2[, error_boolean in boolean]); --error_number_in：取值范围-20000到-20999之间，这样就不会与Oracle的任何错误代码发生冲突。 --error_msg_in：的长度不能超过 2k（< 2048 字节），否则截取 2k --error_boolean：可选项，若为 true，则新错误将被添加到已经引发的错误列表中。若为 false（默认），则覆盖。
11. Oracle DML类型触发器
Oracle DML类型触发器是Oracle开发过程当中最经常用到，也是最常见的触发器，主要是对DML操作，如：insert、delete、update操作事件进行触发。
DML类型触发器安装触发的事件的前后和数据触发的类型可以分为四类：前置行级触发器、后置行级触发器、前置语句级触发器、后置语句级触发器。
创建DML类型触发器的语法结构如下：
create [ or replace] trigger tr_name(触发器)
before | after
delete | insert | update [of column1, colum2...]
[or delete | insert | update of colum1, colum2...]
on table_name(表名)
[for each row]
[follows tr_name1(其它触发器名)]
[when 条件]
declare
--声明部分
begin
--触发器内容部分
end;
语法解析：
-
1、or replace：存在同名的触发器就覆盖保存。
-
2、trigger：创建触发器的关键词。
-
3、before | after：表示是选择的触发器是数据改变之前触发、数据改变之后触发。
-
4、delete | insert | update：表示触发器触发的事件类型是删除、插入或更新。
-
5、for each row：表示行级触发器、不填就是语句级触发器、
-
6、follows：表示触发器的顺序是跟在哪个之后。
-
7、when：表示触发器语句触发的条件。
行级触发器
行级触发器一般用来做数据的校验或者记录数据的操作日志，下面是一个行级触发器的例子：
案例： 利用行级触发器记录更新学生信息表时的操作记录，代码如下：
create or replace trigger tr_stuinfo_update
before update on stuinfo
for each row
begin
--当学生班号发生变化是
if :new.classno <> :old.classno then
--插入操作日志
insert into oplog
(LOGID, --日志ID
TABLENAME, --表名
COLNAME, --列名
NEWDATA, --改变后数据
OLDDATA, --改变前数据
OPDATE, --操作时间
OPERATOR) --操作人
values
(pk_oplog_id.nextval,
'stuinfo',
'classno',
:new.classno,
:old.classno,
sysdate,
'jsq');
end if;
end;
代码解析：
-
1、这是一个学生信息表(stuinfo) update 的前置行级触发器，当修改学生的班号时，会把修改的记录的操作信息记录在日志表(oplog)中。
-
2、行级触发器通过 :new 和 :old 来访问变化之后的数据和变化之前的数据，update 类型触发器，新旧数据都可以访问，delete 类型触发器，只能访问 :old 值，insert 类型触发器只能访问 :new 值。
建立好触发器，我们更改一条数据看下效果，代码如下：
update stuinfo t set t.classno = 'C201802' where t.stuid = 'SC201801006';
select * from oplog;
语句级触发器
语句级触发器一般是用来做特定限制语句操作的作用，比如在某一段时间内禁止某一部分语句操作，下面是一个语句级触发器的案例：
案例： 比如今天是12月15号，我就禁止每月的15号禁止操作学生信息表（stuinfo）的插入和删除或修改操作。代码如下：
create or replace trigger tr_stuinf_sql
before update or insert or delete on stuinfo
begin
--每月15号禁止操作学生信息表
if to_char(sysdate, 'dd') = '15' when
raise_application_error(-20001, '每月15号不能对学生信息表进行增删改操作！');
end if;
end;
/
代码解析：
-
1、DML 语句触发器就是行级触发器省略掉 for each row 的写法。
-
2、raise_application_error 是主动给客户端抛出-20001代码错误的信息。
--语法 raise_application_error(error_number_in in number, error_msg_in in varchar2[, error_boolean in boolean]); --error_number_in：取值范围-20000到-20999之间，这样就不会与Oracle的任何错误代码发生冲突。 --error_msg_in：的长度不能超过 2k（< 2048 字节），否则截取 2k --error_boolean：可选项，若为 true，则新错误将被添加到已经引发的错误列表中。若为 false（默认），则覆盖。
总结：
同一个对象上可以有多个DML触发器，但是触发器触发的时候有先后顺序，比如before型触发器比after型触发器先触发，在此基础上行级触发器，比语句级触发器更早触发。同类型的触发器的先后顺序就按follows关键词+触发器名进行排序。
12. Oracle DDL类型触发器
Oracle DDL类型触发器主要是对于Oracle数据库的DDL操作触发的触发器，主要包括create、drop、alter等DDL事件，经常利用DDL类型触发器记录DDL操作记录或者限定对某个对象进行DDL操作。
Oracle DDL类型触发器的语法结构
DDL类型触发器的编写语法如下：
create or replace trigger tr_name(触发器)
before | after
ddl_event | database_event
on schema(数据库对象) | database(数据库)
[follows tr_name1(其它触发器名)]
[when 条件]
declare
--声明部分
begin
--触发器内容部分
end;
/
语法解析：
1、or replace：存在同名的触发器就覆盖保存。
2、trigger：创建触发器的关键词。
3、before | after：表示是选择的触发器是在进行DDL操作之前触发还是在操作之后触发。
4、ddl_event：表示的DDL事件，有create（创建）、alter（修改）、drop（删除）等常用DDL操作。
5、schema | database：表示触发器是作用在数据库对象上还是数据库上。
6、follows：表示触发器的顺序是跟在哪个触发器之后。
7、when：表示触发器触发的附带条件，比如时间。
下面通过一个案例来解析Oracle DDL类型触发器的写法：
案例1： 利用Oracle DDL类型触发器给学生信息表（stuinfo）做一个禁止删除、修改表结构的触发器，代码如下：
create or replace trigger tr_stuinfo_ddl
before alter or drop on schema
begin
--禁止对学生信息表进删除和修改操作
if dictionary_obj_name = 'STUINFO' then
--修改表结构
if sysevent = 'ALTER' then
raise_application_error(-20001, '禁止学生信息表stuinfo进行alter操作！');
end if;
--删除表结构
if sysevent = 'DROP' then
--抛出错误
raise_application_error(-20001， '禁止学生信息表stuinfo进行drop操作！');
end if;
end if;
end;
执行完案例1触发器，我们通过修改学生信息表（stuinfo）测试一下触发器的效果，代码如下：
--修改表结构
alter table stuinfo modify stuaddress varchar2(200);
--删除表结构
drop table stuinfo;
案例2、 利用DDL类型触发器的创建一个数据库级别的触发器，记录用户登录数据库的记录信息。再次我们需要设计一个登录记录表，来保存用户登录信息，代码如下：
-- Create table
create table LOGIN_LOG
(
logid varchar2(20),
loginuser varchar2(100),
logindate date
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table LOGIN_LOG
is '登录日志表';
-- Add comments to the columns
comment on column LOGIN_LOG.logid
is '日志id';
comment on column LOGIN_LOG.loginuser
is '登录用户名';
comment on column LOGIN_LOG.logindate
is '登入时间';
建立DDL类型触发器（数据库级）：
create or replace trigger tr_stuinfo_login
after logon--数据库系统事件
on database
begin
--插入登录日志表
insert into login_log(LOGID, LOGINUSER, LOGINDATE)
values(pk_oplog_id.nextval, sys.login_user, sysdate);
end;
/
建立好案例2触发器，我们通过登录数据库，然后查看下登录日志表，查看一下效果
select logid, loginuser, to_char(logindate, 'yyyymmdd hh24miss') from login_log;
13. Oracle事物
Oracle事物主要用于保持Oracle数据库的数据一致性，Oracle事物可以看成一个SQL块的整体，只要某一个SQL语句错误，那么事物中的SQL被看做一个整体，一起回滚，要么就是一起成功全部提交。
我们经常举例银行转账的例子来解析事物，银行转账可以分为三步数据库操作：
-
1、A账户给B账户转钱500，A账户上金额减少500。
-
2、B账户上金额增加500。
-
3、向转账记录表中插入一条A向B转账500块的记录。
整个交易的过程可以看做一个事物，其中任何一步出差错，整个事物都将回滚，不然交易过程会出现数据不一致的情况，所以整个交易过程必须用上事物，保证数据的一致性，要么一起成功持久化到数据库，要么一起失败回滚。
事物在还未提交之前，当前同一个事物内(即同一个session内)的SQL查询可以查看到变化的数据，但是其他用户查询不到当前未提交的数据。只能等提交后，持久化到数据库，才能查到变化的数据。因此，事物一经提交就不能回滚了。Oracle事物的基本语法有：
-
1、set transaction :设置事物属性。
-
2、commit：提交事物。
-
3、rollback：回滚事物。
-
4、savepoint：设置保存点。
-
5、rollback to savepoint ：回滚到保存点。
事物的类型
Oracle事物的大概可以分为两种事物，一种是显式的事物，另外一种是隐式事物。
-
1、显式事物： 顾名思义就是开发者主动控制事物的提交和回滚，可以利用命令来控制事物的提交，如：常见的PL/SQL语句块就是这种类型的事物。必须利用commit提交。
-
2、隐式事物： 指的是Oracle数据库自己控制事物的提交和回滚。比如oracle的DDL语句（create、drop、alter等）、DCL语句（grant、revoke）等都是一经执行直接持久化到数据库，不需要开发者自己手动提交。或者是DML语句在Oracle数据库设置了自动提交：set autocommit on，也可以不需要主动提交就可以直接持久化到数据库。
案例1、oracle事物例子演示，代码如下：
declare
address varchar2(100);
begin
--取出学生SC201801001的地址
select stuaddress into address from sutinfo where stuid = 'SC201801001';
dbms_ooutput.put_line(address);
--1.修改学生SC201801001的地址为001号
update stuinfo set stuaddress = '福建省厦门市001号' where stuid = 'SC201801001';
--再次取出
select stuaddress into address from stuinfo where stuid = 'SC201801001';
dbms_output.put_line(address);
--设置保存点1
savepoint f1;
--2.修改学生SC201801001的地址为002号
update stuinfo set stuaddress = '福建省厦门市002号' where stuid = 'SC201801001';
--再次取出
select sutaddress into address from stuinfo where stuid = 'SC201801001';
dbms_output.put_line(address);
--回滚到保存点
rollback f1;
commit;
end;
/
代码解析：
1、修改学生“SC201801001”地址为"001号"，并保存一个savepoint（保存点）f1。
2、继续修改学生的地址为“002号”，打印出学生的地址，这个时候回滚到保存点f1，再提交，这个时候学生的地址应该是“001号”。
虽然事物可以使用保存点进行回滚，但是在实际开发当中还是不推荐使用，尽量控制好事物的逻辑，少用保存点，可以把长事物缩减为短事物。
总结
Oracle事物是Oracle数据一致性的基础，Oracle事物具有原子性、一致性、分离性、持久性的特点。分别指的是：
-
1、原子性：Oracle事物是Oracle数据库逻辑控制当中的逻辑单位，它对数据的操作要么是全部执行成功、要么全部不成功。
-
2、一致性：指的是事物执行的前后的数据库的数据必须保持一致状态。只有事物完成后，事物中修改的数据，才能被其他用户读。
-
3、隔离性：事物之间具有并发性，之间不互相干扰。
-
4、持久性：指的是事物中的数据一经提交，就持久化到数据库，即使后面的操作错误被打断，也不会回滚到之前的状态。
14. Oracle锁
Oracle锁是用于数据共享的情景当中，它是一种Oracle的访问机制，在访问同一个资源时，防止不同事物操作同一个数据时，出现数据问题。利用Oracle锁机制，多个会话操作同一个数据时，优先的会话会锁定该数据，其它会话只能等待。
Oracle锁就是事物的隔离性，当前的事物不能影响其它事物。Oracle锁是Oracle自动管理的，它锁定的最小粒度是数据行记录，锁定时，其它事物不能操作该数据记录，但是可以读取锁定时的记录值。因此，Oracle锁的作用是保证数据的完整性。常用的有Oracle排它锁、Oracle共享锁、Oracle行级锁、Oracle表级锁。
Oracle锁的类型
Oracle锁可以分为排它锁和共享锁两种分类：
-
1、排它锁：可以理解为写锁，这种锁是防止资源的共享，用于修改数据。如果一个事物给某个数据加了排它锁，其它事物就不能对它再加任何锁，直到事物完结，排它锁释放。
-
2、共享锁：可以理解为读锁，加了共享锁的数据，只能共享读，不能再给它加排它锁进行写的操作。
Oracle正是使用锁机制来实现系统的高并发，利用不同类型的排它锁或者共享锁来管理并发会话对数据的操作，Oracle锁的类型有：
-
1、DML锁：该类型的锁称为数据锁，控制DML数据操作。
-
2、DDL锁：该类型的锁用来保护数据库对象结构的。
其中，DML锁又可以细分为行级锁和表级锁两种：
-
1、行级锁：行级锁是粒度最细的DML锁，主要用来控制数据行的修改、删除操作。当对表中的某行数据进行修改时，需要对其加上行级排他锁，防止其他事物也对其进行修改，等数据修改完，事物提交时，自动释放。
-
2、表级锁：主要作用是在修改表数据时，会对表结构加一个表级锁，这个时候不应许其它事物对表进行修改或者删除（DDL操作）。
案例： 通过对学生信息表（stuinfo）进行DML操作，来解析Oracle锁的使用机制，步骤如下：
1、修改学生“SC201801006”的名字，代码如下：
update stuinfo set stuname = '张三丰1' where stuid = 'SC201801006';
执行更新代码，此时已经给学生信息表（stuinfo）锁定了一个表级锁，此时表stuinfo不应许进行修改和删除等DDL操作了。此时我们对stuinfo进行删除，看下效果：
在执行DML操作时，Oracle会为对应的表自动加上一个共享锁，当我们进行DDL操作时，我们也要对该表加上排它锁，但是由于存在了共享锁，所以必须等待DML事物操作完结，才能对表进行DDL操作。
2、当我们进行数据操作时，也会给该数据的行记录加上一个行级排他锁，此时不应许其它事物对该数据行进行操作。比如我们此时另外一个事物删除该数据行，看下效果：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qTYTU4Gd-1636903350590)()]
utocommit on，也可以不需要主动提交就可以直接持久化到数据库。
案例1、oracle事物例子演示，代码如下：
declare
address varchar2(100);
begin
--取出学生SC201801001的地址
select stuaddress into address from sutinfo where stuid = 'SC201801001';
dbms_ooutput.put_line(address);
--1.修改学生SC201801001的地址为001号
update stuinfo set stuaddress = '福建省厦门市001号' where stuid = 'SC201801001';
--再次取出
select stuaddress into address from stuinfo where stuid = 'SC201801001';
dbms_output.put_line(address);
--设置保存点1
savepoint f1;
--2.修改学生SC201801001的地址为002号
update stuinfo set stuaddress = '福建省厦门市002号' where stuid = 'SC201801001';
--再次取出
select sutaddress into address from stuinfo where stuid = 'SC201801001';
dbms_output.put_line(address);
--回滚到保存点
rollback f1;
commit;
end;
/
代码解析：
1、修改学生“SC201801001”地址为"001号"，并保存一个savepoint（保存点）f1。
2、继续修改学生的地址为“002号”，打印出学生的地址，这个时候回滚到保存点f1，再提交，这个时候学生的地址应该是“001号”。
虽然事物可以使用保存点进行回滚，但是在实际开发当中还是不推荐使用，尽量控制好事物的逻辑，少用保存点，可以把长事物缩减为短事物。
总结
Oracle事物是Oracle数据一致性的基础，Oracle事物具有原子性、一致性、分离性、持久性的特点。分别指的是：
-
1、原子性：Oracle事物是Oracle数据库逻辑控制当中的逻辑单位，它对数据的操作要么是全部执行成功、要么全部不成功。
-
2、一致性：指的是事物执行的前后的数据库的数据必须保持一致状态。只有事物完成后，事物中修改的数据，才能被其他用户读。
-
3、隔离性：事物之间具有并发性，之间不互相干扰。
-
4、持久性：指的是事物中的数据一经提交，就持久化到数据库，即使后面的操作错误被打断，也不会回滚到之前的状态。
14. Oracle锁
Oracle锁是用于数据共享的情景当中，它是一种Oracle的访问机制，在访问同一个资源时，防止不同事物操作同一个数据时，出现数据问题。利用Oracle锁机制，多个会话操作同一个数据时，优先的会话会锁定该数据，其它会话只能等待。
Oracle锁就是事物的隔离性，当前的事物不能影响其它事物。Oracle锁是Oracle自动管理的，它锁定的最小粒度是数据行记录，锁定时，其它事物不能操作该数据记录，但是可以读取锁定时的记录值。因此，Oracle锁的作用是保证数据的完整性。常用的有Oracle排它锁、Oracle共享锁、Oracle行级锁、Oracle表级锁。
Oracle锁的类型
Oracle锁可以分为排它锁和共享锁两种分类：
-
1、排它锁：可以理解为写锁，这种锁是防止资源的共享，用于修改数据。如果一个事物给某个数据加了排它锁，其它事物就不能对它再加任何锁，直到事物完结，排它锁释放。
-
2、共享锁：可以理解为读锁，加了共享锁的数据，只能共享读，不能再给它加排它锁进行写的操作。
Oracle正是使用锁机制来实现系统的高并发，利用不同类型的排它锁或者共享锁来管理并发会话对数据的操作，Oracle锁的类型有：
-
1、DML锁：该类型的锁称为数据锁，控制DML数据操作。
-
2、DDL锁：该类型的锁用来保护数据库对象结构的。
其中，DML锁又可以细分为行级锁和表级锁两种：
-
1、行级锁：行级锁是粒度最细的DML锁，主要用来控制数据行的修改、删除操作。当对表中的某行数据进行修改时，需要对其加上行级排他锁，防止其他事物也对其进行修改，等数据修改完，事物提交时，自动释放。
-
2、表级锁：主要作用是在修改表数据时，会对表结构加一个表级锁，这个时候不应许其它事物对表进行修改或者删除（DDL操作）。
案例： 通过对学生信息表（stuinfo）进行DML操作，来解析Oracle锁的使用机制，步骤如下：
1、修改学生“SC201801006”的名字，代码如下：
update stuinfo set stuname = '张三丰1' where stuid = 'SC201801006';
执行更新代码，此时已经给学生信息表（stuinfo）锁定了一个表级锁，此时表stuinfo不应许进行修改和删除等DDL操作了。此时我们对stuinfo进行删除，看下效果：
在执行DML操作时，Oracle会为对应的表自动加上一个共享锁，当我们进行DDL操作时，我们也要对该表加上排它锁，但是由于存在了共享锁，所以必须等待DML事物操作完结，才能对表进行DDL操作。
2、当我们进行数据操作时，也会给该数据的行记录加上一个行级排他锁，此时不应许其它事物对该数据行进行操作。比如我们此时另外一个事物删除该数据行，看下效果：
发现，在上一条update事物未提交之前，delete事物一直在等待资源的释放。
扫一扫
4342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
