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

单元 11 创建与管理存储过程 济宁职业技术学院 数据库课程组 学习目标 【知识目标】 理解存储过程的作用。 学会根据需要创建、修改、删除存储过程。 在实际应用开发时能够灵活运用存储过程，以提高开发效率。 【技能目标】 能根据需要创建、修改和存储过程。 能根据实际需要在存储过程中定义并使用输入参数、输出参数。 任务陈述 学生每个班的班主任都需要经常查看他们班学生选修课程的信息； 教务处的老师经常需要查看某个系部开设的选修课程情况、学生选修某一门课程的情况等等。 现在需要在学生选课数据库Xk中创建带有参数的存储，并在需要的时候修改或删除存储过程。 存储过程概述 什么是存储过程？ 是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。 【注意】存储过程保存在C/S的服务器端 存储过程分类： 系统存储过程 用户自定义的存储过程 扩展存储过程（提供从SQL Server到外部程序的接口，以便进行各种维护活动） 存储过程的作用 从以下几个方面考虑： 1、模块化编程：创建的存储过程保存在数据库中，可以被其他程序反复使用。 2、快速执行：存储过程第一次被执行后就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。 3、减少网络通信量：有了存储过程后，在网络上只需要传送一条语句就能执行存储过程。 4、安全机制：通过隔离和加密的方法提高了数据库的安全性：通过授权可以让用户只能执行存储过程而不能直接访问数据库对象；另外，存储过程的定义可以被加密。 存储过程和与图的比较 创建、执行、查看存储过程 创建存储过程（无参数）： CREATE PROCEDURE procedure_name [WITH ENCRYPTION] [WITH RECOMPILE] AS sql_statement 其中： WITH ENCRYPTION：加密存储过程的定义。 WITH RECOMPILE：重新编译存储过程。 【问题11．1】使用T-SQL语句，在Xk数据库中创建存储过程p_Student。该存储过程返回Student表中班级编号为的数据行。 创建p_Student（略） 执行p_Student ：EXEC p_Student 【问题11．3】在SQL Server Management Studio中查看存储过程p_Student的属性。 创建和执行带参数的存储过程 带参数的存储过程： 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 GO EXEC p_StudentPara GO 创建和执行带输出参数的存储过程 声明输出参数的命令语法如下： @parameter_name datatype=[default] OUTPUT 【问题11．7】创建存储过程p_ClassNum，它能够根据用户给定的班级编号统计该班学生人数，并将学生人数返回给用户。 【问题11．8】执行存储过程p_ClassNum。 练一练 1、创建名为p_StuByClass的存储过程，返回“00电子商务”班的所有学生的信息。 2、执行p_StuByClass存储过程，并使用Management Studio查看该存储过程的定义。 3、创建名为p_StuByPara的存储过程，根据给定的班级编码和学生学号，返回相应学生的信息。 【拓展】当缺省参数执行存储过程时，如何处理？ 练一练 创建存储过程p_StuName，要求能够根据用户给定的班级名称和学生学号，查询该学生的姓名，并将该姓名以变量的形式返回给用户。 修改存储过程 修改格式： ALTER PROCEDURE procedure_name [WITH ENCRYPTION] [WITH RECOMPILE] AS sql_statement WITH ENCRYPTION：以对存储过程是定义进行加密。 【问题11．9】使用Transcact-SQL语句。修改存储过程p_StudentPara，使其能根据用户提供的班级名称，而不是班级编号进行模糊查