ORACLE進階操作

1 事務

事務的任務便是使數據庫從一種狀態變換成為另一種狀態，這不同于文件系統，它是數據庫所特用的。

所有的數據庫中，事務只針對DML（增刪改)，不針對select

select只能查看其他事務提交或回滾的數據，不能查看別的事務中正在進行的操作

1.1 事務的特性

原子性(Atomicity)

語句級原子性，過程級原子性，事務級原子性

一致性(Consistency)

狀態一致，同一事務中不會有兩種狀態

隔離性(Isolation)

事務間是互相分離的互不影響

持久性(Durability)

事務提交了，那么狀態就是永久的

1.2 開啟事務

執行任意的DML語句，會自動開啟事務

--語句級：鎖定語句所影響的記錄行
update dept set loc = 'beijing' where deptno=10;

創建回滾點

--過程級
savepoint a

鎖定整張表

select * from 表名 for update

1.3 事務語句

commit=commit work：提交事務.將本次事務中的DML操作全部持久化，并結束本次事務

rollback=rollback work：回滾事務.將本次事務中的DML操作全部取消，并結束本次事務

savepoint：手動開啟事務，并創建回滾點

rollback to savepoint：回滾到指定的回滾點前，滾點之后創建的回滾點與DML操作無效

1.4 事務鎖

當執行事務操作時，Oracle會在被作用的表上加鎖，防止其他用戶改表。

Oracle保證讀一致性，并且永遠都不會臟讀(即讀其他事務的未提交數據)。

--此時其它會話無法修改改行記錄
update dept set loc = 'beijing' where deptno=10;

1.5 提交事務

使用commit語句可以提交事務，當執行了commit語句后，會確認事務的變化，結束事務，刪除保存點，釋放鎖。

當使用commit語句結束事務之后，其他會話將可以查看到事務變化后的新數據。

savepoint a;
update dept set loc = 'beijing' where deptno=10;
--提交事務之后，回滾點被自動刪除
commit;

1.6 回退事務

保存點是事務中的一點，用于取消部分事務，當結束事務時，會自動的刪除該事務所定義的所有保存點。

update dept set loc = 'beijing' where deptno=10;
savepoint a;
update dept set loc = 'beijing' where deptno=20;
savepoint b;
update dept set loc = 'beijing' where deptno=30;
--回滾后，回滾點a之后執行的語句全部無效，包括回滾點b的創建
rollback to a;
--回滾全部事務，未提交的事務操作全部失效
rollback;

1.7 注意

在ORACLE中沒有提供開始事務處理語句，所有的事務都是隱式開始的。也就是說ORACLE中用戶不可以顯式使用命令來開始一個事務。

ORACLE任務第一條修改數據庫的語句，或者一些要求事務處理的場合都是事務隱式的開始。

但是當用戶想要處理一個事務結束時，必須使用commit和rollback語句結束。

2 序列

2.1 sequence

ORACLE中，主鍵不支持自動增長，管理起來較為麻煩，通常使用序列（ sequence ）進行主鍵的管理。

sequence就是一組連續數字的集合：

每個數字只能使用一次
sequence使用前必須初始化

2.2 序列的創建

語法：

create sequence 序列名minvalue 1		--sequence的最小值maxvalue 10000	--sequence的最大值start with 1	--sequence的起始值increment by 1	--sequence的步進值cache 20		--緩存數字個數
;

2.3 常用方法

nextval：選取sequence的下一個值

也用來進行sequence的初始化，每調用1次，向前進步1

select 序列名.nextval from dual;

currval：選取sequence的當前值

select 序列名.currval from dual;

2.4 示例

創建表

create table test1(id number(5) primary key,name varchar2(5)
);

創建序列

create sequence test1_seqminvalue 1maxvalue 10000start with 1increment by 1
;

初始化

select test1_seq.nextval from dual;

插入數據

insert into test1 values(test1_seq.currval,'aa');--1號
insert into test1 values(test1_seq.nextval,'bb');--2號
insert into test1 values(test1_seq.nextval,'cc');--3號
commit;
insert into test1 values(test1_seq.nextval,'ee');--4號
insert into test1 values(test1_seq.nextval,'ff');--5號
rollback;
--每個序號只能使用一次，不會回退
insert into test1 values(test1_seq.nextval,'dd');--6號
insert into test1 values(test1_seq.nextval,'ee');--7號
insert into test1 values(test1_seq.nextval,'ff');--8號

3 視圖

3.1 概述

視圖是基于一個表或多個表或視圖的邏輯表，本身不包含數據，通過它可以對表里面的數據進行查詢和修改(特指簡單視圖)。

視圖是存儲在數據字典里的一條select語句。

視圖基于的表稱為基表。

視圖的特點

視圖可以當成表使用
內容由基表的內容來確定
必須有DBA權限才能創建視圖

視圖的優點

將復雜的sql語句保存在數據庫中,方便管理與調用
可以將一張表通過不同的視圖,從邏輯上當成N張表使用
可以像對普通表賦對象權限一樣,將對象權限交于某個用戶

3.2 視圖的分類

簡單視圖

從單表中獲取數據
可以實現增刪改操作，去影響基表的數據

復雜視圖

從多表獲取數據，包含函數、分組
不允許通過修改視圖去影響基表的數據

3.3 視圖的創建

創建視圖必須擁有DBA權限

語法：

create [or replace] [force|noforce] view 視圖名 [alias,alias...]
as
subquery
[with check option [contraint constraint]]
[with read only]

or replace：若所創建的試圖已經存在，ORACLE自動重建該視圖
force：不管基表是否存在ORACLE都會自動創建該視圖
noforce：只有基表都存在ORACLE才會創建該視圖
alias：為視圖產生的列定義的別名
subquery：一條完整的SELECT語句，可以在該語句中定義別名
with check option：插入或修改的數據行必須滿足視圖定義的約束
with read only：該視圖上不能進行任何DML操作

3.3.1 簡單視圖的創建

--查詢名字中含有A的員工信息
create view emp_A_in_name
as
select * from emp where ename like '%A%';

--視圖的使用與普通表相同
--視圖本身沒有數據,數據來源于視圖基于的表(基表)
select * from emp_A_in_name;
select * from emp_A_in_name where deptno=30;
--通過修改視圖，修改基表的數據
update emp_A_in_name set sal=3000 where empno=7900;

3.3.2 復雜視圖的創建

--查詢各部門的人數、平均/最低/最高薪資、薪資綜合
create view emp_dept_sal
as
select deptno,count(*) num,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal,sum(sal) sum_sal
from emp group by deptno;

3.4 視圖修改

create view dept_view
as
select deptno,dname,loc from dept;

create or replace view dept_view(部門號,部門名,地址)
as
select deptno,dname,loc from dept;

create view dept_view
as
select deptno "部門號",dname "部門名",loc "工作地點"
from dept;

3.5 視圖刪除

drop view dept_view;

4 索引

提高查詢效率，但是DML操作效率下降，不適合用在經常更改的表中

索引可以有N個，但是列組合必須不一樣

4.1 索引的使用原則

建立在WHERE子句經常引用的表列上
多表查詢應該在連接列上建立索引
不要在小表上建立索引
在經常需要排序操作的列上建立索引，可以加快數據排序的速度
索引越多，DML操作的速度越慢，尤其會極大影響INSERT操作和DELETE操作的速度

4.2 索引的分類

按照索引數據的存儲方式：B樹索引、反向索引、位圖索引。
按照索引列的個數：單列索引、復合（組合）索引。
按照索引列值的惟一性：惟一索引、非惟一索。

4.3 索引的創建

語法：create index 索引名 on 表名 (列名1，…，列名N);

4.3.1 單列索引

單列索引是基于單個列所建立的

create index emp_index on emp(job)

4.3.2 復合索引

復合索引是基于多個列所建立的

create index newemp_idx _2 on newemp (sal,comm);

可以使用函數或模糊查詢創建索引
create index my_emp_index_ename on emp('%ename%')

4.3.3 B樹索引

--在建立索引時，留下30%的空間 用作后續插入的預留
create index ind_ename on emp (ename) pctfree 30 tablespace my_file;

4.3.4 唯一索引

create unique index idx_test_uid on test_uid(name);

4.3.5 位圖索引

針對于具有大量重復值的列

create bitmap index sex_bitmap_index on student(sex);