游標(biāo)、存儲過程和觸發(fā)器_第1頁
游標(biāo)、存儲過程和觸發(fā)器_第2頁
游標(biāo)、存儲過程和觸發(fā)器_第3頁
游標(biāo)、存儲過程和觸發(fā)器_第4頁
游標(biāo)、存儲過程和觸發(fā)器_第5頁
已閱讀5頁,還剩42頁未讀 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡介

1、第第10章章 游標(biāo)、存儲過程和觸發(fā)器游標(biāo)、存儲過程和觸發(fā)器 課程描述課程描述介紹介紹OracleOracle數(shù)據(jù)庫程數(shù)據(jù)庫程序設(shè)計(jì)中經(jīng)常會用到序設(shè)計(jì)中經(jīng)常會用到的的3 3個概念,即游標(biāo)、個概念,即游標(biāo)、存儲過程和觸發(fā)器。存儲過程和觸發(fā)器。本章知識點(diǎn)本章知識點(diǎn)p 游標(biāo)游標(biāo) p 存儲過程管理存儲過程管理 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;【例】聲明一個游標(biāo)【例】聲明一個游標(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)控制語句【例】下面介紹一個完整的游標(biāo)應(yīng)用實(shí)例:【例】下面介紹一個完整的游標(biāo)應(yīng)用實(shí)例:/* 打開顯示模式打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分開始聲明部分 varId NUMBER; -聲明變量聲明變量,用來保存游標(biāo)中的用戶編號用來保存游標(biāo)中的用戶編號 varName VARCHAR2(50); -聲明變量聲明變量,用來保存游標(biāo)中的用戶名用來保存游標(biāo)中的用戶名 -定義游標(biāo)定義游標(biāo), varType為參數(shù)為參數(shù), 指定用戶類型編號指定用

6、戶類型編號 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -開始程序體開始程序體 OPEN MyCur(1); -打開游標(biāo)打開游標(biāo),參數(shù)為參數(shù)為1,表示讀取用戶類型編號為表示讀取用戶類型編號為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(用戶編號用戶編號: | varId |,

7、 用戶名用戶名: | varName); -顯示讀取的數(shù)據(jù)顯示讀取的數(shù)據(jù)END; -結(jié)束程序體結(jié)束程序體游標(biāo)屬性游標(biāo)屬性 (1 1)%ISOPEN%ISOPEN屬性屬性 【例】下面的代碼演示當(dāng)使用未打開的游標(biāo)時,將會出現(xiàn)錯誤:【例】下面的代碼演示當(dāng)使用未打開的游標(biāo)時,將會出現(xiàn)錯誤:/* 打開顯示模式 */SET ServerOutput ON; DECLARE -開始聲明部分 varName VARCHAR2(50); -聲明變量,用來保存游標(biāo)中的用戶名 varId NUMBER; -聲明變量,用來保存游標(biāo)中的用戶編號 -定義游標(biāo), varType為參數(shù), 指定用戶類型編號 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(用戶編號: | 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)中的用戶編號 -定義游標(biāo), varType為參數(shù), 指定用戶類型編號 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(用戶編號: | 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)中的用戶編號用來保存游標(biāo)中的用戶編號 - -定義游標(biāo)定義游標(biāo), varType, varType為參數(shù)為參數(shù), , 指定用戶類型編號指定用戶類型編號 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(用戶編號用戶編號: | 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)中的用戶編號 -定義游標(biāo), varType為參數(shù), 指定用戶類型編號 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(用戶編號: | 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ù), 指定用戶類型編號 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é)尾時退出循環(huán) /* 顯示保存在記錄變量var_UserRecord中的數(shù)據(jù) */ dbms_output.put_line(用戶編號: | 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(用戶編號: | var_UserRecord.UserId |, 用戶名: | var_UserRecord.UserName); EN

21、D LOOP;END; -結(jié)束程序體10.2存儲過程管理存儲過程管理 p 過程過程 一種基本的存儲過程,由過程名、參數(shù)和程序體組成。p 函數(shù)函數(shù) 與過程類似,只是函數(shù)有返回值。p 程序包程序包 一組相關(guān)的PL/SQL過程和函數(shù),由包名、說明部分和包體組成。p FUNCTION,PROCEDURE和和PACKAGE區(qū)別區(qū)別 function 和procedure是PL/SQL代碼的集合,通常為了完成一個任務(wù)。procedure 不需要返回任何值而function將返回一個值,Package是為了完成一個商業(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存儲過程管理存儲過程管理 過程過程p添加存儲過程添加存儲過程 過程過程p修改存儲過程修改存儲過程 函數(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ù) 【例】下面介紹一個示例函數(shù)【例】下面介紹一個示例函

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)的說明程序包程序包【例】下面介紹一個示例創(chuàng)建程序包【例】下面介紹一個示例創(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

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論