数据库课程详细教程 第09章课件.ppt

第9章 存储过程与触发器 本章导读 本章介绍了数据库中两个重要的可编程对象存储过程和触发器，并结合实例讲解了在SQL Server中如何创建、修改和管理存储过程和触发器。 学习目的与要求 （1）掌握在SQL Server2005设计和管理存储过程的方法 . （2）掌握在SQL Server2005设计和管理触发器的方法;9.1 设计和管理存储过程;9.1.2　创建存储过程; 2．存储过程的各选项设置规则 1） @ parameter是过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 2）如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL 3）。 OUTPUT选项指示参数是输出参数。 4）如果创建存储过程时，使用 WITH encryption子句，过程定义将以不可读的形式存储。 5） FOR REPLICATION 指定不能在订阅服务器上执行为复制创建的存储过程。 6） SQL 语句指定过程要执行的操作 7）可以在存储过程内引用临时表。; 【例9.1】创建一个存储过程proc_student1用于显示学号为“J0402”的学生基本信息（包括学生学号、姓名、性别、系）。 CREATE PROCEDURE studentcourse.proc_student1 AS SELECT 学号,姓名,性别,系 FROM s WHERE 学号=’J0402’ GO; 1.格式 EXEC | EXECUTE [ @返回状态= ] [schema_name.] 存储过程名称 [ @形参= ] { value | @变量[ OUTPUT ] | [ DEFAULT ] [ ,...n ] [ WITH RECOMPILE ] 2.功能。 “@返回状态”是保存存储过程的返回状态。 “@形参” 是在定义存储过程时，定义的参数。在采用“@形参=value ”格式时，参数名称和常量不必按在存储过程中定义的顺序提供。但是，如果任何参数使用了“@形参=value ”格式，则对后续的所有参数均必须使用该格式。“value”是传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。; 【例9.2】现在我们就来执行刚才创建好的存储过程proc_student1，显示学号为“J0402”的学生基本信息（包括学生学号、姓名、性别、系）。 方法一：在SQLServer Management Studio中执行存储过程 方法二：使用ＳＱＬ命令 在新建的查询窗口输入命令： Execute proc_student1; 1.参数 存储过程的参数在创建时声明，SQL Server支持两种参数：输入参数和输出参数。 1）输入参数 输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。在执行存储过程时，可以为输入参数传递参数值，或使用默认值。 2）输出参数 输出参数允许存储过程将数据值返回给调用程序。OUTPUT关键字用来指出输出参数。; 2．返回值 存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没有显式设置返回代码的值，SQL Server默认返回代码为0，表示成功执行；若返回-1到-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99之间的整数来定义自己的返回状态值，以表示不同的执行结果。在执行存储过程时，要定义一个变量来接收返回的状态值。 １）RETURN语句格式 RETURN [返回整型值的表达式 ] ２）功能 RETURN 语句将无条件地从过程、批处理或语句块中退出。返回整型值。;9.1.5 修改存储过程; 【例9.18】现在我们就来修改刚才在【例9.11】中创建好的存储过程proc_student3，用于显示指定学号的学生各门课程的最高成绩，执行该存储过程返回学号为‘J0401’的学生的最高成绩。 完整的ALTER PROCEDURE语句如下： ALTER PROCEDURE dbo.proc_student3 @num char(6), @max smallint output AS BEGIN SELECT @max=max(成绩) FROM s JOIN sc ON s.学号=sc.学号 WHERE s.学号=@num END; 1．格式 DROP PROCEDURE {存储过程名} [ ,...n ] 2．功能 从当前数据库中删除一个或多个存储过程或过程组。 【例9.19】现在