oracle數(shù)據(jù)庫(kù) 游標(biāo)、存儲(chǔ)過程和觸發(fā)器

Oracle10g數(shù)據(jù)庫(kù)應(yīng)用教程

游標(biāo)、存儲(chǔ)過程和觸發(fā)器課程描述介紹Oracle數(shù)據(jù)庫(kù)程序設(shè)計(jì)中經(jīng)常會(huì)用到的3個(gè)概念，即游標(biāo)、存儲(chǔ)過程和觸發(fā)器。本章知識(shí)點(diǎn)游標(biāo)存儲(chǔ)過程管理觸發(fā)器管理游標(biāo)游標(biāo)的基本概念游標(biāo)控制語句游標(biāo)屬性游標(biāo)FOR循環(huán)游標(biāo)的基本概念游標(biāo)示意圖游標(biāo)的基本概念使用顯式游標(biāo)

（1）說明游標(biāo)。（2）打開游標(biāo)。（3）讀取數(shù)據(jù)。（4）關(guān)閉游標(biāo)。游標(biāo)的基本概念隱式游標(biāo)【例】使用SELECT語句聲明隱式游標(biāo)，從HR.Departments表中讀取Department_name字段的值到變量DepName：SETServerOutputON;DECLAREDepName

HR.Departments.Department_Name%Type;BEGINSELECTDepartment_nameINTODepNameFROMHR.DepartmentsWHEREDepartment_ID=10;dbms_output.put_line(DepName);END;游標(biāo)控制語句（1）聲明游標(biāo)語句CURSOR：DECLARECURSOR<游標(biāo)名>[(<參數(shù)列表>)]IS<SELECT語句>;【例】聲明一個(gè)游標(biāo)MyCur，讀取指定類型的用戶信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游標(biāo)控制語句（2）打開游標(biāo)語句OPEN：OPEN<游標(biāo)名>[(<參數(shù)列表>)];【例】打開游標(biāo)MyCur，讀取類型為1的用戶信息：OPENMyCur(1);游標(biāo)控制語句（3）游標(biāo)取值語句FETCH。游標(biāo)取值語句FETCH的基本語法結(jié)構(gòu)如下：FETCH<游標(biāo)名>INTO<變量列表>;【例】在打開的游標(biāo)MyCur的當(dāng)前位置讀取數(shù)據(jù)：FETCHMyCurINTOvarId,varName;（4）關(guān)閉游標(biāo)語句CLOSE：CLOSE<游標(biāo)名>;【例】關(guān)閉游標(biāo)MyCur：CLOSEMyCur;游標(biāo)控制語句【例】下面介紹一個(gè)完整的游標(biāo)應(yīng)用實(shí)例：/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分

varIdNUMBER;--聲明變量,用來保存游標(biāo)中的用戶編號(hào)

varNameVARCHAR2(50);--聲明變量,用來保存游標(biāo)中的用戶名

--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--開始程序體

OPENMyCur(1);--打開游標(biāo),參數(shù)為1,表示讀取用戶類型編號(hào)為1的記錄

FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

CLOSEMyCur;--關(guān)閉游標(biāo)

dbms_output.put_line('用戶編號(hào):'||varId||',用戶名:'||varName);--顯示讀取的數(shù)據(jù)END;--結(jié)束程序體游標(biāo)屬性（1）%ISOPEN屬性【例】下面的代碼演示當(dāng)使用未打開的游標(biāo)時(shí)，將會(huì)出現(xiàn)錯(cuò)誤：/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分

varNameVARCHAR2(50);--聲明變量,用來保存游標(biāo)中的用戶名

varIdNUMBER;--聲明變量,用來保存游標(biāo)中的用戶編號(hào)

--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--開始程序體

FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

CLOSEMyCur;--關(guān)閉游標(biāo)

dbms_output.put_line('用戶編號(hào):'||varId||',用戶名:'||varName);--顯示讀取的數(shù)據(jù)END;--結(jié)束程序體游標(biāo)屬性【例】修改上面的程序，在使用游標(biāo)之前，調(diào)用%ISOPEN屬性判斷游標(biāo)是否打開。/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分

varNameVARCHAR2(50);--聲明變量,用來保存游標(biāo)中的用戶名

varIdNUMBER;--聲明變量,用來保存游標(biāo)中的用戶編號(hào)

--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--開始程序體

IFMyCur%ISOPEN=FALSEThenOPENMyCur(2);ENDIF;FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

CLOSEMyCur;--關(guān)閉游標(biāo)

dbms_output.put_line('用戶編號(hào):'||varId||',用戶名:'||varName);--顯示讀取的數(shù)據(jù)END;--結(jié)束程序體游標(biāo)屬性（2）%FOUND屬性和%NOTFOUND屬性【例】%FOUND屬性可以循環(huán)執(zhí)行游標(biāo)讀取數(shù)據(jù)：/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分

varNameVARCHAR2(50);--聲明變量,用來保存游標(biāo)中的用戶名

varIdNUMBER;--聲明變量,用來保存游標(biāo)中的用戶編號(hào)

--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--開始程序體

IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

WHILEMyCur%FOUND--如果當(dāng)前游標(biāo)有效，則執(zhí)行循環(huán)

LOOP

dbms_output.put_line('用戶編號(hào):'||varId||',用戶名:'||varName);--顯示讀取的數(shù)據(jù)

FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

ENDLOOP;CLOSEMyCur;--關(guān)閉游標(biāo)END;--結(jié)束程序體游標(biāo)屬性（3）%ROWCOUNT屬性【例】只讀取前2行記錄：/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分

varNameVARCHAR2(50);--聲明變量,用來保存游標(biāo)中的用戶名

varIdNUMBER;--聲明變量,用來保存游標(biāo)中的用戶編號(hào)

--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游標(biāo)屬性BEGIN--開始程序體

IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

WHILEMyCur%FOUND--如果當(dāng)前游標(biāo)有效，則執(zhí)行循環(huán)

LOOP

dbms_output.put_line('用戶編號(hào):'||varId||',用戶名:'||varName);--顯示讀取的數(shù)據(jù)

IFMyCur%ROWCOUNT=2THENEXIT;ENDIF;FETCHMyCurINTOvarId,varName;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)

ENDLOOP;CLOSEMyCur;--關(guān)閉游標(biāo)END;--結(jié)束程序體游標(biāo)FOR循環(huán)【例】聲明記錄類型User_Record_Type和定義記錄變量var_UserRecord：TYPEUser_Record_TypeISRECORD(UserId

Users.UserId%Type,

UserName

Users.UserName%Type);var_UserRecord

User_Record_Type;游標(biāo)FOR循環(huán)【例】PL/SQL記錄可以與游標(biāo)結(jié)合使用：/*打開顯示模式*/SETServerOutputON;DECLARE--開始聲明部分/*聲明記錄類型*/TYPEUser_Record_TypeISRECORD(UserId

Users.UserId%Type,

UserName

Users.UserName%Type);/*定義記錄變量*/var_UserRecord

User_Record_Type;--定義游標(biāo),varType為參數(shù),指定用戶類型編號(hào)

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游標(biāo)FOR循環(huán)BEGIN--開始程序體

IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;LOOPFETCHMyCurINTOvar_UserRecord;--讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)到記錄變量var_UserRecordEXITWHENMyCur%NOTFOUND;--當(dāng)游標(biāo)指向結(jié)果集結(jié)尾時(shí)退出循環(huán)

/*顯示保存在記錄變量var_UserRecord中的數(shù)據(jù)*/

dbms_output.put_line('用戶編號(hào):'||var_UserRecord.UserId||',用戶名::'||var_UserRecord.UserName);ENDLOOP;CLOSEMyCur;--關(guān)閉游標(biāo)END;--結(jié)束程序體游標(biāo)FOR循環(huán)【例】典型游標(biāo)FOR循環(huán)的例子：/*打開顯示模式*/SETServerOutputON;DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--開始程序體

FORvar_UserRecordINMyCur(1)LOOP/*顯示保存在記錄變量var_UserRecord中的數(shù)據(jù)*/

dbms_output.put_line('用戶編號(hào):'||var_UserRecord.UserId||',用戶名::'||var_UserRecord.UserName);ENDLOOP;END;--結(jié)束程序體10.2存儲(chǔ)過程管理過程函數(shù)程序包過程CREATEPROCEDURE語句來創(chuàng)建過程：CREATE[ORREPLACE]PROCEDURE<過程名>

[<參數(shù)列表>

]IS|AS[<局部變量聲明>]BEGIN<過程體>END[<過程名>];過程【例】創(chuàng)建示例過程ResetPwd，此過程的功能是將表Users中指定用戶的密碼重置為111111：CREATEORREPLACEPROCEDUREUserMan.ResetPwd(UserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=UserId;END;過程存儲(chǔ)過程管理過程添加存儲(chǔ)過程過程修改存儲(chǔ)過程函數(shù)CREATEFUNCTION語句來創(chuàng)建函數(shù)：CREATE[ORREPLACE]FUNCTION<函數(shù)名>

[<參數(shù)列表>

][RETURN<函數(shù)數(shù)據(jù)類型>]IS|AS[<局部變量聲明>]BEGIN<過程體>RETURN<函數(shù)值>END[<過程名>];函數(shù)【例】下面介紹一個(gè)示例函數(shù)GetPwd，此函數(shù)的功能是在表Users中根據(jù)指定的用戶名返回該用戶的密碼信息：CREATEFUNCTIONUserMan.GetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwd

Users.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=''||name||'';RETURNoutpwd;END;函數(shù)函數(shù)管理函數(shù)添加函數(shù)函數(shù)修改函數(shù)程序包CREATEPACKAGE語句來創(chuàng)建包的說明部分：CREATE[ORREPLACE]PACKAGE<程序包名>

IS|AS[<聲明部分>]END[<程序包名>];程序包【例】下面介紹一個(gè)示例創(chuàng)建程序包MyPack，它包含前面2小節(jié)中的過程ResetPwd和函數(shù)GetPwd：CREATEORREPLACEPACKAGEUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER);FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%Type;ENDMyPack;程序包程序包管理程序包添加程序包程序包CREATEPACKAGEBODY語句來創(chuàng)建包體部分：CREATEPACKAGEBODY<程序包名>

IS|AS[<聲明部分>][<過程體>][<函數(shù)體>][<初始化部分>]END[<程序包名>];程序包【例】下面創(chuàng)建程序包MyPack的包體體部分：CREATEPACKAGEBODYUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=UserId;END;FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwd

Users.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=''||name||'';RETURNoutpwd;END;ENDMyPack;程序包程序包管理查看MyPack程序包體程序包添加程序包程序包調(diào)用程序包中的過程<方案名>.<程序包名>.<過程名>調(diào)用程序包中的函數(shù)：<方案名>.<程序包名>.<過程名>【例】調(diào)用UserMan.MyPack.GetPwd函數(shù)，返回指定用戶的密碼信息：SETServerOutputON;DECLARE

varPwd

Users.UserPwd%Type;BEGIN

varPwd:=UserMan.MyPack.GetPwd('Admin');

dbms_output.put_line(varPwd);END;程序包DROPPACKAGEBODY命令刪除程序包體：DROPPACKAGEBODYUserMan.MyPack;DROPPACKAGE命令刪除程序包的說明部分：DROPPACKAGEUserMan.MyPack;綜合訓(xùn)練--針對(duì)表scott.t_student,按下列要求創(chuàng)建并執(zhí)行子程序1、創(chuàng)建程序包user_manage2、在user_manage程序包中創(chuàng)建下列子程序：（1）創(chuàng)建過程query_utbs,實(shí)現(xiàn)scott.t_student表中所有記錄的查詢輸出功能；（2）分別創(chuàng)建過程ins_utbs,del_utbs,實(shí)現(xiàn)scott.t_student表中單個(gè)記錄的添加、刪除操作；（3）創(chuàng)建函數(shù)user_logon,用于判斷學(xué)號(hào)和姓名是否正確。10.3觸發(fā)器管理觸發(fā)器的基本概念創(chuàng)建及使用觸發(fā)器觸發(fā)器的基本概念觸發(fā)器(trigger)是一些過程，與表關(guān)系密切，用于保護(hù)表中的數(shù)據(jù)，當(dāng)一個(gè)基表被修改(INSERT、UPDATE或DELETE)時(shí)，觸發(fā)器自動(dòng)執(zhí)行，例如通過觸發(fā)器可實(shí)現(xiàn)多個(gè)表間數(shù)據(jù)的一致性和完整性。觸發(fā)器和應(yīng)用程序無關(guān)。觸發(fā)器的基本概念觸發(fā)器的類型有三種：(1) DML觸發(fā)器。Oracle可以在DML(數(shù)據(jù)操縱語句)語句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)，并且可以在每個(gè)行或該語句操作上進(jìn)行觸發(fā)。(2) 替代觸發(fā)器。由于在Oracle中不能直接對(duì)有兩個(gè)以上的表建立的視圖進(jìn)行操作，所以給出了替代觸發(fā)器。它是Oracle專門為進(jìn)行視圖操作的一種處理方法。(3) 系統(tǒng)觸發(fā)器。在Oracle8i時(shí)，提供了第三種類型的觸發(fā)器叫系統(tǒng)觸發(fā)器。它可以在Oracle數(shù)據(jù)庫(kù)系統(tǒng)的時(shí)間中進(jìn)行觸發(fā)，如Oracle數(shù)據(jù)庫(kù)的關(guān)閉或打開等。觸發(fā)器的基本概念觸發(fā)事件。INSERT。當(dāng)指定的表發(fā)生插入（INSERT）操作時(shí)執(zhí)行觸發(fā)器。UPDATE。當(dāng)指定的表發(fā)生修改（UPDATE）操作時(shí)執(zhí)行觸發(fā)器。DELETE。當(dāng)指定的表發(fā)生刪除（DELETE）操作時(shí)執(zhí)行觸發(fā)器。觸發(fā)器的基本概念觸發(fā)時(shí)間。BEFORE。在指定的事件發(fā)生之前執(zhí)行觸發(fā)器。AFTER。在指定的事件發(fā)生之后執(zhí)行觸發(fā)器。觸發(fā)級(jí)別。行觸發(fā)。對(duì)觸發(fā)事件影響的每一行執(zhí)行觸發(fā)器。語句觸發(fā)。對(duì)于觸發(fā)事件只能觸發(fā)一次，而且不能訪問受觸發(fā)器影響的每一行的值。創(chuàng)建及使用觸發(fā)器CREATETRIGGER語句來創(chuàng)建觸發(fā)器：CREATE[ORREPLACE]TRIGGER<觸發(fā)器名>[BEFORE|AFTER]<觸發(fā)事件>ON<表名>[FOREACHROW][WHEN<條件表達(dá)式>]<PL/SQL程序體>創(chuàng)建及使用觸發(fā)器創(chuàng)建觸發(fā)器的限制創(chuàng)建觸發(fā)器有以下限制：(1) 代碼大小。觸發(fā)器代碼大小必須小于32K。(2) 觸發(fā)器中有效語句可以包括DML語句，但不能包括DDL語句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。但是，對(duì)于系統(tǒng)觸發(fā)器(systemtrigger)可以使用CREATE、ALTER、DROPTABLE和ALTER…COMPILE語句。創(chuàng)建及使用觸發(fā)器觸發(fā)器觸發(fā)次序

Oracle對(duì)事件的觸發(fā)有16種，它們按照一定次序執(zhí)行：(1)執(zhí)行BEFORE語句的觸發(fā)器；(2)對(duì)于受語句影響的每一行：執(zhí)行BEFORE語句行級(jí)觸發(fā)器執(zhí)行DML語句執(zhí)行AFTER行級(jí)觸發(fā)器。(3) 執(zhí)行AFTER語句級(jí)觸發(fā)器。創(chuàng)建及使用觸發(fā)器1、DML語句觸發(fā)器

DML語句觸發(fā)器是指當(dāng)執(zhí)行DML語句時(shí)被隱含執(zhí)行的觸發(fā)器。如果在表上針對(duì)某種DML操作建立了語句觸發(fā)器，那么當(dāng)執(zhí)行DML操作時(shí)會(huì)自動(dòng)執(zhí)行觸發(fā)器的相應(yīng)代碼。語法如下：CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent1[ORevent2ORevent3]ONtable_namePL/SQLblock;創(chuàng)建及使用觸發(fā)器[例1]建立BEFORE語句觸發(fā)器，保證學(xué)生信息的修改只能在工作時(shí)間。

CREATEORREPLACETRIGGERtr_sec_stu

BEFOREINSERTORUPDATEORDELETEONscott.t_student

DECLARE

--可以在這里聲明私有變量

BEGIN

IFto_char(SYSDATE,‘DY’,‘nls_date_language=AMERICAN’)in(‘SAT’,‘SUN’)THEN

raise_application_error(-20001,‘不能在休息日改變學(xué)生信息');

ENDIF;

ENDtr_sec_stu;

/創(chuàng)建及使用觸發(fā)器2、DML行觸發(fā)器

DML行觸發(fā)器是指當(dāng)執(zhí)行DML操作時(shí)，每作用一行被觸發(fā)一次的觸發(fā)器。語法如下：CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent1[ORevent2ORevent3]ONtable_name[REFERENCINGOLDASold|NEWASnew]FOREACHROW[WHENcondition]PL/SQLblock;創(chuàng)建及使用觸發(fā)器[例2]假設(shè)數(shù)據(jù)庫(kù)中增加一新表student_HIS，表結(jié)構(gòu)和表t_student相同，用來存放從t_student表中刪除的記錄。創(chuàng)建一個(gè)觸發(fā)器，當(dāng)t_studnent表被刪除一行，把刪除的記錄寫到表student_HIS中。

CREATEORREPLACETRIGGERdel_student BEFOREDELETEONscott.t_student

FOREACHROW BEGIN INSERTINTOstudent_HISVALUES(:OLD.stuno,:OLD.stuname,:OLD.stusex,OLD.stubir);ENDdel_student;創(chuàng)建及使用觸發(fā)器使用DML觸發(fā)器的注意事項(xiàng)當(dāng)編寫DML觸發(fā)器時(shí)，觸發(fā)器代碼不能從觸發(fā)器所對(duì)應(yīng)的基表中讀取數(shù)據(jù)。例如，如果要基于S表建立觸發(fā)器，該觸發(fā)器的執(zhí)行代碼不能包含對(duì)S表的查詢操作。創(chuàng)建及使用觸發(fā)器4、替代（Instead_of）觸發(fā)器

Instead_of用于對(duì)視圖的DML觸發(fā)。由于視圖有可能由多個(gè)表進(jìn)行關(guān)聯(lián)而成，因而并非所有的關(guān)聯(lián)都是可更新的。創(chuàng)建及使用觸發(fā)器5、系統(tǒng)觸發(fā)器

系統(tǒng)觸發(fā)器可以在DDL或數(shù)據(jù)庫(kù)系統(tǒng)事件上被觸發(fā)。語法格式：

CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent[ddl_eventORdatabase_event]ON[DATABASE|SCHEMA][WHENcondition]PL/SQLblock;創(chuàng)建及使用觸發(fā)器[例3]當(dāng)用戶登錄或退出是，將信息記錄到表log_table中。

1、創(chuàng)建表log_table

CREATETABLElog_table(

usernamevarchar2(20),--記錄用戶名

logon_timedate,--記錄用戶登錄時(shí)間

logoff_timedate,--記錄用戶退出時(shí)間