单元创建与管理存储过程

单元11

创建与管理存储过程济宁职业技术学院数据库课程组大型数据库1第1页，共20页。学习目标【知识目标】理解存储过程的作用。学会根据需要创建、修改、删除存储过程。在实际应用开发时能够灵活运用存储过程，以提高开发效率。【技能目标】能根据需要创建、修改和存储过程。能根据实际需要在存储过程中定义并使用输入参数、输出参数。2第2页，共20页。任务陈述学生每个班的班主任都需要经常查看他们班学生选修课程的信息；教务处的老师经常需要查看某个系部开设的选修课程情况、学生选修某一门课程的情况等等。现在需要在学生选课数据库Xk中创建带有参数的存储，并在需要的时候修改或删除存储过程。3第3页，共20页。存储过程概述什么是存储过程？是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。【注意】存储过程保存在C/S的服务器端存储过程分类：系统存储过程用户自定义的存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）4第4页，共20页。存储过程的作用从以下几个方面考虑：1、模块化编程：创建的存储过程保存在数据库中，可以被其他程序反复使用。2、快速执行：存储过程第一次被执行后就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。3、减少网络通信量：有了存储过程后，在网络上只需要传送一条语句就能执行存储过程。4、安全机制：通过隔离和加密的方法提高了数据库的安全性：通过授权可以让用户只能执行存储过程而不能直接访问数据库对象；另外，存储过程的定义可以被加密。5第5页，共20页。存储过程和与图的比较视图存储过程SQL语句只能是SELECT语句可以包含流控语句、声明和使用变量以及SELECT语句输入、返回结果不能接受参数，只能返回结果集可以有输入、输出参数，也可以有返回值典型应用多个表格的连接查询完成某个特定的较复杂的任务6第6页，共20页。创建、执行、查看存储过程创建存储过程（无参数）：CREATEPROCEDUREprocedure_name[WITHENCRYPTION][WITHRECOMPILE]ASsql_statement其中：WITHENCRYPTION：加密存储过程的定义。WITHRECOMPILE：重新编译存储过程。【问题11．1】使用T-SQL语句，在Xk数据库中创建存储过程p_Student。该存储过程返回Student表中班级编号为“20000001”的数据行。创建p_Student（略）执行p_Student：EXECp_Student【问题11．3】在SQLServerManagementStudio中查看存储过程p_Student的属性。7第7页，共20页。创建和执行带参数的存储过程带参数的存储过程：CREATEPROCEDUREprocedure_name@parameter_namedatatype=[default][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement【问题11．4】使用Transcact-SQL语句。创建存储过程p_StudentPara。该存储过程能根据给定的班级编号，返回该班级编号对应的所有学生信息。

创建p_StudentPara（略）。执行带输入参数的存储过程（两种方法）：EXECp_StudentPara@ClassNo='20000001‘GOEXECp_StudentPara'20000001GO8第8页，共20页。创建和执行带输出参数的存储过程声明输出参数的命令语法如下：@parameter_namedatatype=[default]OUTPUT【问题11．7】创建存储过程p_ClassNum，它能够根据用户给定的班级编号统计该班学生人数，并将学生人数返回给用户。【问题11．8】执行存储过程p_ClassNum。

9第9页，共20页。练一练1、创建名为p_StuByClass的存储过程，返回“00电子商务”班的所有学生的信息。2、执行p_StuByClass存储过程，并使用ManagementStudio查看该存储过程的定义。3、创建名为p_StuByPara的存储过程，根据给定的班级编码和学生学号，返回相应学生的信息。【拓展】当缺省参数执行存储过程时，如何处理？10第10页，共20页。练一练创建存储过程p_StuName，要求能够根据用户给定的班级名称和学生学号，查询该学生的姓名，并将该姓名以变量的形式返回给用户。11第11页，共20页。修改存储过程修改格式：ALTERPROCEDUREprocedure_name[WITHENCRYPTION][WITHRECOMPILE]ASsql_statementWITHENCRYPTION：以对存储过程是定义进行加密。【问题11．9】使用Transcact-SQL语句。修改存储过程p_StudentPara，使其能根据用户提供的班级名称，而不是班级编号进行模糊查询，并要求加密存储过程。12第12页，共20页。重命名、删除存储过程重命名存储过程：【问题11．10】将存储过程p_ClassNum重新命名为p_CalcClassNum。删除存储过程：

DROPPROCEDUREproc_name【问题11．11】使用Transcact-SQL语句。删除存储过程p_Student。13第13页，共20页。重新编译存储过程什么情况下重新编译？进行了索引或影响数据库统计的更改后，需要重新编译，以重新优化查询。重新编译存储过程的3种方法：1、在创建时指明重编译2、在执行时指明重编译3、使用系统存储过程重新编译14第14页，共20页。重新编译存储过程【问题11．13】使用WITHRECOMPILE子句。创建存储过程p_StudentPara。该存储过程能根据给定的班级编号返回该班级编号对应的所有学生信息。要求每次执行时进行重新编译和优化。【问题11．14】以重新编译的方式执行存储过程p_StudentPara。【问题11．15】执行下面的语句将导致使用Student表的触发器和存储过程在下次执行时重新编译。15第15页，共20页。系统存储过程系统存储过程：sp_who显示当前用户和进程的信息回顾学过的系统存储过程：1、与“数据库”有关：sp_helpdb、sp_help、sp_dboption、sp_renamedb

2、与“表”有关：sp_help、sp_rename3、与“索引”有关：sp_helpindex、sp_rename4、与“默认值”有关：sp_helpindex、sp_rename5、与“规则”有关：sp_unbindrule、sp_unbindrule6、与“用户自定义数据类型”有关：sp_addtype、sp_droptype7、与“视图”有关：sp_helptext、sp_depends16第16页，共20页。扩展存储过程扩展存储过程：xp_cmdshell执行操作系统命令xp_enumgroups列出域中的组的信息xp_loginconfig报告SQLServer的登录安全信息【问题11．17】执行下列xp_cmdshell语句将返回指定目录的匹配文件列表。

EXECxp_cmdshell'dirc:\*.exe'17第17页，共20页。复习与巩固独自完成课本page224实训创建存储过程，返回系部编码为“01”的所有班级的名称。创建带输入参数的存储过程，根据给定的学生学号，返回该学生选修的所有课程的信息，包括：课程名、上课时间、学分、授课教师。并执行该存储过程，以得到学号为“00000001”的学生选修的课程信息。创建带输入、输出参数的存储过程，以得到给定系部编码对应的系部所开设课程的总报名人数。并编程执行该存储过程。18第18页，共20页。单元小结存储过程的作用创建、执行简单的存储过程创建、执行带参数（输入、输出）的存储过程（重点