数据库存储过程及触发器.ppt

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