第8章 存储过程PPT课件.ppt

第8章存储过程 使用SQLServer创建应用程序时 SQL编程语言是应用程序和SQLServer数据库之间的主要编程接口 使用SQL程序时 可用两种方法存储和执行程序 可以将程序存储在本地 并创建向SQLServer发送命令并处理结果的应用程序 也可以将程序作为存储过程存储在SQLServer中 并创建执行存储过程并处理结果的应用程序 8 1了解存储过程 在SQLServer中有多种可用的存储过程 系统存储过程 用户自定义的存储过程和扩展存储过程 本节了解这些存储过程的作用和用法 8 1 1存储过程的作用 存储过程类似于程序中的一个方法 方法中可以包含多行代码 存储过程也是封装多条SQL语句的方法 开发人员可以在以下情况下使用存储过程 1 包含输入参数和输出参数 执行多个批处理语句 2 包含多条SQL语句 用来控制或操作数据库 3 向调用程序返回状态值 以指示成功或失败 如果失败 还给出失败的原因 8 1 2存储过程的种类 存储过程主要分为 用户定义的存储过程 扩展存储过程和系统存储过程 1 用户定义的存储过程2 扩展存储过程3 系统存储过程 8 2创建存储过程 可以使用SQLServerManagementStudio中的对象资源管理器创建SQL存储过程 也可以使用SQL语句CREATEPROCEDURE来创建存储过程 因为还没有详细介绍SQL语句 所以本节主要介绍使用SQLServerManagementStudio来创建存储过程 创建存储过程时 应指定 所有输入参数和返回的输出参数 执行数据库操作 包括调用其他过程 的编程语句 返回至调用过程以标识成功或失败的状态值 捕获和处理潜在的错误所需的任何错误处理语句 8 2 1命名存储过程 因为默认的系统存储过程是以sp 开头 所以在创建存储过程时要注意 前缀可以是公司简介 数据库简称等 尽量不要用sp 如果用户自定义的存储过程与系统存储过程名称相同 则该存储过程将永远不会执行 系统始终执行的是系统存储过程 8 2 2用SQLServerManagementStudio创建存储过程 本节介绍如何使用SQLServerManagementStudio中的对象资源管理器创建SQL存储过程 并提供一个在AdventureWorks数据库中创建简单存储过程的示例 1 创建存储过程2 创建存储过程的示例 8 3执行存储过程 若要执行存储过程 可以使用EXECUTE语句 如果存储过程是批处理中的第一条语句 那么不使用EXECUTE也可以执行存储过程 8 3 1执行系统存储过程 系统存储过程以字符sp 开头 笔者建议使用sys架构名称对所有系统存储名称进行限定 以防止名称冲突 以下示例说明执行系统存储过程的推荐方法 EXECsys sp who 8 3 2执行用户定义存储过程 执行用户定义存储过程时 笔者建议至少用架构名称限定存储过程名称 以下示例说明执行用户定义存储过程的推荐方法 USEAdventureWorks GOEXECdbo uspGetEmployeeManagers50 或者 EXECAdventureWorks dbo uspGetEmployeeManagers50 GO 8 3 3指定存储过程的参数 指定存储过程的参数 通常包括指定参数 指定参数顺序和使用参数的默认值 本节将学会使用输入参数和输出参数 如果编写的存储过程允许接受参数 则在执行存储过程时 需要为其指定参数 参数值必须是常量或变量 不能将函数 视图等指定为参数值 变量可以是用户定义变量或系统变量 如 id 8 4修改和删除存储过程 修改和删除存储过程是实现存储过程的重要部分 当创建完一个存储过程 可根据系统的需要 而重新进行修改 甚至在不需要该存储过程的情况下 删除存储过程 8 4 1修改存储过程 在SQLServerManagementStudio中使用对象资源管理器修改SQL存储过程的操作步骤如下 1 在 对象资源管理器 窗口中 连接到某个数据库引擎实例 再展开该实例 2 依次展开 数据库 存储过程所属的数据库 可编程性 节点 3 展开 存储过程 节点 右击需要修改的存储过程 在弹出的快捷菜单中选择 修改 命令 打开修改存储过程脚本的窗口 4 修改存储过程的脚本 SQL语句 8 4 1修改存储过程 5 若要测试语法 选择 查询 分析 命令 进行语法测试 6 若要修改存储过程 选择 查询 执行 命令 执行存储过程 7 若要保存脚本 选择 文件 保存 命令 完成脚本修改的保存 8 接受文件名或使用新名称替换之 再单击 保存 按钮 完成存储过程的修改 8 4 2删除存储过程 不需要存储过程时可将其删除 如果数据库中还有另一个存储过程调用了已被删除的存储过程 那么SQLServer将在执行调用时提示错误消息 8 5存储过程中的程序控制语句 SQLServer2008中的存储过程支持一种编程模式 可通过一些语句来控制程序的走向 如返回值大于1 则执行A选项 如果小于1 则执行B选项 这是一个选择语句 本节就介绍存储过程的这些控制语句 8 5 1控制语句概述 在SQLServer中 程序控制语句用于控制Transact SQL语句 语句块或存储过程的执行流程 其与常见的程序设计语言类似 8 5 2IF ELSE条件选择语句 在SQLServer中 为了控制程序的执行方向 提供了IF ELSE条件判断语句 IF ELSE语句的语法格式如下 IF ELSE 8 5 3BEGIN END语句块 之所以把它称为语句块 是因为在BEGIN语句后 可以书写大量代码 写完后再添加END语句 位于BEGIN和END之间的Transact SQL语句都属于这个语句块 可视作一个单元来执行 执行BEGIN END经常在条件语句 如IF ELSE 中使用 在BEGIN END中可嵌套 使用另外的BEGINEND来定义另一程序块 BEGIN END的语法格式如下 BEGINEND 8 5 4CASE语句 CASE语句用于多条件分支选择 可完成计算多个条件并为每个条件返回单个值 SQLServer的CASE多分支语句包括都支持可选ELSE参数的两种格式 简单CASE表达式 该表达式将某个表达式与一组简单表达式进行比较以确定结果 搜索CASE表达式 搜索计算一组布尔表达式以确定结果 1 简单CASE表达式2 搜索CASE表达式 8 5 5WHILE循环语句 WHILE语句有一个条件 只要符合这个条件 就重复执行SQL语句或语句块的循环内容 WHILE命令在设定的条件成立时 会重复执行SQL语句或程序块 可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行 WHILE语句也可以嵌套 WHILE循环结构的语法可表示如下 WHILEBEGIN BREAK CONTINUE SQL语句或程序块 END 8 5 6WAITFOR等待语句 WAITFOR语句指定延迟一段时间 时间间隔或一个时刻 来执行 触发 一个Transact SQL语句 语句块 存储过程或事务 WAITFOR语句的语法如下 WAITFOR DELAY time to pass TIME time to execute 8 6存储过程必须注意的问题 合理地编写自己需要的存储过程 可以最大限度地利用SQLServer的各种资源 作为一个初学者 在编写存储过程时 可能会遇到很多的问题不能解决 或者对存储过程还有很多的疑问 这里说说在设计和使用存储过程中常会遇到的一些问题 8 6 1从存储过程返回给定参数的值 已经建立的存储过程myfirstprocedure返回了参数的值 根据返回值类型的不同 可以将存储过程分为3类 返回记录集的存储过程 返回数值的存储过程以及行为存储过程 返回记录集的存储过程的执行结果是一个记录集 典型的例子是从数据库中检索出符合某一个或几个条件的记录 返回数值的存储过程执行完以后返回一个值 行为存储过程仅仅是用来实现数据库的某个功能 而没有返回值 例如在数据库中的更新和删除操作 8 6 2处理执行存储过程时的错误 一般在写存储过程的时候 就应写上错误处理的语句来处理错误 SQLServer有接近3800个预定义错误代码 错误定义的级别从0 25 20以上的错误代表重大错误 通常意味着该错误会导致存储进程立刻终止在访问存储进程的时候发生错误 可以通过 error系统函数返回错误代码 通过查阅可以得到真实的错误信息和级别 8 6 3存储过程的语句要求 一般写存储过程给变量赋值要用select或set 两个都可以 如set name cast getdate asvarchar 20 另外不要忘记as 在建立存储过程中要用as 如 createprocedureexample1as SQL语句分行书写 每行语句最好有相应的注释 总之规范书写格式 多一些注释 能让其他开发人员 维护人员很容易读懂代码 定位问题 提高工作效率 8 6 4执行存储过程无反应 执行存储过程的时候 没有一点反映 执行一直不结束 是怎么回事 这种情况一般是发生了死锁现象 发生死锁一般有如下几种可能 死循环 重复执行存储过程 表空间不够 8 7 1存储过程和函数的区别 存储过程是用户定义的一系列SQL语句的集合 涉及特定表或其他对象的任务 用户可以调用存储过程 而函数通常是数据库已定义的方法 它接收参数并返回某种类型的值 函数并不涉及特定用户表 本题主要针对一般的开发人员 因为存储过程和函数在开发程序中使用的频率非常高 8 7 2存储过程是否也有输入和输出参数 存储过程是批处理地执行一段SQL语句 这些语句中可以定义变量 也可以定义输入参数和输出参数 通过这些参数 存储过程可以方便地进行数据处理功能 对程序开发人员设置此题 是为了让他们更