[第13章存储过程.ppt

第13章 存储过程 本章知识点 存储过程概述 存储过程分类 存储过程执行过程 创建存储过程 修改及删除存储过程 调用存储过程 在存储过程中使用游标 存储过程概述 场 景 在BigCollege选课系统中，系统用户经常在线查询课程信息，包括：课程名、开设院系、授课老师、开课时间、开课地点、最大选课人数和目前已选人数等信息。由于该查询在程序中很多地方都要用到，而且使用频率非常高，因此，开发人员想用一种可以重用而又高性能的方式来实现。 存储过程概述 问题分析 所查询的信息分布在Classes、ClassRoom、Department三张表中，需要用连接查询,查询代码如下： 存储过程概述 问题分析 存在的问题 该查询功能在程序中的很多地方使用，因此同样一段代码要重复写多遍，如果查询信息有所改变，则要改变很多地方，给程序员带来一定的麻烦。 系统使用人员每次提交查询，数据库服务器会对查询语句进行编译、解析和执行，而且是反复做同样的事情，浪费服务器资源。 存储过程概述 解决问题 解决方案 常规SQL语句能实现查询功能，但存在一些问题，如：代码复用率低、可维护性差和性能低，因此SQL SERVER给出了一种可重用、易维护和高效的解决方案 —— 存储过程（Stored Procedure ） 存储过程概述 定 义 将多次重复执行的实现特定功能的代码段编写成一个“过程”，将其保存在数据库中，并由SQL Server服务器通过其过程名来进行调用，这样的“过程”就叫做存储过程 特 征 存储过程是一个SQL语句组合。在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行 存储过程实现了模块化编程。被创建后可以被多个用户共享和重用，有效的减少网络流量，提高SQL语句的执行效率，提高数据库的安全性能 存储过程概述 优 点 存储过程允许标准组件式编程 存储过程能够实现较快的执行速度 存储过程能够减少网络流量 存储过程可被作为一种安全机制来充分利用 创建存储过程 基本语法 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关键字。 如果存储过程带有参数，则在执行过程时，需带上相应的参数。 执行存储过程 执行存储过程的步骤 编译阶段 在创建时，系统对其语句进行语法检查。若有语法错误则创建失败，否则创建成功 解析阶段 在首次执行时，从系统中读取存储过程，并检查引用的数据库对象是否存在。若存在找不到的数据库对象则产生错误，否则进入执行阶段 执行阶段 依次执行存储过程中的SQL语句 存储过程概述 存储过程分类 用户定义的存储过程 T-SQL存储过程 CLR存储过程 系统存储过程 临时存储过程 远程存储过程 扩展存储过程 存储过程概述 查看存储过程 查看数据库的的存储过程也可以通过两种方法： 利用代码命令 利用Manager Studio 利用代码命令查看存储过程 sp_help proc_name命令查看名为proc_name的存储过程。 sp_helptext proc_name命令查看名为proc_name的存储过程的详细代码 存储过程概述 删除存储过程两种方式 在Management Studio中选中要删除的存储过程，右键单击“删除”命令即可