[IT认证]第9章SQL Server数据库应用开发技术.ppt

第9章 用户定义函数、存储过程与触发器,知识技能目标:1理解用户定义函数、存储过程、触发器的概念 和用途2掌握自定义函数的创建、使用、修改和删除3掌握存储过程的创建、使用、修改和删除4掌握触发器的创建、使用、修改和删除,引导案例：在教务管理系统中，有些数据操作经常发生。例如，经常需要根据姓名查询一个学生的所有选修课的信息；学生表中的学生信息一有增减，就需要自动修改班级表中的班级人数。在高级语言中，需要重复执行的代码可以设计为函数或者过程。同样，在数据库系统中，可以通过用户定义函数、存储过程、触发器等技术来实现上述功能。本章介绍用户定义函数、存储过程、触发器等技术。,第9章 用户定义函数、存储过程与触发器,9.1 用户定义函数 9.2 存储过程 9.3 触发器 小结,第9章 用户定义函数、存储过程与触发器,9.1 用户定义函数,9.1.1 用户定义函数的概念及分类 在SQL Server 中，除了系统内置的函数外，用户可以在数据库中自己定义函数。用户自定义函数是由一个或多个T_SQL语句组成的子程序，它可以接受参数、执行操作，并将操作结果以值的形式返回。,返回目录,SQL Server的用户定义函数分为两类：标量值函数、表值函数。表值函数又分为内嵌表值函数和多语句表值函数。,9.1 用户定义函数,在SQL Server中，除了系统内置的函数外，用户可以在数据库中自己定义函数。用户定义函数是由一个或多个T_SQL语句组成的子程序，它可以接受参数、执行操作，并将操作结果以值的形式返回。SQL Server的用户定义函数分为两类：标量值函数和表值函数。表值函数又分为内嵌表值函数和多语句表值函数。这里只介绍标量值函数和内嵌表值函数。,9.1.1 用户定义函数的概念及分类,返回目录,9.1 用户定义函数,在SQL Server 中使用用户定义函数有以下优点：（1）允许模块化程序设计 函数存储在数据库中，只需创建一次，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。（2）执行速度更快 T-SQL 用户定义函数通过缓存计划并在重复执行时重用它来降低 T-SQL 代码的编译开销。因此每次使用用户定义函数时均无须重新解析和重新优化，从而缩短了执行时间。（3）减少网络流量 某些约束比较复杂，无法用单一标量的表达式表示，此时可以表示为函数，在 WHERE 子句中调用，以减少发送至客户端的数据量。,9.1.1 用户定义函数的概念及分类,返回目录,9.1 用户定义函数,9.1.2 创建和使用标量值用户定义函数,标量值用户定义函数返回单个数据值，即标量值。返回类型可以是除 text、ntext、image、cursor 和 timestamp 外的任何数据类型。,返回目录,9.1 用户定义函数,9.1.2 创建和使用标量值用户定义函数,1创建标量值用户定义函数（1）用T_SQL语句创建语法格式：CREATE FUNCTION 函数名(参数名 AS 参数类型，n)RETURNS 返回值类型 BEGIN 函数体 RETURN 标量值表达式 END【例9.1】统计某专业男女生同学的比例。,返回目录,9.1 用户定义函数,9.1.2 创建和使用标量值用户定义函数,1创建标量值用户定义函数（2）用SQL Server Management Studio创建1）打开SQL Server Management Studio；2）在对象资源管理器中展开要建创建用户定义函数的数据库；3）依次展开数据库下“可编程性”、“函数”、“标量值函数”；4）在“标量值函数”结点上单击右键，选择“新建标量值函数”；5)在随后打开的通用模板中已经给出了创建标量值函数所需的语句的基本格式。修改其中的语句为需要的语句；6)单击“分析”按钮，检查语法是否正确；7)单击“执行”按钮，执行代码。,返回目录,9.1 用户定义函数,9.1.2 创建和使用标量值用户定义函数,2标量值用户自定义函数的使用 可以在使用标量表达式的位置调用标量值函数，包括计算列和 CHECK 约束定义中。【例9.2】查询“计算机应用技术”专业男女生的比例。,返回目录,9.1 用户定义函数,9.1.3 创建和使用表值用户定义函数,内嵌表值用户定义函数返回一个SELECT 语句查询的结果集，是table数据类型。,1创建内嵌表值函数（1）用T_SQL语句创建语法格式：CREATE FUNCTION 函数名(参数名 AS 参数类型，n)RETURNS TABLE RETURN 查询语句,【例9.3】创建用户定义函数，其功能是可用于查看某班某课程的成绩单。,返回目录,9.1 用户定义函数,9.1.3 创建和使用表值用户定义函数,1创建内嵌表值函数（2）用SQL Server Management Studio创建 1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建用户定义函数的数据库，如“教务管理数据库”。3）依次展开数据库下“可编程性”、“函数”、“表值函数”节点。4）在“表值函数”结点上单击右键，选择“新建内联表值函 数”，将打开一个通用模板。5）模板已经给出了创建标量值函数所需的语句的基本格式。修 改其中的语句为需要的语句。6）单击“分析”按钮，检查语法是否正确。7）单击“执行”按钮，完成。,返回目录,9.1 用户定义函数,9.1.3 创建和使用表值用户定义函数,2调用内嵌表值函数 内嵌表值函数在 SELECT、INSERT、UPDATE 或 DELETE 语句的FROM 子句中调用。在调用函数的时候要指明函数的拥有者。【例9.4】查“计算机应用班071班”的“高等数学”课程的成绩单。,返回目录,9.1 用户定义函数,9.1.4 查看用户定义函数,1用T-SQL语句查看用户定义函数 可以通过使用一些系统函数和系统存储过程来查看用户定义函数。（1）查看函数的定义查看函数的定义可使用OBJECT_DEFINITION 和sp_helptext。【例9.5】查看用户定义函数“某班某课成绩单”的定义【例9.6】查看用户定义函数“某班某课成绩单”的定义（2）获得有关函数的信息获得有关函数的信息（例如函数的创建时间、参数等）可使用sp_help。【例9.7】查看用户定义函数“某班某课成绩单”的信息,返回目录,9.1 用户定义函数,9.1.4 查看用户定义函数,1用T-SQL语句查看用户定义函数（3）查看用户定义函数的依赖关系查看用户定义函数的依赖关系可使用sp_depends【例9.8】查看用户定义函数“某班某课成绩单”的依赖关系,返回目录,9.1 用户定义函数,9.1.4 查看用户定义函数,2用SQL Server Management Studio查看用户定义函数 1）打开SQL Server Management Studio。2）在对象资源管理器中展开要查看的数据库，如“教务管理数据库”。3）依次展开数据库下“可编程性”、“函数”节点。4）在“函数”节点下，展开“表值函数”或“标量值函数”，找到需要查看信息的函数，并在函数名上单击右键，选择“查看依赖关系”，查看与函数有依赖关系的对象的信息；选择“编写函数脚本为”-“create到”-“新建查询编辑窗口”查看函数源代码。,返回目录,9.1 用户定义函数,9.1.5 修改用户定义函数,1用T_SQL语句修改用户定义函数 使用ALTER FUNCTION语句,与CREATE FUNCTION语句类似。,【例9.9】修改“某专业男女比例”函数，将比值的小数位设置为1位。,返回目录,9.1 用户定义函数,9.1.5 修改用户定义函数,2.用SQL Server Management Studio修改自定义函数 用SQL Server Management Studio修改自定义函数的方法和查看函数信息的方法类似，只需在第4步中函数名上单击右键时，在快捷菜单中选择“修改”即可打开函数脚本进行修改，最后选择“执行”按钮。,返回目录,9.1 用户定义函数,9.1.6 删除用户定义函数,1用T_SQL语句删除用户定义函数 语法格式：DROP FUNCTION 函数名,.n【例9.10】将自定义函数“某专业男女比例”删除。,2用SQL Server Management Studio删除自定义函数 用SQL Server Management Studio删除自定义函数的方法和查看函数信息的方法类似，只需在第4步中函数名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删除对象”窗口中选择“确定”按钮。,返回目录,9.2 存储过程,9.2.1 存储过程的概念,存储过程是存储在SQL Server服务器中的一组预编译过的T-SQL语句组成的模块，用来完成某项特定的功能。存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。存储过程分为用户定义的存储过程和系统存储过程。系统存储过程由系统自带并存储在master数据库中，带有 sp_ 前缀，在任何数据库中都可以调用。系统存储过程功能强大，种类繁多，几乎可以满足各类数据库管理需求。用户定义的存储过程是由用户创建的，必须在所属的数据库中才能被执行。,返回目录,9.2 存储过程,9.2.2 存储过程的优点,（1）执行速度快存储过程是预先编译好放在数据库内的，减少编译语句所花费的时间。（2）提高系统安全性通过为用户授权，使用户必须通过执行存储过程来访问数据，而不能直接访问存储过程中引用的对象，从而提高了数据安全性。（3）程序设计模块化存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性。XI（4）减少网络通信流量一个需要数百行 T-SQL 代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。,返回目录,9.2 存储过程,9.2.3 创建存储过程,1用T_SQL语句创建存储过程语法格式：CREATE PROCEDURE 存储过程名 参数 数据类型 OUTPUT,.n AS BEGIN SQL语句.n END说明：存储过程可以带参数，也可以不带参数，参数可以是输入参数，也可以是输出参数，输出参数用OUTPUT指明。,返回目录,9.2 存储过程,9.2.3 创建存储过程,1用T_SQL语句创建存储过程【例9.11】创建“学生名单”存储过程，其功能是输出所有学生的名单【例9.12】创建“某班学生名单”存储过程，其功能是查看某个班级的学生名单，结果按学号排序。【例9.13】创建“查询学生”存储过程，其功能是根据学号查询学生的姓名和年龄。,返回目录,9.2 存储过程,9.2.3 创建存储过程,2用SQL Server Management Studio创建存储过程 1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建存储过程的数据库，如“教务管理数据库”。3）依次展开数据库下“可编程性”-“存储过程”。4）在“存储过程”节点单击右键，选择“新建存储过程”，将会自动打开一个模板。5）模板已经给出了创建存储过程所需的语句的基本格式。修改其中的语句为需要的语句。6）单击“分析”按钮，检查语法是否正确。7）单击“执行”按钮，完成。,返回目录,9.2 存储过程,9.2.4 执行存储过程,语法格式：EXECUTE 存储过程名 参数=值|变量名 OUTPUT,.n 说明：如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用这个存储过程，但如果对存储过程的调用不是批处理的第一条语句，则不能直接使用存储过程的名字调用这个存储过程，要用EXEC(UTE)关键字。存储过程如果有参数，调用的时候需要传递参数，如果是输出参数，还需要加OUTPUT关键字。,返回目录,9.2 存储过程,9.2.4 执行存储过程,【例9.14】查看学校学生名单。【例9.15】查看计算机应用071班学生名单。【例9.16】查看学号为“20070101101”的学生的姓名和年龄。,返回目录,9.2 存储过程,9.2.5 查看存储过程,1用T-SQL语句查看存储过程 通过使用系统存储过程来查看。（1）使用sp_helptext查看存储过程的定义 例如：EXEC sp_helptext dbo.某班学生名单（2）使用sp_help查看有关存储过程的信息 例如：EXEC sp_help dbo.某班学生名单（3）使用sp_depends查看存储过程的依赖关系 例如：EXEC sp_depends dbo.某班学生名单,返回目录,9.2 存储过程,9.2.5 查看存储过程,2.用SQL Server Management Studio查看存储过程 1）打开SQL Server Management Studio。2）在对象资源管理器中展开要查看的数据库，如“教务管理数 据库”。3）依次展开数据库下“可编程性”-“存储过程”。4）在要查看信息的存储过程的名字上单击右键，选择“查看依 赖关系”，查看与存储过程有依赖关系的对象的信息；选择“编写存储过程脚本为”-“create到”-“新建查询编辑窗口”查看存储过程源代码。,返回目录,9.2 存储过程,9.2.6 修改存储过程,1用T_SQL语句修改存储过程 语法格式：ALTER PROCEDURE 存储过程名 参数 数据类型 OUTPUT,.n AS BEGIN SQL语句.n END【例9.17】修改“学生名单”存储过程，结果按学号排序。,返回目录,9.2 存储过程,9.2.6 修改存储过程,2.用SQL Server Management Studio修改存储过程 用SQL Server Management Studio修改存储过程的方法和查看存储过程信息的方法类似，只需在第4步中存储过程名上单击右键时，在快捷菜单中选择“修改”即可打开存储过程脚本进行修改，最后选择“执行”按钮。,返回目录,9.2 存储过程,9.2.7 删除存储过程,1用T_SQL语句删除存储过程语法格式：DROP PROCEDURE 存储过程名,.n【例9.18】将存储过程“学生名单”删除。2用SQL Server Management Studio删除存储过程 用SQL Server Management Studio删除存储过程的方法和查看存储过程信息的方法类似，只需在第4步中存储过程名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删除对象”窗口中选择“确定”按钮。,返回目录,9.3 触发器,9.3.1 触发器的概念及分类,触发器是一种特殊的存储过程，它不带任何的输入输出参数，不能被显式调用。触发器与特定的SQL Server对象（如表）相关联，在这些对象的指定操作发生时，服务器自动执行该触发器。,SQL Server2005 包括两大类触发器：1)DDL 触发器。当服务器或数据库中发生数据定义语言(DDL)事件（CREATE、ALTER、DROP）时将调用DDL触发器。利用DDL触发器可以限制数据库中未经许可的更新和变化。2)DML 触发器。当数据库中发生数据操作语言(DML)事件，即在指定表或视图中发生INSERT、UPDATE或 DELETE操作时，将调用 DML 触发器。DML 触发器经常用于强制执行业务规则和数据完整性。这里只介绍DML触发器。,返回目录,9.3 触发器,9.3.1 触发器的概念及分类,DML触发器建立在表或者视图上根据触发器被执行的时机，分为AFTER触发器和INSTEAD OF触发器。A.AFTER 指定触发器仅在触发操作（INSETRT、UPDATE或DELETE操作）及检查约束都已成功执行后才被激发；一个表可以定义多个AFTER类型的触发器，但不能对视图定义AFTER触发器。B.INSTEAD OF 指定触发器是“代替”引起触发器执行的语句（INSETRT、UPDATE或DELETE操作）的。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。根据DML触发器所指定的数据修改语句，可以分为INSETRT触发器、UPDATE触发器、DELETE触发器，它们分别由INSETRT语句、UPDATE语句、DELETE语句触发。,返回目录,9.3 触发器,9.3.1 触发器的概念及分类,DML 触发器在以下方面非常有用：级联操作：DML 触发器可通过数据库中的相关表实现级联更改。强化约束：DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK约束更强的限制，因为DML 触发器可以引用其他表中的列。审核变化：DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。多响应：一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取不同的操作来响应同一个修改语句。,返回目录,9.3 触发器,9.3.2 创建触发器,1用T_SQL语句创建触发器语法格式：CREATE TRIGGER 触发器名 ON 表名|视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS SQL_语句.n,返回目录,9.3 触发器,9.3.2 创建触发器,1用T_SQL语句创建触发器说明：1）可在触发器的主体中使用 UPDATE()测试对某个列的修改。2）在触发器中有两个临时表inserted、deleted可以访问。deleted表用于存储被DELETE 或UPDATE 语句删除或更新前的行的副本；inserted表用于存储被INSERT或UPDATE语句插入或更新后的行的副本。即旧行被复制到deleted表中，新行被复制到inserted表中。,返回目录,9.3 触发器,9.3.2 创建触发器,1用T_SQL语句创建触发器【例9.19】对学生表创建插入触发器，当有一条新的记录插入表中，就更新班级表中相应班级的人数。触发器建立后，执行以下语句，并观察学生表、班级表的变化：INSERT INTO 学生表(学号,姓名,班级)VALUES(20070101150,张三,jy071)【例9.20】对学生表创建删除触发器，当有一条记录被删除，就减少班级表中相应班级的人数。触发器建立后，执行以下语句，并观察学生表、班级表的变化：DELETE 学生表 WHERE 学号=20070101150,返回目录,9.3 触发器,9.3.2 创建触发器,1用T_SQL语句创建触发器【例9.21】对学生表创建更新触发器，当有一条记录被更新，就修改班级表中相关班级的人数。触发器建立后，执行以下语句，并观察学生表、班级表的变化：UPDATE 学生表 SET 班级=jy081 WHERE 学号=20070101112,返回目录,9.3 触发器,9.3.2 创建触发器,2.用SQL Server Management Studio创建触发器1）打开SQL Server Management Studio。2）在对象资源管理器中展开要创建触发器的数据库，如“教务管理 数据库”。3）展开数据库下“表”节点。4）展开要创建触发器的表。5)在“触发器”项上，单击右键，选则“新建触发器”，将会自 动打开一个模板。6）模板已经给出了创建触发器所需的语句的基本格式。修改其中的 语句为需要的语句。7）单击“分析”按钮，检查语法是否正确。8）单击“执行”按钮，完成。,返回目录,9.3 触发器,9.3.3 查看触发器,1用T-SQL语句查看触发器（1）使用sp_helptext查看触发器的定义 例如：EXEC sp_helptext 更新学生（2）使用sp_help查看有关触发器的信息 例如：EXEC sp_help 更新学生（3）使用sp_depends查看触发器的依赖关系 例如：EXEC sp_depends 更新学生,返回目录,9.3 触发器,9.3.3 查看触发器,2用SQL Server Management Studio查看触发器 用SQL Server Management Studio查看触发器的方法和建立触发器类似，只需在第步，展开“触发器”后，在要查看的触发器名字上单击右键，选择“查看依赖关系”，查看与触发器有依赖关系的对象的信息；选择“编写触发器脚本为”-“create到”-“新建查询编辑窗口”查看函数源代码。,返回目录,9.3 触发器,9.3.4 修改触发器,1用T-SQL语句修改触发器 语法格式：ALTER TRIGGER 触发器名 ON 表名|视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS SQL_语句.n,2用SQL Server Management Studio修改触发器 用SQL Server Management Studio修改触发器的方法和查看触发器信息的方法类似，只需在第5步中触发器名上单击右键时，在快捷菜单中选择“修改”即可打开触发器脚本进行修改，最后选择“执行”按钮。,返回目录,9.3 触发器,9.3.5 删除触发器,1T_SQL语句删除触发器 语法格式：DROP TRIGGER 触发器名,.n【例9.22】将触发器“更新学生”删除。,2用SQL Server Management Studio删除触发器 用SQL Server Management Studio删除触发器的方法和查看触发器信息的方法类似，只需在第5步中触发器名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删除对象”窗口中选择“确定”按钮。,返回目录,9.3 触发器,9.3.5 使用触发器的注意事项,1）触发器只能在当前的数据库中创建。2）CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于 一个表。3）如果一个表的外键在定义时包含 DELETE/UPDATE 操作的级联，则 不能为表定义 INSTEAD OF DELETE/UPDATE 触发器。4）DELETE 触发器不能捕获 TRUNCATE TABLE 语句。5）在 DML 触发器中不允许使用下列 Transact-SQL 语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。6）如果一个表上同时有约束和触发器，约束优先检查，如果触发器 的操作与约束冲突，则触发器将不执行。7）SQL Server 不支持针对系统表的用户定义触发器，因此不要为系 统表创建用户定义触发器。,返回目录,小结,本章主要介绍了用户定义函数、存储过程与触发器。它们都是一组T_SQL语句的集合，可以完成一个独立的功能，可以被多次调用。用户定义函数是用来扩充内置函数的，具有返回值；存储过程可以由用户直接调用，减少网络流量，加快执行速度；触发器是一种特殊的存储过程，由某些活动触发而自动执行。其中存储过程和触发器功能强大，使用灵活，是设计和开发数据库应用程序必不可少的工具。,返回目录,