第8节 存储过程.ppt

第8章 存储过程 本章知识点 存储过程概述 存储过程分类 存储过程执行过程 创建存储过程 修改及删除存储过程 调用存储过程 在存储过程中使用游标 存储过程概述 场 景 某大学学生选课系统中，用户经常在线查询一些选课信息，比如，班主任经常查询本班的选课信息，查询那门课有那些学生选，那些学生选了那些课，已经完成选课的学生人数等。比如，学生经常查询自己都选修了那些课程，那些课程已经修完，所获得的总学分是多少，已经修完的课程成绩是多少等信息。由于这些查询在程序中很多地方都要用到，而且使用频率非常高，因此，开发人员想用一种可以重用而又高性能的方式来实现。 存储过程概述 问题分析 比如，学生想查看自己选修了哪些课程，已经修完的课程成绩是多少，已经获得的总学分是多少。针对这个问题，所查询的信息分布在（学生）xs表、（课程）kc表和（学生选课）xs_kc表中，需要用连接查询,查询代码如下： 存储过程概述 问题分析 存在的问题 该查询功能在程序中的很多地方使用，因此同样一段代码要重复写多遍，如果查询信息有所改变，则要改变很多地方，给程序员带来一定的麻烦。 系统使用人员每次提交查询，数据库服务器会对查询语句进行编译、解析和执行，而且是反复做同样的事情，浪费服务器资源。 存储过程概述 解决问题 解决方案 常规SQL语句能实现查询功能，但存在一些问题，如：代码复用率低、可维护性差和性能低，因此SQL SERVER给出了一种可重用、易维护和高效的解决方案 —— 存储过程（Stored Procedure ） 存储过程概述 定 义 将多次重复执行的实现特定功能的代码段编写成一个“过程”，将其保存在数据库中，并由SQL Server服务器通过其过程名来进行调用，这样的“过程”就叫做存储过程 特 征 存储过程是一个SQL语句组合。在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行 存储过程实现了模块化编程。被创建后可以被多个用户共享和重用，有效的减少网络流量，提高SQL语句的执行效率，提高数据库的安全性能 存储过程概述 优 点 执行速度快，改善系统性能。存储过程在服务器端运行，可以利用服务器强大的计算能力和速度，执行速度快。而且存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句执行的更快。 减少网络流量。用户可以发送一条执行存储过程的命令就可以实现一个复杂的操作，而不需要在网络上传送几百条T-SQL语句，这样可以减少在客户端与服务器之间传递的语句数量，减轻了服务器的负担。 存储过程概述 优 点 增强代码的重用性和共享性。存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。所有的客户端都可以使用相同的存储过程来访问和修改数据。 提供了一种安全机制。如果存储过程支持用户所要执行的所有操作，可以不授予用户直接访问表、视图的权限，而是授权用户执行存储过程的权限，这样，可以防止把数据库中表的细节信息暴露给用户，保证表中数据的安全性。 存储过程概述 存储过程分类 用户定义的存储过程 T-SQL存储过程，T-SQL语句的集合，可以接受和返回参数。 CLR存储过程，引用Microsoft .NET framework中公共语言运行时方法，可以接受和返回参数。 系统存储过程 主要访问系统表中信息完成系统管理任务，存储在master数据库中，可以在其他数据库中任意调用，由前缀sp_标示。 临时存储过程 存储在tempdb中，以#开头，只有创建该过程的连接才能执行它，当连接关闭时，将自动删除临时存储过程。 存储过程概述 存储过程分类 远程存储过程 从服务器上调用的用户自定义存储过程。 扩展存储过程 以xp_开头，可以调用动态连接库中所包含的函数，从而扩展了T-SQL的功能，并且可以像调用T-SQL过程一样调用它。 创建存储过程 使用可视化向导创建 步骤： （1）展开要创建存储过程的数据库，找到“可编程性”节点，展开后右键单击“存储过程”，选择“新建存储过程”，此时在窗口中间打开创建存储过程的模板，其中已经加入了一些创建存储过程的代码。 （2）选择“查询”菜单中的“指定模板参数的值”，打开一个对话框。 （3）设置好需要的参数值。 （4）修改模板中的内容。 （5）单击工具栏上的执行按钮，完成存储过程的创建。 下面以在student数据库上创建学生查询选课信息的存储过程为例，演示如何通过可视化向导创建存储过程。 创建存储过程 基本语法 CREATE PROC | PROCEDURE [ schema_name. ] procedure_name [ @parameter [ type_schema_na