数据库存储过程与触发器

《数据库存储过程与触发器》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、第8章存储过程与触发器本章学习目标l理解存储过程和触发器的作用l熟练创建和管理存储过程及触发器l灵活运用存储过程和触发器提高系统开发效率8.1存储过程8.1.1存储过程基础知识1．存储过程简介存储过程是存储在服务器上的由SQL语句和控制流语句组成的一个预编译集合。存储过程划分为系统存储过程以及用户存储过程。一个存储过程可以作为一个独立的单元进行处理。在SQLServer中可以通过存储过程来完成很多管理任务，利用一些常用的系统存储过程可以很方便的查看、操作数据库对象。系统存储过程的命名通常以”sp_”作为前缀，并且

2、存储于Master数据库中，如果用户拥有足够的权限，就可以在任何数据库中调用这些存储过程。存储过程提供了一种封装某一个需要重复执行任务的方法。一旦定义了一个存储过程之后，在应用程序中就可以对其进行调用。在存储过程中，不仅可以包含程序流、逻辑以及对数据库的查询，而且也可以接受参数、输出参数、返回单个或多个结果集。所以通过设计自定义用户存储过程，用户可以使其实现强大的编程功能。使用存储过程可以比单独的SQL语句完成更为复杂的功能，并且系统会对存储过程中的SQL语句进行了预编译处理，使得执行速度有了大幅度的提升。存储

3、过程被第一次调用后，会保存在高速缓冲区中，这样再次执行同一个存储过程时，会提高了重复调用的效率。在实际应用中，可以将复杂的商业规则封装在存储过程中，从而提高程序语句的利用率。2．存储过程的优点存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。可以在单个存储过程中执行一系列SQL语句，因而可以用于设计、封装企业的功能模块。应用程序使用相同的存储过程进行操作，确保了在数据访问、操纵的一致性。可以在当前的存储过程内部

4、引用其它存储过程，这样可以将复杂语句进行简化。提供了一种数据库访问的途径，只需要用户拥有执行存储过程的权限，那么通过使用存储过程就可以完成对数据库的各种操作，如添加数据、修改数据、删除数据等，而不需要考虑用户是否拥有存储过程所处理的数据对象的访问权限。3．使用存储过程的注意事项在命名自定义存储过程时避免与系统存储过程名相同。存储过程最多能够支持32层的嵌套。命名存储过程中的标识符时，长度不能超过128个字符。存储过程中参数的个数不能超过2100。8.1.2创建和执行存储过程在SQLServer中，既可以通过企业管

5、理器，也可以通过使用CREATEPROCEDRUE语句的方式来创建存储过程。1．使用企业管理器创建存储过程步骤：①在企业管理器中，展开服务器组、服务器节点、数据库节点。②选择某一个数据库并在该节点上点击鼠标右键，选择【新建】菜单下的【存储过程】级联菜单，将弹出如图8-1所示的对话框。图8-1新建存储过程对话框③在【存储过程属性】对话框中的【文本】编辑框中，对[OWNER]以及[PROCEDURENAME]部分进行修改，分别修改为该存储过程的所有者和存储过程名称，并且输入该存储过程所包含的SQL语句。如图8-2所示

6、为创建了一个用于查询学生信息的存储过程。图8-2用于查询学生信息的存储过程④如果需要验证存储过程中的SQL语句的正确性，可以单击【检查语法】按钮，如果没有语法错误则会弹出如图8-3所示的“语法检查成功”的对话框。图8-3“语法检查成功”的提示对话框⑤当存储过程中没有语法错误后，可以单击【确定】按钮来保存自定义的存储过程。2．使用CREATEPROCEDURE语句来创建存储过程该语句可以创建永久使用的存储过程，也可以创建局部临时过程、全局临时过程。语法格式为：CREATEPROCEDURE存储过程名称[;数值]

7、[{@参数数据类型}[VARYING][=参数的默认值][OUTPUT][,…n][WITH{RECOMPILE

8、ENCRYPTION

9、RECOMPILE,ENCRYPTION}][FORREPLICATION]ASSQL语句[...n]]其中：存储过程名称必须符合标识符命名规则，而且对于数据库以及所有者命名必须唯一。一个完整的命名不能超过128个字符。【例8-1】使用CREATEPROCEDURE语句创建一个名称为proc_bjrs的存储过程，用于检索现有班级及人数。CREATEPROCEDUREpro

10、c_bjrsASSELECTDISTINCT(班级表.班级编号),班级表.班级名称，人数=COUNT(学生基本信息表.学号)FROM学生基本信息表,班级表WHERE班级表.班级编号=学生基本信息表.班级编号GROUPBY班级表.班级编号,班级表.班级名称GO在查询分析器中执行以上命令后，然后执行该存储过程EXECUTEproc_bjrs，运行结果如图8-4所示。图8-