《sql server数据库应用与实践教程》课件第10章-游标

思考1

数据库中有教师表如下：Teacher(TeaID,TeaName,TeaTitle,CourseNum)其中，TeaTitle表示职称，CourseNum表示本月课时数，请根据教师表，写程序完成教师工资的自动生成。工资表如下：Salary(TeaID,TeaName,Wage),其中Wage当月表示工资。计算公式如下：Wage=基本工资+课时费基本工资：讲师（1000），副教授（1500），教授（2000）课时费=每课时工资×课时数每课时工资：讲师（80），副教授（90），教授（100）2TeaIDTeaNameTeaTitleCourseNum0001Mary讲师500002Tom教授400003Mike副教授40TeaIDTeaNameWage0001Mary50000002Tom60000003Mike5100第十章游标3目标4理解游标的概念使用游标10.1游标5

在开发数据库应用程序时，经常需要使用SELECT语句查询数据库时，查询返回的数据存放在结果集中。用户在得到结果集后，需要逐行逐列的获取其中存储的数据，根据不同的值做不同的处理。这时候需要使用游标，游标就是一种定位并控制结果集的机制。游标的基本概念6游标是映射结果集并在结果集内的单个行上建立一个位置的实体。有了游标，用户就可以访问结果集中的任意一行数据了。在将游标放置到某行之后，可以在该行或从该位置开始的行块上执行操作。最常见的操作是提取（检索）当前行或行块。10.3游标使用步骤7声明游标。打开游标。读取游标数据。关闭游标。释放游标。10.3.1声明游标8语法：

DECLARE游标名称CURSORFORselect语句示例：声明一个游标，结果集为“Student”表中所有的男同学。解决方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’10.3.2打开游标9语法：

OPEN游标名称示例：打开上例中声明的游标解决方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudent注：不能打开一个未被声明过的游标，否则编译器报错！已打开的游标关闭之前不能再次打开，否则编译器也报错！10.3.3读取游标数据10语法：

FETCHNEXT游标名称[INTO@变量名[,...n]]示例：基于上例中声明的游标，读取第一行的数据。解决方案：

DECLAREcurMaleStudentCURSORFORSELECT*FROMStudentWHEREStuSex=‘男’ OPENcurMaleStudentFETCHNEXTFROMcurMaleStudent10.3.4游标函数11@@FETCH_STATUS函数返回被FETCH语句执行的最后游标的状态。返回值如下：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使用游标读取数据到变量中14语法：FETCHNEXT游标名称[INTO@变量名[,...n]]说明：该语句的使用必须和游标声明语句配合使用DECLARE游标名称CURSORFORselect语句Select语句出现的字段必须与INTO后出现的变量名个数相等，且一一对应示例15使用游标遍历Department表中的每个系的系名，然后把系名打印出来。DECLARE@DepartmentNamevarchar(20)DECLAREcurDepartmentcursorforSELECTDepNameFROMDepartmentOPENcurDepartmentFETCHcurDepartmentinto@DepartmentNameWhile(@@fetch_status=0)BEGIN Print'DepartmentName='+@DepartmentName FETCHcurDepartmentinto@DepartmentNameENDCLOSEcurDepartmentDEALLOCATEcurDepartment游标一般创建在存储过程中使用练习16使用游标完成教师工资的生成DECLARE@TeaIDchar(10)--用于存放教师工号DECLARE@TeaTitlevarchar(20)--用于存放教师职称DECLARE@CourseNumint--用于存放教师本月课时数DECLARE@Wagedecimal--用于存放本月工资DECLAREcurTeacherWageCURSORFORSELECTTeaID,TeaTitle,CourseNumFROMTeacherOPENcurTeacherWage--声明游标指向教师表--将游标当前指向的行的TeaID、TeaTitle、CourseNum字段值赋给变量保存FETCHcurTeacherWageINTO@TeaID,@TeaTitle,@CourseNum练习17WHILE(@@FETCH_STATUS=0)BEGIN --根据教师的职称及课时数，计算工资，保存变量@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