sql2000课件第8章存储过程与触发器.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 ] ] 数值选项 便于对存储过程进行分组，这样在删除存储过程时可以将同一个组中的所有过程同时删除。比如，一个组中有两个存储过程，orderproc;1、orderproc;2，那么可以使用DROP PROCEDURE orderproc删除整个组。 参数 在CREATE PROCEDURE语句中可以申明一个或者多个参数。在执行存储过程时必须提供每个参数的值。定义参数时将@作为参数名称的第一个字符，而且必须符合标识符的命名规则。参数的作用范围仅限于过程内部。 数据类型 每一个参数都需要为其确定一个数据类型。除cursor类型以外，所有的数据类型都适用于存储过程中的参数。参数的数据类型为cursor时，必须指定VARYING以及OUTPUT选项。 SQL语句 为在存储过程中需要执行的操作。 【例8-1】使用CREATE PROCEDURE语句创建一个名称为proc_bjrs的存储过程，用于检索现有班级及人数。CREATE PROCEDURE proc_bjrs AS SELECT DISTINCT(班级表.班级编号),班级表.班级名称,人数=COUNT(学生基本信息表.学号) FROM 学生基本信息表,班级表 WHERE 班级表.班级编号=学生基本信息表.班级编号 GROUP BY 班级表.班级编号