sqlserver2008数据库应用与开发教学课件作者李新德第七章

第七章 存储过程和触发器,项目十 存储过程的创建与使用 项目十一 触发器的创建与使用,返回,项目十 存储过程的创建与使用,一、存储过程的概念 存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。存储过程可以接受输入参数，向客户端返回表格或标量结果和消息，调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输出参数。存储过程是数据库中的一个重要对象，一个设计良好的数据库应用程序常常用到存储过程。 二、存储过程的优点 （1） 存储过程运行的速度比较快，它在服务器中运行，比SQL 语句的运行速度快210 倍。,下一页,返回,项目十 存储过程的创建与使用,（2） 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值，可以向程序返回错误原因。 （3） 使用存储过程可以完成所有的数据库操作，并通过编程方式控制对数据库信息访问的权限，确保数据库的安全。 （4） 存储过程主要在服务器中运行，以减少对客户机的压力。 （5） 可以在单个存储过程中执行一系列SQL 语句，可以自动完成一些需要预先执行的任务。 （6） 增加网络流量，降低网络负担，如果使用单条调用语句的方式，就必须传输大量的SQL 语句。,上一页,下一页,返回,项目十 存储过程的创建与使用,三、存储过程的类型 （1） 系统存储过程。系统存储过程可以作为命令执行，定义在系统数据库master 中，以“_sp”为前缀。 （2） 扩展存储过程。扩展存储过程以“xp_”开头，是在SQL Server 2008 环境之外执行的动态链接库DLL。因为扩展存储过程命令不易编写，而且可能会引发安全性问题，所以本书不详细介绍扩展存储过程。 （3） 用户存储过程。用户存储过程可以通过TSQL 语言编写，也可以通过CLR 方式编写。,上一页,下一页,返回,项目十 存储过程的创建与使用, TSQL 存储过程是指保存的TSQL 语句集合，它可以接受和返回用户提供的参数。 CLR 存储过程是指对Microsoft.NET Framework 公共语言运行时（CLR）方法的引用，它可以接受和返回用户提供的参数。它在.NET Framework 程序集中是作为类的公共静态方法实现的。 任务 存储过程的创建与使用 【知识准备】 1. 用TSQL 命令创建存储过程 语法形式如下：,上一页,下一页,返回,项目十 存储过程的创建与使用,create proc 新存储过程名;number 指定存储过程名 as 执行的操作n 其中各参数含义如下： （1） 新存储过程名：用于指定存储过程名，必须符合标识符规则，并且对于数据库及所在架构必须唯一。这个名称应当尽量避免与系统内置函数的名称相同，否则会发生错误，也应当尽量避免使用“sp_”作为前缀。,上一页,下一页,返回,项目十 存储过程的创建与使用,（2） 执行的操作：过程中要包含的任意数目和类型的TSQL 语句，存储过程体中可以包含一条或多条TSQL 语句，除了DCL、DML、DDL 命令外，还能包含过程式语句，如变量的定义与赋值语句、流程控制语句。 2. 用TSQL 命令执行存储过程 语法形式如下： exec 存储过程名 3. 用TSQL 命令删除存储过程 语法形式如下：,上一页,下一页,返回,项目十 存储过程的创建与使用,drop procedure 存储过程名 ,n 【任务实施】 1. 无参数的存储过程 创建无参数的存储过程，返回STUDY 数据库中表tb_student 中学号为001204 的学生的成绩情况。 创建存储过程exp1 的代码如下： use STUDY go create procedure exp1,上一页,下一页,返回,项目十 存储过程的创建与使用,as select * from tb_student where studentid='001204' go 将上述代码输入到“新建查询”中，单击“执行”，完成存储过程exp1 的创建，如图71所示。 执行存储过程的程序代码为：,上一页,下一页,返回,项目十 存储过程的创建与使用,exec exp1 输入上述代码并用鼠标选中，再单击“执行”，结果如图72 所示。 2. 带参数的存储过程 从STUDY 数据库的3 个表中查询某人指定课程的成绩和学分。 创建存储过程exp2 的代码如下：,上一页,下一页,返回,项目十 存储过程的创建与使用,on b.courseid=t.courseid where a.name=name and t.coursename=coursename go 将上述代码输入到“新建查询”中，单击“执行”，完成存储过程exp2 的创建，如图73所示。 执行存储过程的代码为： execute exp2 “张林“,“计算机基础“ 输入上述代码并用鼠标选中，再单击“执行”，结果如图74 所示。 3. 创建模式匹配参数的存储过程,上一页,下一页,返回,项目十 存储过程的创建与使用,从STUDY 的3 个表的连接中返回指定姓李的学生的学号、姓名、所选课程名称及该课程的成绩。 创建存储过程的代码如下：,上一页,下一页,返回,项目十 存储过程的创建与使用,将上述代码输入到“新建查询”中，单击“执行”，完成存储过程exp3 的创建，如图75所示。 执行存储过程的代码为： exec exp3 输入上述代码并用鼠标选中，再单击“执行”，结果如图76 所示。 4. 删除存储过程 将存储过程exp1 从数据库中删除，语句如下： drop procedure exp1 输入上述代码并用鼠标选中，再单击“执行”，就可以删除存储过程，也可以在“资源管理器”中用鼠标操作来删除，如图77 所示。,上一页,返回,项目十一 触发器的创建与使用,一、触发器的概念 触发器是一个关联到表的数据对象，它不需要被调用，当针对一个表的特殊事件出现时，它就会被触发。 触发器是一种特殊类型的存储过程，也是由SQL 语句组成的，因此用在存储过程中的语句也可以用在触发器的定义中。触发器与表的关系密切，用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器将自动执行。 触发器由“触”和“发”两个动作组成，当对一张表进行insert、update 或delete 等操作时（此为“触”），SQL Server 2008 就会自动执行触发器所定义的SQL 语句（此为“发”）。,下一页,返回,项目十一 触发器的创建与使用,二、触发器的类型 在SQL Server 2008 中，按照触发事件的不同可将触发器分为两大类： （1） DML 触发器：DML 触发器在数据库中发生数据操作语言（DML）事件时将启用。DML 事件包括在指定表或视图中修改数据的insert 语句、update 语句和delete 语句。因而，DML 触发器还可分为3 种类型：insert 触发器、update 触发器和delete 触发器。 利用DML 触发器可以方便地保持数据库中的数据完整性。,上一页,下一页,返回,项目十一 触发器的创建与使用,（2） DDL 触发器：DDL 触发器是SQL Server 2008 新增的功能，也是由相应的事件触发，但DDL 触发器触发的事件是数据定义语句（DDL）语句。这些语句主要是以create、alter、drop 等关键字开头的语句。 DDL 触发器的主要作用是执行管理操作，例如审核系统、控制数据库的操作等。在通常情况下，DDL 触发器主要用于以下操作需求：防止对数据库架构进行某些修改；希望数据库中发生某些变化以利于相应数据库架构中的更改；记录数据库架构中的更改或事件。 三、inserted 表和deleted 表,上一页,下一页,返回,项目十一 触发器的创建与使用,在触发器执行的时候，系统会产生两个临时表：inserted 表和deleted 表。 1. inserted 表 当向表中插入数据时，insert 触发器触发执行，插入到触发器表中的新行被插入到inserted表中。 2. deleted 表 当删除表中的记录时，delete 触发器触发执行，从触发器表中删除的行被插入到deleted表中。,上一页,下一页,返回,项目十一 触发器的创建与使用,inserted 表和deleted 表都是临时表，它们在触发器执行时被创建，待触发器执行完后消失，所以，只可以在触发器的语句中使用select 语句查询这两个表，不能直接对inserted 表和deleted 表中的数据进行更改。 修改一条记录等于插入一条新记录，同时删除旧记录，故当更新表中的记录时，update触发器触发执行，先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到deleted 表中，插入的新行被插入到inserted 表中。 任务 触发器的创建与使用,上一页,下一页,返回,项目十一 触发器的创建与使用,【知识准备】 （1） inserted 表和deleted 表。 详见【相关知识和技能】。 （2） 使用create trigger 命令创建DML 触发器的语法形式如下： create trigger 触发器名称 on 表|视图 for |after | instead of insert,update,delete,上一页,下一页,返回,项目十一 触发器的创建与使用,as 触发器条件;.n 其中各参数含义如下： after 说明触发器在指定的所有操作都已成功执行后才触发。如果仅指定for 关键字，则after 是默认设置。不能在视图上定义after 触发器。 instead of 指定执行触发器而不是执行触发SQL 语句，从而替代触发语句的操作。 delete,insert,update指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。,上一页,下一页,返回,项目十一 触发器的创建与使用, as 是触发器要执行的操作。 （3） 使用create trigger 命令创建DDL 触发器的语法形式如下： create trigger 触发器名称 on 当前服务器 | 当前数据库 for | after 事件名称|事件组名称 ,.n as 触发器条件;.n 其中各参数含义如下：,上一页,下一页,返回,项目十一 触发器的创建与使用, 事件名称|事件组名称：TSQL 语言事件的名称或事件组的名称，事件执行后，将触发此DDL 触发器。其中，事件名称有：CREATE_TABLE、ALTER_TABLE、DROP_TABLE、CREATE_USER 、CREATE_VIEW 等；事件组名称有：CREATE_DATABASE 、ALTER_DATABASE 等。 as 是触发器要执行的操作。 （4） 使用drop trigger 语句删除触发器 触发器本身是存在于表中的，因此，当表被删除时，表中的触发器也将一起被删除。删除触发器使用drop trigger 语句，语法格式如下：,上一页,下一页,返回,项目十一 触发器的创建与使用,drop trigger 触发器名称,.n;/*删除DML 触发器*/ drop trigger 触发器名称,.non 当前数据库 | 当前服务器; /*删除DDL 触发器*/ 【任务实施】 （1） 创建insert 触发器，每次向表中插入一行数据就会激活该触发器，从而执行触发器中的操作。 （2） 创建update 触发器。update 触发器在对触发器执行update 语句后触发。,上一页,下一页,返回,项目十一 触发器的创建与使用,如修改表tb_student 中的学号时，同时也要将表tb_score 中的学号修改成相应的学号（假设表tb_student和表tb_score 之间没有定义外键约束）。,上一页,下一页,返回,项目十一