Oracle事务与常用数据库对象
一、事务
1.作用
业务上的一个逻辑单元，它能保证数据所有操作要么都成功、要么都失败
2.事务提交方式
- 显示提交（commit）
- 显示回滚（rollback）
- DDL语句（隐式提交）
- 正常结束（quit/exit）
- 非正常结束程序（隐式回滚）
3.特点
原子性：同时成功或失败
一致性：无论在事务执行前、中、后，数据库始终处于一致状态
隔离性：事务之间不会相互影响
持久性：事务提交成功，数据修改是永久；回滚，数据不被修改、
4.事务（案例）
（1）解锁SCOTT用户
su - oracle
sqlplus sys as sysdba
SQL> alter user scott account unlock; //解锁scott用户初始密码是tiger
SQL> conn scott //链接到scott用户
SQL> alter user scott identified by pwd123; //修改密码为pwd123
SQL> conn scott
SQL> select tname,tabtype from tab; //查询示例用户下的示例表
（2）不使用事务
SQL> insert into dept values ('50','HAHA','null'); //往dept表插入新纪录
SQL>insert into dept values ('60','HAHA_1','null');
SQL> select * from dept;
注：当不使用事务时，每个SQL语句成功即永久修改
（3）使用事务
SQL> insert into dept values ('70','HEHE','null');
SQL> insert into dept values ('80','HEHE_1','null');
SQL> commit; //将已上语句作为事务提交
注：当使用事务后，如事务中某个SQL语句执行失败，则全部事务回滚
SQL> insert into dept values ('90','HEHE_2','null');
SQL> rollback; //取消当前事务所有修改
SQL> select * from dept; //查看未发现90的记录
（4）开启自动提交事务
SQL> set autocommit on
二、Oracle数据库对象
数据库对象是逻辑结构的集合，最基本的数据库对象是表
其他数据库对象包括：
三、索引
- 表相关的一个可选结构
- 用以提高 SQL 语句执行的性能
- 减少磁盘I/O
- 使用CREATE INDEX 语句创建索引
- 在逻辑上和物理上都独立于表的数据
- Oracle 自动维护索引
索引创建原则：
- 频繁搜索的列可以创建索引
- 经常进行排序、分组的列可以作为索引列
- 经常用做连接的列（主键、外键）可以作为索引列
- 行少、列中值少不创建索引
- 将索引放在一个单独的表空间存储，该表空间避免有退回段、临时段、表
- 对于大型索引而言，使用NOLOGGING（不记录日志）子句创建
1.索引格式
（1）创建索引
SQL> CREATE INDEX index_name ON tablename(columnname)TABLESPACE index_tbs;
（2）重建索引
SQL> ALTER INDEX index_name REBUILD;
（3）合并索引碎片
SQL> ALTER INDEX index_name COALESCE;
（4）删除索引
SQL> DROP INDEX index_name;
2.标准索引
（1）案例
SQL> create index emp_index on emp(ename); //给emp表下的ename列创建名为emp_index的标准索引
SQL> select * from emp where ename='KING'; //使用设置了index的列加快查询
SQL> select * from emp where sal='1500'; //该列sal没有设置索引，不会加快查询
3.反向键索引
反转索引列键值的每个字节，建立在值是连续增长的列上
SQL> CREATE INDEX index_name ON tablename(columnname) REVERSE;
（1）案例
SQL> create index emp_reverse on emp(SAL) reverse;
SQL> select * from emp where sal='1500'; //使用设置了sal的列加快查询
SQL> select *from emp where DEPTNO='20'; //该列deptno没有设置索引，不会加快查询
4.位图索引
位图索引适合创建在低基数列上，位图索引不直接存储ROWID，而是存储字节位到ROWID的映射；减少响应时间,节省空间占用
（1）B树索引和为位图索引对比
SQL> CREATE BITMAP index_name ON tablename(columnname);
（2）案例
SQL> create bitmap index emp_weitu on emp(job) ;
SQL> select * from emp where job='CLERK'; //使用设置了CLERK的列加快查询
SQL> select * from emp where MGR='7902'; //该列MILLER没有设置索引，不会加快查询
5.唯一索引
- 确保在定义索引的列中没有重复值
- Oracle 自动在表的主键列上创建唯一索引
- 使用CREATEUNIQUE INDEX语句创建唯一索引
SQL> CREATE UNIQUE INDEX index_name ON tablename(columnname);
6.函数索引
需要创建的索引需要使用表中一列或多列的函数或表达式
SQL> CREATE INDEX emp_ename_upper_idx ON tablename(UPPER(columnname));
四、视图
1.特点
- 提供了另外一种级别的表安全性
- 隐藏的数据的复杂性
- 简化的用户的SQL命令
- 隔离基表结构的改变
- 通过重命名列，从另一个角度提供数据
2.作用
- 虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典中，视图来自于一个或者多个实际表（基表），视图也可以从其他视图中产生
- 简单来说就是一大串SQL语句的别名
3.分类
（1）简单视图
- 是基于单个基表，不包括函数和分组函数的视图
- 可以在此视图中执行DML语句
（2）复杂视图
- 从多个表提取数据，包括函数和分组函数的视图
- 复杂视图不一定能进行DML操作
4.视图创建格式
SQL> CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)]AS select_statement [WITH CHECK OPTION] [WITH READ ONLY];
注：创建视图用户必须拥有create view权限
1）案例
SQL> quit;
sqlplus as sys sysdba
SQL> grant create view to scott; //授予scott创建视图权限
SQL> conn scott
SQL> create view open_view as select * from emp; //创建视图名为open_view的视图
SQL> select * from open_view;
五、物化视图
- 物化视图也称实体化视图、快照（oracle8i前的叫法），有数据并占用物理存储空间
- 具有物理存储的特殊视图，占据物理空间，就像表一样
- 是远程数据的本地副本，或者用来生成基于数据表求和的汇总表
1.创建物化视图步骤
①授予权限。创建物化视图的权限、QUERY REWRITE的权限，以及对创建物化视图所涉及的表的访问权限和创建表的权限
②创建物化视图日志
③创建物化视图
案例
（1）授予用户权限
SQL> quit;
sqlplus sys as sysdba
SQL> grant create materialized view to scott;
SQL> grant query rewrite to scott;
SQL> grant create any table to scott;
SQL> grantselect any table to scott;
SQL> conn scott;
（2）创建物化视图日志
SQL> create materialized view log on dept with rowid;
SQL> create materialized view log on emp with rowid;
（3）创建物化视图
SQL> create materialized view mview_test build immediaterefresh fast on commit enable query rewrite as selectd.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowide_rowid from dept d, emp e where d.deptno=e.deptno;
SQL> select * from mview_test;
2.删除物化视图步骤
- 与删除普通视图相似，需要添加一个MATERIALIZED关键字
案例
SQL> drop materialized view mview_test;
3.普通视图与物化视图
“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处
普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的，首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作
六、序列
生成唯一，连续的整数的数据对象。用于自动生成主键或者唯一索引
1.序列创建格式
SQL> create sequece sequece_name
[start with integer] //第一个***
[increment byinteger] //默认值1,正数为升序，负数为降序
[maxvalue integer |nomaxvalue] //序列可生成的最大值，默认为nomaxvalue=1027/-1
[minvalue integer |nominvalue] //序列的最小值，默认为nominvalue=1/-1026
[cycle |nocycle] //序列到最大/小时是否重头开始，默认为nocycle不再继续生成值
[cache |nocache] //预先分配一组***保存在内存中，加快序列的访问;默认为nocache=20个***
案例
SQL> create sequence seq_1 start with 0increment by 1 maxvalue 100 minvalue 0 nocycle cache 10;
SQL> create table hiahia ( toyid number not null, toynamevarchar(20), toyprice number);
SQL> insert into hiahia (toyid, toyname,toyprice) values (seq_1.nextval,'twenty',25);
SQL> select * from hiahia;
2.序列更新
SQL>ALTER SEQUENCEsequece_name MAXVALUE value CYCLE;
案例
SQL> ALTERSEQUENCE seq_1 maxvalue 1000;
3.序列删除
SQL> DROP SEQUENCE sequece_name;
案例
SQL> drop sequence seq_1;
七、同义词
将模式（用户）下的表做一个别名
1.分类
（1）私有同义词
- 只有当前模式可操作
- 只有当前模式的用户能访问;自身模式创建时需要有"create synonym"，为其他用户创建需要有"create any synonym"权限
SQL> create synonym name for user.table;
案例
SQL>quit;
sqlplus sys as sysdba
SQL> grant create synonym to scott;
SQL> conn scott;
SQL> create synonym sy_dept forscott.dept;
SQL> select * from sy_dept;
SQL> quit;
sqlplus sys as sysdba
SQL> select * from sy_dept;
（2）公有同义词
- 所有模式都可操作
- 公有同义词可以被所有的数据库用户访问，隐藏基表并降低SQL语句的复杂性，创建时需要"create public synonym"权限
SQL> create public synonym name foruser.table;
案例
SQL>quit;
sqlplus sys as sysdba
SQL> grant create public synonym toscott;
SQL> conn scott;
SQL> create public synonym pub_dept forscott.dept;
SQL> select * from pub_dept;
SQL> quit;
sqlplus sys as sysdba
SQL> select * from pub_dept;
八、分区表
- 允许用户将一个表分成多个分区
- 用户可以执行查询，只访问表中的特定分区
- 将不同的分区存储在不同的磁盘，提高访问性能和安全性
- 可以独立地备份和恢复每个分区
1.特点
- 改善表的查询性能
- 表更容易管理
- 便于备份和恢复
- 提高数据安全性
注：数据量大于2GB使用；已有的数据和新添加的数据有明显的界限划分
2.格式
SQL> PARTITION BY RANGE (column_name)
(
PARTITION part1 VALUE LESS THAN (range1),
PARTITION part2 VALUE LESS THAN (range2),
...
[PARTITION partN VALUE LESS THAN (MAXVALUE)]
);
3.案例：
SQL> create table sales (sales_idnumber,product_id varchar2(5),sales_date date not null) partition by range(sales_date)(partition p1 values less than (to_date('2015-04-1','yyyy-mm-dd')),partition p2 values less than (to_date('2015-07-1','yyyy-mm-dd')),partition p3values less than (to_date('2015-10-1','yyyy-mm-dd')),partition p4 values lessthan (to_date('2016-01-1','yyyy-mm-dd')),partition p5 values less than(maxvalue));
SQL> insert into salesvalues(1,'linux',(to_date('2015-04-1','yyyy-mm-dd')));
SQL> insert into salesvalues(1,'linux',(to_date('2015-07-1','yyyy-mm-dd')));
SQL> insert into salesvalues(1,'linux',(to_date('2015-10-1','yyyy-mm-dd')));
SQL> insert into salesvalues(1,'linux',(to_date('2016-01-1','yyyy-mm-dd')));
SQL> select * from sales partition(p1); //由于键值等于分区值自动落入下一分区
SQL> select * from sales partition(p2);
SQL> select * from sales partition(p3);
SQL> select * from sales partition(p4);
SQL> select * from sales partition(p5);
注：当数据等于分区表限定值时，该数据自动落入下一分区中
SQL> delete from sales partition(p4);
SQL> select * from sales partition(p4); //验证，分区数据丢失
SQL> select *from sales partition(p5); //其他分区不受影响
注：当删除某个分区数据中，其他分区不会受到任何影响
tiaopide:hiahia