16 第13章 存储过程.ppt

第13章 存储过程 本章知识点 存储过程概述 存储过程分类 存储过程执行过程 创建存储过程 修改及删除存储过程 调用存储过程 在存储过程中使用游标 存储过程概述 场 景 在BigCollege选课系统中，系统用户经常在线查询某课程信息，包括：课程名、开设院系、授课老师、开课时间、开课地点、最大选课人数和目前已选人数等信息。由于该查询在程序中很多地方都要用到，而且使用频率非常高，因此，开发人员想用一种可以重用而又高性能的方式来实现。 存储过程概述 问题分析 所查询的信息分布在Classes、ClassRoom、Department三张表中，需要用连接查询,查询代码如下： 存储过程概述 问题分析 存在的问题 该查询功能在程序中的很多地方使用，因此同样一段代码要重复写多遍，如果查询信息有所改变，则要改变很多地方，给程序员带来一定的麻烦。 系统使用人员每次提交查询，数据库服务器会对查询语句进行编译、解析和执行，而且是反复做同样的事情，浪费服务器资源。 存储过程概述 解决问题 解决方案 常规SQL语句能实现查询功能，但存在一些问题，如：代码复用率低、可维护性差和性能低，因此SQL SERVER给出了一种可重用、易维护和高效的解决方案 —— 存储过程（Stored Procedure ） 将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,?那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。 存储过程概述 定 义 存储过程是SQL Server应用最广泛、最灵活的技术，是存储在SQL Server服务器中的一组预编译过的T-SQL语句 ，可以多次重复执行实现一个特定的功能，然后通过其过程名来进行调用。 特 征 存储过程在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行。 存储过程实现了模块化编程。被创建后可以被多个用户共享和重用，有效的减少网络流量，提高SQL语句的执行效率，提高数据库的安全性能。 存储过程概述 存储过程分类 用户定义的存储过程 T-SQL存储过程 CLR存储过程 系统存储过程 临时存储过程 远程存储过程 扩展存储过程 存储过程概述 优 点 存储过程是已经被数据库预编译过的语句，不必每次调用而重新编译，客户将查询发送到服务器端后，中间省略了分析、解析和编译的过程，大大提高了效率。 存储过程允许标准组件式编程 存储过程能够实现较快的执行速度 存储过程能够减少网络流量 存储过程可被作为一种安全机制来充分利用 创建存储过程 创建前考虑的事项： (1)一个存储过程是一个单独的功能模块，不要与其它SQL 语句合并在一起。 (2)数据库所有者具有默认的创建存储过程的权限。 (3)存储过程作为数据库对象其命名必须符合命名规则，在一个数据库中，不能有同名的存储过程。 (4)只能在当前数据库中创建属于当前数据库的存储过程。 创建存储过程 基本语法 CREATE PROC | PROCEDURE [ schema_name. ] procedure_name ( [ @parameter [ type_schema_name. ] data_type [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ ,...] ) [ WITH ENCRYPTION ] AS BEGIN sql_statement [ ,... ] END 创建存储过程 基本语法：参数说明 schema_name ： 过程所属架构的名称 procedure_name ： 过程名称 @ parameter ： 过程的参数 data_type ： 参数的数据类型 VARYING ：指定作为输出参数支持的结果集，仅适用于cursor参数 DEFAULT ： 参数的默认值 OUTPUT ： 指示参数是输出参数 ENCRYPTION ： 将CREATE PROCEDURE语句的原始文本加密 AS ： 指明该存储过程将要执行的动作 sql_statement ： 要包含在过程中的一个或多个T-SQL语句 创建存储过程 执行代码 执行存储过程 语 法 [EXECUTE | EXEC] proc_name 注 意 存储过程可以接收参数，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 在执行存储过程时，如果语句是一个批处理中的第一个语句，则不一定要指定 EXECUTE 或EXEC关键字。 存储过程不能被引用在语句当中。