Oracle事务和对象详解

共计 9203 个字符，预计需要花费 24 分钟才能阅读完成。

一、Oracle 事务
·事务的含义：事务是业务上的一个逻辑单元，为了保证数据的所有操作要么全部完成，要么全部失败。
1、事务的开始是从一条 SQL 语句开始，结束于下面的几种情况：
1）显示提交：输入 commit 指令，事务完成提交
2）显示回滚：输入 rollback 指令，未提交的事务丢掉，回滚到事务开始时的状态。
3）DDL 语句：即 create、drop 等语句，这些语句会使事务自动隐式提交
4）结束程序：输入 exit 退出数据库，则自动提交事务；或者意外终止、出现程序崩溃，则事务自动回滚。
2、事务的特点 -ACID 特性
1）原则性：要么同时成功，要么同时失败的原则
2）一致性：如，a 转账给 b，最总结果 a + b 的金钱总数是不变的
3）隔离性：当出现多个事务出现，它们之间是互相隔离、互不影响的
4）持久性：事务一旦提交，则数据永久修改。
3、关于事务的三个命令
commit：立即提交事务
rollback：回滚事务
set autocommit on/off：设置 / 关闭自动提交
二、索引
·索引是 Oracle 的一个对象，是与表关联的可选结构，用于加快查询速度，提高检索性能。
1、特点
1）适当使用索引可以提高查询速度、建立索引的数量无限制
2）可以对表的一列或者多列建立索引
3）索引是需要磁盘空间，可以指定表空间存储索引。
4）是否使用索引有 Oracle 决定
2、索引的分类
B 树索引：从顶部为根，逐渐向下一级展开
唯一索引：定义索引的列没有任何重复
非唯一索引：与唯一索引相反
反向键索引：对与数字列作用较大，会将 1234 生成 4321 进行查询的索引
位图索引：应用于数据仓库和决策支持系统中。优点是相对于 b 树索引，可以减少响应时间；相对于其他索引，其空间占用少。
函数索引：使用函数涉及正在创建索引的列的索引
3、创建索引
操作时我们可以使用 Oracle 的 scott 用户进行测试，首先解锁，在改一个密码，登陆进去就可以操作了

create [unique] index i_name on t_name(c_list) [tablespace ts_name];
注释：create … index … on 创建索引
unique 唯一索引
i_name 创建的索引名称
t_name 在哪个表创建
(c_list) 列名
ts_name 表空间名
举例：
create index emp_ename_idx on emp(ename);
：b 树索引
create unique index emp_ename_unique_idx on emp(ename);
：唯一索引
create index emp_ename_reverse_idx on emp(ename) reverse;
：反向索引
create index emp_ename_upper_idx on emp(upper(ename));
：函数索引
4、创建索引的原则
1）频繁检索的列
2）经常排序、分组的列
3）主键 / 外键
4）大型索引使用 nologing 子句创建
5）需定期进行组织索引、碎片整理
5、维护索引的命令
1）重建索引
alter index i_table rebuild [tablespace ts_name];
2）合并索引碎片
alter index i_name coalesce;
3）删除索引
drop index i_name;
4）查看索引（使用 user_indexes、user_ind_columns 字典）
select index_name,index_tyep,table_name,column_name from user_indexes; 或者
select index_name,table_name,_column_name from user_ind_columns where
index_name like‘emp%’;
三、视图
·视图是一个虚表，不占用物理空间，视图本身的定义语句存放于字典里，可以由一个或者多个表中获得数据。目的是为了方便查询，而不需要在敲繁琐的查询语句。
1、视图的作用
1）通过限制对表中预定的一组行和列进行查看，可以防止用户看到无权限数据，提供了安全性
2）简化了用户的命令、隐藏了数据的复杂性，方便操作
3）视图可以对列进行重命名，提升了数据库的灵活性和人性化
4）视图将应用程序与基表定义的修改进行了隔离，表结构的修改不会影响视图结构。
2、创建视图
create [or replace] [force] view vname [(alias[,alias]…)] as SQL
[with check option] [with read only];
注释：or replace：覆盖
force：强制的；视图中的基表是否存在，都会创建
vname：创建的视图名称
[(alias[,alias]…)]：可选项，视图别名，可以由多个
SQL：SQL 查询语句
with check option：用于更改视图时的约束
with read only：只读
举例：
create view vname as select from tname where‘a=b’;
简单的视图
create view vname as select from tname where‘a=b’with read only;
只读视图
create force view vname as select from tname where‘a=b’;
强制创建视图，假如‘tname’表不存在，也强制创建，也就是错误视图。
create table tname (a (char));
alter view vname compile;
查看错误视图（使用字典）
show errors view;
show
创建 tname 表；然后编译错误视图，使其生效
3、在视图中使用 DML 语句（insert、update、delete）的限制
1）DML 语句只能修改视图中的一个基表
2）如果修改违反了 check option 约束，则无法修改
3）如果视图包括连接运算符、DISTINCT 运算符、集合运算符、聚合函数和 group by 子句，则无法更新视图
4）如果包含伪列、表达式，也无法更新视图
4、维护视图
1）删除视图
drop view vname;
2）查询已有视图(user_views 字典)
select viewname from user_views;
5、物化视图
·顾名思义，物化视图就是将视图物理化存到磁盘，它与基表实时同步。物化视图可以避免 order by 子句或者多表连接查询带来的效率降低和时耗，但是会占用用磁盘空间。
1）物化视图同步的刷新方式可分为：
on commit：即提交事务以后
on demand：简单讲就是用户手动刷新
2）物化视图同步的刷新类型
complete：完全刷新
fast：追求速度，增量刷新
force：Oracle 自动判断使用 complete 还是 fast 类型刷新
never：从不刷新
·关于以上四个类型，Oracle 默认选择 force 刷新。工作中无特殊需求，无需更改
6、创建物化视图
1）首先用 sys 登陆，为 scott 用户授权

2）创建物化视图日志

3）正式创建

注释：build immediate：立即创建
refresh fast：刷新类型为 fast
enable query rewrite：启用查询重写
4）删除物化视图
**drop materialized view materview;***

更多详情见请继续阅读下一页的精彩内容：https://www.linuxidc.com/Linux/2018-11/155179p2.htm

一、Oracle 的序列
·序列是用来生成惟一的连续的整数的数据库对象。序列通常可以用来自动生成主键或者唯一键的值；可根据数值进行升序或者降序的排列
·比如创建班级的学生信息登记表，可以将学号使用序列自动生成。班级的学号都是 10000~10060 这个范围，每登记一个学生，序列自动增长一个。
1、创建序列的命令语法
create sequence seq_name [start with interger] [increment by interger] [maxvalue interger] [minvalue interger] [cycle|nocycle] [cache interger|nocache];
注释：
create sequence seq_name：创建序列
start with：从 … 开始生成序列
increment by：每次增长的数值
maxvalue：最大的序列值，默认不限制最大值
minvalue：最小的序列值，默认不限制最小值
cycle：翻译为中文是圆的意思，这里其实是指循环，当序列到达最大值 / 最小值时，从初始值开始继续生成。默认为 nocycle，不进行循环生成。
cache：预先分配出来的序列，保存于缓存中，可用于快速访问序列号。默认为 nocache，即不分配缓存
举例：
create sequence seq_1
start with 10000
increment by 1
maxvalue 10060
nocycle;
：还是上面的例子，创建一个 seq_1 序列，从 10000 开始，每次增长为 1，最大值为 10060，不进行循环生成。
2、我们建立好了序列自然需要将序列应用到我们创建的表（table）中。可以通过 nextval、currval 伪列来访问该序列的值。
·nextval：这个伪列会在创建序列后第一次使用时，返回该序列的初始值。在以后使用时，将使用 increment by 子句的值来增加序列值
·currval：会返回序列的最后一次引用 nextval 时返回的值。
举例：
create table student_class25(stu_id number not null,stu_name varchar2(20),
stu_age number);
创建一个 student_class25 表
insert into student_class25 values (seq_1.nextval,’zhangsan’,’13’);
insert into student_class25 values (seq_1.nextval,’lisi’,’13’);
…
向 student_class25 表中插入数据，在对应 stu_id 这一列我们引用序列 seq_1。
select seq_1.currval from dual;
查询序列当前值
## 如果在你指定缓存的环境下，执行 startup force 重启数据库，接着写的数据的序列号会从初始值 + 缓存值开始。例如，初始值为 10，缓存值为 30 个，那么执行 startup force 后，序列号会从 40 开始生成。
3、维护序列
1）查看序列（使用字典 user_sequences）
select from user_sequences;
2）删除序列
drop sequence seq_name;
3）更改序列
alter sequence seq_name […];
二、Oracle 的同义词
·顾名思义，同义词就是意思相同的词语，你可以看做是对象的一个别名，就像 liunx 操作系统中的命令的别名一样。但是 linux 命令别名只提供了方便容易记忆的好处。
1、而在 Oracle 中，同义词可用来：
1）简化 Oracle 中输入的 SQL 语句，通过给对象建立同义词，我们可以简化复杂的 SQL 语句，方便记忆和操作
2）隐藏对象的名称和所有者，给对象建立了同义词，如 scott 模式下的 emp 表（scott,emp），我们建立一个同义词就叫做 se。那么我们在查询这个表的时候就不用担心暴露表的位置，提供了一定的安全性
3）提供对数据库的公共访问，可以通过给远程连接建立公有同义词给其他的用户查看。
4）以及为分布式数据库的远程对象提供位置透明性，我们给数据库链接（将其看做是一个对象）建立同义词，使用户可以无阻碍的查看数据表，这时候对于用户而言，位置就是透明的。
2、同义词分类（两种）
1）私有同义词：为当前模式的用户私有，其他用户无法使用
2）公有同义词：与私有同义词相反
3、创建同义词
create [or replace] [public] synonym sy_name for object_name;
注释：
create … for：给 … 创键同义词
[or replace]：该可选项的作用是覆盖（如果存在同名的同义词就会将其覆盖掉）
[public]：加上该选项表示创建公有同义词
sy_name：同义词名
object_name：对象名称，如 scott.emp（即 scott 用户下的 emp 表）
4、举例
create synonym sy_name for scott.emp;
为 scott.emp 表创建私有同义词
create public synonym sy_name for scott.emp;
创建公有同义词
## 创建好的公有同义词不代表用户就可以使用，我们还需要对需要操作这个表的用户进行授权
5、复杂应用
1）建立 oracle 连接的命令
举例：
①conn system/pwd123@neworcl;
②create database link link_1 connect system identified by pwd123 using orcl;
③create synonym sy_t for scott.emp@link_1;
通过以上命令建立一条连接到远程数据库的数据库链接，并为链接中数据库的 scott.emp 表建立同义词。
6、维护同义词
1）查看同义词
select from user_synonyms;
2）删除公有 / 私有同义词
drop [public] synonym sy_name;
三、数据库链接
1、数据库链接是定义一个数据库到另一个数据库路径的对象，可以通过数据库链接查询远程表、执行远程程序。在分布式环境中，Oracle 数据库链接是必须的。有一点，oracle 数据库链接是单向的；一个链接，能从 a 数据库链接到 b 数据库，那么无法从 b 数据库链接到 a 数据库。
2、数据库链接分类
private：属于创建该链接的用户，也只有该用户可以使用
public：属于 public，允许本地数据库中所有拥有数据库访问权限的用户使用
global：全集类型，网络中的数据库用户均可使用
3、创建数据库链接
create database link connect to username identified by user
passwd using
‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbtest)
)
)’;
四、分区表
·Oracle 可将表的行分成多个部分（存储于不同的表空间），这些部分组成的表就是分区表，这些部分被称为 Oracle 分区。而分区表的应用一般来说，表的大小要足够大才可以体现分区表的作用，太小的表不建议使用。
1、分区表有很大的作用：
1）改善标的查询性能；同时实现分开管理
2）便于备份和恢复，如果一部分的数据丢失，只需要单独恢复对应分区即可
3）分开存储数据，提高安全性
4）分区对于用户而言也是透明的
2、创建分区表
create table t_name
(stu_id number,stu_name varchar2(20),stu_age number)
partition by range (stu_id)
(
partition p1 values less than (),
partition p2 values less than (),
parti…
);

：

一、Oracle 事务
·事务的含义：事务是业务上的一个逻辑单元，为了保证数据的所有操作要么全部完成，要么全部失败。
1、事务的开始是从一条 SQL 语句开始，结束于下面的几种情况：
1）显示提交：输入 commit 指令，事务完成提交
2）显示回滚：输入 rollback 指令，未提交的事务丢掉，回滚到事务开始时的状态。
3）DDL 语句：即 create、drop 等语句，这些语句会使事务自动隐式提交
4）结束程序：输入 exit 退出数据库，则自动提交事务；或者意外终止、出现程序崩溃，则事务自动回滚。
2、事务的特点 -ACID 特性
1）原则性：要么同时成功，要么同时失败的原则
2）一致性：如，a 转账给 b，最总结果 a + b 的金钱总数是不变的
3）隔离性：当出现多个事务出现，它们之间是互相隔离、互不影响的
4）持久性：事务一旦提交，则数据永久修改。
3、关于事务的三个命令
commit：立即提交事务
rollback：回滚事务
set autocommit on/off：设置 / 关闭自动提交
二、索引
·索引是 Oracle 的一个对象，是与表关联的可选结构，用于加快查询速度，提高检索性能。
1、特点
1）适当使用索引可以提高查询速度、建立索引的数量无限制
2）可以对表的一列或者多列建立索引
3）索引是需要磁盘空间，可以指定表空间存储索引。
4）是否使用索引有 Oracle 决定
2、索引的分类
B 树索引：从顶部为根，逐渐向下一级展开
唯一索引：定义索引的列没有任何重复
非唯一索引：与唯一索引相反
反向键索引：对与数字列作用较大，会将 1234 生成 4321 进行查询的索引
位图索引：应用于数据仓库和决策支持系统中。优点是相对于 b 树索引，可以减少响应时间；相对于其他索引，其空间占用少。
函数索引：使用函数涉及正在创建索引的列的索引
3、创建索引
操作时我们可以使用 Oracle 的 scott 用户进行测试，首先解锁，在改一个密码，登陆进去就可以操作了

create [unique] index i_name on t_name(c_list) [tablespace ts_name];
注释：create … index … on 创建索引
unique 唯一索引
i_name 创建的索引名称
t_name 在哪个表创建
(c_list) 列名
ts_name 表空间名
举例：
create index emp_ename_idx on emp(ename);
：b 树索引
create unique index emp_ename_unique_idx on emp(ename);
：唯一索引
create index emp_ename_reverse_idx on emp(ename) reverse;
：反向索引
create index emp_ename_upper_idx on emp(upper(ename));
：函数索引
4、创建索引的原则
1）频繁检索的列
2）经常排序、分组的列
3）主键 / 外键
4）大型索引使用 nologing 子句创建
5）需定期进行组织索引、碎片整理
5、维护索引的命令
1）重建索引
alter index i_table rebuild [tablespace ts_name];
2）合并索引碎片
alter index i_name coalesce;
3）删除索引
drop index i_name;
4）查看索引（使用 user_indexes、user_ind_columns 字典）
select index_name,index_tyep,table_name,column_name from user_indexes; 或者
select index_name,table_name,_column_name from user_ind_columns where
index_name like‘emp%’;
三、视图
·视图是一个虚表，不占用物理空间，视图本身的定义语句存放于字典里，可以由一个或者多个表中获得数据。目的是为了方便查询，而不需要在敲繁琐的查询语句。
1、视图的作用
1）通过限制对表中预定的一组行和列进行查看，可以防止用户看到无权限数据，提供了安全性
2）简化了用户的命令、隐藏了数据的复杂性，方便操作
3）视图可以对列进行重命名，提升了数据库的灵活性和人性化
4）视图将应用程序与基表定义的修改进行了隔离，表结构的修改不会影响视图结构。
2、创建视图
create [or replace] [force] view vname [(alias[,alias]…)] as SQL
[with check option] [with read only];
注释：or replace：覆盖
force：强制的；视图中的基表是否存在，都会创建
vname：创建的视图名称
[(alias[,alias]…)]：可选项，视图别名，可以由多个
SQL：SQL 查询语句
with check option：用于更改视图时的约束
with read only：只读
举例：
create view vname as select from tname where‘a=b’;
简单的视图
create view vname as select from tname where‘a=b’with read only;
只读视图
create force view vname as select from tname where‘a=b’;
强制创建视图，假如‘tname’表不存在，也强制创建，也就是错误视图。
create table tname (a (char));
alter view vname compile;
查看错误视图（使用字典）
show errors view;
show
创建 tname 表；然后编译错误视图，使其生效
3、在视图中使用 DML 语句（insert、update、delete）的限制
1）DML 语句只能修改视图中的一个基表
2）如果修改违反了 check option 约束，则无法修改
3）如果视图包括连接运算符、DISTINCT 运算符、集合运算符、聚合函数和 group by 子句，则无法更新视图
4）如果包含伪列、表达式，也无法更新视图
4、维护视图
1）删除视图
drop view vname;
2）查询已有视图(user_views 字典)
select viewname from user_views;
5、物化视图
·顾名思义，物化视图就是将视图物理化存到磁盘，它与基表实时同步。物化视图可以避免 order by 子句或者多表连接查询带来的效率降低和时耗，但是会占用用磁盘空间。
1）物化视图同步的刷新方式可分为：
on commit：即提交事务以后
on demand：简单讲就是用户手动刷新
2）物化视图同步的刷新类型
complete：完全刷新
fast：追求速度，增量刷新
force：Oracle 自动判断使用 complete 还是 fast 类型刷新
never：从不刷新
·关于以上四个类型，Oracle 默认选择 force 刷新。工作中无特殊需求，无需更改
6、创建物化视图
1）首先用 sys 登陆，为 scott 用户授权

2）创建物化视图日志

3）正式创建

注释：build immediate：立即创建
refresh fast：刷新类型为 fast
enable query rewrite：启用查询重写
4）删除物化视图
**drop materialized view materview;***

更多详情见请继续阅读下一页的精彩内容：https://www.linuxidc.com/Linux/2018-11/155179p2.htm