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

1、第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器 介紹介紹Oracle數(shù)據(jù)庫程序設(shè)計中經(jīng)常會用到的數(shù)據(jù)庫程序設(shè)計中經(jīng)常會用到的3個概念，即游標、個概念，即游標、存儲過程和觸發(fā)器。存儲過程和觸發(fā)器。第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器本章學(xué)習(xí)目標本章學(xué)習(xí)目標 理解游標的基本概念；理解游標的基本概念； 掌握游標的基本操作、屬性操作和循環(huán)游標；掌握游標的基本操作、屬性操作和循環(huán)游標； 掌握掌握PL/SQL語言的三種存儲過程；語言的三種存儲過程； 理解觸發(fā)器的概念，掌握創(chuàng)建和使用觸發(fā)器的理解觸發(fā)器的概念，掌握創(chuàng)建和使用觸發(fā)器的方法；方法； 第第1010章章 游標

2、、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器本章知識點本章知識點 游標游標 存儲過程管理存儲過程管理 觸發(fā)器管理觸發(fā)器管理 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標 游標的基本概念游標的基本概念 游標控制語句游標控制語句 游標屬性游標屬性 游標游標FOR循環(huán)循環(huán) 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標的基本概念游標的基本概念游標：游標：游動的光標。游標是映射在結(jié)果集中一行數(shù)游動的光標。游標是映射在結(jié)果集中一行數(shù)據(jù)上的位置實體，有了游標，用戶就可以訪問結(jié)果據(jù)上的位置實體，有了游標，用戶就可以訪問結(jié)果集中的任意一行數(shù)據(jù)了。將游標放置到某行后，即

3、集中的任意一行數(shù)據(jù)了。將游標放置到某行后，即可對該行數(shù)據(jù)進行操作，最常見的操作是提取當(dāng)前可對該行數(shù)據(jù)進行操作，最常見的操作是提取當(dāng)前行數(shù)據(jù)。游標分兩種：行數(shù)據(jù)。游標分兩種： 顯式游標顯式游標 隱式游標隱式游標第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標的基本概念游標的基本概念 游標示意圖游標示意圖 游標游標第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標的基本概念游標的基本概念隱式游標隱式游標 不需要聲明，使用時也不需要執(zhí)行打開和關(guān)閉操作。不需要聲明，使用時也不需要執(zhí)行打開和關(guān)閉操作。實際上，就是在實際上，就是在Select語句中增加了語句中增加了INT

4、O子句，把結(jié)果集自動讀取子句，把結(jié)果集自動讀取到指定的比變量中。到指定的比變量中?！纠渴褂谩纠渴褂肧ELECT語句聲明隱式游標，從語句聲明隱式游標，從Students表中表中讀取讀取Sname字段的值到變量字段的值到變量VSname：DECLARE VSname Students.Sname%Type;BEGINSELECT Sname INTO VSnameFROM StudentsWHERE Sdept=Automation;dbms_output.put_line(VSname);END;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標的基本概念游標的基本概念 顯式

5、游標顯式游標 顯式游標需要聲明，在使用之前需顯式游標需要聲明，在使用之前需要打開游標，使用完成后要關(guān)閉游標。使用顯式要打開游標，使用完成后要關(guān)閉游標。使用顯式游標的步驟包括：游標的步驟包括： （1）聲明游標。）聲明游標。 （2）打開游標。）打開游標。 （3）讀取數(shù)據(jù)。）讀取數(shù)據(jù)。 （4）關(guān)閉游標。）關(guān)閉游標。第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標控制語句游標控制語句 （1）聲明游標語句）聲明游標語句CURSOR：DECLARE CURSOR () IS;【例】聲明一個游標【例】聲明一個游標MyCur，讀取指定類型的，讀取指定類型的用戶信息：用戶信息：DECLARE

6、CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標控制語句游標控制語句（2）打開游標語句）打開游標語句OPEN：OPEN () ;【例】打開游標【例】打開游標MyCur，讀取類型為，讀取類型為1的用戶信的用戶信息：息：OPEN MyCur(1);第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標控制語句游標控制語句（3）游標取值語句）游標取值語句FETCH。游標取值語句。游標取值語

7、句FETCH的基本語法結(jié)構(gòu)如下：的基本語法結(jié)構(gòu)如下：FETCH INTO ;【例】在打開的游標MyCur的當(dāng)前位置讀取數(shù)據(jù)：FETCH MyCur INTO varId,varName;（4）關(guān)閉游標語句）關(guān)閉游標語句CLOSE：CLOSE ;【例】關(guān)閉游標MyCur：CLOSE MyCur;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標控制語句游標控制語句【例】下面介紹一個完整的游標應(yīng)用實例：【例】下面介紹一個完整的游標應(yīng)用實例：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varId NUMBE

8、R; -聲明變量聲明變量,用來保存游標中的用戶編號用來保存游標中的用戶編號 varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標中的用戶名用來保存游標中的用戶名 -定義游標定義游標, varType為參數(shù)為參數(shù), 指定用戶類型編號指定用戶類型編號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體開始程序體 OPEN MyCur(1); -打開游標打開游標,參數(shù)為參數(shù)為1,表示讀取用戶類型編號為表示讀取用戶類型編號

9、為1的記錄的記錄 FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)據(jù)讀取當(dāng)前游標位置的數(shù)據(jù) CLOSE MyCur; -關(guān)閉游標關(guān)閉游標 dbms_output.put_line(用戶編號用戶編號: | varId |, 用戶名用戶名: | varName); -顯示讀取的顯示讀取的數(shù)據(jù)數(shù)據(jù)END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器The End第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標屬性游標屬性 （1）%ISOPE

10、N屬性屬性 判斷游標是否被打開，如果打開，則判斷游標是否被打開，如果打開，則%ISOPEN等于等于TRUE，否則等于，否則等于FALSE?！纠肯旅娴拇a演示當(dāng)使用未打開的游標時，將會出現(xiàn)錯誤：【例】下面的代碼演示當(dāng)使用未打開的游標時，將會出現(xiàn)錯誤：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標中的用戶名用來保存游標中的用戶名 varId NUMBER; -聲明變量聲明變量,用來保存游標中的用戶編號用來保存游標中的用戶編號 -定義游標定義游標,

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

12、END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標屬性游標屬性【例】修改上面的程序，在使用游標之前，調(diào)用【例】修改上面的程序，在使用游標之前，調(diào)用%ISOPEN屬性判斷游標是否打開。屬性判斷游標是否打開。/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標中的用戶名用來保存游標中的用戶名 varId NUMBER; -聲明變量聲明變量,用來保存游標中的用戶編號用來保存游標中的用戶編號 -定義游標定義游標

13、, varType為參數(shù)為參數(shù), 指定用戶類型編號指定用戶類型編號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(2); END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)據(jù)讀取當(dāng)前游標位置的數(shù)據(jù) CLOSE MyCur; -關(guān)閉游標關(guān)閉游標 dbms_output.put_line(用戶編

14、號用戶編號: | varId |, 用戶名用戶名: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù)END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標屬性游標屬性（2）%FOUND屬性和屬性和%NOTFOUND屬性屬性 %FOUND屬性用來判斷游標所在的行是否有效，如果有效，屬性用來判斷游標所在的行是否有效，如果有效，返回返回TRUE，無效時返回，無效時返回FALSE?！纠?FOUND屬性可以循環(huán)執(zhí)行游標讀取數(shù)據(jù)：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 va

15、rName VARCHAR2(50); -聲明變量聲明變量,用來保存游標中的用戶名用來保存游標中的用戶名 varId NUMBER; -聲明變量聲明變量,用來保存游標中的用戶編號用來保存游標中的用戶編號 -定義游標定義游標, varType為參數(shù)為參數(shù), 指定用戶類型編號指定用戶類型編號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); E

16、ND IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)據(jù)讀取當(dāng)前游標位置的數(shù)據(jù) WHILE MyCur%FOUND -如果當(dāng)前游標有效，則執(zhí)行循環(huán)如果當(dāng)前游標有效，則執(zhí)行循環(huán) LOOP dbms_output.put_line(用戶編號用戶編號: | varId |, 用戶名用戶名: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù) FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)據(jù)讀取當(dāng)前游標位置的數(shù)據(jù) END LOOP; CLOSE MyCur; -關(guān)閉游標關(guān)閉游標END; -結(jié)束程序體結(jié)束程序體

17、第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標屬性游標屬性（3）%ROWCOUNT屬性屬性 返回到當(dāng)前位置為止游標返回到當(dāng)前位置為止游標讀取的記錄行數(shù)。讀取的記錄行數(shù)?！纠恐蛔x取前【例】只讀取前2行記錄：行記錄：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標中的用戶名用來保存游標中的用戶名 varId NUMBER; -聲明變量聲明變量,用來保存游標中的用戶編號用來保存游標中的用戶編號 -定義游標定義游標, varType

18、為參數(shù)為參數(shù), 指定用戶類型編號指定用戶類型編號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標屬性游標屬性BEGIN -開始程序體開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)讀取當(dāng)前游標位置的數(shù)據(jù)據(jù) WHILE MyCur%FOUND

19、 -如果當(dāng)前游標有效，則執(zhí)行循環(huán)如果當(dāng)前游標有效，則執(zhí)行循環(huán) LOOP dbms_output.put_line(用戶編號用戶編號: | varId |, 用戶名用戶名: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù) IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -讀取當(dāng)前游標位置的數(shù)讀取當(dāng)前游標位置的數(shù)據(jù)據(jù) END LOOP; CLOSE MyCur; -關(guān)閉游標關(guān)閉游標END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器第第1010章章

20、游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) 游標游標FOR循環(huán)是顯式游標的一種快捷使用方式，循環(huán)是顯式游標的一種快捷使用方式，它使用它使用FOR循環(huán)依次讀取結(jié)果集中的行數(shù)據(jù)。循環(huán)依次讀取結(jié)果集中的行數(shù)據(jù)。當(dāng)當(dāng)FOR循環(huán)開始時，游標被自動打開（不再需要循環(huán)開始時，游標被自動打開（不再需要OPEN語句）；語句）；每循環(huán)一次，系統(tǒng)自動讀取游標當(dāng)前行的數(shù)據(jù)每循環(huán)一次，系統(tǒng)自動讀取游標當(dāng)前行的數(shù)據(jù)（不需要使用（不需要使用FETCH語句）；語句）；當(dāng)退出循環(huán)時，游標自動關(guān)閉（不需要使用當(dāng)退出循環(huán)時，游標自動關(guān)閉（不需要使用CLOSE語句）。語句）。第第1010章章 游標、存儲過程和

21、觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán)游標游標FOR循環(huán)通常與循環(huán)通常與PL/SQL記錄一起使用。記錄一起使用。PL/SQL記錄（記錄（RECORD）是由一組數(shù)據(jù)構(gòu)成的邏輯單元，并）是由一組數(shù)據(jù)構(gòu)成的邏輯單元，并不保存在數(shù)據(jù)庫中，與變量一樣，保存在內(nèi)存空間中。不保存在數(shù)據(jù)庫中，與變量一樣，保存在內(nèi)存空間中。使用記錄時，需要先定義記錄的結(jié)構(gòu)，然后聲明記錄變量。使用記錄時，需要先定義記錄的結(jié)構(gòu)，然后聲明記錄變量。定義記錄類型的基本語法如下：定義記錄類型的基本語法如下： TYPE IS RECORD (字段聲明字段聲明 ,字段聲明字段聲明 );定義記錄變量的方法與定義普通變量的方法相同，

22、語法如下：定義記錄變量的方法與定義普通變量的方法相同，語法如下： 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) 【例】聲明記錄類型【例】聲明記錄類型User_Record_Type和定義記和定義記錄變量錄變量var_UserRecord：TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);var_UserRecord User_Record_Type;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循

23、環(huán)循環(huán) 【例】【例】PL/SQL記錄可以與游標結(jié)合使用：記錄可以與游標結(jié)合使用：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分/* 聲明記錄類型聲明記錄類型 */TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);/* 定義記錄變量定義記錄變量 */var_UserRecord User_Record_Type; -定義游標定義游標, varType為參數(shù)為參數(shù), 指定用戶類型編號指定用戶類型編

24、號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) BEGIN -開始程序體開始程序體 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord; -讀取當(dāng)前游標位置的讀取當(dāng)前游標位置的數(shù)據(jù)到記錄變量數(shù)據(jù)到記錄變量var_UserRecord EXIT WH

25、EN MyCur%NOTFOUND; -當(dāng)游標指向結(jié)果集結(jié)尾時當(dāng)游標指向結(jié)果集結(jié)尾時退出循環(huán)退出循環(huán) /* 顯示保存在記錄變量顯示保存在記錄變量var_UserRecord中的數(shù)據(jù)中的數(shù)據(jù) */ dbms_output.put_line(用戶編號用戶編號: | var_UserRecord.UserId |, 用戶名用戶名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -關(guān)閉游標關(guān)閉游標END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸

26、發(fā)器游標游標FOR循環(huán)循環(huán) 典型游標典型游標FOR循環(huán)需要先對游標進行聲明，然后才循環(huán)需要先對游標進行聲明，然后才可以使用。典型游標可以使用。典型游標FOR循環(huán)的語法說明如下：循環(huán)的語法說明如下： FOR IN LOOP 語句語句1； 語句語句2； 語句語句n; END LOOP;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) 帶子查詢的典型游標帶子查詢的典型游標FOR循環(huán)的語法說明如下：循環(huán)的語法說明如下： FOR IN LOOP 語句語句1； 語句語句2； 語句語句n; END LOOP;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游

27、標游標FOR循環(huán)循環(huán) 【例】典型游標【例】典型游標FOR循環(huán)的例子：循環(huán)的例子：/* 打開顯示模式打開顯示模式 */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ù)中的數(shù)據(jù) */ dbms_output.put_line

28、(用戶編號用戶編號: | var_UserRecord.UserId |, 用戶名用戶名: | var_UserRecord.UserName); END LOOP;END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器游標游標FOR循環(huán)循環(huán) 【例】帶子查詢游標【例】帶子查詢游標FOR循環(huán)的例子：循環(huán)的例子：/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; BEGIN -開始程序體開始程序體 FOR var_UserRecord IN (SE

29、LECT UserId,UserName FROM Users WHERE UserType=1) LOOP /* 顯示保存在記錄變量顯示保存在記錄變量var_UserRecord中的數(shù)據(jù)中的數(shù)據(jù) */ dbms_output.put_line(用戶編號用戶編號: | var_UserRecord.UserId |, 用戶名用戶名: | var_UserRecord.UserName); END LOOP;END; -結(jié)束程序體結(jié)束程序體第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器10.2存儲過程管理存儲過程管理 過程過程 函數(shù)函數(shù) 程序包程序包 第第1010章章 游標、存儲

30、過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程 CREATE PROCEDURE語句來創(chuàng)建過程：語句來創(chuàng)建過程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ; 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程【例】創(chuàng)建示例過程【例】創(chuàng)建示例過程ResetPwd，此過程的功能是，此過程的功能是將表將表Users中指定用戶的密碼重置為中指定用戶的密碼重置為111111：CREATE OR REPLACE PROCEDURE ResetPwd( VUserId IN NUMBER)ASBEGIN UPDATE Users SET Us

31、erPwd = 111111 WHERE UserId = VUserId;END;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程 過程的調(diào)用過程的調(diào)用 可以使用可以使用EXECUTE命令調(diào)用過程。如命令調(diào)用過程。如 EXECUTE ResetPwd(1);-將編號為將編號為1的用戶密碼重置的用戶密碼重置 SELECT UserName,UserPwd FROM Users; 過程的刪除過程的刪除 可以使用可以使用DROP PROCEDURE命令刪除過程。命令刪除過程。【例】刪除過程【例】刪除過程ResetPwd。 DROP PROCEDURE ResetPwd第第10

32、10章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程 存儲過程管理存儲過程管理 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程 添加存儲過程添加存儲過程 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器過程過程 修改存儲過程修改存儲過程 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) CREATE FUNCTION語句來創(chuàng)建函數(shù)：語句來創(chuàng)建函數(shù)：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)

33、器函數(shù)函數(shù) 【例】下面介紹一個示例函數(shù)【例】下面介紹一個示例函數(shù)GetPwd，此函數(shù)的，此函數(shù)的功能是在表功能是在表Users中根據(jù)指定的用戶名返回該中根據(jù)指定的用戶名返回該用戶的密碼信息：用戶的密碼信息：CREATE FUNCTION 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;第第1010章章

34、 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) 函數(shù)的調(diào)用函數(shù)的調(diào)用SET ServerOutput on;DECLARE varPwd Users.UserPwd%TYPE;BEGIN varPwd:=GetPwd(Admin); dbms_output.put_line(varPwd);End;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) 函數(shù)的刪除函數(shù)的刪除 刪除函數(shù)用刪除函數(shù)用DROP語句語句 語法格式為：語法格式為： DROP FUNCTION 【例】刪除函數(shù)【例】刪除函數(shù)GetPwd; DROP FUNCTION GetPwd第第1010章章 游標

35、、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) 函數(shù)管理函數(shù)管理 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) 添加函數(shù)添加函數(shù) 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器函數(shù)函數(shù) 修改函數(shù)修改函數(shù) 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 CREATE PACKAGE語句來創(chuàng)建包的說明部分：語句來創(chuàng)建包的說明部分：CREATE OR REPLACE PACKAGE IS | AS END ;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包【例】下面介紹一個示例創(chuàng)建程序包【例】下面介紹一

36、個示例創(chuàng)建程序包MyPack，它，它包含前面包含前面2小節(jié)中的過程小節(jié)中的過程ResetPwd和函數(shù)和函數(shù)GetPwd：CREATE OR REPLACE PACKAGE MyPackISPROCEDURE ResetPwd( VUserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 程序包管理程序包管理 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包

37、程序包 添加程序包添加程序包 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 CREATE PACKAGE BODY語句來創(chuàng)建包體部分：語句來創(chuàng)建包體部分：CREATE PACKAGE BODY IS | AS END ;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包【例】下面創(chuàng)建程序包【例】下面創(chuàng)建程序包MyPack的包體體部分：的包體體部分：CREATE PACKAGE BODY MyPackISPROCEDURE ResetPwd( VUserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd

38、 = 111111 WHERE UserId = VUserId;END;FUNCTION 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;END MyPack;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 程序包管理查看程序包管理查看MyPack程序包體程序包體 第第1

39、010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 添加程序包添加程序包 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 調(diào)用程序包中的過程調(diào)用程序包中的過程. 調(diào)用程序包中的函數(shù)：調(diào)用程序包中的函數(shù)：. 【例】調(diào)用【例】調(diào)用MyPack.GetPwd函數(shù)，返回指定用戶的密碼信函數(shù)，返回指定用戶的密碼信息：息：SET ServerOutput ON;DECLARE varPwd Users.UserPwd%Type;BEGIN varPwd:=MyPack.GetPwd(Admin); dbms_output.put_line(varPwd);EN

40、D;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包程序包中過程的調(diào)用方法：程序包中過程的調(diào)用方法：【補充例】調(diào)用【補充例】調(diào)用MyPack. ResetPwd過程，修過程，修改指定用戶的密碼信息：改指定用戶的密碼信息：SET ServerOutput ON;DECLAREBEGINMyPack.ResetPwd(3); END;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器程序包程序包 DROP PACKAGE BODY命令刪除程序包體：命令刪除程序包體：DROP PACKAGE BODY UserMan.MyPack; DROP PACKAGE命令

41、刪除程序包的說明部分：命令刪除程序包的說明部分：DROP PACKAGE UserMan.MyPack;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器10.3觸發(fā)器管理觸發(fā)器管理 觸發(fā)器的基本概念觸發(fā)器的基本概念 創(chuàng)建及使用觸發(fā)器創(chuàng)建及使用觸發(fā)器 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器觸發(fā)器的基本概念觸發(fā)器的基本概念 觸發(fā)器觸發(fā)器是一種特殊的存儲過程，當(dāng)指定表中的數(shù)據(jù)是一種特殊的存儲過程，當(dāng)指定表中的數(shù)據(jù)發(fā)生變化時自動運行。發(fā)生變化時自動運行。 觸發(fā)器與普通存儲過程的不同之處在于：觸發(fā)器與普通存儲過程的不同之處在于：觸發(fā)器的觸發(fā)器的執(zhí)行是由事件觸發(fā)的，而

42、普通存儲過程是由命令調(diào)執(zhí)行是由事件觸發(fā)的，而普通存儲過程是由命令調(diào)用的。用的。第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器觸發(fā)器的基本概念觸發(fā)器的基本概念按觸發(fā)事件不同，可分為：按觸發(fā)事件不同，可分為：1. INSERT。當(dāng)指定的表發(fā)生插入（。當(dāng)指定的表發(fā)生插入（INSERT）操）操作時執(zhí)行觸發(fā)器。作時執(zhí)行觸發(fā)器。2. UPDATE。當(dāng)指定的表發(fā)生修改（。當(dāng)指定的表發(fā)生修改（UPDATE）操作時執(zhí)行觸發(fā)器。操作時執(zhí)行觸發(fā)器。3. DELETE。當(dāng)指定的表發(fā)生刪除（。當(dāng)指定的表發(fā)生刪除（DELETE）操）操作時執(zhí)行觸發(fā)器。作時執(zhí)行觸發(fā)器。第第1010章章 游標、存儲過程和觸發(fā)器

43、游標、存儲過程和觸發(fā)器觸發(fā)器的基本概念觸發(fā)器的基本概念 按觸發(fā)時間不同，可分為：按觸發(fā)時間不同，可分為：1. BEFORE。在指定的事件發(fā)生之前執(zhí)行觸發(fā)器。在指定的事件發(fā)生之前執(zhí)行觸發(fā)器。2. AFTER。在指定的事件發(fā)生之后執(zhí)行觸發(fā)器。在指定的事件發(fā)生之后執(zhí)行觸發(fā)器。按觸發(fā)級別不同，可分為：按觸發(fā)級別不同，可分為：1. 行觸發(fā)。對觸發(fā)事件影響的每一行執(zhí)行觸發(fā)器。行觸發(fā)。對觸發(fā)事件影響的每一行執(zhí)行觸發(fā)器。2. 語句觸發(fā)。對于觸發(fā)事件只能觸發(fā)一次，而且不能語句觸發(fā)。對于觸發(fā)事件只能觸發(fā)一次，而且不能訪問受觸發(fā)器影響的每一行的值。訪問受觸發(fā)器影響的每一行的值。第第1010章章 游標、存儲過程和觸發(fā)

44、器游標、存儲過程和觸發(fā)器創(chuàng)建及使用觸發(fā)器創(chuàng)建及使用觸發(fā)器 CREATE TRIGGER語句來創(chuàng)建觸發(fā)器：語句來創(chuàng)建觸發(fā)器：CREATE OR REPLACE TRIGGER BEFORE | AFTER ON FOR EACH ROW WHEN 第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器創(chuàng)建及使用觸發(fā)器創(chuàng)建及使用觸發(fā)器【例】創(chuàng)建一個觸發(fā)器【例】創(chuàng)建一個觸發(fā)器MyTrigger，它的作用是當(dāng)，它的作用是當(dāng)表表USERMAN.UserType中中TypeId列的值發(fā)生變列的值發(fā)生變化時，自動更新表化時，自動更新表USERMAN.Users中的中的UserType列的值，從而保證

45、數(shù)據(jù)的完整性：列的值，從而保證數(shù)據(jù)的完整性：CREATE OR REPLACE TRIGGER MyTriggerAFTER UPDATE ON UserTypeFOR EACH ROWBEGIN UPDATE Users SET UserType = :new.TypeId WHERE UserType = :old.TypeId;END;第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器創(chuàng)建及使用觸發(fā)器創(chuàng)建及使用觸發(fā)器第第1010章章 游標、存儲過程和觸發(fā)器游標、存儲過程和觸發(fā)器創(chuàng)建及使用觸發(fā)器創(chuàng)建及使用觸發(fā)器【例】創(chuàng)建一個觸發(fā)器【例】創(chuàng)建一個觸發(fā)器MyTrigger，它的作用是當(dāng)，它的作用是當(dāng)表表 Courses中中Cno列的值發(fā)生變化時，自動更列的值發(fā)生變化時，自動更新