SQL_Server_2005数据库原理及应用教程第8章_存储过程和触发器.ppt

第8章 存储过程和触发器 8.1 存储过程 存储过程是独立存在于数据表之外的数据库对象，存储过程可以由用户调用，也可以由另一个过程或触发器调用。存储过程的参数可以被传递和返回，出错代码也可以被检验。 8.1.1 存储过程概述 存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果存储过程中使用了参数）来执行存储过程。 在SQL Server中使用存储过程具有如下优点。 1．模块化编程 2．快速执行 3．减少网络通信量 4．可以作为一种安全机制 8.1.2 存储过程的分类 1．系统存储过程 在SQL Server 2005中，许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程称为系统存储过程。系统存储过程主要存储在master数据库中并以sp_为前缀，系统存储过程从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。 2．用户自定义存储过程 是由用户创建并完成某一特定功能的存储过程，是封装了可重用代码的Transact-SQL语句模块。用户自定义存储过程可以接受输入参数，向客户端返回表格或标量结果和消息，调用数据定义语言和数据操作语言语句，以及返回输出参数。用户自定义的存储过程有两种类型：Transact-SQL存储过程或CLR（公共语言运行时）存储过程。 3．扩展存储过程 允许使用高级编程语言创建应用程序的外部例程，从而使得SQL Server的实例可以动态地加载和运行DLL。 8.1.3 用户存储过程的创建与执行 在SQL Server 2005中，可以使用两种方法创建存储过程：一种是使用SQL Server Management Studio；另一种是使用Transact-SQL命令CREATE PROCEDURE。 用户使用CREATE PROCEDURE语句创建存储过程时，只能在当前数据库中创建存储过程。只有数据库的拥有者具有默认的创建存储过程的权限，他可以将此权限转让给其他用户。 创建存储过程时，创建者需要给存储过程取一个有别于其他存储过程的名称，然后再编写存储过程中包含的一系列SQL语句。 1．语法格式 创建存储过程的语法格式： CREATE PROC[EDURE] procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT]][,...n]  WITH??AS sql_statement [ ...n ] ①procedure_name：用于指定要创建的存储过程的名称。 ②number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 ③@parameter：过程中的参数，在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 ④data_type：用于指定参数的数据类型。 ⑤VARYING：用于指定作为输出OUTPUT参数支持的结果集。 ⑥DEFAULT：用于指定参数的默认值。 ⑦OUTPUT：表明该参数是一个返回参数。 ⑧AS：用于指定该存储过程要执行的操作。 ⑨sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。 2．相关注意事项 ①不能将 CREATE PROCEDURE语句与其他 SQL 语句组合到单个批处理中。 ②创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 ③存储过程是数据库对象，名称必须遵守标识符规则。 ④只能在当前数据库中创建存储过程。 ⑤一个存储过程的最大尺寸为128M。 ⑥可以在存储过程内引用临时表。 ⑦如果执行的存储过程调用另一个存储过程，则被调的存储过程可以访问由第一个存储过程创建的包括临时表在内的所有对象。 ⑧存储过程中参数的最大数量为2100。 ⑨不要以sp_为前缀创建任何存储过程。 例8.1 使用SQL命令窗口创建一个存储过程，计算两个整数之和并输出。 打开SQL Server Management Studio，用鼠标单击“新建查询”按钮打开SQL命令窗口，输入下面的建立存储过程的代码后执行，以便建立该存储过程，如图8.2所示。 CREATE PROCEDURE add_two @p1 int，@p2 int AS declare @sum int select @sum=@p1+@p2 print @sum 例8.3 创建带有通配符参数的存储过程 下面的存储过程是从课程表中返回相关课程(提供课程名称)的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有课程