數(shù)據(jù)庫原理及應用-數(shù)據(jù)庫對象

《數(shù)據(jù)庫系統(tǒng)及應用》主講:陳業(yè)斌教授安徽工業(yè)大學零一索引零二視圖零三數(shù)據(jù)庫三級模式結構零四同義詞零五序列零六PL/SQL編程零七存儲過程零八觸發(fā)器目錄第七講數(shù)據(jù)庫對象索引如果一個數(shù)據(jù)表存在海量地數(shù)據(jù)記錄,當對表執(zhí)行指定條件地查詢時,常規(guī)地查詢方法是把表地所有記錄都讀取出來,然后再把每一條記錄與查詢條件行對比,最后返回滿足條件地記錄;上述操作地時間開銷與I/O開銷都非常大;為了提高查詢效率,可以考慮通過建立索引來達到。索引相當于一本書地目錄。索引一.索引地概念索引是根據(jù)表一列或若干列按照一定地順序建立地列值與記錄之間地對應關系表,默認地情況下,系統(tǒng)創(chuàng)建地為B樹索引。九八一零一九八一零二九八一零三九八二零一九八二零二九八一零一王萍九八二零一黎明九八一零二王萍九八二零二成功九八一零三陽光索引表學生表索引二.創(chuàng)建索引地目地與方式使用索引加速數(shù)據(jù)查詢地速度,減少磁盤I/O地次數(shù),但同時也會增加系統(tǒng)維護索引地開銷;創(chuàng)建索引方式:（一）隱式創(chuàng)建:DBMS一般會在創(chuàng)建PRIMARYKEY與UNIQUE約束列上自動建立索引;（二）顯式創(chuàng)建:使用CREATEINDEX語句創(chuàng)建。索引三.創(chuàng)建索引CREATE[UNIQUE]INDEX<索引名>ON表名(<列名>[ASC|DESC])索引可建立在一列或多列上,各列名之間用逗號分隔;ASC(升序)或DESC指定索引值地排列次序,缺省為ASC;[UNIQUE]指明此索引地每一個索引值只對應唯一地數(shù)據(jù)記錄。（唯一）建立了索引后,DBMS負責使用與維護索引。索引四.刪除索引一般格式:DROPINDEX<表名>.<索引名稱>說明:用戶只能刪除自己建立地索引;索引例一:對XS表地姓名列創(chuàng)建索引。例二:對XS表地姓名列創(chuàng)建唯一索引。例三:為CJ表創(chuàng)建學號列與課程號列地復合索引createindexxs_sname_idxonxs(sname);createuniqueindexxs_sname_uniq_idxonxs(sname);createindexcj_idxoncj(sno,o);索引五.索引地說明表越大,索引越能有效地改善查詢地響應時間,對于少于一零零行地表建立索引可能不合算;對WHERE子句最常使用地列建立索引。對連接列建立索引,也可大大地改善連接查詢地速度;只要可能,盡量將索引列定義為NOTNULL;一個表上最多可創(chuàng)建一六個索引;應根據(jù)需要建立索引,應當在查詢速度與插入更新速度之間行權衡。通常不要在一個表上建立多于三個索引。視圖簡單地說,視圖可以看成是一個窗口,它所反映地是一個表或若干表地局部數(shù)據(jù)。視圖一經(jīng)定義,用戶就可以把它當作表一樣來查詢數(shù)據(jù)。視圖與基本表不同,視圖是一個虛表,即視圖儲存地是查詢語句而不是查詢結果。視圖是定義在基本表上地,也可以定義在視圖上;一個視圖可在幾個表或視圖上建立,一個表或視圖也可建立多個視圖。視圖二視圖一基本表一基本表二基本表三視圖一.視圖定義CREATE[ORREPLACE]VIEW<視圖名>AS<子查詢>[WITHCHECKOPTION][WITHREADONLY]執(zhí)行CREATEVIEW語句時只是把視圖定義存入數(shù)據(jù)字典,并不執(zhí)行其地SELECT語句。WITHCHECKOPTION表示對視圖行UPDATE,INSERT與DELETE操作時要保證更新,插入與刪除地行滿足視圖定義地謂詞條件。如果子查詢包含有計算列,則需要指定列名（別名）。視圖例一:建立計算機系地學生視圖xs_dept_view。例二:把學生地學號及它地均成績定義為視圖cj_avg_view。createorreplaceviewxs_dept_viewasselect*fromxswheredept='計算機';createorreplaceviewcj_avg_view(sno,avg_grade)asselectsno,avg(grade)fromcjgroupbysno;視圖用戶

查詢視圖基表例:求均成績?yōu)榫帕惴忠陨系貙W生地學號與成績。二.視圖地使用

Select*fromcj_avg_viewwhereavg_grade>九零;視圖三.視圖地更新對視圖地更新最終要轉(zhuǎn)換成對基本表地更新,但實際上,在RDBMS,并非所有地視圖都是可以更新地,有些視圖地更新不能唯一地有意義地轉(zhuǎn)換成對基本表地更新。

例:將‘零零一一零一’學生地均成績修改為九零分。視圖四.視圖地刪除DROPVIEW<視圖名>例:刪除視圖cj_avg_view

Dropviewcj_avg_view;數(shù)據(jù)庫地三級模式結構數(shù)據(jù)庫應用B應用D應用E外模式一外模式二外模式n模式應用C內(nèi)模式DBMSOS應用A外模式/模式映像模式/內(nèi)模式映像外部級單個用戶視圖概念級全局視圖內(nèi)部級存儲視圖...用戶

視圖DBA視圖內(nèi)部

視圖數(shù)據(jù)庫地三級模式結構數(shù)據(jù)庫三層模式/兩級映象地好處方便了用戶地使用,簡化了用戶地接口;實現(xiàn)了數(shù)據(jù)地獨立;物理數(shù)據(jù)獨立邏輯數(shù)據(jù)獨立有利于數(shù)據(jù)享;有利于數(shù)據(jù)地安全與保密;同義詞一.概述通過同義詞可以給表,索引,視圖等數(shù)據(jù)庫對象創(chuàng)建一個別名,來隱藏一些信息,提供一定地安全。當DBA改變數(shù)據(jù)庫對象地名稱時,通過同義詞可以避免前臺應用程序地改變。同義詞分公有與私有,公有同義詞每個用戶都能使用,私有同義詞需要具有訪問權限地用戶才能使用。同義詞二.格式:CREATE[ORREPLACE][PUBLIC]SYNONYM<同義詞名>FOR<對象名>例一:創(chuàng)建公同義詞cj_v訪問視圖cj_avg_view例二:創(chuàng)建私有同義詞cj_view訪問視圖cj_avg_view

createorreplacepublicsynonymcj_vforcj_avg_view;

createorreplacesynonymcj_viewforcj_avg_view;序列一.概述序列是ORACLE提供地用于生成一系列唯一數(shù)值地數(shù)據(jù)庫對象,以實現(xiàn)自動提供唯一地主鍵值。序列可以為多個用戶并發(fā)環(huán)境使用,為多個用戶生成不重復地順序數(shù)值。序列二.格式:CREATESEQUENCE<對象名>STARTWITHnINCREMENTBYn（二）目地:創(chuàng)建自增主鍵值（三）取值:<對象名>.nextval<對象名>.currval序列例:創(chuàng)建序列xs_seq用于生成學生表地學號。

createsequencexs_seqmaxvalue九九九九九九startwith九九九零零零incrementby一cache五零;insertintoxs(sno,sname)values(xs_seq.nextval,'aaa');selectxs_seq.currvalfromdual;PL-SQL編程一.PL/SQL地特點PL(ProcedureLanguage)/SQL）是Oracle在數(shù)據(jù)庫引入地一種過程化地編程語言。PL/SQL是對SQL地擴充:具有為程序開發(fā)而設計地特;在PL/SQL可以使用變量,行流程控制,來實現(xiàn)比較復雜地業(yè)務邏輯;PL/SQL嵌入到Oracle服務器地,可以把它看作Oracle服務器內(nèi)地一個引擎,所以具有很高地執(zhí)行效率。PL-SQL編程二.PL/SQL程序結構Declare [變量聲明]begin[語句序列]exception[異常處理程序]End;/PL-SQL編程一.[字段名]%TYPE類型聲明一個與指定表地指定列地相同類型地變量,用于存放一個字段地值。二.[表名]%ROWTYPE類型聲明一個與指定表地行結構相同地變量,用于存放一條記錄。三.CURSOR類型聲明一個表對象類型地變量,用于存放一個查詢結果集。三.PL/SQL特殊類型地變量PL-SQL編程注意:一.在PL/SQL程序,SELECT語句返回地數(shù)據(jù)是一行時,SELECT語句總是與INTO相配合,INTO后跟用于接收查詢結果地變量,形式如下:SELECT列名一,列名二...

INTO變量一,變量二...

FROM表名WHERE條件;二.當程序要接收返回地多行結果時,可以采用游標變量來存放。PL-SQL編程例一:查詢學號為"零零一一零一"地學生地學號與姓名。setserveroutputon;DECLAREstunoxs.sno%type;stunamexs.sname%type;BEGINselectsno,snameintostuno,stunamefromxswheresno='零零一一零一';DBMS_OUTPUT.put_line(stuno||stuname);END;/PL-SQL編程例二:查詢學號為"零零一一零一"地學生地信息。setserveroutputon;DECLARE Stuxs%rowtype;BEGIN select*intostufromxswheresno='零零一一零一'; DBMS_OUTPUT.put_line(stu.sno||stu.sname);END;/游標游標（Cursor）:用來查詢數(shù)據(jù)庫,獲取記錄集合（結果集）地指針,可以讓開發(fā)者一次訪問一行或多行結果集,在每條結果集上作操作。游標是SQL地一個內(nèi)存工作區(qū),由系統(tǒng)或用戶以變量地形式定義。游標地作用就是用于臨時存儲從數(shù)據(jù)庫提取地數(shù)據(jù)塊。在某些情況下,需要把數(shù)據(jù)從存放在磁盤地表調(diào)到計算機內(nèi)存行處理,最后將處理結果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理地速度才會提高,否則頻繁地磁盤數(shù)據(jù)換會降低效率。游標CURSOR就是PL/SQL地一種實現(xiàn)對表地對象化操作方法。一分為兩種:顯式游標:當查詢返回結果超過一行時,此時用戶不能使用selectinto語句,就需要一個游標來處理結果集。隱式游標:在執(zhí)行SQL語句時,ORACLE系統(tǒng)會自動產(chǎn)生一個隱式游標,主要用于處理數(shù)據(jù)操縱語句（INSERT與DELETE語句）地執(zhí)行結果,當使用隱式游標地屬時,在屬名前加上隱式游標地默認名SQL。一.游標地類型游標二.游標地定義游標定義格式:

CURSOR游標名isSelect語句;游標顯式游標有兩種使用方式:游標變量循環(huán);游標for循環(huán)。三.游標地使用游標游標變量循環(huán)格式:open游標;loop

fetch

游標

into游標變量;--處理語句;exit

when游標%notfound;endloop;close游標;游標FOR循環(huán)格式:for游標變量

in游標loop

--處理語句;endloop;游標(一)%FOUND:布爾型,如果SQL語句至少影響一行數(shù),則%FOUND等于true,否則等于false。(二)%NOTFOUND:布爾型,與%FOUND相反。(三)%ROWCOUNT:整型,返回受SQL語句影響地行數(shù)。(四)%ISOPEN:布爾型,判斷游標是否被打開,如果打開%ISOPEN等于true,否則等于false。三.游標變量游標例一:查詢所有學生地學號與姓名。（利用游標變量循環(huán)）DECLARE stuxs%rowtype; cursorcur_stuisselect*fromxs;BEGIN opencur_stu; loop fetchcur_stuintostu; exitwhencur_stu%notfound; DBMS_OUTPUT.put_line(stu.sno||stu.sname); endloop; closecur_stu;END;游標例二:查詢所有學生地學號與姓名。（利用for循環(huán)）DECLARE cursorcur_stuisselect*fromxs;BEGIN forstucurincur_stuloop DBMS_OUTPUT.put_line(stucur.sno||stucur.sname); endloop;END;BEGIN forstucurinselect*fromxsloop DBMS_OUTPUT.put_line(stucur.sno||stucur.sname); endloop;END;游標例三:修改所有學生地總學分,返回修改地記錄數(shù)。（利用隱式游標）BEGINUPDATExsSETtotalcredit=totalcredit+一;IFSQL%notfoundthenDBMS_OUTPUT.put_line('沒有記錄被更改');ESLEDBMS_OUTPUT.put_line('有'||sql%rowcount||'條記錄被更改');ENDIF;END;異常處理在編寫PL/SQL程序時,避免不了會發(fā)生一些錯誤.對于出現(xiàn)地這些錯誤,ORACLE通常采用異常來處理,異常處理代碼通常放在PL/SQL塊地EXCEPTION代碼塊,根據(jù)異常產(chǎn)生地機制與原理,可將ORACLE系統(tǒng)異常分為兩大類:一.預定義異常: ORACLE系統(tǒng)自身為用戶提供地,以便檢查用戶代碼失敗一般原因。二.自定義異常: 用戶自己定義地異常。異常處理錯誤名稱錯誤代碼異常號錯誤含義INVALID_CURSORORA_零一零零一-零一零零一試圖使用沒有打開地游標DUP_VAL_ON_INDEXORA_零零零零一

-零零零零一保存重復值到惟一索引約束地列ZERO_DIVIDEORA_零一四七六-零一四七六發(fā)生除數(shù)為零地除法錯誤INVALID_NUMBERORA_零一七二二-零一七二二試圖對無效字符行數(shù)值轉(zhuǎn)換ROWTYPE_MISMATCHORA_零六五零四-零六五零四主變量與游標地類型不兼容VALUE_ERRORORA_零六五零二-零六五零二轉(zhuǎn)換,截斷或算術運算發(fā)生錯誤TOO_MANY_ROWSORA_零一四二二-零一四二二SELECT…INTO…語句返回多于一行地數(shù)據(jù)NO_DATA_FOUNDORA_零一四零三-零一四零三SELECT…INTO…語句沒有數(shù)據(jù)返回一.Oracle預定義異常異常處理二.用戶定義異常自己定義異常類型?？梢栽诼暶鞑糠侄x新地異常類型。語法如下:錯誤名EXCEPTION;用戶定義地錯誤不能由系統(tǒng)來觸發(fā),需要由程序顯式地觸發(fā),觸發(fā)地語法是:RAISE錯誤名;異常處理處理部分位于程序地可執(zhí)行部分之后,是由WHEN語句引導地多個分支構成地。EXCEPTION

WHEN錯誤一[OR錯誤二]THEN

語句序列一

WHEN錯誤三[OR錯誤四]THEN

語句序列二

……

WHENOTHERS

語句序列n

END;三.異常處理語法格式異常處理例一:查詢學號為"零零一一零一"地學生地信息。DECLARE stunoxs.sno%type; stunamexs.sname%type;BEGIN selectsno,snameintostuno,stunamefromxs; DBMS_OUTPUT.put_line(stuno||stuname);EXCEPTIONWHENno_data_foundthendbms_output.put_line('數(shù)據(jù)沒找到');WHENtoo_many_rowsthendbms_output.put_line('結果集超過一行');END;異常處理例二:CJ表地GRADE列不能插入NULL值。DECLARE null_expexception; stucjcj%rowtype;BEGINstucj.sno:='零零一二四一';stucj.o:='二零六';insertintocjvalues(stucj.sno,stucj.o,stucj.grade);ifstucj.gradeisnullthenraisenull_exp;endif;EXCEPTIONWHENnull_expthendbms_output.put_line('成績不能為空值');rollback;WHENothersthendbms_output.put_line('其它異常');END;存儲過程存儲過程與函數(shù)也是一種PL/SQL塊,是存入數(shù)據(jù)庫地PL/SQL塊。但存儲過程與函數(shù)不同于已經(jīng)介紹過地PL/SQL程序,我們通常把PL/SQL程序稱為無名塊,而存儲過程與函數(shù)是以命名地方式存儲于數(shù)據(jù)庫地。其優(yōu)點如下:存儲過程在服務器端運行,執(zhí)行速度快;確保數(shù)據(jù)庫地安全,只有被授權地用戶或創(chuàng)建者本身才能執(zhí)行存儲過程;簡化輸入;可以重復執(zhí)行.存儲過程一.創(chuàng)建存儲過程createorreplaceprocedure<過程名>(參數(shù)[in|out|inout]數(shù)據(jù)類型,…）as[變量聲明;]begin執(zhí)行語句;[exception錯誤處理部分]end;/存儲過程二.執(zhí)行存儲過程方法一:EXECUTE模式名.存儲過程名[(參數(shù)一,…)];方法二:BEGIN

模式名.存儲過程名[(參數(shù)一,…)];

END;存儲過程例一:根據(jù)學號與課程號刪除學生成績信息。CREATEORREPLACEPROCEDUREPRO_DELCJ(stunoinxs.sno%type,stuoinkc.o%type)isBEGINdeletefromcjwheresno=stunoando=stuo;exceptionwhenno_data_foundthendbms_output.put_line('數(shù)據(jù)沒找到');whenothersthendbms_output.put_line('產(chǎn)生異常');END;存儲過程例二:根據(jù)學號與課程號查詢學生成績信息。CREATEORREPLACEPROCEDUREPRO_SELGRADE(stunoinxs.sno%type,stuoinkc.o%type,stugradeoutcj.grade%type)isBEGINselectgradeintostugradefromcjwheresno=stunoando=stuo;exceptionwhenno_data_foundthendbms_output.put_line('數(shù)據(jù)沒找到');whenothersthendbms_output.put_line('產(chǎn)生異常');END;存儲過程例二:執(zhí)行。DECLAREgradecj.grade%type;BEGINpro_selgrade('零零一二四一','一零一',grade);dbms_output.put_line(grade);END;/觸發(fā)器

觸發(fā)器（Trigger）是一種特殊類型地存儲過程。觸發(fā)器不同于存儲過程。觸發(fā)器主要是通過觸發(fā)而被系統(tǒng)自動調(diào)用執(zhí)行地,而存儲過程是通過存儲過程名字而被直接調(diào)用。觸發(fā)器通常是與基本表緊密聯(lián)系在一起地,可以看作是基本表定義地一部分。觸發(fā)器是在特定表上行定義地,該表也稱為觸發(fā)器表。當有針對觸發(fā)器表地操作時,例如,在表插入（Insert）,刪除（Delete）,修改（Update）數(shù)據(jù)時,那么觸發(fā)器就自動觸發(fā)執(zhí)行。觸發(fā)器觸發(fā)器可分為三類:DMLDDL數(shù)據(jù)庫觸發(fā)器類型可分為四類:DML觸發(fā)器DDL觸發(fā)器替代觸發(fā)器數(shù)據(jù)庫觸發(fā)器本課程只介紹DML觸發(fā)器一.觸發(fā)器類型觸發(fā)器種類關鍵字含義DML(三種)INSERT在表或視圖插入數(shù)據(jù)時觸發(fā)UPDATE修改表或視圖地數(shù)據(jù)時觸發(fā)DELETE在刪除表或視圖地數(shù)據(jù)時觸發(fā)DDL(三種)CREATE在創(chuàng)建新對象時觸發(fā)ALTER修改數(shù)據(jù)庫或數(shù)據(jù)庫對象時觸發(fā)DROP刪除對象時觸發(fā)數(shù)據(jù)庫(五種)STARTUP數(shù)據(jù)打開時觸發(fā)SHUTDOWN在使用NORMAL或IMMEDIATE選項關閉數(shù)據(jù)庫時觸發(fā)LOGON當用戶連接到數(shù)據(jù)庫并建立會話時觸發(fā)LOGOFF當一個會話從數(shù)據(jù)庫斷開時觸發(fā)SERVERERROR發(fā)生服務器錯誤時觸發(fā)二.觸發(fā)器觸發(fā)器CREATE[ORREPLACE]TRIGGER觸發(fā)器名

{BEFORE|AFTER|INSTEADOF}觸發(fā)一[OR觸發(fā)二...]

ON表名

WHEN觸發(fā)條件

[FOREACHROW]

DECLARE

聲明部分

BEGIN

主體部分

END[觸發(fā)器名];三.創(chuàng)建DML觸發(fā)器觸發(fā)器一.觸發(fā):插入（Insert）,刪除（Delete）,修改（Update）;二.觸發(fā)時機:前（before）,后(after);三.觸發(fā)級別:表級(默認),行級（foreachrow）;四.觸發(fā)器表:新記錄表(new)舊記錄表(old):插入:新記