存储过程与触发器综合概述.pdf

第8章 存储过程与触发器 本章学习目标 l 理解存储过程和触发器的作用 l 熟练创建和管理存储过程及触发器 l 灵活运用存储过程和触发器提高系统开 发效率 8.1 存储过程 8.1.1 存储过程基础知识 1．存储过程简介 存储过程是存储在服务器上的由SQL语句和控制流语句 组成的一个预编译集合。存储过程划分为系统存储过程以及 用户存储过程。一个存储过程可以作为一个独立的单元进行 处理。在SQL Server中可以通过存储过程来完成很多管理任 务，利用一些常用的系统存储过程可以很方便的查看、操作 数据库对象。系统存储过程的命名通常以”sp_”作为前缀， 并且存储于Master数据库中，如果用户拥有足够的权限，就 可以在任何数据库中调用这些存储过程。 存储过程提供了一种封装某一个需要重复执行任务的方法。 一旦定义了一个存储过程之后，在应用程序中就可以对其进 行调用。在存储过程中，不仅可以包含程序流、逻辑以及对 数据库的查询，而且也可以接受参数、输出参数、返回单个 或多个结果集。 所以通过设计自定义用户存储过程，用户可以使其实现 强大的编程功能。 使用存储过程可以比单独的SQL语句完成更为复杂的功 能，并且系统会对存储过程中的SQL语句进行了预编译处理， 使得执行速度有了大幅度的提升。存储过程被第一次调用后， 会保存在高速缓冲区中，这样再次执行同一个存储过程时， 会提高了重复调用的效率。在实际应用中，可以将复杂的商 业规则封装在存储过程中，从而提高程序语句的利用率。 2 ．存储过程的优点 •存储过程一旦执行一次后，其执行的计划就会驻留在计算机 的高速缓冲存储器中。其后对同一个存储过程的调用就可以 直接利用编译后在高速缓存中的二进制形式来完成操作。 •可以在单个存储过程中执行一系列SQL语句，因而可以用于 设计、封装企业的功能模块。应用程序使用相同的存储过程 进行操作，确保了在数据访问、操纵的一致性。 • 可以在当前的存储过程内部引用其它存储过程，这样可以 将复杂语句进行简化。 • 提供了一种数据库访问的途径，只需要用户拥有执行存储 过程的权限，那么通过使用存储过程就可以完成对数据库 的各种操作，如添加数据、修改数据、删除数据等，而不 需要考虑用户是否拥有存储过程所处理的数据对象的访问 权限。 3．使用存储过程的注意事项 •在命名自定义存储过程时避免与系统存储过程名相同。 •存储过程最多能够支持32层的嵌套。 •命名存储过程中的标识符时，长度不能超过128个字符。 •存储过程中参数的个数不能超过2100 。 8.1.2 创建和执行存储过程 在SQL Server中，既可以通过企业管理器，也可以通过使用 CREATE PROCEDRUE语句的方式来创建存储过程。 1．使用企业管理器创建存储过程 步骤： ①在企业管理器中，展开服务器组、服务器节点、数据库节 点。 ②选择某一个数据库并在该节点上点击鼠标右键，选择 【新 建】菜单下的 【存储过程】级联菜单，将弹出如图8-1所示的 对话框。 图8-1 新建存储过程对话框 ③在 【存储过程属性】对话框中的 【文本】编辑框中，对 [OWNER]以及[PROCEDURE NAME]部分进行修改，分别 修改为该存储过程的所有者和存储过程名称，并且输入该存 储过程所包含的SQL语句。如图8-2所示为创建了一个用于 查询学生信息的存储过程。 图8-2 用于查询学生信息的存储过程 ④如果需要验证存储过程中的SQL语句的正确性，可以单击 【检查语法】按钮，如果没有语法错误则会弹出如图8-3所 示的“语法检查成功”的对话框。 图8-3 “语法检查成功”的提示对话框 ⑤当存储过程中没有语法错误后，可以单击 【确定】按钮来 保存自定义的存储过程。 2 ．使用CREATE PROCEDURE语句来创建存储过程 该语句可以创建永久使用的存储过程，也可以创建局部临时 过程、全局临时过程。 语法格式为： CREATE PROCEDURE 存储过程名称[;数值] [{@参数数据类型} [VARYING] [=参数的默认值] [OUTPUT] [,…n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPT