《计算机SQLT》PPT课件.ppt

第四阶段,存储过程,本章案例,查看C#编程考试成绩平均分以及未通过考试的学生名单 查看某门考试成绩平均分以及未通过考试的学生名单，及格线可能随时变化，不再是60分。 创建一个实现加法计算并将运算结果作为输出参数的存储过程 查看某门考试成绩平均分以及未通过考试的学生名单，并返回未通过的人数。,本章目标,理解什么是存储过程及其作用 掌握常用系统存储过程 掌握创建存储过程的SQL语句及用法,预习检查,存储过程分为哪三类？ 系统存储过程的名称一般以什么字符开头？ 创建存储过程时，声明输出参数的关键字是什么？,提问,什么是存储过程,存储过程（Stored Procedured）是由一些SQL语句和控制语句组成的被封装起来的过程。 存储过程驻留在数据库中，可以被应用程序调用，也可以被另一个存储过程调用。,存储过程,存储过程 是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中 。 存储过程的特点 存储过程在服务器端运行，执行速度快 。 存储过程执行一次以后 ，其执行规划就驻留在高速缓冲存储器。 确保数据库的安全性 。 减少网络流量 。 允许更快执行 。,简单认识存储过程,简单认识存储过程,为什么使用存储过程,减少网络通信量 执行速度更快 更强的适应性 分布式工作,存储过程的分类,存储过程分为三类 系统存储过程 扩展存储过程 用户定义的存储过程,系统存储过程,系统存储过程（System stored procedured）的名字以“SP_”为前缀,演示：打开系统存储过程,常用的系统存储过程,调用系统存储过程,扩展存储过程,扩展存储过程（Extended stored procedured）是对动态链接库（DLL）函数的调用。扩展存储过程通常是以“XP_”为前缀。,演示：扩展存储过程的调用,用户自定义存储过程,用户可以创建以下三种存储过程: 不带参数的存储过程 带参数的存储过程 带输出参数的存储过程,创建不带参数的存储过程,语法,T-SQL语句,建议：自定义存储过程的名称最好以USP_开头,创建存储过程的示例,查看C#编程考试成绩平均分以及未通过考试的 学生名单,问题,演示：创建不带参数的存储过程,调用不带参数的存储过程,EXEC 存储过程名,语法,演示：调用不带参数的存储过程,创建带输入参数的存储过程,语法,T-SQL语句,创建带参数的存储过程的示例,查看某门考试成绩平均分以及未通过考试的学生 名单，及格线可能随时变化，不再是60分。,问题,分析,由于考试科目和及格线不确定，需要在存储过程中设置两个参数，用于接受用户传递过来的考试科目和及格线。,演示：创建带参数的存储过程,调用带输入参数的存储过程,方式一： exec usp_score_byparam SQL Server基础编程,70 方式二： exec usp_score_byparam coursename=SQL Server基础编程 ,pass=70,参数的名称必须和存储过程中的参数相同,演示：调用带参数的存储过程,课堂练习,问题描述：创建存储过程，查看某个班级的所有学员的信息。 掌握要点：带参数的存储过程,创建带输出参数的存储过程,语法,T-SQL语句,创建带输出参数的存储过程,创建一个实现加法计算并将运算结果作为输出参数的存储过程,问题,演示：创建带输出参数的存储过程,调用带输出参数的存储过程,调用时也要使用OUTPUT,演示：调用带输出参数的存储过程,存储过程的示例,查看某门考试成绩平均分以及未通过考试的学生名单，并返回未通过的人数,问题,演示：创建和调用存储过程,错误处理,如何使用系统变量ERROR？,提问,用户自定义错误,除了使用系统变量ERROR，我们还可以使用RAISERROR语句定义用户指定的错误信息，用户同样也可以得到错误信息。,语法,用户自定义错误,演示：用户自定义错误,相关实践知识8-1,使用只读游标，查询并显示输出pubs数据库中的authors表中state字段的值是CA的au_fname、au_lname的值。打印出“author name：au_lname的值 au_fname的值”。,USE pubs GO /*定义只读游标*/ DECLARE author_csr CURSOR READ_ONLY FOR SELECT au_fname,au_lname FROM authors WHERE state=CA ORDER BY au_fname,au_lname /*定义变量*/ DECLARE lname varchar(20),fname varchar(20),定义游标,相关实践知识8-2,/*打开游标*/ OPEN author_csr /*执行第一次数据读取操作*/ FETCH NEXT FROM author_csr INTO lname,fname /*循环游标读取操作*/ WHILE FETCH_STATUS=0 BEGIN PRINT author name:+lname+ +fname FETCH NEXT FROM author_csr INTO lname,fname END /*关闭游标*/ CLOSE author_csr /*释放游标*/ DEALLOCATE author_csr,打开游标,关闭游标,删除游标,使用游标,FETCH_STATUS,相关实践知识8-3,执行结果如图：,相关实践知识8-4,使用滚动游标，查询并显示输出pubs数据库中的authors表中state字段的值是CA的au_fname、au_lname的值，显示顺序为：先最后一行作者的姓名、最后一行的上一行作者的姓名、第四行作者的姓名、第二行的作者的姓名。,/*定义游标*/ DECLARE author_csr1 SCROLL CURSOR FOR SELECT au_fname,au_lname FROM authors WHERE state=CA ORDER BY au_fname,au_lname /*打开游标*/ OPEN author_csr1 /*数据集中的最后一行*/ FETCH LAST FROM author_csr1 /*当前游标所在行的上一行*/ FETCH PRIOR FROM author_csr1,相关实践知识8-5,/*提取数据集中的第4行*/ FETCH ABSOLUTE 4 FROM author_csr1 /*提取当前行的前两行*/ FETCH RELATIVE -2 FROM author_csr1 /*数据集中的第一行*/ FETCH FIRST FROM author_csr1 /*关闭游标*/ CLOSE author_csr1 /*释放游标*/ DEALLOCATE author_csr1,相关实践知识8-6,执行结果如图：,相关实践知识8-7,使用游标更新pubs数据库中的titles表中类型type是business的书的价钱上涨50%。,DECLARE title_csr CURSOR FOR SELECT title,price FROM titles WHERE type=business FOR UPDATE OF price /*打开游标*/ OPEN title_csr /*使用游标提取第一行数据*/ FETCH title_csr WHILE FETCH_STATUS = 0 BEGIN UPDATE titles SET price= price*1.5 WHERE CURRENT OF title_csr FETCH title_csr END CLOSE title_csr DEALLOCATE title_csr,相关实践知识8-8,总结,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 游标 定义语句格式 打开语句格式 提取数据语句格式 关闭语句格式 删除语句格式,游标2-1,游标 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 SQL Server2000中三种游标实现： Transact-SQL游标 API服务器游标 只进游标 静态游标 键集驱动游标 动态游标 客户游标,游标2-2,游标的特点是： 检索得到的数据集更加灵活 可有针对性的对数据进行操作 拥有对数据进行删除和更新的能力 为何使用游标： 游标提供了一种比较好的解决方案，可以将批操作变成行操作。,定义游标的基本格式,DECLARE CURSOR语句定义Transact-SQL服务器游标的特性，其语法格式为：,DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n ,打开游标,打开游标的基本格式,OPEN GLOBAL cursor_name | cursor_variable_name ,使用游标提取数据,使用游标提取数据,FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n ,关闭和删除游标,关闭游标 删除游标,CLOSE GLOBAL cursor_name | cursor_variab