大型数据库第3章transactsql语言.ppt

2023年10月11日,第1页,第3章使用Transact-SQL编程,本章概述 本章要点本章内容,2023年10月11日,第2页,本章概述,如何方便地在数据库中执行访问数据库对象、查询业务数据等各种操作呢？这需要掌握使用Transact-SQL语言的技术。Transact-SQL语言是微软公司在关系型数据库管理系统Microsoft SQL Server中的ISO SQL的实现。SQL(Structure Query Language，结构化查询语言)语言是国际标准化组织(International Standardize Organization，ISO)采纳的标准数据库语言。通过使用Transact-SQL语言，用户几乎可以完成SQL Server数据库中的各种操作。本章全面介绍Transact-SQL语言的特点和使用方式。,2023年10月11日,第3页,本章要点,Transact-SQL语言和SQL语言之间的关系Transact-SQL语言的特点和执行方式数据定义语言的类型和特点数据操纵语言的类型和特点数据控制语言的类型和特点事务管理语言的类型和特点附加语言元素的类型和特点,2023年10月11日,第4页,本章内容,第一节 Transact-SQL语言简介第二节 函数第三节 增强功能本章小结,第一节 Transact-SQL语言简介,1970年6月，埃德加考特(Edgar Frank Codd)在Communications of ACM 上发表了大型共享数据库数据的关系模型一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，即关系模型。1970 年以后，考特继续致力于完善与发展关系理论。1972 年，他提出了关系代数和关系演算的概念，定义了关系的并、交、投影、选择、连接等各种基本运算，为SQL语言的形成和发展奠定了理论基础。1979年，SQL在商业数据库中成功得到了应用。,2023年10月11日,第5页,标准版本,1986年，美国国家标准化组织正式发表了编号为X3.135-1986的SQL标准，并且在1987年得到了ISO组织的认可，被命名为ISO9075-1987。后来这个标准在1992、1999、2003、2006、2008年等不断地得到了扩充和完善。1992年发布的标准是SQL92，也称为SQL2。1999年发布的标准称为SQL:1999，也称为SQL3。该版本增加了迭代查询、触发器、控制流以及面向对象功能。2003年，SQL标准引入了XML支持、自动生成值等特征。2006年的标准在XML数据的存储和查询方面有了更多的增强。,2023年10月11日,第6页,Transact-SQL语言,Transact-SQL语言是微软公司在Microsoft SQL Server系统中使用的语言，是对SQL语言的一种扩展形式。Transact-SQL语言是一种交互式查询语言，具有功能强大、简单易学的特点。,2023年10月11日,第7页,特点,Transact-SQL语言有4个特点：一是一体化的特点，集数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加语言元素为一体。二是有两种使用方式，即交互使用方式和嵌入到高级语言中的使用方式。三是非过程化语言，只需要提出“干什么”，不需要指出“如何干”，语句的操作过程由系统自动完成。四是类似于人的思维习惯，容易理解和掌握。,2023年10月11日,第8页,类型,在Microsoft SQL Server 2008系统中，根据Transact-SQL语言的功能特点，可以把Transact-SQL语言分为5种类型，即数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。,2023年10月11日,第9页,Transact-SQL语言的执行方式,在Microsoft SQL Server 2008系统中，主要使用SQL Server Management Studio工具来执行Transact-SQL语言编写的查询语句。除此之外，还可以使用sqlcmd实用命令、PowerShell工具来执行Transact-SQL语句。下面主要介绍SQL Server Management Studio工具的特点。,2023年10月11日,第10页,在查询窗口中执行Transact-SQL语句,2023年10月11日,第11页,数据定义语言,数据定义语言用于创建数据库和数据库对象，为数据库操作提供对象。例如，数据库以及表、触发器、存储过程、视图、索引、函数、类型及用户等都是数据库中的对象，需要定义后才能使用。在DDL中，主要的Transact-SQL语句包括CREATE语句、ALTER语句和DROP语句。,2023年10月11日,第12页,CREATE语句示例,2023年10月11日,第13页,ALTER语句示例,2023年10月11日,第14页,DROP语句示例,2023年10月11日,第15页,数据操纵语言,数据操纵语言主要是用于操纵表、视图中数据的语句。当创建表对象之后，初始状态时该表是空的，没有任何数据。如何向表中添加数据呢？这时需要使用INSERT语句。如何检索表中数据呢？可以使用SELECT语句。如果表中数据不正确，可以使用UPDATE语句进行更新。当然，也可以使用DELETE语句删除表中的数据。实际上，DML语言包括了INSERT、SELECT、UPDATE和DELETE等语句。,2023年10月11日,第16页,附加的语言元素,除了前面介绍的语句之外，Transact-SQL语言还包括了附加的语言元素。这些附加的语言元素主要包括标识符、变量和常量、运算符、表达式、数据类型、函数、控制流语言、错误处理语言和注释等。下面，详细介绍这些内容。,2023年10月11日,第17页,标识符,在Transact-SQL语言中，数据库对象的名称就是其标识符。在Microsoft SQL Server系统中，所有的数据库对象都可以有标识符，例如服务器、数据库、表、视图、索引、触发器和约束等。大多数对象的标识符是必需的，例如，创建表时必须为表指定标识符。但是，也有一些对象的标识符是可选的，例如，创建约束时用户可以不提供标识符，其标识符由系统自动生成。,2023年10月11日,第18页,变量和常量,常量是表示特定数据值的符号，常量也被称为字面量。常量的格式取决于它所表示的值的数据类型。例如，This is a book.、May 1,2006、98321等都是常量。对于字符常量或时间日期型常量，需要使用单引号引起来。当字符串中有单引号时，则用两个单引号来表示。Unicode常量必须有一个大写的N来区别字符串常量。,2023年10月11日,第19页,变量和常量,在Microsoft SQL Server 2008系统中，变量也被称为局部变量，是可以保存单个特定类型数据值的对象。全局变量以开头命名，局部变量以开头命名。全局变量由系统提供，用于存储一些系统信息，只可以使用全局变量，不可以自定义全局变量。,2023年10月11日,第20页,声明和赋值,在Transact-SQL语言中，可以使用DECLARE语句声明变量。在声明变量时需要注意：第一，为变量指定名称，且名称的第一个字符必须是；第二，指定该变量的数据类型和长度；第三，默认情况下将该变量值设置为NULL。可以在一个DECLARE语句中声明多个变量，变量之间使用逗号分割开。变量的作用域是可以引用该变量的Transact-SQL语句的范围。变量的作用域从声明变量的地方开始到声明变量的批处理的结尾。,2023年10月11日,第21页,有两种为变量赋值的方式：使用SET语句为变量赋值和使用SELECT语句选择列表中当前所引用值来为变量赋值。使用select和print语句可以显示变量内容。,使用变量,2023年10月11日,第23页,例：查询计算机系学生人数，并输出。declare renshu intselect renshu=count(*)from student where sdept=CSprintCS人数：+cast(renshu as varchar(10),运算符,运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。在Microsoft SQL Server 2008系统中，可以使用的运算符可以分为算术运算符、逻辑运算符、赋值运算符、字符串串联运算符、按位运算符、一元运算符和比较运算符等。,2023年10月11日,第24页,使用赋值运算符,2023年10月11日,第25页,使用字符串串联运算符,2023年10月11日,第26页,使用位运算符,2023年10月11日,第27页,表达式,在Transact-SQL语言中，表达式是由标识符、变量、常量、标量函数、子查询以及运算符等的组合。在Microsoft SQL Server 2008系统中，表达式可以在多个不同的位置使用，这些位置包括查询中检索数据的一部分、搜索数据的条件等。表达式可以分为简单表达式和复杂表达式两种类型。简单表达式只是一个变量、常量、列名或标量函数，复杂表达式是由两个或多个简单表达式通过使用运算符连接起来的表达式。在复杂表达式中，两个或多个表达式有相同的数据类型，优先级低的数据类型可以隐式地转换为优先级高的数据类型。,2023年10月11日,第28页,注释,所有的程序设计语言都有注释。注释是程序代码中不执行的文本字符串，用于对代码进行说明或暂时仅用正在进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期以及算法描述等。在Microsoft SQL Server系统中，支持两种注释方式，即双连字符(-)注释方式和正斜杠星号字符对(/*/)注释方式。,2023年10月11日,第29页,使用注释,2023年10月11日,第30页,控制流语言,一般地，结构化程序设计语言的基本结构是顺序结构、条件分支结构和循环结构。顺序结构是一种自然结构，条件分支结构和循环结构都需要根据程序的执行状况对程序的执行顺序进行调整。在Transact-SQL语言中，用于控制语句流的语言被称为控制流语言。Microsoft SQL Server 2008系统提供了8种控制流语句。,2023年10月11日,第31页,1.Beginend2.Ifelse3.while4.break5.continue6.goto7.Return8.wairfor,Beginend,Ifelse,例：查询并显示MA系人数。begindeclare renshu intselect renshu=count(*)from student where sdept=MAif renshu=0 print MA系无人elseprintMA人数：+cast(renshu as varchar(10)end,while,语法：WHILE Boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE sql_statement|statement_block BREAK：导致从最内层的 WHILE 循环中退出CONTINUE：使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。,while,例：求1到10的累加和。declare i int,sum intset i=1set sum=0while(i=10)beginset sum=sum+iset i+=1endprint sum=+cast(sum as varchar(20),waitfor,用于延迟后续的代码执行，或等到指定的时间后再执行后续的代码。语法：WAITFOR DELAY time_to_pass|TIME time_to_execute(receive_statement)|get_conversation_group_statement),TIMEOUT timeoutDELAY：可以继续执行批处理、存储过程或事务之前必须经过的指定时段，最长可为24小时。time_to_pass：等待的时段。可以使用datetime数据可接受的格式之一指定time_to_pass，也可以将其指定为局部变量。不能指定日期；因此，不允许指定datetime值的日期部分。TIME：指定的运行批处理、存储过程或事务的时间。time_to_execute：WAITFOR 语句完成的时间。可以使用datetime数据可接受的格式之一指定time_to_execute，也可以将其指定为局部变量。不能指定日期；因此，不允许指定datetime值的日期部分。,例1：在晚上10:20(22:20)执行存储过程sp_update_job，将作业testJob改名为UpdatedJob。USE msdb;EXECUTE sp_add_job job_name=TestJob;BEGIN WAITFOR TIME 22:20;EXECUTE sp_update_job job_name=TestJob,new_name=UpdatedJob;END;GO,例2：在两小时的延迟后执行存储过程。BEGIN WAITFOR DELAY 02:00;EXECUTE sp_helpdb;END;GO,错误捕捉语言Trycatch,为了增强程序的健壮性，必须对程序中可能出现的错误进行及时的处理。在Transact-SQL语言中，可以使用两种方式处理发生的错误：使用TRYCATCH构造和使用ERROR函数。在Transact-SQL语句中，可以使用TRYCATCH构造来处理Transact-SQL代码中的错误。,2023年10月11日,第39页,错误捕捉语言Trycatch,TRYCATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块。如果在 TRY 块内的 Transact-SQL 语句中检测到错误条件，则控制将被传递到 CATCH 块（可在此块中处理此错误）。CATCH 块处理该异常错误后，控制将被传递到 END CATCH 语句后面的第一个 Transact-SQL 语句。如果 TRY 块中没有错误，控制将传递到关联的 END CATCH 语句后紧跟的语句。,TRY 块以 BEGIN TRY 语句开头，以 END TRY 语句结尾。CATCH 块必须紧跟 TRY 块。CATCH 块以 BEGIN CATCH 语句开头，以 END CATCH 语句结尾。TRY.CATCH 块不处理导致数据库引擎关闭连接的严重性为 20 或更高的错误。但是，只要连接不关闭，TRY.CATCH 就会处理严重性为 20 或更高的错误。严重性为 10 或更低的错误被视为警告或信息性消息，TRY.CATCH 块不处理此类错误。,TRY.CATCH 使用下列错误函数来捕获错误信息：ERROR_NUMBER()返回错误号。ERROR_MESSAGE()返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名或时间）提供的值。ERROR_SEVERITY()返回错误严重性。ERROR_STATE()返回错误状态号。ERROR_LINE()返回导致错误的例程中的行号。ERROR_PROCEDURE()返回出现错误的存储过程或触发器的名称。,例：使用TRYcatch处理被零除错误。BEGIN TRY SELECT 1/0;END TRYBEGIN CATCH SELECT ERROR_NUMBER()AS ErrorNumber,ERROR_SEVERITY()AS ErrorSeverity,ERROR_STATE()as ErrorState,ERROR_PROCEDURE()as ErrorProcedure,ERROR_LINE()as ErrorLine,ERROR_MESSAGE()as ErrorMessage;END CATCH;,例：执行插入语句，向student表中插入一条记录，出错时显示错误信息。BEGIN TRY insert into student values(201215124,刘秀,男,20,CS,2012)END TRYBEGIN CATCH SELECT ERROR_NUMBER()AS ErrorNumber,ERROR_SEVERITY()AS ErrorSeverity,ERROR_STATE()as ErrorState,ERROR_PROCEDURE()as ErrorProcedure,ERROR_LINE()as ErrorLine,ERROR_MESSAGE()as ErrorMessage;END CATCH;,使用ERROR函数,功能：返回执行的上一个 Transact-SQL 语句的错误号。如果前一个 Transact-SQL 语句执行没有错误，则返回 0。如果前一个语句遇到错误，则返回错误号。由于 ERROR 在每一条语句执行后被清除并且重置，因此应在语句验证后立即查看它，或将其保存到一个局部变量中以备以后查看。,例：插入数据到student，并显示错误号。insert into student values(201215125,刘音,女,20,CS,2012);print ERROR,七、数据库对象的引用方法,Server_name.database_name.schema_name.|database_name.schema_name.|schema_name.object_nameServer_name：链接服务器名称或远程服务器名称；Database_name：如果对象是驻留在SQL Server的本地实例中，则database_name是SQL Server数据库的名称。如果对象在链接服务器中，则database_name是OLE_DB目录。Schema_name：如果对象是驻留在SQL Server的本地实例中，则schema_name是包含对象的架构名称。如果对象在链接服务器中，则schema_name是OLE_DB架构名称。Object_name：对象名称。,说明：什么时候不能省略？不同的使用不能省略。比如，当要访问的数据库对象与正在使用的数据库在同一台服务器上，但不在同一个数据库中，可以省略服务器名，但要指定database。,八、使用go语句分隔多个批,SQL Server2008种会将一批T-SQL语句当成一个执行单元，将其编译后一次执行，而不是一条条语句分别编译执行。不同的批之间用go来分隔。,九、数据类型转换,1使用cast转换语法：CAST(expression AS data_type(length),2使用CONVERT转换语法：CONVERT(data_type(length),expression,style)Style：指定CONVERT函数如何转换expression的整数表达式。如果样式为NULL，则返回NULL。该范围是由data_type确定的。如果expression为date或time数据类型，则style可以为下表中显示的值之一。其他值作为0进行处理。SQL Server使用科威特算法来支持阿拉伯样式的日期格式。,(1)这些样式值将返回不确定的结果。包括所有(yy)（不带世纪数位）样式和一部分(yyyy)（带世纪数位）样式。(2)默认值（style 0或100、9或109、13或113、20或120以及21或121）始终返回世纪数位(yyyy)。(3)转换为datetime时输入；转换为字符数据时输出。(4)为用于XML而设计。对于从datetime或smalldatetime到字符数据的转换，其输出格式如上一个表所述。(5)回历是有多种变体的日历系统。SQL Server使用科威特算法。,3隐式数据类型转换在发生隐式数据类型转换时，尽量将数据转换成数据范围大的那一种数据类型以便可以容纳更多的数据。不是每种数据类型都可以相互转换，也不是所有数据类型都可以隐式转换。在某些数据类型转换时，可能会损失精度。,第二节 函数内置函数,Microsoft SQL Server 2008系统提供了许多内置函数，这些函数可以完成许多特殊的操作，大大提高系统的易用性。内置函数分为如下几类：行集函数:返回可在 SQL 语句中像表引用一样使用的对象。聚合函数:对一组值进行运算，但返回一个汇总值。排名函数:对分区中的每一行均返回一个排名值。标量函数:对单一值进行运算，然后返回单一值。只要表达式有效，即可使用标量函数。,1常用聚合函数,Avg、COUNT、COUNT_BIG（返回组中的项数。返回bigint数据类型值）、MAX、MIN、SUM（空值被忽略）、STDEV（返回指定表达式中所有值的标准偏差）、STDEVP（返回指定表达式中所有值的总体标准偏差）、VAR（返回指定表达式中所有值的方差）、VARP（返回指定表达式中所有值的总体方差）。COUNT_BIG 的用法与 COUNT 函数类似。两个函数唯一的差别是它们的返回值。COUNT_BIG 始终返回 bigint 数据类型值。COUNT 始终返回 int 数据类型值。,标量函数,(1)数学函数,（2）字符串函数,（3）日期函数,（4）元数据函数,元数据函数可以返回数据库和数据库对象的属性信息。元数据是描述数据的数据。通常用于描述数据的结构和意义。例如：OBJECT_ID：返回架构范围内对象的数据库对象标识号。DATABASEPROPERTYEX：返回指定数据库的指定数据库选项或属性的当前设置。DB_NAME：返回数据库名称。,(5)安全函数,例如：CURRENT_USER()函数返回当前用户的名称。SUSER_NAME()函数返回当前登录用户名。,(6)配置函数,配置函数返回当前配置信息。例：显示当前服务器名称。servername-返回运行 SQL Server 的本地服务器的名称。,(7)游标函数,例如：FETCH_STATUS：返回针对连接当前打开的任何游标发出的上一条游标 FETCH 语句的状态。返回值说明：0表示FETCH语句成功。-1表示FETCH语句失败或行不在结果集中。-2表示提取的行不存在。,例：使用游标显示学生信息。DECLARE xs_Cursor CURSOR FORSELECT sno,sname,ssex,sage,sdept,sgradeFROM student;OPEN xs_Cursor;FETCH NEXT FROM xs_Cursor;WHILE FETCH_STATUS=0 BEGIN FETCH NEXT FROM xs_Cursor;END;CLOSE xs_Cursor;DEALLOCATE xs_Cursor;GO,（8）系统函数,执行运算后返回 SQL Server 实例中有关值、对象和设置的信息。例如：CAST和CONVERT函数将一种数据类型的数据转变为另一种数据类型的数据。IS_DATE函数报告它的输入是不是一个有效日期。CURRENT_USER()函数返回当前用户的名称。,case,功能：计算条件列表并返回多个可能结果表达式之一。CASE 具有两种格式：（1）简单 CASE 函数（2）CASE 搜索函数,简单 CASE 函数,语法：CASE input_expression WHEN when_expression THEN result_expression.n ELSE else_result_expression END参数说明：input_expression：使用简单 CASE 格式时所计算的表达式。input_expression 是任意有效的表达式。WHEN when_expression：使用简单CASE格式时要与input_expression进行比较的简单表达式。when_expression是任意有效的表达式。THEN result_expression：当input_expression=when_expression 计算结果为TRUE，或者Boolean_expression计算结果为TRUE时返回的表达式。ELSE else_result_expression：比较运算计算结果不为TRUE时返回的表达式。如果忽略此参数且比较运算计算结果不为 TRUE，则 CASE 返回 NULL。,作用：计算input_expression，然后按指定顺序对每个WHEN子句的input_expression=when_expression进行计算。返回input_expression=when_expression的第一个计算结果为TRUE的 result_expression。如果input_expression=when_expression的计算结果均不为TRUE，则在指定了ELSE子句的情况下，SQL Server数据库引擎将返回else_result_expression；若没有指定ELSE子句，则返回 NULL 值。,CASE 搜索函数,语法：CASE WHEN Boolean_expression THEN result_expression.n ELSE else_result_expression END作用：按指定顺序对每个WHEN子句的Boolean_expression进行计算。返回Boolean_expression的第一个计算结果为TRUE的result_expression。如果Boolean_expression计算结果不为TRUE，则在指定ELSE子句的情况下，数据库引擎将返回else_result_expression；若没有指定ELSE子句，则返回NULL值。,例：显示学生成绩信息，要求90分以上显示为优秀，80-90显示为良好，70-80显示为中等，60-70显示为及格，60分以下显示为不及格。select sno,cno,case when grade=90 then 优秀when grade=80 then 良好when grade=70 then 中等when grade=60 then 及格when grade60 then 不及格endfrom sc,3.排名函数,2023年10月11日,第71页,（1）rank返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。语法：RANK()OVER()参数：将FROM子句生成的结果集划分成RANK函数适用的分区。：确定将RANK值应用于分区中的行时所基于的顺序。,注意：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。,例：对每门课程进行排名。select cno,sno,grade,RANK()over(PARTITION BY CNO ORDER BY grade desc)名次 from SC,（2）DENSE_RANK返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。语法：DENSE_RANK()OVER()注意：如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。,（3）ROW_NUMBER返回结果集分区内行的序列号，每个分区的第一行从 1 开始。语法：ROW_NUMBER()OVER(),例：显示学生信息并显示行号。select ROW_NUMBER()OVER(ORDER BY Sno)序号,*from Student例：返回第4行数据。With xh_xs as(select ROW_NUMBER()OVER(ORDER BY Sno)序号,*from Student)select*from xh_xs where 序号=4,（4）NTILE将有序分区中的行分到指定数目的组中。各个组有编号，编号从1开始。对于每一个行，NTILE 将返回此行所属的组的编号。语法：NTILE(integer_expression)OVER(),例：将学生成绩按照学号升序排列后分为3组。select*,NTILE(3)over(order by sno)组 from sc例：将学生按学号分组后，每组中按照成绩降序排列后分为3组。select*,NTILE(3)over(partition by sno order by grade desc)组 from sc,二、用户自定义函数,在Microsoft SQL Server 2008系统中，用户定义函数是接受参数、执行操作并且将运算结果以值的形式返回的例程。这种返回值既可以是单个标量值，也可以是一个结果集。在Microsoft SQL Server 2008系统中，用户定义函数可以使用Transact-SQL语言编写，也可以使用.NET编程语言来编写。,1用户定义函数的特点,使用Transact-SQL编写的用户定义函数通过缓存计划并在重复执行时重用它来降低Transact-SQL代码的编译开销。也就是说，每次使用用户定义函数时均无需重新解析和重新优化，从而大大缩短了执行时间。减少网络流量，基于某种无法用单一标量表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，该函数可以在WHERE子句中调用，以减少发送至客户端的数字或行数。,2用户定义函数结构,在Microsoft SQL Server 2008系统中，所有的用户定义函数都具有相同的由两部分组成的结构：标题和正文。标题可以定义下列内容。具有可选架构/所有者名称的函数名称。输入参数名称和数据类型。可以用于输入参数的选项。返回参数数据类型和可选名称。可以用于返回参数的选项。正文定义了函数将要执行的操作，这些操作可以是一个或多个Transact-SQL语句，也可以是.NET程序集的引用。,在创建用户定义函数时，每个完全限定用户函数名称(schema_name.function_name)必须唯一。,函数的BEGIN END块中的语句不能有任何副作用。函数副作用是指对具有函数外作用域(例如修改数据库表)的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象(如局部游标或局部变量)的更改。不能在函数中执行的操作包括：对数据库表的修改，对不在函数上的局部游标进行操作，发送电子邮件，尝试修改目录，以及生成返回至用户的结果集。,3定义,在Microsoft SQL Server 2008系统中，使用CREATE FUNCTION语句可以创建标量函数、内联表值函数和多语句表值函数。需要说明的是，如果RETURNS子句指定了一种标量数据类型，则该函数为标量值。如果RETURNS子句指定了TABLE，则该函数为表值函数。根据函数主体的定义方式，表值函数可以分为内联函数或多语句函数。内联函数可以用于获得参数化视图的功能。,4查看函数,Microsoft SQL Server 2008系统提供了几个可以用于查看用户定义函数信息的系统存储过程和目录视图。使用这些工具，可以查看用户定义函数的定义、获取函数的架构和创建时间、列出指定函数所使用的对象等信息。,可以使用sys.sql_modules、OBJECT_DEFINITION、sp_helptext等工具查看用户定义函数的定义，使用sys.objects、sys.parameters、sp_help等工具查看有关用户定义函数的信息，使用sys.sql_dependencies、sp_depends等工具查看用户定义函数的依赖关系。,5函数种类,按照函数返回类型，用户自定义函数的类型分为表值函数（TVF，TABLE-Valued Function）、标量值函数（Scalar-Valued Function,SVF）和聚合函数。按照创建这些类型和用户自定义函数的技术，分为Transact-SQL函数和CLR函数。,(1)表值函数,1.多语句表值函数多语句表值自定义函数返回值是一个表，返回值表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询,对数据进行多次筛选与合并。,例1：创建一个无参数的多语句表值函数，实现查询。Create function dbo.multisql_tablevalued_func()RETURNS Table_Var_Name TABLE(sno char(10),sname char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM studentRETURNEND,例2：创建一个带参数的多语句表值函数。Create function dbo.multisql_tablevalued_func2(xh char(10)RETURNS Table_Var_Name TABLE(sno char(10),sname char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xhRETURNEND执行该函数：Select*from dbo.multisql_tablevalued_func2(201215122),例3：创建一个带参数默认值的多语句表值函数。Create function dbo.multisql_tablevalued_func3(xh char(10)=201215122)RETURNS Table_Var_Name TABLE(xh char(10),xm char(20)ASBEGININSERT INTO Table_Var_Name SELECT sno,sname FROM student where sno=xhRETURNEND执行带默认值的函数：Select*from dbo.multisql_tablevalued_func2(default)当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。,(2)内联表值函数,是一种特殊的多语句表值函数，仅含1条SELECT语句。内联表值函数以表的形式返问一个值，即它返回的是一个表。内联表值自定义函数没有由BEGIN END语句块中包含的函数体，而是直接使用RETURN子句，其中包含的SELECT语句将数据从数据库中筛选出来形成一个表，使用内联表值自定义函数可以提供参数化的视图。功能,例4：创建一个内联表值函数，实现例3的功能。Create function dbo.inline_func(xh char(10)=201215122)RETURNS TABLEASRETURN(SELECT sno,sname FROM student where sno=xh)执行该函数：Select*from dbo.inline_func(default)Select*from dbo.inline_func(0403),(3)标量值函数,标量值函数返回一个确定类型的标量，值的类型为除text,ntext,image，cursor,timestampp和table类型外的其他数据类型。,例5：创建一个标量值函数，查询指定姓名的学生的学号。Create function dbo.scalar_func(xm char(10)RETURNS char(10)ASBEGINDECLARE Result char(10)SELECT Result=sno FROM student where sname=xmRETURN Resultend执行该函数：Select db