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

Oracle10g數(shù)據(jù)庫應用教程

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

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

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

varIdNUMBER;--聲明變量,用來保存游標中的用戶編號

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

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

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

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

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

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

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

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

varIdNUMBER;--聲明變量,用來保存游標中的用戶編號

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

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

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

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

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

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

varIdNUMBER;--聲明變量,用來保存游標中的用戶編號

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

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

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

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

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

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

varIdNUMBER;--聲明變量,用來保存游標中的用戶編號

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

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

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

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

LOOP

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

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

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

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

varIdNUMBER;--聲明變量,用來保存游標中的用戶編號

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

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

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

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

LOOP

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

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

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

Users.UserId%Type,

UserName

Users.UserName%Type);var_UserRecord

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

Users.UserId%Type,

UserName

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

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

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

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

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

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

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

dbms_output.put_line('用戶編號:'||var_UserRecord.UserId||',用戶名::'||var_UserRecord.UserName);ENDLOOP;END;--結(jié)束程序體10.2存儲過程管理過程函數(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;過程存儲過程管理過程添加存儲過程過程修改存儲過程函數(shù)CREATEFUNCTION語句來創(chuàng)建函數(shù)：CREATE[ORREPLACE]FUNCTION<函數(shù)名>

[<參數(shù)列表>

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

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

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

CREATEORREPLACETRIGGERtr_sec_stu

BEFOREINSERTORUPDATEORDELETEONscott.t_student

DECLARE

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

BEGIN

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

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

ENDIF;

ENDtr_sec_stu;

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

DML行觸發(fā)器是指當執(zhí)行DML操作時，每作用一行被觸發(fā)一次的觸發(fā)器。語法如下：CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent1[ORevent2ORevent3]ONtable_name[REFERENCINGOLDASold|NEWASnew]FOREACHROW[WHENcondition]PL/SQLblock;創(chuàng)建及使用觸發(fā)器[例2]假設(shè)數(shù)據(jù)庫中增加一新表student_HIS，表結(jié)構(gòu)和表t_student相同，用來存放從t_student表中刪除的記錄。創(chuàng)建一個觸發(fā)器，當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ā)器的注意事項當編寫DML觸發(fā)器時，觸發(fā)器代碼不能從觸發(fā)器所對應的基表中讀取數(shù)據(jù)。例如，如果要基于S表建立觸發(fā)器，該觸發(fā)器的執(zhí)行代碼不能包含對S表的查詢操作。創(chuàng)建及使用觸發(fā)器4、替代（Instead_of）觸發(fā)器

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

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

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

1、創(chuàng)建表log_table

CREATETABLElog_table(

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

logon_timedate,--記錄用戶登錄時間

logoff_timedate,--記錄用戶退出時間