单元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：重新编译存储过程问题11．1】使用T-SQL语句，在Xk数据 库中创建存储过程p_Student该存储过程 返回Student表中班级编号为“20000001”的 数据行创建p_Student（略）执行p_Student ：EXEC p_Student【问题11．3】在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【问题11．4】使用Transcact-SQL语句创建 存储过程p_StudentPara该存储过程能根 据给定的班级编号，返回该班级编号对应 的所有学生信息创建p_StudentPara（略）执行带输入参数的存储过程（两种方法） ：EXEC p_StudentPara @ClassNo='20000001‘GOEXEC p_StudentPara '20000001GO8大型数据库（SQL Server） 单元11 创建与管理存储过程创建和执行带输出参数的存储过程|声明输出参数的命令语法如下 ： @parameter_name datatype=[default] OUTPUT【问题11．7】创建存储过程 p_ClassNum，它能够根据用户给定 的班级编号统计该班学生人数，并 将学生人数返回给用户。

问题11．8】执行存储过程 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：以对存储过 程是定义进行加密问题11．9】使用Transcact-SQL 语句。

修改存储过程 p_StudentPara，使其能根据用户 提供的班级名称，而不是班级编 号进行模糊查询，并要求加密存 储过程12大型数据库（SQL Server） 单元11 创建与管理存储过程重命名、删除存储过程|重命名存储过程： 【问题11．10】将存储过程 p_ClassNum重新命名为 p_CalcClassNum删除存储过程：DROP PROCEDURE proc_name|【问题11．11】使用Transcact-SQL 语句删除存储过程p_Student13大型数据库（SQL Server） 单元11 创建与管理存储过程重新编译存储过程|什么情况下重新编译？进行了索引或影响数据库统 计的更改后，需要重新编译 ，以重新优化查询重新编译存储过程的3种方法 ： 1、在创建时指明重编译 2、在执行时指明重编译 3、使用系统存储过程重新编译14大型数据库（SQL Server） 单元11 创建与管理存储过程重新编译存储过程|【问题11．13】使用WITH RECOMPILE子句创建存储过程 p_StudentPara该存储过程能根据给 定的班级编号返回该班级编号对应的 所有学生信息。

要求每次执行时进行 重新编译和优化 |【问题11．14】以重新编译的方式执 行存储过程p_StudentPara问题11．15】执行下面的语句将导 致使用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的登 录安全信息|【问题11．17】执行下列 xp_cmdshell 语句将返回指定目录的 匹配文件列表。

EXEC xp_cmdshell 'dir c:\*.exe'17大型数据库（SQL Server） 单元11 创建与管理存储过程复习与巩固|独自完成课本page224实训|创建存储过程，返回系部编码为“01” 的所有班级的名称创建带输入参数的存储过程，根据 给定的学生学号，返回该学生选修 的所有课程的信息，包括：课程名 、上课时间、学分、授课教师并 执行该存储过程，以得到学号为 “00000001”的学生选修的课程信息创建带输入、输出参数的存储过程 ，以得到给定系部编码对应的系部 所开设课程的总报名人数并编程 执行该存储过程18大型数据库（SQL Server） 单元11 创建与管理存储过程单元小结|存储过程的作用|创建、执行简单的存储过程|创建、执行带参数（输入、输 出）的存储过程（重点）|重点：存储过程的创建和执行 ，特别是带有参数的存储过程|修改、加密、重命名、删除存 储过程|重编译存储过程19大型数据库（SQL Server） 单元11 创建与管理存储过程预习单元12|思考问题： 1、什么时候使用触发器？有何作 用？ 2、触发器是在什么时候被触发执 行的？ 3、如何创建一个触发器？ 4、触发器中的INSERTEN表和 DELETED表和我们前面学过的 表格有什么不同之处？20。