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