《数据库原理与应用SQL Server 2005项目教程-电子教案-郭庚麒 第九章》由会员分享，可在线阅读，更多相关《数据库原理与应用SQL Server 2005项目教程-电子教案-郭庚麒 第九章（35页珍藏版）》请在金锄头文库上搜索。
1、数据库原理与应用 SQL Server 2005项目教程,主编 郭庚麒 王槐彬,中国水利水电出版社,第九章 T-SQL语言的高级应用,知识要点,用户定义函数的基本概念及使用,存储过程的基本概念及使用,游标的基本概念及使用,事务的基本概念及使用,触发器的基本概念及使用,项目实景,在数据库的开发中，可能会遇到这样的一些需求： 编好的SQL查询代码可以被其他开发人员或者程序调用以提高开发效率。 出于安全考虑需要对用户隐藏表的细节，但又可以让用户操作数据。 客户端程序中冗长的SQL查询语句运行的时候占用带宽，效率低下，希望把这些代码转移到SQL Server服务器上去存储和执行，并且预先编译好以提高执
2、行效率。 SQL Server中提供的存储过程可以很好地解决上面的问题。存储过程可以把SQL 代码组织成为可以重复调用的代码块，在服务器端编译执行。 解决代码重用的另一个好方法是定义函数。使用用户定义函数开发程序，可以简化问题，提高效率。 在企业中，数据库开发人员可以使用用户定义函数、存储过程、游标、事务、触发器实现复杂的逻辑，进行数据操作或者实现数据的完整性。合理利用它们可以优化性能并增强安全性，更好地开发和维护应用程序。,项目目标,学习完本章后，读者应该能够完成以下任务：实现对数据库中用户定义函数、存储过程、游标、事务、触发器的创建和使用。,9.1 用户定义函数,有没有这样一种方法，它既能
3、封装一些复杂的T-SQL代码，并根据需要设置函数，同时又能返回程序所需的值呢？ 上面提问的答案是肯定的。函数是由一条或多条T-SQL语句组成的代码段，用于实现一些常用的功能。编写好的函数可以重复使用。在SQL Server中，除了可以使用系统内置函数外，还允许创建用户定义函数。 用户定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的例程。参数是零个或多个输入参数，但不支持输出参数，返回值可以是单个标量值或结果集。可以使用CREATE FUNCTION语句创建，使用ALTER FUNCTION 语句修改，以及使用DROP FUNCTION语句删除。 SQL Server支持
4、3种用户定义函数：标量函数、内嵌表值函数、多语句表值函数。,9.1.1 标量函数,标量函数根据输入参数值的不同来获得不同的函数值，在标量函数中可以使用多个输入参数，而函数的返回值是单个数据值。 标量函数的函数体可包括一条或多条T-SQL语句。这些T-SQL语句以BEGIN开始，以END结束；用RETURNS子句定义该函数返回值的数据类型，用RETURN语句返回该函数的值。,基本语法格式如下： CREATE FUNCTION 函数名称 ( 参数名称 标量数据类型 = 默认值 ,.n ) RETURNS 标量返回值类型 AS BEGIN 函数体 RETURN 标量表达式 END,各参数含义： 函数
5、名称 用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。 参数名称 用户定义函数的参数，使用符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。函数最多可以有1024个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。 标量数据类型 参数的数据类型。所有标量数据类型（包括 bigint 和 sql_variant）都可用作用户定义函数的参数。 标量返回值类型 是标量用户定义函数的返回值。标量返回值类型可以是 SQL Server 支持的任何标量数据类型（text、ntext、image、cursor 和 ti
6、mestamp 除外）。 标量表达式 指定标量函数返回的标量值。,9.1.1 标量函数,9.1.1 标量函数,【例9-1】下面的SQL语句在数据库中创建了一个CubicVolume的用户定义函数，然后使用该函数计算一个长方体的体积。 CREATE FUNCTION fn_CubicVolume (CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1) RETURNS decimal(12,3) AS BEGIN RETURN (CubeLength * CubeWidth * CubeHeight) END
7、GO PRINT 长、宽、高分别为6、4、3的立方体的体积 =+STR（dbo.fn_CubicVolume(6,4,3) GO,9.1.1 标量函数,执行结果是： 长、宽、高分别是6、4、3的立方体的体积 = 72.000 对于返回标量值的标量函数，可以在T-SQL语句中允许相同数据类型的标量表达式中进行调用。调用时至少应使用函数的两部分名称，即使是所有者本人也必须如此。其语法格式如下： 所有者名.函数名称(参数表达式,.n) 当函数的参数有默认值时，可以在调用函数时使用default关键字获得默认值。,9.1.2 内嵌表值函数,内嵌表值函数及多语句表值函数，返回的都是一个表（table），
8、而不是一个标量数据。在内嵌表值函数中，通过单个SELECT语句定义TABLE返回值，内嵌表值函数没有相关联的返回变量。对于内嵌表值函数，没有函数主体；表是单个 SELECT 语句的结果集。,基本语法格式如下： CREATE FUNCTION 函数名称 ( 参数名称 数据类型 = 默认值 ,.n ) RETURNS TABLE AS RETURN (select语句) 其中各参数的含义与标量函数的参数含义类似。RETURNS TABLE子句表明函数的返回值是一个表，select语句是定义内嵌表值函数返回值的单个SELECT语句。,9.1.2 内嵌表值函数,使用内嵌表值函数可以提供参数化的视图功能
9、。比如，创建一个仅包含T001组的运动员视图，使用的代码如下： USE sportsmeet GO CREATE VIEW ath_T001 AS SELECT * FROM athlete WHERE a_teamid=T001 使用这个视图只能查看T001组的运动员信息，因为在视图的查询语句中不允许有变量。如果需要查看其他组的运动员信息，则还需要创建其他视图。,【例9-2】创建查看某一组运动员信息的函数。 USE sportsmeet GO CREATE FUNCTION Team (teamid char(4) RETURNS TABLE AS RETURN ( SELECT * FRO
10、M athlete WHERE a_teamid=teamid ),9.1.2 内嵌表值函数,在调用函数时，由于内嵌表值函数返回用户的唯一信息是由该函数返回的table，因此返回表值函数可用在T-SQL查询中允许有表或视图表达式的地方。可以使用以下语句查看T001组的运动员信息。 SELECT * FROM Team(T001) 该语句和以下查询ath_T001视图返回的结果一样。 SELECT * FROM ath_T001 但使用函数还可以查看其他组的运动员信息，这是视图所不能做到的。例如，用以下语句查看T002组的运动员信息。 SELECT * FROM Team(T002),9.1.3
11、 多语句表值函数,多语句表值函数也是返回表的函数，内嵌表值函数返回的是单个SELECT语句的结果集。而多语句表值函数可以使用复杂逻辑和多条T-SQL语句来构建表，这些语句可生成行并将行插入到表中，最后返回表。,基本语法格式如下： CREATE FUNCTION 函数名 ( 参数名称 数据类型 = 默认值 ,.n ) RETURNS 表返回变量 TABLE(表的结构定义) AS BEGIN 函数体 RETURN END 其中，各参数的含义与标量函数的参数含义类似。RETURNS语句指定TABLE作为返回的数据类型，并定义表的名称和格式。在多语句表值函数中，函数体是一系列填充表返回变量的 Tran
12、sact-SQL语句。,9.1.3 多语句表值函数,【例9-3】在sportsmeet数据库中创建一个函数，该函数可返回某组运动员的信息。 CREATE FUNCTION t_team(teamid char(4) RETURNS t TABLE -返回表t，下句是定义其表结构 (a_athleteid char(6),a_name char(10) AS BEGIN INSERT t -向t中插入满足条件的记录 SELECT a_athleteid, a_name FROM athlete WHERE a_teamid=teamid RETURN END 可以在FROM子句中调用多语句表值函
13、数。可以使用以下语句查看T001组运动员信息。 SELECT * FROM t_team(T001),9.1.4 修改及删除用户定义函数,ALTER FUNCTION是修改用户定义函数的语句。其语法与CREATE FUNCTION的语法类似。当创建函数时使用加密子句，则必须使用此种方法进行修改。 DROP FUNCTION是删除用户定义函数的语句，使用该语句可以从当前的数据库中删除一个或多个用户定义函数，多个函数之间用逗号分隔。 例如，删除t_team函数可写成： DROP FUNCTION t_team,9.2 存储过程,在开发SQL Server应用程序过程中，T-SQL语句是应用程序与S
14、QL Server数据库之间使用的主要编程接口。应用程序与SQL Server数据库交互执行某些操作有两种方法：一种是存储在本地的应用程序中一系列操作命令，应用程序向SQL Server发送每一个命令，并对返回的数据进行处理；另一种是在SQL Server中定义某个过程，其中记录了一系列的操作，每次应用程序只需调用该过程就可完成该操作。这种在SQL Server数据库中定义的过程被称为存储过程。 存储过程是一组可编译为单个执行计划的T-SQL语句集合。SQL Server中的存储过程类似于编程语言中的过程和函数，可以使用T-SQL语句EXECUTE（或EXEC）来执行存储过程。,9.2.1 存
15、储过程概述,存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过调用执行，调用时可指定相关参数。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值，可以：,包含可以在数据库中执行操作的语句，包括调用其他存储过程等。,接受输入参数。,将状态值返回给发起调用的存储过程或批处理以指示成功或失败。,以输出参数的形式将多个值返回到发起调用的存储过程或客户端应用程序。,9.2.1 存储过程概述,1存储过程类型 存储过程主要分为三类：系统存储过程、用户定义存储过程、扩展存储过程
16、。 （1）系统存储过程。在安装 SQL Server 2005时，系统创建了很多系统存储过程，它们存储在源数据库中。系统存储过程主要用于从系统表中获取信息，命名以“sp_”为前缀。 （2）扩展存储过程。是对动态链接库（DLL）函数的调用，在SQL Server 2005环境外执行，一般以“xp_”作为前缀。 （3）用户定义存储过程。是由用户为完成某一特定功能而编写的存储过程。用户定义存储过程存储在当前的数据库中，建议以“pr_”作为前缀，分为两种类型：Transact-SQL或CLR。 Transact-SQL Transact-SQL存储过程是指保存的 Transact-SQL 语句集合，可以接受和返回用户提供的参数。 CLR CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。它们在 .NET Fram
