第19章 存儲過程和游標(biāo)

1、第第1919章章 存儲過程和游標(biāo)存儲過程和游標(biāo)o19.1 19.1 存儲過程存儲過程o19.2 19.2 游標(biāo)游標(biāo)2022年3月16日2時27分119.1 19.1 存儲過程存儲過程o19.1.1 19.1.1 存儲過程概念存儲過程概念o19.1.2 19.1.2 創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行存儲過程o19.1.3 19.1.3 查看和修改存儲過程查看和修改存儲過程2022年3月16日2時27分219.1.1 19.1.1 存儲過程概念存儲過程概念o用用 T-SQL T-SQL編寫代碼時，可用兩種方法編寫代碼時，可用兩種方法存儲和執(zhí)行代碼存儲和執(zhí)行代碼：n 在客戶端存儲代碼，并創(chuàng)建向在客戶端存

2、儲代碼，并創(chuàng)建向 SQL SQL Server Server 發(fā)送發(fā)送SQLSQL命令（或命令（或SQLSQL語句）并處語句）并處理返回結(jié)果的應(yīng)用程序理返回結(jié)果的應(yīng)用程序n 將將SQLSQL語句存儲在數(shù)據(jù)庫服務(wù)器端語句存儲在數(shù)據(jù)庫服務(wù)器端數(shù)據(jù)庫數(shù)據(jù)庫中，由用戶調(diào)用執(zhí)行中，由用戶調(diào)用執(zhí)行o這些存儲在數(shù)據(jù)庫服務(wù)器端的這些存儲在數(shù)據(jù)庫服務(wù)器端的SQLSQL語句語句就是就是存儲過程存儲過程。2022年3月16日2時27分3存儲過程存儲過程功能功能o可以可以接受輸入?yún)?shù)并以輸出參數(shù)的形式接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個值返回至調(diào)用過程或批處理。將多個值返回至調(diào)用過程或批處理。o可以可以包含執(zhí)行數(shù)據(jù)庫

3、操作（包括調(diào)用其包含執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其它過程）的語句。它過程）的語句。 o可以可以向調(diào)用者返回狀態(tài)值，以表明成功向調(diào)用者返回狀態(tài)值，以表明成功或失敗?；蚴?。2022年3月16日2時27分4存儲過程好處存儲過程好處o允許模塊化程序設(shè)計允許模塊化程序設(shè)計o改善性能改善性能o減少網(wǎng)絡(luò)流量減少網(wǎng)絡(luò)流量o可作為安全機制使用可作為安全機制使用2022年3月16日2時27分519.1.2 19.1.2 創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行存儲過程CREATE PROCCREATE PROC EDURE EDURE 存儲過程名存儲過程名 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 = = defaultdefault

4、-默認(rèn)值默認(rèn)值 OUTPUT OUTPUT -是輸出參數(shù)是輸出參數(shù) , . , . n n AS AS SQLSQL語句語句 . . n n 2022年3月16日2時27分6執(zhí)行存儲過程的執(zhí)行存儲過程的SQLSQL語句語句 EXECEXEC UTE UTE 存儲過程名存儲過程名 實參實參 , OUTPUT , n , OUTPUT , n 2022年3月16日2時27分7o 例例1 1查詢計算機系學(xué)生的考試情況，列查詢計算機系學(xué)生的考試情況，列出學(xué)生的姓名、課程名和考試成績。出學(xué)生的姓名、課程名和考試成績。2022年3月16日2時27分8執(zhí)行此存儲過程：執(zhí)行此存儲過程： EXEC studen

5、t_grade1 EXEC student_grade1CREATE PROCEDURE student_grade1CREATE PROCEDURE student_grade1ASAS SELECT SELECT SnameSname, , CnameCname, Grade, Grade FROM Student s INNER JOIN SC FROM Student s INNER JOIN SC ON ON s.Snos.Sno = = SC.SnoSC.Sno INNER JOIN Course c INNER JOIN Course c ON ON c.Cnoc.Cno = =

6、 sc.Cnosc.Cno WHERE WHERE SdeptSdept = = 計算機系計算機系 o 例例2 2帶有輸入?yún)?shù)的存儲過程：查詢某帶有輸入?yún)?shù)的存儲過程：查詢某個指定系學(xué)生的考試情況，列出學(xué)生的個指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績。姓名、所在系、課程名和考試成績。2022年3月16日2時27分9執(zhí)行：查詢信息管理系學(xué)生的修課情況執(zhí)行：查詢信息管理系學(xué)生的修課情況。 EXEC student_grade2 EXEC student_grade2 信息管理系信息管理系 CREATE PROCEDURE student_grade2CREATE PROCED

7、URE student_grade2 dept char(20)dept char(20)ASAS SELECT SELECT SnameSname, , SdeptSdept, , CnameCname, Grade, Grade FROM Student s INNER JOIN SC FROM Student s INNER JOIN SC ON ON s.Snos.Sno = = SC.SnoSC.Sno INNER JOIN Course c INNER JOIN Course c ON ON c.Cnoc.Cno = = SC.CnoSC.Cno WHERE WHERE Sdept

8、Sdept = dept = depto 例例3 3帶有多個輸入?yún)?shù)并有默認(rèn)值的存帶有多個輸入?yún)?shù)并有默認(rèn)值的存儲過程：查詢某個學(xué)生某門課程的考試儲過程：查詢某個學(xué)生某門課程的考試成績，若沒有指定課程，則默認(rèn)課程為成績，若沒有指定課程，則默認(rèn)課程為VBVB。2022年3月16日2時27分10CREATE PROCEDURE student_grade3 CREATE PROCEDURE student_grade3 snamesname char(10), char(10), cnamecname char(20) = VB char(20) = VBAS AS SELECT SELECT S

9、nameSname, , CnameCname, Grade, Grade FROM Student s INNER JOIN SC FROM Student s INNER JOIN SC ON ON s.Snos.Sno = = SC.snoSC.sno INNER JOIN Course c INNER JOIN Course c ON ON c.Cnoc.Cno = = SC.CnoSC.Cno WHERE WHERE snamesname = = snamesname AND AND cnamecname = = cnamecname參數(shù)傳遞方式參數(shù)傳遞方式o（1 1）按參數(shù)位置傳遞

10、值）按參數(shù)位置傳遞值n 執(zhí)行存儲過程的執(zhí)行存儲過程的EXECEXEC語句中的實參排列順語句中的實參排列順序必須與定義存儲過程時定義的參數(shù)的順序必須與定義存儲過程時定義的參數(shù)的順序一致。序一致。o（2 2）按參數(shù)名傳遞值按參數(shù)名傳遞值n 執(zhí)行存儲過程的執(zhí)行存儲過程的EXECEXEC語句中要指明定義存語句中要指明定義存儲過程時指定的參數(shù)的名字以及此參數(shù)的儲過程時指定的參數(shù)的名字以及此參數(shù)的值，而不關(guān)心參數(shù)的定義順序。值，而不關(guān)心參數(shù)的定義順序。2022年3月16日2時27分11示例示例o按參數(shù)位置傳遞按參數(shù)位置傳遞EXEC student_grade3 EXEC student_grade3 吳賓

11、吳賓, , 高等數(shù)學(xué)高等數(shù)學(xué) o按參數(shù)名傳遞按參數(shù)名傳遞EXEC Student_grade3 EXEC Student_grade3 snamesname = = 吳賓吳賓, , cnamecname = = 高等數(shù)學(xué)高等數(shù)學(xué)2022年3月16日2時27分12關(guān)于默認(rèn)值關(guān)于默認(rèn)值o如果在定義存儲過程時為參數(shù)指定了如果在定義存儲過程時為參數(shù)指定了默認(rèn)值，則在執(zhí)行存儲過程時可以不默認(rèn)值，則在執(zhí)行存儲過程時可以不為有默認(rèn)值的參數(shù)提供值。為有默認(rèn)值的參數(shù)提供值。o例如：例如： EXEC student_grade3 EXEC student_grade3 吳賓吳賓 o相當(dāng)于執(zhí)行：相當(dāng)于執(zhí)行： EXE

12、C student_grade3 EXEC student_grade3 吳賓吳賓, VB, VB2022年3月16日2時27分13o 例例4 4帶有輸出參數(shù)的存儲過程。計算全帶有輸出參數(shù)的存儲過程。計算全體學(xué)生人數(shù)，并將計算結(jié)果作為輸出參體學(xué)生人數(shù)，并將計算結(jié)果作為輸出參數(shù)返回給調(diào)用者。數(shù)返回給調(diào)用者。2022年3月16日2時27分14 執(zhí)行此存儲過程：執(zhí)行此存儲過程： Declare res Declare res intint -變量聲明語句變量聲明語句 Execute Execute Count_TotalCount_Total res res outputoutput Print r

13、es Print res -輸出語句輸出語句Create Procedure Create Procedure Count_TotalCount_Totaltotal total intint output outputAs As Select total = COUNT(Select total = COUNT(* *) FROM Student ) FROM Student 局部變量局部變量o 例例5 5帶輸入?yún)?shù)和輸出參數(shù)的存儲過程。帶輸入?yún)?shù)和輸出參數(shù)的存儲過程。統(tǒng)計指定課程（課程名）的平均成績，并統(tǒng)計指定課程（課程名）的平均成績，并將統(tǒng)計的結(jié)果作為輸出參數(shù)。將統(tǒng)計的結(jié)果作為輸出參數(shù)。

14、CREATE PROC CREATE PROC AvgGradeAvgGrade cncn char(20), char(20), avg_gradeavg_grade intint output outputASAS SELECT SELECT avg_gradeavg_grade = = AVG(GradeAVG(Grade) FROM SC ) FROM SC JOIN Course C ON JOIN Course C ON C.CnoC.Cno = = SC.CnoSC.Cno WHERE WHERE CnameCname = = cncn執(zhí)行此存儲過程，查詢執(zhí)行此存儲過程，查詢VB

15、VB課程的平均成績。課程的平均成績。 DECLARE DECLARE Avg_GradeAvg_Grade intint EXEC EXEC AvgGradeAvgGrade VB, VB, Avg_GradeAvg_Grade output output Print Print Avg_GradeAvg_Grade示例示例o 例例6 6刪除指定課程（課程名）考試成績刪除指定課程（課程名）考試成績不及格學(xué)生的此門課程的修課記錄。不及格學(xué)生的此門課程的修課記錄。2022年3月16日2時27分16CREATE PROC CREATE PROC Del_SCDel_SC cncn varchar(2

16、0) varchar(20)ASAS DELETE FROM SC WHERE Grade 60 DELETE FROM SC WHERE Grade = 80 = dept AND Grade = 802022年3月16日2時27分1919.2 19.2 游標(biāo)游標(biāo)o19.2.1 19.2.1 游標(biāo)概念游標(biāo)概念o19.2.2 19.2.2 使用游標(biāo)使用游標(biāo)o19.2.3 19.2.3 游標(biāo)示例游標(biāo)示例2022年3月16日2時27分20游標(biāo)游標(biāo)o由由SELECTSELECT語句返回的行集包括所有滿語句返回的行集包括所有滿足條件子句的行，這足條件子句的行，這個個行集被稱為行集被稱為結(jié)結(jié)果集果集。o

17、但有時用戶需要對結(jié)果集中的每一行但有時用戶需要對結(jié)果集中的每一行或部分行進(jìn)行單獨的處理，或部分行進(jìn)行單獨的處理，而而這在這在SELECTSELECT的結(jié)果集中是無法實現(xiàn)的。的結(jié)果集中是無法實現(xiàn)的。o游標(biāo)游標(biāo)就是提供這種機制的結(jié)果集擴展就是提供這種機制的結(jié)果集擴展，它使我們可以逐行處理結(jié)果集。，它使我們可以逐行處理結(jié)果集。2022年3月16日2時27分2119.2.1 19.2.1 游標(biāo)概念游標(biāo)概念o 游標(biāo)包括兩部分內(nèi)容：游標(biāo)包括兩部分內(nèi)容：n 游標(biāo)游標(biāo)結(jié)果集結(jié)果集：由定義游標(biāo)的：由定義游標(biāo)的SELECTSELECT語句返回語句返回的結(jié)果的集合。的結(jié)果的集合。n 游標(biāo)游標(biāo)當(dāng)前行指針當(dāng)前行指針：指

18、向該結(jié)果集中某一行的：指向該結(jié)果集中某一行的指針。指針。2022年3月16日2時27分22游標(biāo)當(dāng)前游標(biāo)當(dāng)前行指針行指針游標(biāo)結(jié)游標(biāo)結(jié)果集果集游標(biāo)特點游標(biāo)特點o允許定位結(jié)果集中的特定行。允許定位結(jié)果集中的特定行。o允許從結(jié)果集的當(dāng)前位置檢索行。允許從結(jié)果集的當(dāng)前位置檢索行。o支持對結(jié)果集當(dāng)前行數(shù)據(jù)進(jìn)行修改支持對結(jié)果集當(dāng)前行數(shù)據(jù)進(jìn)行修改。o為由其他用戶對顯示在結(jié)果集中的數(shù)為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)所做的更改提供不同級別的可見性據(jù)所做的更改提供不同級別的可見性支持。支持。2022年3月16日2時27分2319.2.2 19.2.2 使用游標(biāo)使用游標(biāo)2022年3月16日2時27分24是是否否聲明

19、游標(biāo)聲明游標(biāo)打開游標(biāo)打開游標(biāo)提取數(shù)據(jù)提取數(shù)據(jù)處理完成？處理完成？關(guān)閉游標(biāo)關(guān)閉游標(biāo)釋放資源釋放資源聲明游標(biāo)聲明游標(biāo)DECLARE DECLARE cursor_namecursor_name CURSOR CURSOR FORWARD_ONLY | SCROLL FORWARD_ONLY | SCROLL -指針移動方式指針移動方式 -游標(biāo)類型游標(biāo)類型 靜態(tài)靜態(tài)| |鍵集鍵集| |動態(tài)動態(tài)| |只向前只向前 STATIC|KEYSET|DYNAMIC|FAST_FORWARDSTATIC|KEYSET|DYNAMIC|FAST_FORWARD -游標(biāo)操作方式游標(biāo)操作方式 只讀只讀| |可定位更改

20、可定位更改| |不可定位更改不可定位更改 READ_ONLY | SCROLL_LOCKS | OPTIMISTIC READ_ONLY | SCROLL_LOCKS | OPTIMISTIC FOR FOR select_statementselect_statement FOR UPDATE OF FOR UPDATE OF column_namecolumn_name ,.n ,.n 2022年3月16日2時27分25打開游標(biāo)打開游標(biāo)OPEN OPEN cursor_namecursor_nameo只能打開已聲明但還沒有打開的游只能打開已聲明但還沒有打開的游標(biāo)標(biāo)。o打開之后，游標(biāo)的當(dāng)前行

21、指針打開之后，游標(biāo)的當(dāng)前行指針指向指向結(jié)果集第一行結(jié)果集第一行。2022年3月16日2時27分26提取數(shù)據(jù)提取數(shù)據(jù)FETCHFETCH NEXT | PRIOR | FIRST | LAST NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | | ABSOLUTE n | nvarnvar | RELATIVE n | | RELATIVE n | nvarnvar FROM FROM cursor_namecursor_name INTO INTO variable_namevariable_name,.n,.n2022年3月16日2時27分27FETCH_

22、STATUSFETCH_STATUSo在對游標(biāo)數(shù)據(jù)進(jìn)行提取的過程中，可在對游標(biāo)數(shù)據(jù)進(jìn)行提取的過程中，可以使用以使用FETCH_STATUSFETCH_STATUS全局變量判斷全局變量判斷數(shù)據(jù)提取的狀態(tài)。數(shù)據(jù)提取的狀態(tài)。oFETCH_STATUSFETCH_STATUS返回返回FETCHFETCH語句執(zhí)行后語句執(zhí)行后的游標(biāo)最終狀態(tài)。的游標(biāo)最終狀態(tài)。2022年3月16日2時27分28返回值返回值含義含義0 0FETCHFETCH語句成功。語句成功。-1-1FETCHFETCH語句失敗或此行不在結(jié)果集中。語句失敗或此行不在結(jié)果集中。-2-2被提取的行不存在。被提取的行不存在。關(guān)閉游標(biāo)關(guān)閉游標(biāo)CLOS

23、E CLOSE cursor_namecursor_name o關(guān)閉游標(biāo)后，系統(tǒng)并關(guān)閉游標(biāo)后，系統(tǒng)并不不釋放游標(biāo)的資釋放游標(biāo)的資源，并且也沒有改變游標(biāo)的定義，源，并且也沒有改變游標(biāo)的定義，可可再次使用再次使用OPENOPEN語句重新打開此游標(biāo)語句重新打開此游標(biāo)。2022年3月16日2時27分29釋放游標(biāo)釋放游標(biāo)DEALLOCATE DEALLOCATE cursor_namecursor_name o釋放分配給游標(biāo)的所有資源。釋放分配給游標(biāo)的所有資源。2022年3月16日2時27分3019.2.3 19.2.3 游標(biāo)示例游標(biāo)示例o 例例9. 9. 定義一個查詢姓定義一個查詢姓“王王”的學(xué)生姓名

24、的學(xué)生姓名和所在系的游標(biāo)，并輸出游標(biāo)結(jié)果。和所在系的游標(biāo)，并輸出游標(biāo)結(jié)果。2022年3月16日2時27分31- 聲明存放結(jié)果集各列數(shù)據(jù)的變量DECLARE sn CHAR(10), dept VARCHAR(20)- 聲明游標(biāo) DECLARE Sname_cursor CURSOR FOR SELECT Sname, Sdept FROM Student WHERE Sname LIKE 王%- 打開游標(biāo)OPEN Sname_cursor - 首先提取第一行數(shù)據(jù) FETCH NEXT FROM Sname_cursor INTO sn, dept - 檢查FETCH_STATUS判斷是否還有可提取的數(shù)據(jù)o 例例10. 10. 聲明帶聲明帶SCROLLSCROLL選項的游標(biāo)，并通選項的游標(biāo)，并通過絕對定位功能實現(xiàn)游標(biāo)當(dāng)前行的任意過絕對定位功能實現(xiàn)游標(biāo)當(dāng)前行的任意方向的滾動。聲明查詢計算機系學(xué)生姓方向的滾動。聲明查詢計算機系學(xué)生姓名、選的課程名和成績的游標(biāo)，并將游名、選的課程名和成