Oracle事务与常用数据库对象

最新推荐文章于 2021-07-29 15:53:41 发布

CallousMaster

最新推荐文章于 2021-07-29 15:53:41 发布

阅读量294

点赞数 2

分类专栏： Oracle 文章标签： Oracle事务与常用数据库对象

本文链接：https://blog.csdn.net/Callousmaster/article/details/80519659

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、事务

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));