oracle系列（四）oracle事務(wù)和常用數(shù)據(jù)庫(kù)對(duì)象

博主QQ：819594300

博客地址：http://zpf666.blog.51cto.com/

有什么疑問(wèn)的朋友可以聯(lián)系博主，博主會(huì)幫你們解答，謝謝支持！

前言：本次內(nèi)容的相關(guān)知識(shí)點(diǎn)我們?cè)趯W(xué)習(xí)sqlserver2008R2的時(shí)候介紹過(guò)一些，包括：事務(wù)、索引、視圖等。那么今天我們學(xué)習(xí)在oracle上實(shí)現(xiàn)這些重要的內(nèi)容，以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的優(yōu)化。

一、事務(wù)

1、簡(jiǎn)介

事務(wù)是數(shù)據(jù)處理的核心，是業(yè)務(wù)上的一個(gè)邏輯單元，它能夠保證其中對(duì)數(shù)據(jù)所有的操作，要么全部成功，要么全部失敗。DBMS通過(guò)事務(wù)的管理來(lái)協(xié)調(diào)用戶的并發(fā)行為，減少用戶訪問(wèn)資源的沖突。

1）顯示提交：當(dāng)事務(wù)遇到COMMIT指令時(shí)，將結(jié)束事務(wù)并永久保存所有的更改的數(shù)據(jù)。

2）顯示回滾：當(dāng)事務(wù)遇到ROLLBACK指令時(shí)，也將結(jié)束事務(wù)的執(zhí)行，但是此時(shí)它回滾所有更改的數(shù)據(jù)到事務(wù)開(kāi)始時(shí)的原始值，即取消更改，數(shù)據(jù)沒(méi)有變化。

3）DDL語(yǔ)句：一旦用戶執(zhí)行了DDL（數(shù)據(jù)定義語(yǔ)言，如create，drop等）語(yǔ)句，則之前的所有DML（數(shù)據(jù)操作語(yǔ)言）操作作為一個(gè)事務(wù)提交，這種提交稱為隱示提交。

4）正常結(jié)束程序：如果oracle數(shù)據(jù)庫(kù)應(yīng)用程序正常結(jié)束，如使用sqlplus工具更改了數(shù)據(jù)，而正常退出該程序（exit），則oracle自動(dòng)提交事務(wù)。

5）非正常地結(jié)束程序：當(dāng)程序崩潰或意外終止時(shí)，所有數(shù)據(jù)更改都被回滾，這種回滾成為隱示回滾。

2、事務(wù)的特點(diǎn)

事務(wù)有4個(gè)特性，簡(jiǎn)寫為ACID特性。

1）原則性：以轉(zhuǎn)賬操作為例，轉(zhuǎn)出賬戶余額減少和轉(zhuǎn)入余額增加是兩個(gè)DML語(yǔ)句，但是必須作為一個(gè)不可分割的完整操作。要么同時(shí)成功，要么同時(shí)失敗，只轉(zhuǎn)出而沒(méi)有轉(zhuǎn)入顯然是不可接受的。

2）一致性：無(wú)論是在事務(wù)前、事務(wù)中、事務(wù)后，數(shù)據(jù)庫(kù)始終處于一致的狀態(tài)。例如：轉(zhuǎn)賬前分別是2000和1000，總金額是3000，轉(zhuǎn)賬300后分別是1700和1300，總金額還是3000.這就叫做一致性。不一致就是在某個(gè)時(shí)間點(diǎn)查詢到的總金額不是3000.

3）隔離性：在某個(gè)時(shí)間段，肯定有很多人都在轉(zhuǎn)賬，每個(gè)人的轉(zhuǎn)賬都是在自己的事務(wù)中，所以在一個(gè)數(shù)據(jù)庫(kù)中，會(huì)有很多事物同時(shí)存在。雖然同時(shí)存在很多事物，但是事物之間不會(huì)相互影響。

4）持久性：如果事物提交成功，則數(shù)據(jù)修改永遠(yuǎn)生效，如果是回滾，則數(shù)據(jù)完全沒(méi)有沒(méi)修改，就相當(dāng)于沒(méi)有這件事情發(fā)生。

3、學(xué)會(huì)事物的控制

1）使用COMMIT和ROLLBACK實(shí)現(xiàn)事物控制

COMMIT：提交事物，把事物中對(duì)數(shù)據(jù)庫(kù)的修改進(jìn)行永久保存。

ROLLBACK：回滾事物，取消對(duì)數(shù)據(jù)庫(kù)所做的任何修改。

首先執(zhí)行插入數(shù)據(jù)。

執(zhí)行COMMIT提交事物，數(shù)據(jù)將會(huì)永久保存。

再次插入數(shù)據(jù)，并執(zhí)行rollback回滾。

查詢沒(méi)有發(fā)現(xiàn)70這一行數(shù)據(jù)

2）使用AUTOCOMMIT實(shí)現(xiàn)事物的自動(dòng)提交

Oracle提供了一種自動(dòng)提交DML操作的方式，這樣一旦用戶執(zhí)行了DML操作，如UPDATE，DELETE等，數(shù)據(jù)就會(huì)自動(dòng)提交。

例2：使用autocommit實(shí)現(xiàn)事物自動(dòng)提交，設(shè)置autocommit為ON

說(shuō)明：只要提前執(zhí)行了set  autocommit on 命令，數(shù)據(jù)就會(huì)自動(dòng)提交，及時(shí)執(zhí)行了回滾數(shù)據(jù)也會(huì)依然保存。

3）驗(yàn)證隔離性

（1）建立表yuangong，并插入數(shù)據(jù)。

此時(shí)insert記錄的事務(wù)并沒(méi)有提交，沒(méi)有提交事務(wù)就沒(méi)有真正的完成，此時(shí)還有rollback的機(jī)會(huì)。

（2）查詢一下

（3）然后打開(kāi)一個(gè)新的sqlplus會(huì)話，查看表時(shí)會(huì)發(fā)現(xiàn)并沒(méi)有新插入的記錄，這是事物的隔離性。

（4）在第一個(gè)sqlplus會(huì)話中提交事物

（5）提交之后才能在第二個(gè)會(huì)話中看到被插入的第四條記錄

4）驗(yàn)證持久性

一旦使用commit命令來(lái)結(jié)束某個(gè)事務(wù)，那么就必須保證數(shù)據(jù)庫(kù)不丟失這個(gè)事務(wù)。在事務(wù)進(jìn)行期間，隔離性的原則要求除了指定會(huì)話涉及的用戶之外的任何用戶都不能查看當(dāng)前所做的變化。不過(guò)事務(wù)一旦完成，所有用戶都必須能夠立即看到所做的變化，同時(shí)數(shù)據(jù)庫(kù)必須保證這些變化絕不會(huì)丟失。Oracle通過(guò)使用日志文件來(lái)滿足這個(gè)需求。日志文件具有兩種形式：聯(lián)機(jī)重做日志文件，歸檔重做日志文件。

一個(gè)正確配置的oracle數(shù)據(jù)庫(kù)是不可能丟失數(shù)據(jù)的。當(dāng)然用戶的錯(cuò)誤（包括不恰當(dāng)?shù)腄ML或刪除對(duì)象）也會(huì)造成數(shù)據(jù)的丟失。DDL語(yǔ)句有自動(dòng)提交功能（create、drop、truncate、alter）

（1）

（2）新建表students，并插入一條數(shù)據(jù)

（3）回滾事務(wù)

（4）再次寫入數(shù)據(jù)

（5）退出sqlplus

（6）在另外一個(gè)sqlplus中查看students表中的記錄，會(huì)發(fā)現(xiàn)新插入的lisi的記錄了。如果使用sqlplus工具更改了數(shù)據(jù)之后，正常退出sqlplus時(shí)，oracle會(huì)自動(dòng)提交事物。

關(guān)于事物的總結(jié)：

1. 需要注意的是，Commit:只是用來(lái)確認(rèn)這個(gè)數(shù)據(jù)已經(jīng)正式的修改了，不一定非得寫入硬盤，DBWn什么都不做。執(zhí)行commit命令時(shí)發(fā)生的所有物理操作時(shí)LGWR進(jìn)程將日志緩沖區(qū)的內(nèi)容寫入磁盤。DBWN進(jìn)程完全沒(méi)有執(zhí)行任何操作。DBWN進(jìn)程與提交事物處理沒(méi)有關(guān)系，不過(guò)最終DBWN進(jìn)程會(huì)將變化的數(shù)據(jù)塊寫入磁盤。

2. commit和rollback語(yǔ)句只應(yīng)于DML語(yǔ)句，我們無(wú)法回滾DDL語(yǔ)句。DDL語(yǔ)句一旦被執(zhí)行就會(huì)立即具有持久狀態(tài)。

3.自動(dòng)提交和隱式提交：oracle在某些情況下可以進(jìn)行自動(dòng)提交：執(zhí)行DDL語(yǔ)句是一種情況，退出某個(gè)用戶進(jìn)程也是一種自動(dòng)提交。 

二、索引

1、索引的含義

Oracle 數(shù)據(jù)庫(kù)對(duì)象又稱模式對(duì)象，數(shù)據(jù)庫(kù)對(duì)象是邏輯結(jié)構(gòu)的集合，最基本的數(shù)據(jù)庫(kù)對(duì)象是表，索引也是其中之一。其他數(shù)據(jù)庫(kù)對(duì)象包括：

索引是oracle的一個(gè)對(duì)象，是與表關(guān)聯(lián)的可選結(jié)構(gòu)，提供了一種快速訪問(wèn)數(shù)據(jù)的途徑，提高了數(shù)據(jù)庫(kù)檢索性能。索引使數(shù)據(jù)庫(kù)程序無(wú)需對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需要的數(shù)據(jù)。就像書(shū)的目錄，可以通過(guò)目錄快速查找所需信息，無(wú)需閱讀整本書(shū)。

2、索引的特點(diǎn)

適當(dāng)?shù)厥褂盟饕梢蕴岣卟樵兯俣?/span>

可以對(duì)表的一列或多列建立索引

建立索引的數(shù)量沒(méi)有限制

索引需要磁盤存儲(chǔ)，可以指定表空間，由oracle自動(dòng)維護(hù)

索引對(duì)用戶透明，檢索時(shí)是否使用索引由oracle自身決定

Oracle數(shù)據(jù)庫(kù)管理系統(tǒng)在訪問(wèn)數(shù)據(jù)時(shí)使用以下三種方式：

全表掃描

通過(guò)ROWID（行地址，快速訪問(wèn)表的一行）

使用索引

注：當(dāng)沒(méi)有索引或者不選擇使用索引時(shí)就用全表掃描的方式

3、索引的分類

1）B樹(shù)索引結(jié)構(gòu)

索引的頂部為根，其中包含指向下一級(jí)索引的項(xiàng)。下一級(jí)為分支塊，分支塊又指向索引中下一級(jí)的塊，最低一級(jí)的塊稱為葉節(jié)點(diǎn)，其中包含指向表數(shù)據(jù)行的索引項(xiàng)。葉節(jié)點(diǎn)為雙向連接，有助于按關(guān)鍵字值得升序和降序掃描索引。

例如：查詢id從2到31行的數(shù)據(jù)

上圖中使用索引遍歷過(guò)程如下：

先找到id<=50的分支塊，再找到30-40的分支塊，在找到id=31對(duì)應(yīng)的索引項(xiàng)，之后通過(guò)葉節(jié)點(diǎn)雙向鏈接，平行地找到包含id=2的索引塊，完成對(duì)id的查詢。

4、創(chuàng)建索引的語(yǔ)法

create [unique] index 索引名稱 on 表名（列名）[tablespace 表空間名稱]

解釋：

[unique]用于指定唯一索引，默認(rèn)情況下為非唯一索引

[tablespace]為索引指定表空間

1）創(chuàng)建標(biāo)準(zhǔn)索引

SQL> create index index_name on tablename(columnname)

     tablespace  index_tbs;

2）重建索引

SQL> alter index index_namerebuld;

3）合并索引碎片

SQL> alter index index_name coalesce

4）刪除索引

SQL> drop indexindex_name;

例1：

1）在雇員表（emp）中，為雇員名稱（ename）列創(chuàng)建b樹(shù)索引。

5、創(chuàng)建唯一索引

確保在定義索引的列中沒(méi)有重復(fù)值

Oracle 自動(dòng)在表的主鍵列上創(chuàng)建唯一索引

使用CREATE UNIQUE INDEX語(yǔ)句創(chuàng)建唯一索引

語(yǔ)法如下：

SQL> CREATE UNIQUE INDEX index_name

     ONtablename(columnname);

例：在薪水級(jí)別（salgrade）表中，為級(jí)別編號(hào)grade列創(chuàng)建唯一索引。

6、反向鍵索引

與常規(guī)B樹(shù)索引相反，反向鍵索引在保持列順序的同時(shí)反轉(zhuǎn)索引列的字節(jié)。反向鍵索引通過(guò)反轉(zhuǎn)索引鍵的數(shù)據(jù)值，使得索引的修改平均分布到整個(gè)索引樹(shù)上。主要應(yīng)用于所多個(gè)實(shí)例同時(shí)訪問(wèn)一個(gè)數(shù)據(jù)庫(kù)的場(chǎng)景中。如下圖：

如果在常規(guī)的B樹(shù)索引情況下，由于兩個(gè)雇員號(hào)empno索引在索引樹(shù)種的位置相近而處于同一個(gè)索引塊中，多個(gè)實(shí)例同時(shí)更新時(shí)會(huì)發(fā)生沖突，從而導(dǎo)致i/o訪問(wèn)上的瓶頸。所以這時(shí)候可以使用反向鍵索引。反向鍵索引通常建立在一些連續(xù)增長(zhǎng)的列上，如：編號(hào)。

例：在雇員emp表中，為雇員編號(hào)empno列創(chuàng)建反向鍵索引。CREATE INDEX emp_empno_reverse_idx ON emp(empno) REVERSE;

提示此列已經(jīng)建立索引了，執(zhí)行下面命令查詢索引有哪些：

下面查詢一下PK_EMP索引是為哪列創(chuàng)建的：

由上圖可以看出，PK_EMP索引是為empno列創(chuàng)建的，所以上面創(chuàng)建反向鍵索引創(chuàng)建不了，說(shuō)明相同的列不能創(chuàng)建多個(gè)索引。

所以要么把PK_EMP索引刪除，要么就保留這個(gè)索引，不創(chuàng)建反向鍵索引

那我們現(xiàn)在把原來(lái)的PK_EMP刪除

提示無(wú)法刪除，因?yàn)檫@個(gè)索引的表EMP有主鍵，想刪除索引，必須去掉主鍵，命令如下：

然后創(chuàng)建反向鍵索引：

查詢建立是否成功：

7、位圖索引

位圖索引適合低于基數(shù)的列，即該列的值是有限的幾個(gè)。例如：雇員表中的工種（job）列，即便是幾百萬(wàn)條雇員記錄，工種也是有限的。Job列可以作為位圖索引，類似的還有圖書(shū)表中的圖書(shū)類別列等。

圖索引不直接存儲(chǔ)ROWID，而是存儲(chǔ)字節(jié)位到ROWID的映射，減少響應(yīng)時(shí)間,節(jié)省空間占用。位圖索引不應(yīng)當(dāng)在頻發(fā)發(fā)生insert、update、delete操作的表上使用，這是因?yàn)閱蝹€(gè)位圖索引指向表的很多數(shù)據(jù)行，當(dāng)修改索引項(xiàng)時(shí)需要將其指向的數(shù)據(jù)行全部鎖定，這會(huì)嚴(yán)重降低數(shù)據(jù)庫(kù)的并發(fā)處理能力。位圖索引適合用于數(shù)據(jù)倉(cāng)庫(kù)和決策支持系統(tǒng)中。

例：在雇員emp表中，為工種（job）列創(chuàng)建位圖索引。

基本語(yǔ)法：

CREATE BITMAP INDEX weitu ON emp(job);

查詢一下建立是否成功:

8、組合索引

類似sqlserver的復(fù)合索引，在表內(nèi)多列上創(chuàng)建索引。索引中的列不必與表中的列順序一致，也不必相互鄰接。

例：雇員表中部門和職務(wù)列上的索引。組合索引的列最多包含32列。

9、基于函數(shù)的索引

需要?jiǎng)?chuàng)建的索引需要使用表中一列或多列的函數(shù)或表達(dá)式，也可以將基于函數(shù)的索引創(chuàng)建為B樹(shù)索引或位圖索引。

基本語(yǔ)法：

SQL> CREATE INDEX emp_ename_upper_idx

     ON tablename(UPPER(columnname));

例：在雇員（emp）表中，為雇員名稱（ename）列創(chuàng)建小寫函數(shù)索引。

現(xiàn)在建立基于函數(shù)的索引：

查詢一下建立是否成功:

10、創(chuàng)建索引的原則

頻繁搜索的列可以作為索引列

經(jīng)常排序，分組的列可以作為索引

經(jīng)常用作連接的列（主鍵/外鍵）可以作為索引

將索引放在一個(gè)單獨(dú)的表空間中，不要放在有回退段、臨時(shí)段和表的表空間中

對(duì)于大型索引而言，考慮使用NOLOGIN子句創(chuàng)建大型索引。

根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新組織索引，進(jìn)行碎片整理。

例：將索引放在一個(gè)單獨(dú)的表空間中

1）使用sys登錄創(chuàng)建表空間

2）修改索引到表空間。

3）查詢一下是否修改。

由上圖可以看出表空間已經(jīng)改為了new_tbs。

例2：使用nologging子句

11、查看索引列相關(guān)的信息：索引名、表名、索引列。

12、維護(hù)索引

1）重建索引

索引需要維護(hù)，如果建立了索引的表中有大量的刪除和插入操作，會(huì)使得索引很大，因?yàn)閯h除操作后，刪除值的索引空間不能被自動(dòng)重新使用，對(duì)于大表和DML操作很頻繁的表，索引的維護(hù)是很重要的。Oracle提供了rebuild指令來(lái)重建索引。使索引空間可以重用刪除值所占用的空間，使索引更加緊湊。

2）合并索引碎片

合并索引碎片可以釋放部分磁盤空間，是索引維護(hù)的一種重要方式，也是維護(hù)磁盤空間的方式，類似于磁盤碎片整理，把不用的空間釋放出來(lái)再利用。

3）刪除索引

三、視圖

1、概述

視圖是一個(gè)虛表，不占用物理空間，因?yàn)橐晥D本身的定義語(yǔ)句存儲(chǔ)在數(shù)據(jù)字典里，視圖中的數(shù)據(jù)是一個(gè)或多個(gè)實(shí)際表中獲得的。那些用于產(chǎn)生視圖的表叫做該視圖的基表。一個(gè)視圖也可以從另一個(gè)視圖中產(chǎn)生。

2、視圖的優(yōu)點(diǎn)：

1) 提供了另外一種級(jí)別的表安全性

2) 隱藏的數(shù)據(jù)的復(fù)雜性：一個(gè)視圖可能是用多表連接定義的，但用戶不需要知道多表連接的語(yǔ)句也可以查詢數(shù)據(jù)。

3) 簡(jiǎn)化的用戶的SQL命令：查詢視圖的時(shí)候不需要寫出復(fù)雜的查詢語(yǔ)句，只需要查詢視圖名稱即可。

4) 隔離基表結(jié)構(gòu)的改變：視圖創(chuàng)建好了之后，如果修改了表的結(jié)構(gòu)，也不會(huì)影響視圖的。

5) 通過(guò)重命名列，從另一個(gè)角度提供數(shù)據(jù)：例如在銷售系統(tǒng)中，每日下班前要對(duì)當(dāng)日數(shù)據(jù)進(jìn)行匯總，在銷售人員眼中，該匯總表成為日銷售統(tǒng)計(jì)表，在財(cái)務(wù)人眼中，該銷售表成為銷售日?qǐng)?bào)表。

3、創(chuàng)建視圖的語(yǔ)法：

1） CREATE [OR REPLACE] [FORCE] VIEW

    view_name[(alias[, alias]...)]

  ASselect_statement

  [WITH CHECKOPTION]

  [WITH READONLY];

解釋：

OR REPLACE:如果視圖已存在，此選項(xiàng)將重新創(chuàng)建該視圖。

FORCE:如果使用此關(guān)鍵字，則無(wú)論基表是否存在，都將創(chuàng)建視圖。

NOFORCE：這是默認(rèn)值，如果使用此關(guān)鍵字，則僅當(dāng)基表存在時(shí)才創(chuàng)建視圖。

VIEW_NAME：要?jiǎng)?chuàng)建視圖的名稱

Alias：指定由視圖的查詢所選擇的表達(dá)式或列的別名。別名的數(shù)目必須與視圖所選擇的表達(dá)式的數(shù)目相匹配。

Select_statement:select語(yǔ)句

WITH CHECK OPTION :此選項(xiàng)指定只能插入或更新視圖可以訪問(wèn)的行。術(shù)語(yǔ)constraint表示為CHECK OPTION約束指定的名稱。

WITH READ ONLY：此選項(xiàng)保證不能在此視圖上執(zhí)行任何修改操作。

2）視圖中的ORDER BY子句

可以在創(chuàng)建視圖時(shí)在SELECT語(yǔ)句中使用ORDERBY子句，以便按照特定的順序進(jìn)行排序，這樣，在查詢視圖時(shí)即使不使用ORDER BY子句，結(jié)果集也會(huì)按指定的順序進(jìn)行排列。

3）創(chuàng)建帶有錯(cuò)誤的視圖

如果在create view語(yǔ)句中使用FORCE選項(xiàng)，即使存在系列情況，oracle也會(huì)創(chuàng)建視圖。

視圖定義的查詢引用了一個(gè)不存在的表。

視圖定義的查詢引用了現(xiàn)有表中無(wú)效的列。

視圖的所有者沒(méi)有所需的權(quán)限。

在這些情況下，oracle僅檢查createview語(yǔ)句中的語(yǔ)法錯(cuò)誤。如果語(yǔ)法正確，將會(huì)創(chuàng)建視圖，并將視圖的定義存在數(shù)據(jù)字典中。但是，該視圖卻不能使用。這種視圖被認(rèn)為是“帶錯(cuò)誤創(chuàng)建”的?？墒褂肧HOWERRORS VIEW視圖名來(lái)查看錯(cuò)誤。

4、實(shí)驗(yàn)案例：對(duì)單表視圖的操作

1）連接到oracle，使用scott用戶登錄

2）創(chuàng)建表order_master

3）插入數(shù)據(jù)

4）創(chuàng)建訂單狀態(tài)為"p"的視圖，提示沒(méi)有創(chuàng)建視圖的權(quán)限。

5）授予創(chuàng)建視圖的權(quán)限（使用sys用戶登錄）

6）再次創(chuàng)建視圖

7）查詢視圖

8）通過(guò)視圖修改數(shù)據(jù)，將狀態(tài)為p的訂單修改為d，但是修改完成之后再次查詢視圖將查不出任何數(shù)據(jù)，因?yàn)樾薷牧藙?chuàng)建視圖時(shí)作為條件的列。

9）為了避免修改視圖后查詢不到的現(xiàn)象，使用with_check_option語(yǔ)句創(chuàng)建檢查約束，以防止上述情況發(fā)生，同時(shí)可以使用constraint指定約束名稱。

10）再次寫入數(shù)據(jù)，并且再次更新（出現(xiàn)違規(guī)提示）

11）看一下最終結(jié)果

5、實(shí)驗(yàn)案例：創(chuàng)建只讀視圖

1）使用read only創(chuàng)建只讀視圖

2）查詢視圖

3）再次更新視圖，提示無(wú)法對(duì)只讀視圖進(jìn)行dml操作。

6、實(shí)驗(yàn)案例：創(chuàng)建帶有錯(cuò)誤的視圖

1）使用force創(chuàng)建帶有錯(cuò)誤的視圖，其中venmast表不存在，但是也能創(chuàng)建成功。

2）創(chuàng)建表

3）重新編譯現(xiàn)有視圖，使其生效。

4）測(cè)試查詢視圖

7、創(chuàng)建order by 子句的視圖（查詢視圖之后會(huì)自動(dòng)排序）

然后再創(chuàng)建一個(gè)降序的：

8、DML語(yǔ)句和復(fù)雜視圖

DML語(yǔ)句是指用于修改數(shù)據(jù)的insert、delete和update語(yǔ)句。因?yàn)橐晥D是一個(gè)虛擬的表，所以這些語(yǔ)句也可以與視圖一同使用。一般情況下不通過(guò)視圖修改數(shù)據(jù)，而是直接修改基表，因?yàn)檫@樣條例更清晰。在視圖上使用DML語(yǔ)句有如下限制（相對(duì)于表）。

1)DML語(yǔ)句只能修改視圖中的一個(gè)基表。

2)如果過(guò)記錄的修改違反了基表的約束條件，則將無(wú)法更新視圖。

3)如果創(chuàng)建的視圖包含連接運(yùn)算符，DISTINCT運(yùn)算符、集合運(yùn)算符、聚合函數(shù)和groupby子句，則將無(wú)法更新視圖。

4)如果創(chuàng)建的視圖包含偽列表達(dá)式，則將無(wú)法更新視圖。

簡(jiǎn)單視圖基于單個(gè)基表，不包括函數(shù)和分組函數(shù)，那么可以在此視圖中進(jìn)行insert、update、delete操作，這些操作實(shí)際上在基表中插入、更新和刪除行。

復(fù)雜視圖從多個(gè)表提取數(shù)據(jù)，包括函數(shù)分組函數(shù)。復(fù)雜視圖不一定能進(jìn)行DML操作。

1）查詢視圖

通過(guò)數(shù)據(jù)字典user_views可以查詢當(dāng)前用戶下創(chuàng)建的視圖名稱。

2）刪除視圖

要從數(shù)據(jù)庫(kù)中刪除視圖，可以使用drop view命令。

9、物化視圖

1）物化視圖的含義

物化視圖是和普通視圖相對(duì)應(yīng)的。在oracle使用普通視圖時(shí)，它會(huì)重復(fù)執(zhí)行創(chuàng)建視圖的所有sql語(yǔ)句，如果這樣的sql語(yǔ)句含有多張表的連接或者order by 子句，而且表數(shù)據(jù)量很大，則會(huì)非常耗時(shí)，效率非常低下，為了解決這個(gè)問(wèn)題，oracle提出了物化視圖的概念。

簡(jiǎn)單的講，物化視圖就是具有物理存儲(chǔ)的特殊視圖，占據(jù)物理空間，就像表一樣。物化視圖是基于表、物化視圖等創(chuàng)建的。他需要和源表進(jìn)行同步，不斷地刷新物化視圖中的數(shù)據(jù)。

物化視圖中有兩個(gè)重要概念:查詢重寫和物化視圖的同步。

查詢重寫：

對(duì)sql語(yǔ)句進(jìn)行重寫，當(dāng)用戶使用sql語(yǔ)句對(duì)基表進(jìn)行查詢時(shí)，如果已經(jīng)建立了基于這些表的物化視圖，oracle將自動(dòng)計(jì)算和使用物化視圖來(lái)完成查詢，在某些情況下可以節(jié)約查詢時(shí)間，減少系統(tǒng)i/o。Oracle將這種查詢優(yōu)化技術(shù)成為查詢重寫。參數(shù)QUERY_REWRITE_ENABLED決定是否使用重寫查詢，該參數(shù)為布爾型。在創(chuàng)建物化視圖需要使用ENABLE_QUERY REWRITE來(lái)啟動(dòng)查詢重寫功能。通過(guò)SHOW指令可以查看該參數(shù)的值。

2）物化視圖的同步：

物化視圖是基于表創(chuàng)建的，所以當(dāng)基表變化時(shí)，需要同步數(shù)據(jù)以更新物化視圖中的數(shù)據(jù)，這樣保持物化視圖中的數(shù)據(jù)和基表的數(shù)據(jù)一致性。Oracle提供了兩種物化視圖的刷新方式，決定何時(shí)進(jìn)行刷新，即ON COMMIT方式和ON DEMAND方式。

ON COMMIT方式：指物化視圖在對(duì)基表的DML操作事務(wù)提交的同時(shí)進(jìn)行刷新。

ON DEMAND方式：指物化視圖在用戶需要的時(shí)候進(jìn)行更新，可以手工通過(guò)DBMS_MVIEW.REFRESH等方式來(lái)進(jìn)行刷新，也可以通過(guò)JOB定時(shí)進(jìn)行刷新。

選擇刷新方式之后，還需要選擇一種刷新類型，刷新類型指定刷新時(shí)基表與物化視圖如何實(shí)現(xiàn)數(shù)據(jù)的同步，oracle提供了以下4種刷新類型。

COMPLETE:對(duì)整個(gè)物化視圖進(jìn)行完全的刷新。

FAST：采用增量刷新，只刷新自上次刷新后進(jìn)行的修改。

FORCE:oracle在刷新時(shí)會(huì)去判斷是否可以進(jìn)行快速刷新，如果可以則采用FAST方式，否則采用COMPLETE方式。

NEVER:物化視圖不進(jìn)行任何刷新。

默認(rèn)值是FORCE刷新類型。

3）創(chuàng)建物化視圖

①　創(chuàng)建物化視圖的前提條件

具備創(chuàng)建物化視圖的權(quán)限，QUERY REWRITE的權(quán)限，以及對(duì)創(chuàng)建物化視圖所涉及的表的訪問(wèn)權(quán)限和創(chuàng)建表的權(quán)限。

以sys身份登錄，之后授予scott用戶創(chuàng)建物化視圖的權(quán)限。

②　創(chuàng)建物化視圖日志

物化視圖日志是用戶選擇了FAST刷新類型時(shí)使用的，以增量同步基表的變化。

對(duì)scott用戶的表DETP和表EMP創(chuàng)建物化視圖，所以對(duì)這兩個(gè)基表創(chuàng)建物化視圖日志。

③　創(chuàng)建物化視圖語(yǔ)句

通過(guò)create materializedview 語(yǔ)句創(chuàng)建物化視圖，需要注意各個(gè)參數(shù)的含義

查詢物化視圖：

④　刪除物化視圖

與刪除普通視圖相似，需要添加一個(gè)materialized關(guān)鍵字。

再次查詢視圖，提示視圖不存在。

四、序列

序列是用來(lái)生成唯一、連續(xù)的整數(shù)數(shù)據(jù)庫(kù)對(duì)象。序列通常用來(lái)自動(dòng)生成主鍵或唯一鍵的值。序列可以按升序排列，也可以按降序排列，與excel的自動(dòng)排序，以及sqlserver的標(biāo)識(shí)符是一樣的。

1、創(chuàng)建序列

1）使用sys登錄授予scott創(chuàng)建序列的權(quán)限。

例1：在scott用戶創(chuàng)建序列號(hào)，從序號(hào)1開(kāi)始，每次增加1，最大為2000，不循環(huán)，再增加會(huì)報(bào)錯(cuò)。

2、訪問(wèn)序列

創(chuàng)建了序列之后，可以通過(guò)NEXTVAL和CURRVAL偽列來(lái)訪問(wèn)該序列的值?？梢詮膫瘟兄羞x擇值。但是不能操縱他們的值。

NETXVAL：創(chuàng)建序列后第一次使用NEXTVAL時(shí)，將返回該序列的初始值。以后再引用NETXVAL時(shí)，將使用INCREMENT BY子句的值來(lái)增加序列值，并返回這個(gè)新值。

CURRVAL:返回序列的當(dāng)前值，即最后一次引用NEXTVAL時(shí)返回的值。

例2：在玩具表中，需要標(biāo)識(shí)列toyid作為標(biāo)識(shí)，不需要有任何含義，可以做為主鍵。

1）創(chuàng)建表

2）插入數(shù)據(jù)

3）查詢數(shù)據(jù)

4）查看序列當(dāng)前值

Currval返回序列的當(dāng)前值，即最后一次引用NEXTVAL時(shí)返回的值。

5）測(cè)試currval

3、更改序列

Alter sequence命令用于修改序列的定義。如果要進(jìn)行下列操作，則會(huì)修改序列。

設(shè)置或刪除MINVALUE 或MAXVALUE

修改增量值

修改緩存中的序列號(hào)的數(shù)目

不能更改序列的START WITH參數(shù)

例3： 設(shè)置一個(gè)新的maxvalue，并為xulie序列打開(kāi)了cycle。

例4：修改序列為沒(méi)有最大封頂值

例5：將每次增量設(shè)置為10

4、查看序列

可以通過(guò)查詢名為user_sequences的數(shù)據(jù)字典視圖，來(lái)獲取用戶所創(chuàng)建的序列的詳細(xì)信息。

5、刪除序列

DROP SEQUENCE命令用于刪除序列。

五、同義詞

同義詞是對(duì)象的一個(gè)別名，不占用任何的實(shí)際存儲(chǔ)空間，只在oracle的數(shù)據(jù)字典中保存其定義描述，在使用同義詞時(shí)，oracle會(huì)將其翻譯為對(duì)應(yīng)對(duì)象的名稱。

1、同義詞的用途

1）簡(jiǎn)化sql語(yǔ)句

如果用戶創(chuàng)建的表的名字很長(zhǎng)，可以為這個(gè)表創(chuàng)建一個(gè)oracle同義詞來(lái)簡(jiǎn)化語(yǔ)句。

2）隱藏對(duì)象的名稱和所有者

多用戶協(xié)同開(kāi)發(fā)中，可以屏蔽對(duì)象的名稱及持有者。如果沒(méi)有同義詞，當(dāng)操作其他用戶的表時(shí)，必須通過(guò)“用戶名.表名”的形式操作，采用了oracle同義詞之后就可以隱藏掉用戶名。例如：用戶user1要訪問(wèn)用戶的SCOTT的EMP表，必須使用SCOTT.emp來(lái)引用。如果為用戶創(chuàng)建一個(gè)名為emp的同義詞代表SCOTT.emp，那么user1就可以用該同義詞像訪問(wèn)自己的表一樣引用SCOTT.emp了。

3）為分布式數(shù)據(jù)庫(kù)的遠(yuǎn)程對(duì)象提供位置透明性

要完成遠(yuǎn)程對(duì)象的訪問(wèn)，先要了解數(shù)據(jù)庫(kù)連接的概念。數(shù)據(jù)庫(kù)鏈接是一個(gè)命名的對(duì)象，說(shuō)明一個(gè)數(shù)據(jù)庫(kù)到另一個(gè)數(shù)據(jù)庫(kù)的路徑，通過(guò)其可以實(shí)現(xiàn)不同的數(shù)據(jù)庫(kù)之間的通信。同義詞在數(shù)據(jù)庫(kù)鏈接中的作用就是提供位置透明性。

4）提供對(duì)數(shù)據(jù)庫(kù)對(duì)象的公共訪問(wèn)

公有同義詞只是為數(shù)據(jù)庫(kù)對(duì)象定義了一個(gè)公共的別名，即其他用戶都可以通過(guò)這個(gè)別名訪問(wèn)，但能夠通過(guò)該別名訪問(wèn)成功，還要看是否已經(jīng)具有數(shù)據(jù)庫(kù)對(duì)象的訪問(wèn)權(quán)限。

2、同義詞的分類

同義詞分為以下兩類：私有同義詞和公有同義詞

私有同義詞只能在其模式內(nèi)訪問(wèn)，且不能與當(dāng)前模式的對(duì)象同名

公有同義詞可被所有的數(shù)據(jù)庫(kù)用戶訪問(wèn) 

2-1：私有同義詞

私有同義詞只能被當(dāng)前模式的用戶訪問(wèn)，私有同義詞名稱不可與當(dāng)前模式的對(duì)象名稱相同。要在自身的模式創(chuàng)建私有同義詞，用戶必須擁有create synonym系統(tǒng)權(quán)限。要在其他用戶模式創(chuàng)建私有同義詞，用戶必須擁有createany  synonym系統(tǒng)權(quán)限。

2）創(chuàng)建私有同義詞的語(yǔ)法如下：

CREATE [OR REPLACE] SYNONYM [schema.]synonym_name FOR[schema.]object_name;

[OR REPLACE]:在同義詞存在的情況下替換該同義詞

synonym_name：要?jiǎng)?chuàng)建同義詞的名稱

object_name:指定要為之創(chuàng)建同義詞的對(duì)象的名稱。

例1：在SYSTEM模式下創(chuàng)建私有同義詞訪問(wèn)SCOTT模式下EMP表。

1）以SYSTEM用戶身份登錄數(shù)據(jù)庫(kù)，并訪問(wèn)SCOTT下的EMP表。

2）以system身份登錄數(shù)據(jù)庫(kù),創(chuàng)建同義詞。

3）訪問(wèn)同義詞tyc，實(shí)際訪問(wèn)的是SCOTT的emp表（隱藏了真實(shí)的表名，提高了安全性）

例2：訪問(wèn)網(wǎng)絡(luò)服務(wù)名為orclsv的遠(yuǎn)程數(shù)據(jù)庫(kù)中的表scott.emp

（我這里只有一臺(tái)服務(wù)器，所以我就把自己當(dāng)做遠(yuǎn)程服務(wù)器，效果是一樣的）

1）以SYSTEM用戶身份登錄數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)連接dblink_sw_orcl來(lái)連接遠(yuǎn)程數(shù)據(jù)庫(kù)，其中遠(yuǎn)程數(shù)據(jù)庫(kù)用戶名為system，密碼為123456，本地網(wǎng)絡(luò)服務(wù)器名為orcl，最后查詢遠(yuǎn)程數(shù)據(jù)庫(kù)中的表emp。

2）創(chuàng)建私有同義詞bieming作為遠(yuǎn)程數(shù)據(jù)庫(kù)表emp的別名

3）訪問(wèn)同義詞bieming，對(duì)應(yīng)的是遠(yuǎn)程數(shù)據(jù)庫(kù)中的表。

2-2：公有同義詞

公有同義詞被所有的數(shù)據(jù)庫(kù)訪問(wèn)。公有同義詞可以隱藏基表的身份，并降低sql語(yǔ)句的復(fù)雜性。要?jiǎng)?chuàng)建公有公有同義詞，用戶必須擁有create public SYNOYM的系統(tǒng)權(quán)限。

例3：在scott模式下對(duì)部門表dept創(chuàng)建公有同義詞public_sy_dept,目的是使其他用戶可以直接訪問(wèn)public_sy_dept。

注意：如果不創(chuàng)建公有同義詞，那么其他用戶訪問(wèn)scott模式下創(chuàng)建的同義詞，一定要加scott前綴，即SCOTT.xxx。如果創(chuàng)建了公有同義詞，同義詞有了公有屬性，那么其他用戶都可以使用了。

1）以system用戶身份登錄數(shù)據(jù)庫(kù)，將創(chuàng)建公有同義詞權(quán)限給SCOTT用戶。

2）以sys用戶身份登錄，創(chuàng)建一個(gè)新用戶zpf

3）以scott用戶身份登錄數(shù)據(jù)庫(kù)，并把查詢tmp權(quán)限賦予給zpf

4)創(chuàng)建公有同義詞public_dept作為scott用戶dept表的別名

5）以sys用戶的身份登錄數(shù)據(jù)庫(kù)，賦予給zpf有登錄數(shù)據(jù)庫(kù)的權(quán)限

6）以zpf身份登錄數(shù)據(jù)庫(kù)

3、刪除同義詞

要?jiǎng)h除同義詞，用戶必須擁有相應(yīng)的權(quán)限。

例：刪除同義詞tyc和公有同義詞public_dept，可以執(zhí)行如下語(yǔ)句。

1)以sys身份登錄，賦予zpf有刪除私有和公有同義詞的權(quán)限

2）以zpf用戶身份登錄，來(lái)刪除system創(chuàng)建的私有同義詞tyc和公有同義詞public_dept

六、分區(qū)表

1、分區(qū)表的含義

Oracle允許把一個(gè)表中的所有行分成幾個(gè)部分，并將它們存儲(chǔ)在不同的表空間，分成的每一部分成為一個(gè)分區(qū)，被分區(qū)的表成為分區(qū)表。

對(duì)于包含大量數(shù)據(jù)的表來(lái)說(shuō)，分區(qū)很有用，優(yōu)點(diǎn)有以下幾點(diǎn)：

1）改善表的查詢性能。在對(duì)表進(jìn)行分區(qū)后，用戶執(zhí)行sql查詢時(shí)可以只訪問(wèn)表中的特定分區(qū)而非整個(gè)表。

2）表更容易管理。因?yàn)榉謪^(qū)表的數(shù)據(jù)存儲(chǔ)在多個(gè)部分中，按分區(qū)加載和刪除數(shù)據(jù)比在表中加載和刪除更容易。

3）便于備份和恢復(fù)。可以獨(dú)立地備份和恢復(fù)每個(gè)分區(qū)。

4）提高數(shù)據(jù)安全性。將不同的分區(qū)分布在不同的磁盤，可以減少所有分區(qū)的數(shù)據(jù)同時(shí)損壞的可能性。

復(fù)合一下什么條件的表可以建成分區(qū)表：

1）數(shù)據(jù)量大于2GB。

2）已有的數(shù)據(jù)和新添加的數(shù)據(jù)有明顯的界限劃分。

表分區(qū)對(duì)用戶是透明的，及應(yīng)用程序可以不知道表已被分區(qū)，在更新和查詢分區(qū)表時(shí)當(dāng)做普通表來(lái)操作，但oracle優(yōu)化程序知道表已被分區(qū)。

注意：要分區(qū)的表不能具有LONG和LONG RAW數(shù)據(jù)類型的列。

2、分區(qū)表的分類

Oracle提供的分區(qū)方法有范圍分區(qū)、列表分區(qū)、散列分區(qū)、復(fù)合分區(qū)、間隔分區(qū)和虛擬列分區(qū)等。其中間隔分區(qū)和虛擬列分區(qū)是oracle11g的新特性

范圍分區(qū)案例：

是一種常用的表分區(qū)方法，它是oracle引進(jìn)的第一個(gè)分區(qū)類型。范圍分區(qū)用于可以根據(jù)某些條件按范圍分開(kāi)的數(shù)據(jù)。如果數(shù)據(jù)均勻的分布在所建立的不同的范圍內(nèi)，那么使用范圍分區(qū)將得到最好的分區(qū)效果。范圍可以基于順序數(shù)或部分?jǐn)?shù)，范圍分區(qū)技術(shù)通?；跁r(shí)間（例如月或季度）

1）創(chuàng)建表并且分區(qū)，以age分區(qū)

2）向表中插入數(shù)據(jù)

3）查詢P1區(qū)的數(shù)據(jù)

查詢p200區(qū)的數(shù)據(jù)

4）如果向表中插入以下記錄，會(huì)提示插入的分區(qū)關(guān)鍵字未映射到任何分區(qū)

5）按范圍分區(qū)時(shí)，如果某些記錄暫時(shí)無(wú)法預(yù)測(cè)范圍，可以創(chuàng)建maxvalue分區(qū)，所有不在指定范圍內(nèi)的記錄都會(huì)被存儲(chǔ)到maxvalue所在的分區(qū)中。

6）再次插入以下數(shù)據(jù)

7）查詢

8）查看所有分區(qū)的命令

一般創(chuàng)建范圍分區(qū)時(shí)都會(huì)將最后一個(gè)分區(qū)設(shè)置為maxvalue，將其他數(shù)據(jù)落入此分區(qū)，一旦需要時(shí)可以利用拆分分區(qū)的技術(shù)將需要的數(shù)據(jù)從最后一個(gè)分區(qū)分離出入，單獨(dú)形成一個(gè)分區(qū)，如果沒(méi)有創(chuàng)建最大的分區(qū)，插入的數(shù)據(jù)查出范圍就會(huì)報(bào)錯(cuò)。如果插入的數(shù)據(jù)是分區(qū)鍵上的值，則該數(shù)據(jù)落入下一個(gè)分區(qū)，例如：插入數(shù)據(jù)為20就會(huì)落入p2分區(qū)。