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

2、。（2 2）打開游標(biāo)。）打開游標(biāo)。（3 3）讀取數(shù)據(jù)。）讀取數(shù)據(jù)。（4 4）關(guān)閉游標(biāo)。）關(guān)閉游標(biāo)。游標(biāo)的基本概念游標(biāo)的基本概念p隱式游標(biāo)隱式游標(biāo) 【例】使用【例】使用SELECTSELECT語句聲明隱式游標(biāo)，從語句聲明隱式游標(biāo)，從HR.DepartmentsHR.Departments表中讀取表中讀取Department_nameDepartment_name字段的值到變量字段的值到變量DepNameDepName：SET ServerOutput ON;DECLARE DepName HR.Departments.Department_Name%Type;BEGINSELECT Depart

3、ment_name INTO DepNameFROM HR.DepartmentsWHERE Department_ID=10;dbms_output.put_line(DepName);END;類型描述符游標(biāo)控制語句游標(biāo)控制語句 （1 1）聲明游標(biāo)語句）聲明游標(biāo)語句CURSORCURSOR：DECLARE CURSOR () IS;【例】聲明一個(gè)游標(biāo)【例】聲明一個(gè)游標(biāo)MyCurMyCur，讀取指定類型的用戶，讀取指定類型的用戶信息：信息：DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHE

4、RE UserType = varType;游標(biāo)控制語句游標(biāo)控制語句（2 2）打開游標(biāo)語句）打開游標(biāo)語句OPENOPEN：OPEN () ;【例】打開游標(biāo)【例】打開游標(biāo)MyCurMyCur，讀取類型為，讀取類型為1 1的用戶信的用戶信息：息：OPEN MyCur(1);游標(biāo)控制語句游標(biāo)控制語句（3 3）游標(biāo)取值語句）游標(biāo)取值語句FETCHFETCH。游標(biāo)取值語句。游標(biāo)取值語句FETCHFETCH的基本語法結(jié)構(gòu)如下：的基本語法結(jié)構(gòu)如下：FETCH INTO ;【例】在打開的游標(biāo)MyCur的當(dāng)前位置讀取數(shù)據(jù)：FETCH MyCur INTO varI,d varName;（4 4）關(guān)閉游標(biāo)語句）關(guān)

5、閉游標(biāo)語句CLOSECLOSE：CLOSE ;【例】關(guān)閉游標(biāo)MyCur：CLOSE MyCur;游標(biāo)控制語句游標(biāo)控制語句【例】下面介紹一個(gè)完整的游標(biāo)應(yīng)用實(shí)例：【例】下面介紹一個(gè)完整的游標(biāo)應(yīng)用實(shí)例：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varId NUMBER; -聲明變量聲明變量,用來保存游標(biāo)中的用戶編號(hào)用來保存游標(biāo)中的用戶編號(hào) varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標(biāo)中的用戶名用來保存游標(biāo)中的用戶名 -定義游標(biāo)定義游標(biāo), varType為參數(shù)為參數(shù), 指定用戶類型編號(hào)指定用

6、戶類型編號(hào) CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體開始程序體 OPEN MyCur(1); -打開游標(biāo)打開游標(biāo),參數(shù)為參數(shù)為1,表示讀取用戶類型編號(hào)為表示讀取用戶類型編號(hào)為1的記錄的記錄 FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) CLOSE MyCur; -關(guān)閉游標(biāo)關(guān)閉游標(biāo) dbms_output.put_line(用戶編號(hào)用戶編號(hào): | varId |,

7、 用戶名用戶名: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù)END; -結(jié)束程序體結(jié)束程序體游標(biāo)屬性游標(biāo)屬性 （1 1）%ISOPEN%ISOPEN屬性屬性 【例】下面的代碼演示當(dāng)使用未打開的游標(biāo)時(shí)，將會(huì)出現(xiàn)錯(cuò)誤：【例】下面的代碼演示當(dāng)使用未打開的游標(biāo)時(shí)，將會(huì)出現(xiàn)錯(cuò)誤：/* 打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分 varName VARCHAR2(50); -聲明變量,用來保存游標(biāo)中的用戶名 varId NUMBER; -聲明變量,用來保存游標(biāo)中的用戶編號(hào) -定義游標(biāo), varType為參數(shù), 指定用戶類型編號(hào) CURSOR My

8、Cur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體 FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) CLOSE MyCur; -關(guān)閉游標(biāo) dbms_output.put_line(用戶編號(hào): | varId |, 用戶名: | varName); -顯示讀取的數(shù)據(jù)END; -結(jié)束程序體游標(biāo)屬性游標(biāo)屬性【例】修改上面的程序，在使用游標(biāo)之前，調(diào)用【例】修改上面的程序，在使用游標(biāo)之前，調(diào)用%ISOPEN%ISOPEN屬

9、性判斷游標(biāo)是否打開。屬性判斷游標(biāo)是否打開。/* 打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分 varName VARCHAR2(50); -聲明變量,用來保存游標(biāo)中的用戶名 varId NUMBER; -聲明變量,用來保存游標(biāo)中的用戶編號(hào) -定義游標(biāo), varType為參數(shù), 指定用戶類型編號(hào) CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體 IF MyCur%ISOPEN = FALSE

10、Then OPEN MyCur(2); END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) CLOSE MyCur; -關(guān)閉游標(biāo) dbms_output.put_line(用戶編號(hào): | varId |, 用戶名: | varName); -顯示讀取的數(shù)據(jù)END; -結(jié)束程序體游標(biāo)屬性游標(biāo)屬性（2 2）%FOUND%FOUND屬性和屬性和%NOTFOUND%NOTFOUND屬性屬性【例】【例】%FOUND%FOUND屬性可以循環(huán)執(zhí)行游標(biāo)讀取數(shù)據(jù)：屬性可以循環(huán)執(zhí)行游標(biāo)讀取數(shù)據(jù)：/ /* * 打開顯示模式打開顯示模式 * */ /SET Se

11、rverOutput ON; SET ServerOutput ON; DECLARE -DECLARE -開始聲明部分開始聲明部分 varName VARCHAR2(50); - varName VARCHAR2(50); -聲明變量聲明變量, ,用來保存游標(biāo)中的用戶名用來保存游標(biāo)中的用戶名 varId NUMBER; - varId NUMBER; -聲明變量聲明變量, ,用來保存游標(biāo)中的用戶編號(hào)用來保存游標(biāo)中的用戶編號(hào) - -定義游標(biāo)定義游標(biāo), varType, varType為參數(shù)為參數(shù), , 指定用戶類型編號(hào)指定用戶類型編號(hào) CURSOR MyCur(varType NUMBER)

12、IS CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users SELECT UserId, UserName FROM Users WHERE UserType = varType; WHERE UserType = varType;BEGIN -BEGIN -開始程序體開始程序體 IF MyCur%ISOPEN = FALSE Then IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); OPEN MyCur(1); END IF; END IF; FETCH MyCur INT

13、O varId, varName; - FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) WHILE MyCur%FOUND - WHILE MyCur%FOUND -如果當(dāng)前游標(biāo)有效，則執(zhí)行循環(huán)如果當(dāng)前游標(biāo)有效，則執(zhí)行循環(huán) LOOP LOOP dbms_output.put_line( dbms_output.put_line(用戶編號(hào)用戶編號(hào): | varId |, : | varId |, 用戶名用戶名: | varName); -: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù) FETCH MyCur INTO

14、varId, varName; - FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) END LOOP; END LOOP; CLOSE MyCur; - CLOSE MyCur; -關(guān)閉游標(biāo)關(guān)閉游標(biāo)END; -END; -結(jié)束程序體結(jié)束程序體游標(biāo)屬性游標(biāo)屬性（3 3）%ROWCOUNT%ROWCOUNT屬性屬性 【例】只讀取前【例】只讀取前2 2行記錄：行記錄：/* 打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分 varName VARCHAR2(50); -聲明變量,用來保存游標(biāo)中的

15、用戶名 varId NUMBER; -聲明變量,用來保存游標(biāo)中的用戶編號(hào) -定義游標(biāo), varType為參數(shù), 指定用戶類型編號(hào) CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游標(biāo)屬性游標(biāo)屬性BEGIN -開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) WHILE MyCur%FOUND -如果當(dāng)前游

16、標(biāo)有效，則執(zhí)行循環(huán) LOOP dbms_output.put_line(用戶編號(hào): | varId |, 用戶名: | varName); -顯示讀取的數(shù)據(jù) IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù) END LOOP; CLOSE MyCur; -關(guān)閉游標(biāo)END; -結(jié)束程序體游標(biāo)游標(biāo)FOR循環(huán)循環(huán) 【例】聲明記錄類型【例】聲明記錄類型User_Record_TypeUser_Record_Type和定義記錄和定義記錄變量變量var_UserRecordvar_U

17、serRecord：TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);var_UserRecord User_Record_Type;游標(biāo)游標(biāo)FOR循環(huán)循環(huán) 【例】【例】PL/SQLPL/SQL記錄可以與游標(biāo)結(jié)合使用：記錄可以與游標(biāo)結(jié)合使用：/* 打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分/* 聲明記錄類型 */TYPE User_Record_Type IS RECORD ( UserId Users.UserId%

18、Type, UserName Users.UserName%Type);/* 定義記錄變量 */var_UserRecord User_Record_Type; -定義游標(biāo), varType為參數(shù), 指定用戶類型編號(hào) CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游標(biāo)游標(biāo)FOR循環(huán)循環(huán) BEGIN -開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO

19、var_UserRecord; -讀取當(dāng)前游標(biāo)位置的數(shù)據(jù)到記錄變量var_UserRecord EXIT WHEN MyCur%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); END LOOP; CLOSE MyCur; -關(guān)閉游標(biāo)END; -結(jié)束程序體游標(biāo)游標(biāo)FOR循環(huán)循環(huán) 【例】典型游標(biāo)【例】典型游標(biāo)FORFOR循環(huán)的例子：循環(huán)的例子：/* 打開

20、顯示模式 */SET ServerOutput ON; DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體 FOR var_UserRecord IN MyCur(1) LOOP /* 顯示保存在記錄變量var_UserRecord中的數(shù)據(jù) */ dbms_output.put_line(用戶編號(hào): | var_UserRecord.UserId |, 用戶名: | var_UserRecord.UserName); EN

21、D LOOP;END; -結(jié)束程序體10.2存儲(chǔ)過程管理存儲(chǔ)過程管理 p 過程過程 一種基本的存儲(chǔ)過程，由過程名、參數(shù)和程序體組成。p 函數(shù)函數(shù) 與過程類似，只是函數(shù)有返回值。p 程序包程序包 一組相關(guān)的PL/SQL過程和函數(shù)，由包名、說明部分和包體組成。p FUNCTION,PROCEDURE和和PACKAGE區(qū)別區(qū)別 function 和procedure是PL/SQL代碼的集合，通常為了完成一個(gè)任務(wù)。procedure 不需要返回任何值而function將返回一個(gè)值，Package是為了完成一個(gè)商業(yè)功能的一組function和proceudre的集合過程過程pCREATE PROCEDU

22、RECREATE PROCEDURE語句來創(chuàng)建過程：語句來創(chuàng)建過程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;p參數(shù)聲明格式：參數(shù)聲明格式： IN | OUT | IN OUT :=過程過程【例】創(chuàng)建示例過程【例】創(chuàng)建示例過程ResetPwdResetPwd，此過程的功能是，此過程的功能是將表將表UsersUsers中指定用戶的密碼重置為中指定用戶的密碼重置為111111111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( varUserId IN NUMBER)ASBEGIN UPDATE

23、 Users SET UserPwd = 111111 WHERE UserId = varUserId;END;過程過程p存儲(chǔ)過程管理存儲(chǔ)過程管理 過程過程p添加存儲(chǔ)過程添加存儲(chǔ)過程 過程過程p修改存儲(chǔ)過程修改存儲(chǔ)過程 函數(shù)函數(shù) pCREATE FUNCTIONCREATE FUNCTION語句來創(chuàng)建函數(shù)：語句來創(chuàng)建函數(shù)：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;函數(shù)函數(shù) 【例】下面介紹一個(gè)示例函數(shù)【例】下面介紹一個(gè)示例函

24、數(shù)GetPwdGetPwd，此函數(shù)的功能是，此函數(shù)的功能是在表在表UsersUsers中根據(jù)指定的用戶名返回該用戶的密碼信中根據(jù)指定的用戶名返回該用戶的密碼信息：息：CREATE FUNCTION UserMan.GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName = name; RETURN outpwd;END;函數(shù)函數(shù) p函數(shù)管理函數(shù)管理 函數(shù)函

25、數(shù) p添加函數(shù)添加函數(shù) 函數(shù)函數(shù) p修改函數(shù)修改函數(shù) 程序包程序包 p CREATE PACKAGE語句來語句來創(chuàng)建包的說明部分創(chuàng)建包的說明部分：CREATE OR REPLACE PACKAGE IS | AS END ;-可以包括類型、變量、過程、函數(shù)和游標(biāo)的說明程序包程序包【例】下面介紹一個(gè)示例創(chuàng)建程序包【例】下面介紹一個(gè)示例創(chuàng)建程序包MyPackMyPack，它包含，它包含前面前面2 2小節(jié)中的過程小節(jié)中的過程ResetPwdResetPwd和函數(shù)和函數(shù)GetPwdGetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE Re

26、setPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;程序包程序包p程序包管理程序包管理 程序包程序包p添加程序包添加程序包 程序包程序包pCREATE PACKAGE BODYCREATE PACKAGE BODY語句來語句來創(chuàng)建包體創(chuàng)建包體部分：部分：CREATE PACKAGE BODY IS | AS END ;程序包程序包【例】下面創(chuàng)建程序包【例】下面創(chuàng)建程序包MyPackMyPack的包體體部分：的包體體部分：CREA

27、TE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( varUserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = varUserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName

28、=|name|; RETURN outpwd;END;END MyPack;程序包程序包p 程序包管理查看程序包管理查看MyPackMyPack程序包體程序包體 程序包程序包p添加程序包添加程序包 程序包程序包p調(diào)用程序包中的過程調(diào)用程序包中的過程.p調(diào)用程序包中的函數(shù)：調(diào)用程序包中的函數(shù)：. p【例】調(diào)用【例】調(diào)用UserMan.MyPack.GetPwdUserMan.MyPack.GetPwd函數(shù)，返回指定函數(shù)，返回指定用戶的密碼信息：用戶的密碼信息：SET ServerOutput ON;DECLARE varPwd Users.UserPwd%Type;BEGIN varPwd:= UserMan.MyPack.GetPwd(Admin); dbms_output.put_line(varPwd);END;程序包程序包p DROP PACKAGE BODY DROP PACKAGE BODY命令刪除程序包體：命令刪除程序包體：DROP PACKAGE BODY UserMan.MyPack;p DROP PACKAGE DROP PACKAGE命令刪除程序包的說明部分：命令刪除程序包的說明部分：DR