数据库系统教程 教学课件 ppt 作者 何玉洁 李宝安 第19章 存储过程和游标

《数据库系统教程 教学课件 ppt 作者 何玉洁 李宝安 第19章 存储过程和游标》由会员分享，可在线阅读，更多相关《数据库系统教程 教学课件 ppt 作者 何玉洁 李宝安 第19章 存储过程和游标（35页珍藏版）》请在金锄头文库上搜索。

1、数据库系统教程,第19章 存储过程和游标,1,第19章 存储过程和游标,19.1 存储过程 19.2 游标,2019年5月24日8时46分,2,19.1 存储过程,19.1.1 存储过程概念 19.1.2 创建和执行存储过程 19.1.3 查看和修改存储过程,2019年5月24日8时46分,3,19.1.1 存储过程概念,用 T-SQL编写代码时，可用两种方法存储和执行代码： 在客户端存储代码，并创建向 SQL Server 发送SQL命令（或SQL语句）并处理返回结果的应用程序 将SQL语句存储在数据库服务器端数据库中，由用户调用执行 这些存储在数据库服务器端的SQL语句就是存储过程。,20

2、19年5月24日8时46分,4,存储过程功能,可以接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。 可以包含执行数据库操作（包括调用其它过程）的语句。 可以向调用者返回状态值，以表明成功或失败。,2019年5月24日8时46分,5,存储过程好处,允许模块化程序设计 改善性能 减少网络流量 可作为安全机制使用,2019年5月24日8时46分,6,19.1.2 创建和执行存储过程,CREATE PROC EDURE 存储过程名 参数名 数据类型 = default -默认值 OUTPUT -是输出参数 , . n AS SQL语句 . n ,2019年5月24日8时46分,7,执行存

3、储过程的SQL语句, EXEC UTE 存储过程名 实参 , OUTPUT , n ,2019年5月24日8时46分,8,例1查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。,2019年5月24日8时46分,9,执行此存储过程： EXEC student_grade1,CREATE PROCEDURE student_grade1 AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.Sno INNER JOIN Course c ON c.Cno = sc.Cno WHERE Sdept

4、 = 计算机系,例2带有输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。,2019年5月24日8时46分,10,执行：查询信息管理系学生的修课情况。 EXEC student_grade2 信息管理系,CREATE PROCEDURE student_grade2 dept char(20) AS SELECT Sname, Sdept, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.Sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE Sde

5、pt = dept,例3带有多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为VB。,2019年5月24日8时46分,11,CREATE PROCEDURE student_grade3 sname char(10), cname char(20) = VB AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE sname = sname AND cname = c

6、name,参数传递方式,（1）按参数位置传递值 执行存储过程的EXEC语句中的实参排列顺序必须与定义存储过程时定义的参数的顺序一致。 （2）按参数名传递值 执行存储过程的EXEC语句中要指明定义存储过程时指定的参数的名字以及此参数的值，而不关心参数的定义顺序。,2019年5月24日8时46分,12,示例,按参数位置传递 EXEC student_grade3 吴宾, 高等数学 按参数名传递 EXEC Student_grade3 sname = 吴宾, cname = 高等数学,2019年5月24日8时46分,13,关于默认值,如果在定义存储过程时为参数指定了默认值，则在执行存储过程时可以不为

7、有默认值的参数提供值。 例如： EXEC student_grade3 吴宾 相当于执行： EXEC student_grade3 吴宾, VB,2019年5月24日8时46分,14,例4带有输出参数的存储过程。计算全体学生人数，并将计算结果作为输出参数返回给调用者。,2019年5月24日8时46分,15,执行此存储过程： Declare res int -变量声明语句 Execute Count_Total res output Print res -输出语句,Create Procedure Count_Total total int output As Select total = CO

8、UNT(*) FROM Student,局部变量,例5带输入参数和输出参数的存储过程。统计指定课程（课程名）的平均成绩，并将统计的结果作为输出参数。,CREATE PROC AvgGrade cn char(20), avg_grade int output AS SELECT avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn,执行此存储过程，查询VB课程的平均成绩。 DECLARE Avg_Grade int EXEC AvgGrade VB, Avg_Grade output Pr

9、int Avg_Grade,示例,例6删除指定课程（课程名）考试成绩不及格学生的此门课程的修课记录。,2019年5月24日8时46分,17,CREATE PROC Del_SC cn varchar(20) AS DELETE FROM SC WHERE Grade 60 AND Cno IN ( SELECT Cno FROM Course WHERE Cname = cn),示例,例7将指定课程（课程号）的学分增加指定的分数。,2019年5月24日8时46分,18,CREATE PROC Update_Credit cno varchar(10), inc int AS UPDATE Co

10、urse SET Credit=Credit + inc WHERE Cno = cno,19.1.3 修改存储过程,ALTER PROC EDURE 存储过程名 参数名 数据类型 = default OUTPUT , . n AS SQL语句 . n ,2019年5月24日8时46分,19,示例,例8修改例2定义的存储过程，使其查询指定系考试成绩大于等于80分的学生的修课情况。 ALTER PROCEDURE student_grade2 dept char(20) AS SELECT Sname, Sdept, Cname, Grade FROM Student s INNER JOIN

11、SC ON s.Sno = SC.Sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = dept AND Grade = 80,2019年5月24日8时46分,20,19.2 游标,19.2.1 游标概念 19.2.2 使用游标 19.2.3 游标示例,2019年5月24日8时46分,21,游标,由SELECT语句返回的行集包括所有满足条件子句的行，这个行集被称为结果集。 但有时用户需要对结果集中的每一行或部分行进行单独的处理，而这在SELECT的结果集中是无法实现的。 游标就是提供这种机制的结果集扩展，它使我们可以逐行处理结果集。,2

12、019年5月24日8时46分,22,19.2.1 游标概念,游标包括两部分内容： 游标结果集：由定义游标的SELECT语句返回的结果的集合。 游标当前行指针：指向该结果集中某一行的指针。,2019年5月24日8时46分,23,游标特点,允许定位结果集中的特定行。 允许从结果集的当前位置检索行。 支持对结果集当前行数据进行修改。 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。,2019年5月24日8时46分,24,19.2.2 使用游标,2019年5月24日8时46分,25,声明游标,DECLARE cursor_name CURSOR FORWARD_ONLY | SC

13、ROLL -指针移动方式 -游标类型静态|键集|动态|只向前 STATIC|KEYSET|DYNAMIC|FAST_FORWARD -游标操作方式只读|可定位更改|不可定位更改 READ_ONLY | SCROLL_LOCKS | OPTIMISTIC FOR select_statement FOR UPDATE OF column_name ,.n ,2019年5月24日8时46分,26,打开游标,OPEN cursor_name 只能打开已声明但还没有打开的游标。 打开之后，游标的当前行指针指向结果集第一行。,2019年5月24日8时46分,27,提取数据,FETCH NEXT | PR

14、IOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM cursor_name INTO variable_name,.n,2019年5月24日8时46分,28,FETCH_STATUS,在对游标数据进行提取的过程中，可以使用FETCH_STATUS全局变量判断数据提取的状态。 FETCH_STATUS返回FETCH语句执行后的游标最终状态。,2019年5月24日8时46分,29,关闭游标,CLOSE cursor_name 关闭游标后，系统并不释放游标的资源，并且也没有改变游标的定义，可再次使用OPEN语句重新打开此游标

15、。,2019年5月24日8时46分,30,释放游标,DEALLOCATE cursor_name 释放分配给游标的所有资源。,2019年5月24日8时46分,31,19.2.3 游标示例,例9. 定义一个查询姓“王”的学生姓名和所在系的游标，并输出游标结果。,2019年5月24日8时46分,32,例10. 声明带SCROLL选项的游标，并通过绝对定位功能实现游标当前行的任意方向的滚动。声明查询计算机系学生姓名、选的课程名和成绩的游标，并将游标内容按成绩降序排序。,2019年5月24日8时46分,33,例11建立生成报表的游标。生成显示如下报表形式的游标：首先列出一门课程名（只考虑有人选的课程），然后在此门课程下列出该门课程考试成绩大于等于80分的学生名、性别、所在系和成绩；然后再列出第二门课程名，再在此课程下列出该门课程考试成绩大于等于80分的学生名、性别、所在系和成绩；依此类推。,2019年5月24日8时46分,35,本章学习 顺利结束,