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

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