《sql server數(shù)據(jù)庫應用與實踐教程》課件第10章-游標

思考1

數(shù)據(jù)庫中有教師表如下：Teacher(TeaID,TeaName,TeaTitle,CourseNum)其中，TeaTitle表示職稱，CourseNum表示本月課時數(shù)，請根據(jù)教師表，寫程序完成教師工資的自動生成。工資表如下：Salary(TeaID,TeaName,Wage),其中Wage當月表示工資。計算公式如下：Wage=基本工資+課時費基本工資：講師（1000），副教授（1500），教授（2000）課時費=每課時工資×課時數(shù)每課時工資：講師（80），副教授（90），教授（100）2TeaIDTeaNameTeaTitleCourseNum0001Mary講師500002Tom教授400003Mike副教授40TeaIDTeaNameWage0001Mary50000002Tom60000003Mike5100第十章游標3目標4理解游標的概念使用游標10.1游標5

在開發(fā)數(shù)據(jù)庫應用程序時，經常需要使用SELECT語句查詢數(shù)據(jù)庫時，查詢返回的數(shù)據(jù)存放在結果集中。用戶在得到結果集后，需要逐行逐列的獲取其中存儲的數(shù)據(jù)，根據(jù)不同的值做不同的處理。這時候需要使用游標，游標就是一種定位并控制結果集的機制。游標的基本概念6游標是映射結果集并在結果集內的單個行上建立一個位置的實體。有了游標，用戶就可以訪問結果集中的任意一行數(shù)據(jù)了。在將游標放置到某行之后，可以在該行或從該位置開始的行塊上執(zhí)行操作。最常見的操作是提?。z索）當前行或行塊。10.3游標使用步驟7聲明游標。打開游標。讀取游標數(shù)據(jù)。關閉游標。釋放游標。10.3.1聲明游標8語法：

DECLARE游標名稱CURSORFORselect語句示例：聲明一個游標，結果集為“Student”表中所有的男同學。解決方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’10.3.2打開游標9語法：

OPEN游標名稱示例：打開上例中聲明的游標解決方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudent注：不能打開一個未被聲明過的游標，否則編譯器報錯！已打開的游標關閉之前不能再次打開，否則編譯器也報錯！10.3.3讀取游標數(shù)據(jù)10語法：

FETCHNEXT游標名稱[INTO@變量名[,...n]]示例：基于上例中聲明的游標，讀取第一行的數(shù)據(jù)。解決方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudentFETCHNEXTFROMcurMaleStudent10.3.4游標函數(shù)11@@FETCH_STATUS函數(shù)返回被FETCH語句執(zhí)行的最后游標的狀態(tài)。返回值如下：0：FETCH語句成功?1：FETCH語句失敗或此行不在結果集中?2：被提取的行不存在

示例12示例：使用游標遍歷“Student”表中的每一條記錄。解決方案：DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudentFETCHNEXTFROMcurMaleStudent WHILE@@FETCH_STATUS=0FETCHNEXTFROMcurMaleStudent

10.3.5-10.3.6關閉及釋放游標13關閉游標語法：CLOSE游標名稱釋放游標語法：DEALLOCATE游標名稱注：游標關閉并不代表釋放資源，釋放前不能申明同名游標。示例：使用游標遍歷“Student”表中的每一條記錄。解決方案：DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudentFETCHNEXTFROMcurMaleStudent WHILE@@FETCH_STATUS=0FETCHNEXTFROMcurMaleStudentCLOSEcurMaleStudentDEALLOCATEcurMaleStudent10.4使用游標讀取數(shù)據(jù)到變量中14語法：FETCHNEXT游標名稱[INTO@變量名[,...n]]說明：該語句的使用必須和游標聲明語句配合使用DECLARE游標名稱CURSORFORselect語句Select語句出現(xiàn)的字段必須與INTO后出現(xiàn)的變量名個數(shù)相等，且一一對應示例15使用游標遍歷Department表中的每個系的系名，然后把系名打印出來。DECLARE@DepartmentNamevarchar(20)DECLAREcurDepartmentcursorforSELECTDepNameFROMDepartmentOPENcurDepartmentFETCHcurDepartmentinto@DepartmentNameWhile(@@fetch_status=0)BEGIN Print'DepartmentName='+@DepartmentName FETCHcurDepartmentinto@DepartmentNameENDCLOSEcurDepartmentDEALLOCATEcurDepartment游標一般創(chuàng)建在存儲過程中使用練習16使用游標完成教師工資的生成DECLARE@TeaIDchar(10)--用于存放教師工號DECLARE@TeaTitlevarchar(20)--用于存放教師職稱DECLARE@CourseNumint--用于存放教師本月課時數(shù)DECLARE@Wagedecimal--用于存放本月工資DECLAREcurTeacherWageCURSORFORSELECTTeaID,TeaTitle,CourseNumFROMTeacherOPENcurTeacherWage--聲明游標指向教師表--將游標當前指向的行的TeaID、TeaTitle、CourseNum字段值賦給變量保存FETCHcurTeacherWageINTO@TeaID,@TeaTitle,@CourseNum練習17WHILE(@@FETCH_STATUS=0)BEGIN --根據(jù)教師的職稱及課時數(shù)，計算工資，保存變量@Wage IF(@TeaTitle='講師') SET@Wage=1000+80*@CourseNum ELSEIF(@TeaTitle='副教授') SET@Wage=1500+90*@CourseNum ELSE SET@Wage=2000+100*@CourseNum INSERTINTOSalaryVALUES(@TeaID,@Wage)--插入當前教師的工資

FETCHcurTeacherWageINTO

@TeaID,@TeaTitle,@CourseNumENDCLOSEcurTeacherWageDEALLOCATEcurTeacherWage10.5嵌套游標18有學生表和系表，游標1先找出系表中的每個系，對于找到的系，再使用游標2找出該系的所有學生的信息。嵌套游標19語法：declarecursor_name1for…opencursor_name1fetchcursor_name1While(@@fetch_status=0)begin declarecursor_name2for… opencursor_name2 fetchcursor_name2 while(@@fetch_status=0) … closecursor_name2 deallocatecursor_name2

fetchcursor_name1endclosecursor_name1deallocatecursor_name1示例20有學生表和系表，使用嵌套游標，游標1先找出系表中的每個系，對于找到的系，再使用游標2找出該系的所有學生的信息。示例21有學生表和系表，使用嵌套游標，游標1先找出系表中的每個系，對于找到的系，再使用游標2找出該系的所有學生的信息。declare@DepIDintdeclarecurDepartmentcursorforselectDepIDfromdepartmentopencurDepartmentfetchcurDepartmentinto@DepIDWhile(@@fetch_status=0)begin

declarecurStudentcursorforselect*fromstudent