第十章-存储过程和触发器.ppt

第10章 存储过程和触发器 10.1 存储过程的概念 10.2 建立和执行存储过程 10.3 存储过程的管理与维护 10.4 触发器的概念 10.5 创建和应用DML触发器 10.6 触发器的管理与维护 10.7 DDL触发器 10.8 事务 教学要求：通过本章学习，读者应掌握以下内容： 存储过程和触发器的作用 存储过程的创建、修改和删除 存储过程的灵活运用 触发器的创建、修改和删除 触发器的使用 10.1 存储过程的概念 10．1．1 基本概念 存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 10．1．2 存储过程的优点 （1）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。 （2）防止数据库中表的细节暴露给用户。 （3）提供安全机制。 （4）改进性能。 （5）减少网络流量。 10．1．3 存储过程的类型 （1）系统存储过程。 （2）本地存储过程。 （3）临时存储过程。 （4）远程存储过程。 （5）扩展存储过程。 10.2 建立和执行存储过程 10．2．1 创建和执行简单存储过程 创建存储过程的基本语法如下： CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 其中各参数如下： [WITH ENCRYPTION]：对存储过程进行加密。 [WITH RECOMPILE]：对存储过程重新编译。 【例10.1】 从xsgl数据库的三个表中查询，返回学生的学号、姓名、课程名和成绩。该存储过程实际上只返回一个查询信息。 --建立存储过程 USE xsgl GO CREATE PROCEDURE stu_cj AS SELECT xs.学号,姓名,课程名,成绩 FROM xs INNER JOIN cj ON xs.学号=cj.学号 INNER JOIN kc ON cj.课程号=kc.课程号 GO --调用存储过程 EXECUTE stu_cj  10．2．2 存储过程的执行 存储过程创建成功后，用户可以执行存储过程来检查存储过程的返回结果。执行存储过程的基本语法如下： EXEC[UTE] 存储过程名 在例10.1中已经使用了存储过程的执行语句。同时EXECUTE命令除了可以执行存储过程外还可以执行存放Transact-SQL语句的字符串变量，或直接执行Transact-SQL语句字符串。此时EXECUTE语句的语法格式如下。 EXECUTE({@字符串变量| [N]SQL语句字符串}[+...n] 10．2．3 带输入参数的存储过程 1．建立存储过程 一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 声明带输入参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型[=默认值] [，…n] [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 【例10.3】 从xsgl数据库的三个表中查询某人指定课程的成绩。 USE xsgl GO IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name=stu_cj1 AND type=P) DROP PROCEDURE stu_cj1 GO CREATE PROCEDURE stu_cj1 @name char(10),@cname char(16) AS SELECT xs.学号,姓名,课程名,成绩 FROM xs INNER JOIN cj ON xs.学号=cj.学号INNER JOIN kc ON cj.课程号=kc.课程号 WHERE xs.姓名=@name AND kc.课程名=@cname GO 2．执行存储过程 使用参数名传递参数值，是通过语句“@参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值，其语法格式为： EXECUTE 存储过程名 [@参数名=参数值] [，…n] 例10.3中的存储过程可以有下列几种执行方式。 EXECUTE stu_cj1 @name=王丹,@cname=计算机基础 或 EXECUTE stu_cj1 王丹,计算机基础 【例10.4】 从三个表的连接中返回指定学生的学号、姓名、所选课程名称及