单元11创建与管理存储过程.ppt

单元 11 创建与 管理存 储过程 济宁职业技术学院 数据库课程组 大型数据库 1 大型数据库（SQL Server） 单元11 创建与管理存储过程 学习目标 【知识目标】 |理解存储过程的作用。 |学会根据需要创建、修改、删除存 储过程。 |在实际应用开发时能够灵活运用存 储过程，以提高开发效率。 【技能目标】 |能根据需要创建、修改和存储过程 。 |能根据实际需要在存储过程中定义 并使用输入参数、输出参数。 2 大型数据库（SQL Server） 单元11 创建与管理存储过程 任务陈述 |学生每个班的班主任都需要经常查 看他们班学生选修课程的信息； |教务处的老师经常需要查看某个系 部开设的选修课程情况、学生选修 某一门课程的情况等等。 现在需要在学生选课数据库Xk中创 建带有参数的存储，并在需要的时 候修改或删除存储过程。 3 大型数据库（SQL Server） 单元11 创建与管理存储过程 存储过程概述 |什么是存储过程？ z是一组被编译在一起的T-SQL语句 的集合，它们被集合在一起以完成 一个特定的任务。 z【注意】存储过程保存在C/S的服 务器端 |存储过程分类： z系统存储过程 z用户自定义的存储过程 z扩展存储过程（提供从SQL Server 到外部程序的接口，以便进行各种 维护活动） 4 大型数据库（SQL Server） 单元11 创建与管理存储过程 存储过程的作用 |从以下几个方面考虑： 1、模块化编程：创建的存储过程保存 在数据库中，可以被其他程序反复使 用。 2、快速执行：存储过程第一次被执行 后就驻留在内存中。以后执行就省去 了重新分析、优化、编译的过程。 3、减少网络通信量：有了存储过程后 ，在网络上只需要传送一条语句就能 执行存储过程。 4、安全机制：通过隔离和加密的方法 提高了数据库的安全性：通过授权可 以让用户只能执行存储过程而不能直 接访问数据库对象；另外，存储过程 的定义可以被加密。 5 大型数据库（SQL Server） 单元11 创建与管理存储过程 存储过程和与图的比较 视图存储过程 SQL 语句只能是SELECT 语句 可以包含流控语 句、声明和使用 变量以及SELECT 语句 输入、 返回结果 不能接受参数 ，只能返回结 果集 可以有输入、输 出参数，也可以 有返回值 典型应用多个表格的连 接查询 完成某个特定的 较复杂的任务 6 大型数据库（SQL Server） 单元11 创建与管理存储过程 创建、执行、查看存储过程 |创建存储过程（无参数）： CREATE PROCEDURE procedure_name WITH ENCRYPTION WITH RECOMPILE AS sql_statement 其中： WITH ENCRYPTION：加密存储过程的定义。 WITH RECOMPILE：重新编译存储过程。 |【问题111】使用T-SQL语句，在Xk数据 库中创建存储过程p_Student。该存储过程 返回Student表中班级编号为“20000001”的 数据行。 创建p_Student（略） 执行p_Student ：EXEC p_Student 【问题113】在SQL Server Management Studio中查看存储过程p_Student的属性。 7 大型数据库（SQL Server） 单元11 创建与管理存储过程 创建和执行带参数的存储过程 带参数的存储过程： CREATE PROCEDURE procedure_name parameter_name datatype=default WITH ENCRYPTION WITH RECOMPILE AS sql_statement 【问题114】使用Transcact-SQL语句。创建 存储过程p_StudentPara。该存储过程能根 据给定的班级编号，返回该班级编号对应 的所有学生信息。 创建p_StudentPara（略）。 执行带输入参数的存储过程（两种方法） ： EXEC p_StudentPara ClassNo=20000001 GO EXEC p_StudentPara 20000001 GO 8 大型数据库（SQL Server） 单元11 创建与管理存储过程 创建和执行带输出参数的存储过程 |声明输出参数的命令语法如下 ： parameter_name datatype=default OUTPUT 【问题117】创建存储过程 p_ClassNum，它能够根据用户给定 的班级编号统计该班学生人数，并 将学生人数返回给用户。 【问题118】执行存储过程 p_ClassNum。 9 大型数据库（SQL Server） 单元11 创建与管理存储过程 练一练 1、创建名为p_StuByClass的存储 过程，返回“00电子商务”班 的所有学生的信息。 2、执行p_StuByClass存储过程， 并使用Management Studio查看 该存储过程的定义。 3、创建名为p_StuByPara的存储 过程，根据给定的班级编码和 学生学号，返回相应学生的信 息。 【拓展】当缺省参数执行存储过 程时，如何处理？ 10 大型数据库（SQL Server） 单元11 创建与管理存储过程 练一练 |创建存储过程p_StuName，要求 能够根据用户给定的班级名称 和学生学号，查询该学生的姓 名，并将该姓名以变量的形式 返回给用户。 11 大型数据库（SQL Server） 单元11 创建与管理存储过程 修改存储过程 |修改格式： ALTER PROCEDURE procedure_name WITH ENCRYPTION WITH RECOMPILE AS sql_statement WITH ENCRYPTION：以对存储过 程是定义进行加密。 【问题119】使用Transcact-SQL 语句。修改存储过程 p_StudentPara，使其能根据用户 提供的班级名称，而不是班级编 号进行模糊查询，并要求加密存 储过程。 12 大型数据库（SQL Server） 单元11 创建与管理存储过程 重命名、删除存储过程 |重命名存储过程： 【问题1110】将存储过程 p_ClassNum重新命名为 p_CalcClassNum。 |删除存储过程： DROP PROCEDURE proc_name |【问题1111】使用Transcact-SQL 语句。删除存储过程p_Student。 13 大型数据库（SQL Server） 单元11 创建与管理存储过程 重新编译存储过程 |什么情况下重新编译？ 进行了索引或影响数据库统 计的更改后，需要重新编译 ，以重新优化查询。 |重新编译存储过程的3种方法 ： 1、在创建时指明重编译 2、在执行时指明重编译 3、使用系统存储过程重新编译 14 大型数据库（SQL Server） 单元11 创建与管理存储过程 重新编译存储过程 |【问题1113】使用WITH RECOMPILE子句。创建存储过程 p_StudentPara。该存储过程能根据给 定的班级编号返回该班级编号对应的 所有学生信息。要求每次执行时进行 重新编译和优化。 |【问题1114】以重新编译的方式执 行存储过程p_StudentPara。 |【问题1115】执行下面的语句将导 致使用Student表的触发器和存储过程 在下次执行时重新编译。 15 大型数据库（SQL Server） 单元11 创建与管理存储过程 系统存储过程 系统存储过程： sp_who 显示当前用户和进程的信息 回顾学过的系统存储过程： 1、与“数据库”有关： sp_helpdb、sp_helpfilegroup、sp_dboption、sp_renamedb 2、与“表”有关： sp_help、sp_rename 3、与“索引”有关： sp_helpindex、sp_rename 4、与“默认值”有关： sp_helpindex、sp_rename 5、与“规则”有关： sp_unbindrule、sp_unbindrule 6、与“用户自定义数据类型”有关： sp_addtype、sp_droptype 7、与“视图”有关： sp_helptext、sp_depends 16 大型数据库（SQL Server） 单元11 创建与管理存储过程 扩展存储过程 扩展存储过程： |xp_cmdshell 执行操作系统命令 |xp_enumgroups 列出域中的组的信息 |xp_loginconfig 报告SQL Server的登 录安全信息 |【问题1117】执行下列 xp_cmdshell 语句将返回指定目录的 匹配文件列表。 EXEC xp_cmdshell dir c:*.exe 17 大型数据库（SQL Server） 单元11 创建与管理存储过程 复习与巩固 |独自完成课本page224实训 |创建存储过程，返回系部编码为“01” 的所有班级的名称。 |创建带输入参数的存储过程，根据 给定的学生学号，返回该学生选修 的所有课程的信息，包括：课程名 、上课时间、学分、授课教师。并 执行该存储过程，以得到学号为 “00000001”的学生选修的课程信息。 |创建带输入、输出参数的存储过程 ，以得到给定系部编码对应的系部 所开设课程的总报名人数。并编程 执行该存储过程。 18 大型数据库（SQL Server） 单元11 创建与管理存储过程 单元小结 |存储过程的作用 |创建、执行简单的存储过程 |创建、执行带参数（输入、输 出）的存储过程（重