《Oracle數(shù)據(jù)庫基礎(chǔ)教程第10章人民郵電出版社.ppt》由會員分享，可在線閱讀，更多相關(guān)《Oracle數(shù)據(jù)庫基礎(chǔ)教程第10章人民郵電出版社.ppt（48頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

Oracle10g數(shù)據(jù)庫應(yīng)用教程 授課教師 職務(wù) 第10章游標(biāo) 存儲過程和觸發(fā)器 課程描述介紹Oracle數(shù)據(jù)庫程序設(shè)計(jì)中經(jīng)常會用到的3個(gè)概念 即游標(biāo) 存儲過程和觸發(fā)器 本章知識點(diǎn) 游標(biāo)存儲過程管理觸發(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 DECLAREDepNameHR Departments Department Name Type BEGINSELECTDepartment nameINTODepNameFROMHR DepartmentsWHEREDepartment ID 10 dbms output put line DepName END 游標(biāo)控制語句 1 聲明游標(biāo)語句CURSOR DECLARECURSOR IS 例 聲明一個(gè)游標(biāo)MyCur 讀取指定類型的用戶信息 DECLARECURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType 游標(biāo)控制語句 2 打開游標(biāo)語句OPEN OPEN 例 打開游標(biāo)MyCur 讀取類型為1的用戶信息 OPENMyCur 1 游標(biāo)控制語句 3 游標(biāo)取值語句FETCH 游標(biāo)取值語句FETCH的基本語法結(jié)構(gòu)如下 FETCHINTO 例 在打開的游標(biāo)MyCur的當(dāng)前位置讀取數(shù)據(jù) FETCHMyCurINTOvarI dvarName 4 關(guān)閉游標(biāo)語句CLOSE CLOSE 例 關(guān)閉游標(biāo)MyCur CLOSEMyCur 游標(biāo)控制語句 例 下面介紹一個(gè)完整的游標(biāo)應(yīng)用實(shí)例 打開顯示模式 SETServerOutputON DECLARE 開始聲明部分varIdNUMBER 聲明變量 用來保存游標(biāo)中的用戶編號varNameVARCHAR2 50 聲明變量 用來保存游標(biāo)中的用戶名 定義游標(biāo) varType為參數(shù) 指定用戶類型編號CURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType BEGIN 開始程序體OPENMyCur 1 打開游標(biāo) 參數(shù)為1 表示讀取用戶類型編號為1的記錄FETCHMyCurINTOvarId varName 讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)CLOSEMyCur 關(guān)閉游標(biāo)dbms output put line 用戶編號 varId 用戶名 varName 顯示讀取的數(shù)據(jù)END 結(jié)束程序體 游標(biāo)屬性 1 ISOPEN屬性 例 下面的代碼演示當(dāng)使用未打開的游標(biāo)時(shí) 將會出現(xiàn)錯誤 打開顯示模式 SETServerOutputON DECLARE 開始聲明部分varNameVARCHAR2 50 聲明變量 用來保存游標(biāo)中的用戶名varIdNUMBER 聲明變量 用來保存游標(biāo)中的用戶編號 定義游標(biāo) varType為參數(shù) 指定用戶類型編號CURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType BEGIN 開始程序體FETCHMyCurINTOvarId varName 讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)CLOSEMyCur 關(guān)閉游標(biāo)dbms output put line 用戶編號 varId 用戶名 varName 顯示讀取的數(shù)據(jù)END 結(jié)束程序體 游標(biāo)屬性 例 修改上面的程序 在使用游標(biāo)之前 調(diào)用 ISOPEN屬性判斷游標(biāo)是否打開 打開顯示模式 SETServerOutputON DECLARE 開始聲明部分varNameVARCHAR2 50 聲明變量 用來保存游標(biāo)中的用戶名varIdNUMBER 聲明變量 用來保存游標(biāo)中的用戶編號 定義游標(biāo) varType為參數(shù) 指定用戶類型編號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 用戶編號 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)中的用戶編號 定義游標(biāo) varType為參數(shù) 指定用戶類型編號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)LOOPdbms output put line 用戶編號 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)中的用戶編號 定義游標(biāo) varType為參數(shù) 指定用戶類型編號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)LOOPdbms output put line 用戶編號 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 UserIdUsers UserId Type UserNameUsers UserName Type var UserRecordUser Record Type 游標(biāo)FOR循環(huán) 例 PL SQL記錄可以與游標(biāo)結(jié)合使用 打開顯示模式 SETServerOutputON DECLARE 開始聲明部分 聲明記錄類型 TYPEUser Record TypeISRECORD UserIdUsers UserId Type UserNameUsers UserName Type 定義記錄變量 var UserRecordUser Record Type 定義游標(biāo) varType為參數(shù) 指定用戶類型編號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 用戶編號 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 用戶編號 var UserRecord UserId 用戶名 var UserRecord UserName ENDLOOP END 結(jié)束程序體 10 2存儲過程管理 過程函數(shù)程序包 過程 CREATEPROCEDURE語句來創(chuàng)建過程 CREATE ORREPLACE PROCEDURE IS AS BEGINEND 過程 例 創(chuàng)建示例過程ResetPwd 此過程的功能是將表Users中指定用戶的密碼重置為111111 CREATEORREPLACEPROCEDUREUserMan ResetPwd UserIdINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId UserId END 過程 存儲過程管理 過程 添加存儲過程 過程 修改存儲過程 函數(shù) CREATEFUNCTION語句來創(chuàng)建函數(shù) CREATE ORREPLACE FUNCTION RETURN IS AS BEGINRETURNEND 函數(shù) 例 下面介紹一個(gè)示例函數(shù)GetPwd 此函數(shù)的功能是在表Users中根據(jù)指定的用戶名返回該用戶的密碼信息 CREATEFUNCTIONUserMan GetPwd nameINUsers UserName Type RETURNUsers UserPwd TypeASoutpwdUsers UserPwd Type BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName name RETURNoutpwd END 函數(shù) 函數(shù)管理 函數(shù) 添加函數(shù) 函數(shù) 修改函數(shù) 程序包 CREATEPACKAGE語句來創(chuàng)建包的說明部分 CREATE ORREPLACE PACKAGEIS 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)建包體部分 CREATEPACKAGEBODYIS AS END 程序包 例 下面創(chuàng)建程序包MyPack的包體體部分 CREATEPACKAGEBODYUserMan MyPackISPROCEDUREResetPwd UserIdINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId UserId END FUNCTIONGetPwd nameINUsers UserName Type RETURNUsers UserPwd TypeASoutpwdUsers UserPwd Type BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName name RETURNoutpwd END ENDMyPack 程序包 程序包管理查看MyPack程序包體 程序包 添加程序包 程序包 調(diào)用程序包中的過程 調(diào)用程序包中的函數(shù) 例 調(diào)用UserMan MyPack GetPwd函數(shù) 返回指定用戶的密碼信息 SETServerOutputON DECLAREvarPwdUsers UserPwd Type BEGINvarPwd UserMan MyPack GetPwd Admin dbms output put line varPwd END 程序包 DROPPACKAGEBODY命令刪除程序包體 DROPPACKAGEBODYUserMan MyPack DROPPACKAGE命令刪除程序包的說明部分 DROPPACKAGEUserMan MyPack 10 3觸發(fā)器管理 觸發(fā)器的基本概念創(chuàng)建及使用觸發(fā)器 觸發(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ā)級別 行觸發(fā) 對觸發(fā)事件影響的每一行執(zhí)行觸發(fā)器 語句觸發(fā) 對于觸發(fā)事件只能觸發(fā)一次 而且不能訪問受觸發(fā)器影響的每一行的值 創(chuàng)建及使用觸發(fā)器 CREATETRIGGER語句來創(chuàng)建觸發(fā)器 CREATE ORREPLACE TRIGGER BEFORE AFTER ON FOREACHROW WHEN 創(chuàng)建及使用觸發(fā)器 例 創(chuàng)建一個(gè)觸發(fā)器MyTrigger 它的作用是當(dāng)表USERMAN UserType中TypeId列的值發(fā)生變化時(shí) 自動更新表USERMAN Users中的UserType列的值 從而保證數(shù)據(jù)的完整性 CREATEORREPLACETRIGGERUSERMAN MyTriggerAFTERUPDATEONUSERMAN UserTypeFOREACHROWBEGINUPDATEUSERMAN UsersSETUserType new TypeIdWHEREUserType old TypeId END 創(chuàng)建及使用觸發(fā)器 觸發(fā)器管理 創(chuàng)建及使用觸發(fā)器 添加觸發(fā)器