SQL server 存储过程和触发器.doc

第10章 存储过程与触发器 本章内容 10.1 存储过程概述 10.2 存储过程的创建与使用 10.3 触发器概述 10.4 触发器的创建与使用 10.5 事务处理 10.6 SQL Server的锁机制 10.1 存储过程概述 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。 1. 存储过程的类型 SQL Server存储过程的类型包括： 系统存储过程 用户定义存储过程 临时存储过程 扩展存储过程。 (1)系统存储过程 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，它从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。 通过系统存储过程，SQL Server中的许多管理性或信息性的活动(例如使用sp_depends、sp_helptexts可以了解数据数据库对象、数据库信息)都可以顺利有效地完成。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。 (2)用户定义存储过程 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。 (3)临时存储过程 临时存储过程与临时表类似，分为局部临时存储过程和全局临时存储过程，且可以分别向该过程名称前面添加“#”或“##”前缀表示。“#”表示本地临时存储过程，“##”表示全局临时存储过程。使用临时存储过程必须创建本地连接，当SQL Server关闭后，这些临时存储过程将自动被删除。 由于SQL Server支持重新使用执行计划，所以连接到SQL Server 2000的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。 (4)扩展存储过程 扩展存储过程是SQL Server可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。 2. 存储过程的功能特点 SQL Server的存储过程可实现以下功能： (1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。 (2)包含执行数据库操作的编程语句，包括调用其他过程。 (3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。 存储过程具有以下优点 (1)模块化编程。 (2)快速执行。 (3)减少网络通信量。 (4)提供安全机制。 (5)保证操作一致性。 10.2 存储过程的创建与使用 10.2.1 创建存储过程 10.2.2 执行存储过程 10.2.3 修改存储过程 10.2.4 删除存储过程 10.2.5 存储过程参数与状态值 10.2.1 创建存储过程 1. 使用企业管理器创建存储过程 (1)启动企业管理器，登录到要使用的服务器。 (2)选择要创建存储过程的数据库，在左窗格中单击“存储过程”文件夹。 (3)右击“存储过程”文件夹，在弹出菜单中选择“新建存储过程”选项，此时打开创建存储过程对话框。 (4)在“文本”编辑框中输入存储过程正文。 (5)单击“检查语法”按钮，检查语法是否正确。 (6)单击“确定”按钮，保存存储过程。 (7)在图10-1的右窗格中，右击该存储过程，在弹出菜单中选择“所有任务”，选择“管理权限”，在“对象属性”对话框中设置权限（如设置PUBLIC用户有EXEC权限）。 2. 使用向导创建存储过程 (1)在企业管理器中选择当前服务器，然后执行“工具→向导”菜单命令，弹出“选择向导”对话框。 (2)在“选择向导”对话框中展开“数据库”项，双击“创建存储过程向导”项，弹出“创建存储过程”对话框。 (3)单击“下一步”按钮，进入“选择数据库”对话框 (4)单击“下一步”按钮，进入“选择存储过程”对话框。在该对话框中，设置该存储过程中的表要执行的操作，可同时设置一个或多个操作(用于插入、删除和更新)。如果选择多个操作，每个操作都将创建一个存储过程。这里为customer表创建插入存储过程。 (5)单击“下一步”按钮，进入“完成存储过程创建”对话框。在该对话框中显示将要创建的存储过程的名称及其操作，这里的存储过程名称是insert_customer_1。 (6)在图10-7中选择一个存储过程，单击“编辑”按钮，进入“编辑存储过程属性”对话框。在此可以重新设置存储过程的名称和要操作的字段。 (7)如果要通过SQL语句对正在创建的存储过程进行修改，单击“编辑SQL”按钮，弹出“