sqlserver2005数据库应用与开发教学课件作者李新德7

第七章 存储过程和触发器,2,4,项目十 存储过程的创建与使用,任务 存储过程的创建与使用,项目十一 触发器的创建与使用,任务 触发器的创建与使用,返回,项目十 存储过程的创建与使用,【项目需求】 在SQL Server 2005 中针对三种不同情况创建相应的用户存储过程，并学会存储过程的使用。 【完成项目的条件】 （1）掌握数据库管理系统SQL Server 2005 处于运行状态，用户数据库STUDY 完好。 （2）掌握数据库系统中“对象资源管理器”的使用方法。 （3）掌握T-SQL语言编程知识。 （4）掌握常见的用户存储过程的创建和删除方法。 【方案设计】,下一页,返回,项目十 存储过程的创建与使用,存储过程就是数据库语言中的函数。首先，要具备T-SQL语言编程的基础。其次，需要掌握如何声明存储过程。最后，需要掌握如何执行存储过程。 作为数据库语言中的函数，可以包含或不包含变量。对于有变量的存储过程，还需要在存储过程中定义变量。 本项目将介绍存储过程的创建、执行和删除的方法。 【相关知识和技能】 一、存储过程的概念,上一页,下一页,返回,项目十 存储过程的创建与使用,存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输出参数。存储过程是数据库中的一个重要对象，一个设计良好的数据库应用程序常常都用到存储过程。 二、存储过程的优点 （1）存储过程运行的速度比较快。在服务器中运行，比SQL 语句运行速度开210 倍。 （2）存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值，可以向程序返回错误原因。,上一页,下一页,返回,项目十 存储过程的创建与使用,（3）使用存储过程可以完成所有的数据库操作，并通过编程方式控制对数据库信息访问的权限，确保数据库的安全。 （4）存储过程主要是在服务器中运行时，减少了对客户机的压力。 （5）可以在单个存储过程中执行一系列SQL 语句，也可以自动完成一些需要预先执行的任务。 （6）增加网络流量，降低网络负担。如果使用单条调用语句的方式，就必输大量的SQL 语句。 三、存储过程的类型 （1）系统存储过程：可以作为命令执行，系统存储过程定义在系统数据库master 中，以“_sp”为前缀。,上一页,下一页,返回,项目十 存储过程的创建与使用,（2）扩展存储过程：以“xp_”开头，在SQL Server 2005环境之外执行的动态链接库DLL。但因为扩展存储过程命令不易编写，而且可能会引发安全性问题，所以本书不详细扩展存储过程。 （3）用户存储过程：可以通过T-SQL语言编写，也可以通过CLR 方式编写。 T-SQL 存储过程是指保存的T-SQL 语句集合，可以接受和返回用户提供的参数。例如，存储过程中可能包含根据客户端应用程序提供的信息，在一个或多个表中插入新行所需的语句。存储过程也可能从数据库向客户端应用程序返回数据。 CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。它们在 .NET Framework 程序集中是作为类的公共静态方法实现的。,上一页,返回,任务 存储过程的创建与使用,任务目标 （1）了解存储过程的概念以及存储过程的类型。 （2）熟练掌握设计存储过程的方法。 （3）掌握用T-SQL命令创建、执行和删除存储过程的方法。 任务分析 在SQL Server 中，可以使用两种方法创建存储过程：一个是使用创建存储过程模板来创建存储过程；另一个是利用T-SQL创建存储过程。 知识准备 1. T-SQL 命令创建存储过程 语法形式如下： create proc 新存储过程的名称 ; number ,下一页,返回,任务 存储过程的创建与使用,指定存储过程名 as 执行的操作 n 其中各参数含义如下： （1）新存储过程名：用于指定存储过程名，必须符合标识符规则，并且对于数据库及所在架构必须唯一。这个名称应当尽量避免与系统内置函数名称相同，否则会发生错误，也应当尽量避免使用“sp_”作为前缀。 （2）执行的操作：过程中要包含的任意数目和类型的Transact-SQL 语句，存储过程体中可以包含一条或多条T-SQL 语句，除了DCL、DML、DDL 命令外，还能包含过程式语句，如变量的定义与赋值、流程控制语句等。 2. T-SQL 命令执行存储过程,上一页,下一页,返回,任务 存储过程的创建与使用,语法形式如下： exec 存储过程名 3. T-SQL 命令删除存储过程 语法形式如下： 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的创建。如图7-1所示。 执行存储过程的程序代码为：,上一页,下一页,返回,任务 存储过程的创建与使用,exec exp1 输入上述代码并用鼠标选中，再点击“执行”，结果如图7-2 所示。 2. 带参数存储过程 从STUDY 数据库的三个表中查询某人指定课程的成绩和学分。创建存储过程exp2的代 码如下： use STUDY go create procedure exp2 name char(8), coursename char(16) as select a.studentid, name, coursename, score, t.credit,上一页,下一页,返回,任务 存储过程的创建与使用,from tb_student a inner join tb_score b on a.studentid = b.studentid inner join tb_course t on b.courseid= t.courseid where a.name=name and t.coursename=coursename GO 将上述代码输入到“新建查询”中，点击“执行”，完成存储过程exp2 的创建。如图7-3所示。 执行存储过程的代码为： execute exp2 '张林', '计算机基础' 输入上述代码并用鼠标选中，再点击“执行”，结果如图7-4 所示。,上一页,下一页,返回,任务 存储过程的创建与使用,3. 创建模式匹配参数的存储过程 从STUDY 三个表的连接中返回指定姓李的学生的学号、姓名、所选课程名称及该课程的成绩。 创建存储过程的代码如下： use STUDY go create procedure exp3 name varchar(30) = '李%' as select a.studentid, a.name, c.coursename, b.score from tb_student a inner join tb_score b,上一页,下一页,返回,任务 存储过程的创建与使用,on a.studentid =b.studentid inner join tb_course c on c.courseid= b.courseid where name like name go 将上述代码输入到“新建查询”中，点击“执行”，完成存储过程exp3 的创建。如图7-5所示。 执行存储过程的代码为： exec exp3 输入上述代码并用鼠标选中，再点击“执行”，结果如图7-6 所示。 4. 删除存储过程 将存储过程exp1 从数据库中删除，语句如下：,上一页,下一页,返回,任务 存储过程的创建与使用,drop procedure exp1 输入上述代码并用鼠标选中，再点击“执行”，就可以删除存储过程，也可以在“资源管理器”中用鼠标操作来删除，如图7-7 所示。 注意：（1）不要使用“sp_”前缀，“sp_”前缀是为系统存储过程保留的。 （2）尽量少用可选参数，执行额外的工作会影响系统性能。 任务总结 本任务实践了存储过程的创建、执行和删除的过程。对于存储过程的创建，本任务分别尝试了无参数、带参数和参数模式匹配三个存储过程的实例，从中可体会到存储过程的应用潜力非同一般。,上一页,下一页,返回,任务 存储过程的创建与使用,存储过程技术是数据库程序设计中较高级的技术，具有功能强大、执行效率高、安全性好等优点，有兴趣的同学可以进一步深入去了解。 【项目总结】 本项目介绍了SQL Server 2005 的存储过程，这是个重要的数据库对象。使用存储过程，可以将T-SQL语句和控制流语句预编译并保存到服务器端，不仅提高了访问数据的速度和效率，还提供了良好的安全机制。,上一页,返回,项目十一 触发器的创建与使用,【项目需求】 在数据库管理系统SQL Server 2005中创建三种DML触发器，并学会使用和删除的方法。 【完成项目的条件】 （1）数据库管理系统SQL Server 2005 处于运行状态，用户数据库STUDY完好。 （2）掌握数据库系统中“对象资源管理器”的使用方法。 （3）掌握T-SQL语言编程知识。 （4）掌握创建和删除触发器的语法。 【方案设计】,下一页,返回,项目十一 触发器的创建与使用,先介绍触发器的概念、类型、用处以及创建语法，再以“学生成绩管理系统”中STUDY数据库为例子，详细说明如何用T-SQL 命令创建三种最常见的DML 触发器，通过实例说明创建触发器时参数的意义等。本项目还简单介绍了SQL Server 2005 新增的DDL 触发器及其使用方法。 【相关知识和技能】 一、触发器的概念 触发器是一个关联到表的数据对象。它不需要被调用，当针对一个表的特殊事件出现时，它就会被触发。 触发器是一种特殊类型的存储过程，也是由SQL 语句组成，因此用在存储过程中的语句也可以用在触发器的定义中。触发器与表的关系密切，用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器将自动执行。,上一页,下一页,返回,项目十一 触发器的创建与使用,触发器由“触”和“发”两个动作组成。当对一张表进行insert、update 或delete 等操作时（此为“触”），SQL Server 2005 就会自动执行触发器所定义的SQL 语句（此为“发”）。 二、触发器的类型 在SQL Server 2005 中，按照不同的触发事件可将触发器分为以下两大类。 1. DML 触发器 DML 触发器在数据库中发生数据操作语言（DML）事件时将启用。DML 事件包括在指定表或视图中修改数据的 insert 语句、update 语句和 delete 语句。因而，DML 触发器还可分为三种类型：insert 触发器、update 触发器 和 delete 触发器。,上一页,下一页,返回,项目十一 触发器的创建与使用,利用DML 触发器可以方便地保持数据库中的数据完整性。例如，对于数据库STUDY的三个表tb_student、tb_score 和tb_course，当需要插入某学号的课程成绩时，该学号应该是tb_student 表中存在的，课程号应该是tb_course 表中存在的，此时，可通过定义insert 触发器实现上述功能。再如，当需要删除tb_student 表中某学号的记录时，可以在tb_student 表上定义delete 触发器，该触发器同时删除成绩表tb_score 中所有该学生的记录。 2. DDL 触发器 DDL 触发器