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

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