《存储过程与触发器综合概述(共46页)》由会员分享，可在线阅读，更多相关《存储过程与触发器综合概述(共46页)（45页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程与触发器12021/11/17本章学习目标l 理解存储过程和触发器的作用l 熟练创建和管理存储过程及触发器l 灵活运用存储过程和触发器提高系统开发效率22021/11/17 8.1 存储过程8.1.1 存储过程基础知识1存储过程简介 存储过程是存储在服务器上的由SQL语句和控制流语句组成的一个预编译集合。存储过程划分为系统存储过程以及用户存储过程。一个存储过程可以作为一个独立的单元进行处理。在SQL Server中可以通过存储过程来完成很多管理任务，利用一些常用的系统存储过程可以很方便的查看、操作数据库对象。系统存储过程的命名通常以”sp_”作为前缀，并且存储于Master数据
2、库中，如果用户拥有足够的权限，就可以在任何数据库中调用这些存储过程。存储过程提供了一种封装某一个需要重复执行任务的方法。一旦定义了一个存储过程之后，在应用程序中就可以对其进行调用。在存储过程中，不仅可以包含程序流、逻辑以及对数据库的查询，而且也可以接受参数、输出参数、返回单个或多个结果集。32021/11/17 所以通过设计自定义用户存储过程，用户可以使其实现强大的编程功能。 使用存储过程可以比单独的SQL语句完成更为复杂的功能，并且系统会对存储过程中的SQL语句进行了预编译处理，使得执行速度有了大幅度的提升。存储过程被第一次调用后，会保存在高速缓冲区中，这样再次执行同一个存储过程时，会提高了
3、重复调用的效率。在实际应用中，可以将复杂的商业规则封装在存储过程中，从而提高程序语句的利用率。2存储过程的优点存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。可以在单个存储过程中执行一系列SQL语句，因而可以用于设计、封装企业的功能模块。应用程序使用相同的存储过程进行操作，确保了在数据访问、操纵的一致性。42021/11/17 可以在当前的存储过程内部引用其它存储过程，这样可以将复杂语句进行简化。 提供了一种数据库访问的途径，只需要用户拥有执行存储过程的权限，那么通过使用存储过程就可以完成
4、对数据库的各种操作，如添加数据、修改数据、删除数据等，而不需要考虑用户是否拥有存储过程所处理的数据对象的访问权限。3使用存储过程的注意事项在命名自定义存储过程时避免与系统存储过程名相同。存储过程最多能够支持32层的嵌套。命名存储过程中的标识符时，长度不能超过128个字符。存储过程中参数的个数不能超过2100。52021/11/178.1.2 创建和执行存储过程在SQL Server中，既可以通过企业管理器，也可以通过使用CREATE PROCEDRUE语句的方式来创建存储过程。1使用企业管理器创建存储过程步骤：在企业管理器中，展开服务器组、服务器节点、数据库节点。选择某一个数据库并在该节点上点
5、击鼠标右键，选择【新建】菜单下的【存储过程】级联菜单，将弹出如图8-1所示的对话框。图8-1新建存储过程对话框62021/11/17在【存储过程属性】对话框中的【文本】编辑框中，对OWNER以及PROCEDURE NAME部分进行修改，分别修改为该存储过程的所有者和存储过程名称，并且输入该存储过程所包含的SQL语句。如图8-2所示为创建了一个用于查询学生信息的存储过程。图8-2用于查询学生信息的存储过程72021/11/17如果需要验证存储过程中的SQL语句的正确性，可以单击【检查语法】按钮，如果没有语法错误则会弹出如图8-3所示的“语法检查成功”的对话框。图8-3“语法检查成功”的提示对话框
6、当存储过程中没有语法错误后，可以单击【确定】按钮来 保存自定义的存储过程。82021/11/172使用CREATE PROCEDURE语句来创建存储过程该语句可以创建永久使用的存储过程，也可以创建局部临时过程、全局临时过程。语法格式为：CREATE PROCEDURE 存储过程名称 ;数值参数 数据类型 VARYING =参数的默认值 OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS SQL语句 .n 其中：存储过程名称 必须符合标识符命名规则，而且对于数据库以及所有者命名必须唯一
7、。一个完整的命名不能超过128个字符。92021/11/17【例8-1】使用CREATE PROCEDURE语句创建一个名称为proc_bjrs的存储过程，用于检索现有班级及人数。CREATE PROCEDURE proc_bjrs AS SELECT DISTINCT(班级表.班级编号),班级表.班级 名称， 人数=COUNT(学生基本信息表.学号) FROM 学生基本信息表,班级表 WHERE 班级表.班级编号=学生基本信息表.班级编号 GROUP BY 班级表.班级编号,班级表.班级名称GO在查询分析器中执行以上命令后，然后执行该存储过程EXECUTE proc_bjrs，运行结果如图8
8、-4所示。102021/11/17图8-4 检索现有班级以及人数112021/11/17【例8-2】设计一个带有参数的存储过程，该参数用于传递班级编号，根据该参数在学生基本信息表中检索出某一个班级中所有学生的信息。CREATE PROCEDURE proc_bjcx bjbh VARCHAR(8)AS SELECT * FROM 学生基本信息表 WHERE 班级编号=bjbhGO其中参数bjbh用于接收班级编号，在实际调用中根据该参数将会返回一个结果集。例如，EXECUTE proc_bjcx 20051003，将会显示班级编号为20051003的学生信息。运行结果如图8-5所示122021/
9、11/17图8-5 用于传递班级编号的存储过程132021/11/178.1.3 修改与删除存储过程在SQL Server中，可以通过企业管理器或SQL语句两种方式修改或删除存储过程。1使用企业管理器修改存储过程在企业管理器中，展开服务器组、服务器以及数据库节点。选择某一个数据库，展开该数据库节点后点击【存储过程】，在右侧的窗口中将会显示出存储过程的列表。右击需要修改的存储过程，在弹出菜单中选择【属性】，弹出存储过程属性对话框，如图8-6所示。图8-6 【存储过程属性】对话框142021/11/17在【存储过程属性】对话框的【文本】编辑框中修改包含的SQL语句。需要注意的是，存储过程的名称不能
10、修改。在检查了语法的正确性之后，可以单击【确定】按钮保存修改并关闭对话框。2使用企业管理器重命名存储过程在企业管理器中，展开服务器组、服务器以及数据库节点。选择某一个数据库，展开该数据库节点后点击【存储过程】，在右侧的窗口中将会显示出存储过程的列表。右击需要重命名的存储过程，并选择弹出菜单中的【重命名】命令，如图8-7所示。152021/11/17图8-7 弹出菜单中的【重命名】命令重新输入存储过程的名称后，按下回车键完成修改。162021/11/173使用ALTER PROCEDURE语句修改存储过程ALTER PROCEDURE语句可以对数据库中已有的存储过程进行修该，但不会更改权限，也不
11、影响相关的存储过程或触发器。该语句的语法格式为：ALTER PROCEDURE 存储过程名称 ;数值参数 数据类型 VARYING =参数的默认值 OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS SQL语句 .n 其中，存储过程名称为数据库中已经存在的过程名，语法中的常用选项与CREATE PROCEDURE语句一致。172021/11/17【例8-3】修改已经创建的存储过程proc_bjcx及其功能，将其修改为可以根据系部编号来查询某一个系部中所有学生的信息。修改后将存储过程名
12、称重命名为proc_xbcx。在查询分析器中运行如下命令，即可完成重命名：ALTER PROCEDURE proc_bjcx xbbh VARCHAR(2)AS SELECT * FROM 学生基本信息表 WHERE 班级编号 IN (SELECT 班级编号 FROM 班级表 WHERE 系部编号=xbbh) GOSP_RENAME proc_bjcx , proc_xbcxGO182021/11/17使用SP_RENAME可以重新命名一个现有的存储过程。SP_RENAME的语法格式为：SP_RENAME 原名称 , 新名称。完成修改后查询系部编号为01的学生信息。EXEC proc_xbcx
13、 014使用企业管理器删除存储过程在企业管理器中，展开服务器组、服务器以及数据库节点。选择某一个数据库，展开该数据库后点击【存储过程】，在右侧的窗口中显示出存储过程的列表。右击需要删除的存储过程，并在弹出菜单中选择【删除】命令，将弹出【除去对象】对话框，如图8-8所示。在【除去对象】对话框中单击【全部除去】按钮将会完成对存储过程的删除操作。192021/11/17图8-8 【除去对象】对话框202021/11/175使用DROP PROCEDURE语句删除存储过程使用DROP PROCEDURE语句可以从当前的数据库中删除一个或多个存储过程。DROP PROCEDURE语句的语法格式为：DRO
14、P PROCEDURE 存储过程名称 ,.n 【例8-4】同时删除当前数据库中的两个存储过程，proc_a和proc_b。在查询分析器中运行如下命令，即可完成删除操作：DROP PROCEDURE proc_a , proc_bGO212021/11/178.2 触发器8.2.1 触发器及其作用1触发器简介 触发器属于一种特殊的存储过程，可以在其中包含复杂的SQL语句。触发器与存储过程的区别在于触发器能够自动执行并且不含有参数。通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则的完整性检查。对表进行包括添加数据、删除数据、更新数据中的一种或多种操
15、作时，触发器就会自动执行。 触发器可以划分为三种类别：INSERT触发器、DELETE触发器、UPDATE触发器。这三种触发器分别在发生数据的添加、删除、修改行为时自动执行。222021/11/172触发器的优点 触发器有助于在添加、更新或删除表中的记录时保留表之间已定义的关系。 触发器可以自动调用，当发生了对数据所作的任何修改时，与之相关的触发器就会立刻被激活。 可以完成数据库中相关表之间的级联修改。级联修改是指为了保证数据之间的逻辑性以及依赖关系，在对一张表进行修改的同时，其他表中需要进行的修改能够自动实现。 触发器可以对需要存储的数据加以限制，并且能够实现比CHECK约束更为复杂的功能。
16、在CHECK约束中不允许引用其他表中的列，而触发器可以引用。3触发方式 触发器的触发方式可以分为后触发和替代触发两种方式。后触发：指能够引发触发器的修改操作在完成之后才执行触发器的行为方式。创建该类触发器，需要使用AFTER关键字或者FOR关键字。232021/11/17替代触发：指定执行一个触发器，而不是执行SQL语句，这种替代触发语句的方式称为替代触发方式。8.2.3 创建触发器可以使用企业管理器以及CREATE TRIGGER语句两种方式来创建触发器。1使用企业管理器创建触发器打开企业管理器，在控制台根目录下依次展开服务器组、服务器节点、数据库节点。选择某一个数据库，并双击【表】节点，在右侧窗口的列表中选择其中的一张表。右击所选中的表，在弹出的下拉菜单中选择【所有任务】，并继续选择级联菜单中的【管理触发器】命令，将打开图8-9所示的【触发器属性】对话框。242021/11/17图8-9 【触发器属性】对话框252021/11/17在【触发器属性】对话框的文本框中，将TRIGGER NAME修改为新建触发器的名称，并输入触发器所包含的SQL语句。通过点击【检查语法】按钮来判断触发器
