T-SQL编程与应用.doc

第6章 T-SQL编程与应用6.1 T-SQL语言基础6.1.1 T-SQL语言的编程功能1. 基本功能支持ANSI SQL-92标准：DDL数据定义,DML数据操纵,DCL数据控制,DD数据字典2. 扩展功能l 加入程序流程控制结构l 加入局部变量，系统变量等6.1.2 标识符1. 标识符分类l 常规标识符Regular identifer（严格遵守标识符格式规则）l 界定标识符Delimited identifer（引号或方括号）2. 标识符格式规则SQL server 7.0以前的版本，标识符长度限制在30个字符以内。SQL server2000的标识符：1128个字符；临时表名1116个字符。标识符的第一个字符必须是：大、小写字母、下划线、#。其中，和#在TSQL中有专门的含义。接下来的字符必须是符合Unicode2.0(统一码)标准的字母,或者是十进制数字,或是特殊字符, #, _, $。标识符不能与任何SQL Server保留字匹配。标识符不能包含空格，或别的特殊字符。*不符合规则的标识符必须加以界定（双引号”或方括号）注意：数据库名、表名必须符合标识符规范。3. 对象命名规则所有数据库对象的引用由下面四部分构成：server_name.database_name.schema_name.object_name| database_name.schema_name.object_name| schema_name.object_name| object_name说明：server_name 指定链接的服务器名称或远程服务器名称。database_name 如果对象驻留在 SQL Server 的本地实例中，则指定 SQL Server 数据库的名称。如果对象在链接服务器中，则 database_name 将指定 OLE DB 目录。schema_name 如果对象在 SQL Server 数据库中，则指定包含对象的架构的名称。如果对象在链接服务器中，则 schema_name 将指定 OLE DB 架构名称。object_name 对象的名称。说明：从 SQL Server 2005 开始，每个对象都属于一个数据库架构。数据库架构是一个独立于数据库用户的非重复命名空间。您可以将架构视为对象的容器。可以在数据库中创建和更改架构，并且可以授予用户访问架构的权限。任何用户都可以拥有架构，并且架构所有权可以转移。在SQL Server 2000中架构和用户是没有多大的区别，我们在2000中一般是指所有者。2005后，用户和架构开始明确的分开，架构可以理解为对象的容器或者命名空间。2000中服务器名.数据库名.拥有者名.对象名6.1.3 注释1. 注释多行/* fshjhfjkshfjsdhfsdjffsjdkfljskdlfjkldsfjkdslfjfjfj */2. 注释单行-ghjfghkfdjhgkfhgjfdhgkgjfdh6.2 表达式6.2.1 常量常量是指在程序运行中值不变的量。根据常量的类型不同分为字符型常量，整型常量，日期时间型常量、实型常量、货币常量、全局唯一标识符。1 字符串常量字符串常量分为ASCII字符串常量、UNICODE字符串常量。 ASCII常量：用单引号括起来，由ASCII构成的字符串。如，abcde UNICODE常量：前面有一个N，如Nabcde。（N在SQL92规范中表示国际语言，必须大写）字符串常量必须放在单引号或双引号中。由字母、数字、下划线、特殊字符（!,#）组成。当单引号括住的字符串常量中包含单引号时，用2个单引号表示字符串中的单引号。如，Im ZYT写作Im ZYT。T-SQL中设置SET QUOTED_IDENTIFIER ON | OFF 设置。当SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。不允许用双括号括住字符串常量因为双括号括的是标识符。SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须遵守所有Transact-SQL 标识符规则。允许用双括号括住字符串常量。Microsoft SQL客户端和ODBC驱动程序自动使用ON。说明：Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。强烈建议：用单括号括住字符串常量，别被双引号括的到底是标识符还是字符串搞糊涂。2 整型常量二进制整型常量，0，1组成，如111001。十进制整型常量，如1982十六进制整型常量，用0x开头，如0x3e，0x，只有0x表示空十六进制数。3 日期时间型常量用单引号将日期时间字符串扩起来。如july 22,200722-july-200706-24-198306/24/19831981-05-2319820624 1982年10月1日4 实型常量实型常量有定点和浮点。如165.234，10E235 货币常量用货币符号开头。如￥542324432.25。SQL Server不强制分组，也就是每隔三个数字插一个逗号之类的。6 全局唯一标识符全局唯一标识符(Globally Unique Identification Numbers,GUID)是16字节长的二进制数据类型，是SQL Server根据计算机网络适配器地址和主机时钟产生的唯一号码生成的全局唯一标识符。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF 即为有效的 GUID 值。世界上的任何两台计算机都不会生成重复的 GUID 值。GUID 主要用于在拥有多个节点、多台计算机的网络或系统中，分配必须具有唯一性的标识符。在 Windows 平台上，GUID 应用非常广泛：注册表、类及接口标识、数据库、甚至自动生成的机器名、目录名等。6.2.2 数据类型在 SQL Server 2005 中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。第3章已经提到。6.2.3 变量变量就是在程序执行过程中其值可以改变的量。1局部变量局部变量是作用域局限在一定范围内的T-SQL对象。作用域：若局部变量在一个批处理、存储过程、触发器中被声明或定义，则其作用域就在批处理、存储过程或触发器内。(1) 局部变量声明DECLARE 变量名 数据类型，变量名 数据类型注意： 变量名必须以 at 符 () 开头。局部变量名必须符合有关标识符的规则。 数据类型：是系统提供的类型、CLR用户定义类型或别名数据类型。变量不能是 text、ntext 或 image 数据类型。 变量先声明或定义，然后就可以在SQL命令中使用。默认初值NULL。(2) 赋值格式：SET 变量名=表达式格式：SELECT 变量名=表达式/ SELECT 变量名=输出值 FROM表 where .说明：变量名是除cursor,text,ntext,image外的任何类型变量名；表达式是任何有效的SQLServer表达式。SELECT 变量名=表达式用于将单个值返回到变量中，如果表达式为列名，则返回多个。若SELECT语句返回多个值，则将返回的最后一个值赋给变量。若SELECT语句没有返回值，变量保留当前值；若表达式是不返回值的子查询，则变量为NULL。例6-1：SELECT命令赋值，执行脚本USE educGODECLARE var1 varchar(8) -声明局部变量SELECT var1=学生姓名 -为局部变量赋初始值SELECT var1=Sname -查询结果赋值给变量FROM studentWHERE SID=bj10001SELECT var1 as 学生姓名 -显示局部变量结果执行结果：例6-2：SELECT命令赋值，多个返回值中取最后一个USE educgoDECLARE var1 varchar(8)SELECT var1=读者姓名SELECT var1=Sname-查询结果赋值，返回的是整个列的全部值，但最后一个给变量FROM studentSELECT var1 AS 读者姓名 -显示局部变量的结果执行结果：例6-3：SET命令赋值USE educgo DECLARE no varchar(10) SET no=Bj10001 -变量赋值SELECT SID,SnameFROM studentWHERE SID=no执行结果： 2. 全局变量系统全局变量是SQL Server系统提供并赋值的变量。用户不能建立全局变量，也不能用SET语句改变全局变量的值。格式：变量名记录SQL Server服务器活动状态的一组数据，系统提供的30个全局变量。以下是几个全局变量介绍： ERROR 最后一个错误的错误号 IDENTITY最后一个插入的标识值 LANGUAGE当前使用语言的名称 MAX_CONNECTIONS可以创建的同时链接的最大数目 ROWCOUNT受上一个语言影响的行数 SERVERNAME本地服务器的名称 SERVICENAME该计算机上的服务的名称 TIMETICKS当前计算机上每刻度的微秒数 TRANSCOUNT当前连接打开的事务数 VERSIONSQL Server的版本信息注意：全局变量由开始，由系统定义和维护，用户只能显示和读取，不能修改；局部变量由一个开始，由用户定义和赋值。全局变量总共有33个。 例6-4：显示SQL Server的版本。select versionselect servername -本地服务器名6.2.4 函数SQL Server 2005 提供了一些内置函数，用户可以使用这些函数方便的实现一些功能。以下举例说明一些常用的函数，其他函数请参考联机手册。1. 聚合函数聚合函数对一组值执行计算并返回单一的值。除 COUNT 函数之外，聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。所有聚合函数都具有确定性就是任何时候用一组给定的输入值调用它们时，都返回相同的值。仅在下列项中聚合函数允许作为表达式使用： SELECT 语句的选择列表（子查询或外部查询）。 COMPUTE 或 COMPUTE BY 子句。 HAVING 子句。例：查询出最高分的学号和最高分USE educGOSELECT sid,gradeFROM scwhere grade=(select max(grade) from sc )想想为啥用子查询。2. 日期时间函数日期时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。下表列出日期和时间函数以及它们的确定性属性。(1) DATEADD()：在向指定日期加上一段时间的基础上，返回新的datetime 值语法：DATEADD ( datepart , number, date ) 参数说明：datepart是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft SQL Server 识别的日期部分和缩写number是用来增加 datepart 的值。如果指定一个不是整数的值，则将废弃此值的小数部分。date是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。DECLARE OLDTime datetime -定义日期时间型数据SET OLDTime=12-02-2004 06:30pmSELECT DATEADD(hh,4,OldTime)(2) DATEDIFF()：两时间之差语法DATEDIFF ( datepart , startdate , enddate ) 参数datepart是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft SQL Server 识别的日期部分和缩写。DECLARE FirstTime datetime, SecondTime datetimeSET FirstTime=03-24-2006 6:30pmSET SecondTime=03-24-2006 6:33pmSELECT DATEDIFF(ms,FirstTime,SecondTime) as time1 -第一个参数表示毫秒(3) DATENAME()：返回年月日星期等字符串。语法DATENAME ( datepart , date )参数datepart是指定应返回的日期部分的参数。下表列出了 Microsoft SQL Server 识别的日期部分和缩写。DECLARE StatementDate datetimeSET StatementDate=2006-3-14 3:00 PMSELECT DATENAME(dw,StatementDate)3. 字符函数这些函数对字符串输入值执行操作，返回字符串或数字值。(1) ASCII()返回字符表达式最左端字符的 ASCII 代码值。DECLARE StringTest char(10)SET StringTest=ASCII(Robin )SELECT StringTest(2) CHAR()将 int ASCII 代码转换为字符的字符串函数。DECLARE StringTest char(10)SET StringTest=ASCII(Robin )SELECT CHAR(StringTest)(3) LEFT()返回从字符串左边开始指定个数的字符。DECLARE StringTest char(10)SET StringTest=Robin SELECT LEFT(StringTest,3)(4) LOWER()将大写字符数据转换为小写字符数据后返回字符表达式DECLARE StringTest char(10)SET StringTest=Robin SELECT LOWER(LEFT(StringTest,3)(5) LTRIM()删除起始空格后返回字符表达式DECLARE StringTest char(10)SET StringTest= RobinSELECT Start-+LTRIM(StringTest),Start-+StringTest(6) RIGHT()返回字符串中从右边开始指定个数的 integer_expression 字符。DECLARE StringTest char(10)SET StringTest= RobinSELECT RIGHT(StringTest,3)(7) RTRIM()截断所有尾随空格后返回一个字符串。DECLARE StringTest char(10)SET StringTest=RobinSELECT StringTest+-End,RTRIM(StringTest)+-End(8) STR()由数字数据转换来的字符数据。SELECT A+82 SELECT A+STR(82) SELECT A+LTRIM(STR(82) (9) SUBSTRING()求子串函数DECLARE StringTest char(10)SET StringTest=Robin SELECT SUBSTRING(StringTest,3,LEN(StringTest)(10) UPPER()返回将小写字符数据转换为大写的字符表达式。DECLARE StringTest char(10)SET StringTest=Robin SELECT UPPER(StringTest)4. 空值置换函数ISNULL（空值，指定的空值），用指定的值代替空值。USE LibraryGOSELECT Lendnum,ISNULL(Lendnum,0) AS 空值置换FROM ReaderWHERE ISNULL(Lendnum,0)=0查询结果：6.2.5 运算符SQL Server 2005的运算符和其他高级语言类似，用于指定要在一个或多个表达式中执行的操作，将变量、常量和函数连接起来。优先级运算符类别所包含运算符1一元运算符+（正）、-（负）、（取反）2算术运算符*（乖）、/（除）、%（取模）3算术字符串运算符+（加）、-（减）、+（连接）4比较运算符=（等于）、（大于）、=（大于等于）、（小于）、=（小于等于）、（ 或!=不等于）、!（不大于）5按位运算符&（位与）、|（位或）、（位异或）6逻辑运算符not（非）7逻辑运算符and（与）8逻辑运算符all（所有）、any（任意一个）、between（两者之间）、exists（存在）、in（在范围内）、like（匹配）、or（或）、some（任意一个）9赋值运算符=（赋值）6.3 批处理与脚本6.3.1批处理？1 概念批处理是指包含一条或多条T-SQL语句的语句组，被一次性的执行。是作为一个单元发出的一个和多个SQL语句的集合。SQL Server将批处理编译成一个可执行单元，称为执行计划。批中如果某处发生编译错误，整个执行计划都无法执行。1 .批处理：指包含一条或多条T - SQL语句的语句组，这组语句从应用程序一次性地发送到SQLserver服务器执行。2 .执行单元：SQLserver服务器将批处理语句编译成一个可执行单元，这种单元称为执行单元。3 .若批处理中的某条语句编译出错，则无法执行。若运行出错，则视情况而定。4 .书写批处理时,GO语句作为批处理命令的结束标志，当编译器读取到GO语句时，会把GO语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身不是T - SQL语句的的组成部分，只是一个表示批处理结束的前端指令。2批处理有以下规则：( 1 ). create default , create rule , create trigger, create procedure和createview等语句在同一个批处理中只能提交一个。( 2 ).不能在删除一个对象之后，在同一批处理中再次引用这个对象。( 3 ).不能把规则和默认值绑定到表字段或者自定义字段上之后，立即在同一批处理中使用它们。( 4 ).不能定义一个check约束之后，立即在同一个批处理中使用。( 5 ).不能修改表中一个字段名之后，立即在同一个批处理中引用这个新字段。( 6 ).使用set语句设置的某些set选项不能应用于同一个批处理中的查询( 7 ).若批处理中第一个语句是执行某个存储过程的execute语句，则execute关键字可以省略。若该语句不是第一个语句，则必须写上。3 几种指定批处理的方法(1) 应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。(2) 存储过程或触发器内的所有语句构成一个批处理，每个存储过程或触发器都编译为一个执行计划。(3) 由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。(4) 由sp_executesql存储过程执行的字符串是一个批处理，并编译为一个执行计划。说明：若应用程序发出的批处理过程中含有EXECUTE语句，已执行字符串或存储过程的执行计划将和包含EXECUTE语句的执行计划分开执行。 若sp_executesql存储过程所执行的字符串生成的执行计划也与包含sp_executesql调用的批处理执行计划分开执行。 若批处理中的语句激发了触发器，则触发器执行疾患将和原始的批处理分开执行。2批处理的结束和推出n 批处理结束语句：GO 作为批处理的结束标志。也就是说当编译器执行到GO时会把GO之前的所有语句当作一个批处理来执行。GO 不是T-SQL语句。 GO命令和T-SQL语句不可在同一行，在批处理中的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部（用户定义）变量的作用域限制在一个批处理中，不可在GO命令后引用。在联机帮助里，GO解释为Signals the end of a batch of Transact-SQL statements to the Microsoft SQL Server utilities. 就是一个语句的结束信号。也可以理解为一个分段执行命令（有些地方如此解释，不太好）。GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query AnalyzerEXECUTE功能：执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持 Transact-SQL 批处理内的字符串的执行 联机帮助里解释为Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.n 批处理退出语句：RETURN 整型表达式无条件中止查询、存储过程或批处理的执行。存储过程或批处理不执行位于RETURN之后的语句。当存储过程使用该语句，则可用该语句指定返回给调用应用程序、批处理或过程的整数值。若return语句未指定值，则存储过程的返回值是0。注意：当用于存储过程时，RETURN不能返回空值。例：返回状态，该过程检查在SC表中是否存在选修了80012课程的学生。存在则返回1，不存在返回2 create procedure checksid param char(20)as if (select sid from sc where cid=param)=bj10001 return 1 else return 26.3.2 脚本1 什么是脚本？脚本是存储在文件中一系列T-SQL语句。可包含一个或多个批处理，GO作为批处理结束语句，如果脚本中无GO语句，则作为单个批处理。脚本文件扩展名为.sql6.4 流程控制语句T-SQL 语言支持基本的流控制逻辑，它允许按照给定的某种条件执行程序流和分支，T-SQL 提供的控制流有：IFELSE分支，CASE多重分支，WHILE循环结构，GOTO语句，WAITFOR语句和RETURN语句。6.4.1 IFELSE语句制定 T-SQL 语句的执行条件。如果满足条件，则在 IF 关键字及其条件之后执行 T-SQL 语句：布尔表达式返回 TRUE。可选的 ELSE 关键字引入另一个 T-SQL 语句，当不满足 IF 条件时就执行该语句：布尔表达式返回 FALSE。语法：IF Boolean_expression /* 条件表达式 */ sql_statement | statement_block /* 条件表达式为TRUE时执行 */ ELSE sql_statement | statement_block /* 条件表达式为FALSE时执行 */例6-5：IF查询课程中有没有计算机课。USE educGO-如果课程中有计算机课程，统计其数量，否则显示没有计算机课程IF exists(SELECT * FROM course WHERE cname like 计算机%) SELECT COUNT(*) AS 计算机课程数量 FROM course WHERE cname like 计算机%ELSEPRINT 数据库中没有计算机课程执行结果：例6-6：嵌套IF课程查询USE educGOIF exists(SELECT * FROM course WHERE cname=计算机组成原理) SELECT COUNT(*) AS 计算机组成原理 FROM course WHERE cname=计算机组成原理ELSE IF exists(SELECT * FROM course WHERE cname=编译原理) SELECT COUNT(*) AS 编译原理 FROM course WHERE cname=编译原理 ELSE PRINT 计算机组成原理和编译原理都没开！执行结果：在实际程序中，IFELSE语句中不止包含一条语句，而是一组的SQL语句。为了可以一次执行一组SQL语句，这时就需要使用BEGINEND语句将多条语句封闭起来。其语法格式为：BEGINsql_statement | statement_block /* 语句块 */END说明：BEGIN.END语句块允许嵌套。例6-7：BEGING END图书查询USE educGOIF exists(SELECT * FROM course WHERE cname=计算机组成原理) begin SELECT COUNT(*) AS 计算机组成原理 FROM course WHERE cname=计算机组成原理 endELSE begin IF exists(SELECT * FROM course WHERE cname=编译原理) SELECT COUNT(*) AS 编译原理 FROM course WHERE cname=编译原理 ELSE PRINT 计算机组成原理和编译原理都没开！ End6.4.2 WHILE语句设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。语法：WHILE 逻辑表达式Begin T-SQL语句组break/*终止整个语句的执行*/continue/*结束一次循环体的执行*/END备注：如果嵌套了两个或多个 WHILE 循环，则内层的 BREAK 将退出到下一个外层循环。将首先运行内层循环结束之后的所有语句，然后重新开始下一个外层循环。例6-8：一个小循环程序DECLARE X intSET X=0WHILE x3 BEGIN SET x=X+1 PRINT x=+convert(char(1),x) -类型转换函数convertEND执行结果：x=1x=2x=3BREAK语句退出while或ifelse语句中最内层的循环。退出本次循环 continue语句 退出本层循环。6.4.3 GOTO语句GOTO语句将执行语句无条件跳转到标签处，并从标签位置继续处理。GOTO语句和标签可在过程、批处理或语句块中的任何位置使用。其语法格式为：GOTO label6.4.4 WAITFOR语句WAITFOR语句，称为延迟语句。就是暂停执行一个指定的时间间隔或者到一个指定的时间。其语法格式为：WAITFOR DELAY time_to_pass /* 设定等待时间 */| TIME time_to_execute /* 设定等待到某一时刻 */注意：执行 WAITFOR 语句时，事务正在运行，并且其他请求不能在同一事务下运行。WAITFOR 不更改查询的语义。如果查询不能返回任何行，WAITFOR 将一直等待，或等到满足 TIMEOUT 条件（如果已指定）。time：要等待的时间。可以按datetime数据可接受的格式指定time，也可用局部变量指定此参数。不能指定日期，只能指定时间。例6-9：延迟30秒执行查询。USE educGOWAITFOR DELAY 00:00:30SELECT * FROM student执行结果：例6-10：在时刻21:20:00执行查询。USE educGOWAITFOR TIME 21:20:00SELECT * FROM student执行结果：（用合适的时刻，上机体会）6.5 CASE表达式计算条件列表并返回多个可能结果表达式之一。CASE 具有两种格式： l 简单CASE表达式将某个表达式与一组简单表达式进行比较以确定结果。 l CASE 搜索表达式计算一组逻辑表达式以确定结果。两种格式都支持可选的 ELSE 参数。语法：1. 简单式CASE 表达式 WHEN 表达式的值1 THEN 返回表达式1 WHEN 表达式的值2 THEN 返回表达式2 ELSE 返回表达式nEND例6-11：显示学生选课的数量。USE educGOselect sid,课程数量=case count(*) when 1 then 选修了一门课 when 2 then 选修了两门课 when 3 then 选修了三门课endfrom scgroup by sid执行结果：2. 搜索式CASE WHEN 逻辑表达式1 THEN 返回表达式1 WHEN 逻辑表达式2 THEN 返回表达式2 ELSE 返回表达式nEND例6-12：上例USE educGOselect sid,count(*) as 数量,课程数量=case when count(*)=1 then 选修了一门课 when count(*)=2 then 选修了两门课 when count(*)=3 then 选修了三门课endfrom scgroup by sid6.6 自定义函数User-Defined Function(UDF)6.6.1 概述1 什么是UDF？用户定义函数（UDF）是执行计算并返回一个值（标量值或表）的一段程序。Microsoft SQL Server 2000引入了UDF，你可以用T-SQL编写UDF并应用于查询、计算列（computed column）和约束。SQL Server 2005又引入了公共语言运行库（common language runtime CLR）集成，你可以使用任意 .NET语言编写UDF及其他程序和对象。2 UDF分类SQL Server所支持的两种UDF：返回单个值的标量值UDF和返回一个表的表值UDF（内联和多语句）。 标量型函数 (Scalar function)返回一个确定类型的标量。函数体用BEGIN-END括起来。 内联表值型函数(inline table valued function)以表的形式返回一个返回值。即它返回的是一个表。没有用BEGIN-END括起来的函数体。其返回的表由一个位于return子句中的Select命令从数据库中得到的。 多语句表值型函数(Multi-sataement table-valued function)可以看作是标量型和内联表值型函数的结合体。返回值是一个表，但和标量函数一样有一个用Begin-End括起来的函数体，返回值表中的数据是由函数体中的语句插入的。3 注意 UDF可以嵌入到查询、约束和计算列中。定义UDF的代码不能影响函数范围之外的数据库状态，也就是说，UDF代码不能修改表中的数据或调用会产生副作用（side effect）的函数（例如，RAND）。 UDF的代码只能创建表变量，不能创建或访问临时表，也不允许使用动态执行。 在Microsoft SQL Server 2005系统中，可以分别使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION语句来实现用户定义函数的创建、修改和删除。在创建用户定义函数时，每个完全限定用户函数名称(schema_name.function_name)必须惟一。 用户自定义函数不能用于执行一系列可以改变数据库状态的操作。6.6.2 创建用户自定义函数1 用T-SQL创建用户自定义函数用户自定义函数分为：标量型函数 (Scalar function)内联表值型函数(inline table valued function)多语句表值型函数(Multi-sataement table-valued function) 标量型函数：标量型函数返回一个确定类型的标量值，其返回值类型为除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。函数体语句定义在begin - end语句内，其中包含了可以返回值的Transact - SQL命令。语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =default ,n )returns scalar_return_data_type with,n as begin function_bodyreturn scalar_expression end 其中： .function_option有两个可选值：encryption | schemabindingencryption:加密选项，让SQLServer对系统表中有关createfunction的声明加密，以防止用户自定义函数作为SQLServer复制的一部分被发布。schemabinding，计划绑定选项。将用户自定义函数绑定到它所引用的数据库对象，则函数所涉及的的数据库对象从此将不能被删除或修改，除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。 .owner_name:指定用户自定义函数的所有者。 .function_name:指定用户自定义函数的名称。 .database_name.owner_name.function_name应是唯一的。 . parameter_name :定义一个式多个参数的名称，一个函数最多可以定义1024个参数，每个参数前用 符号标明 ，参数的作用范围是整个函数，参数只能替代常量，不能替代表名，列名或其它数据库对象名称，用户自定义函数不支持输出参数。 .scalar_parameter_data_type:指定标量参数的数据类型，除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。 .scalar_return_data_type:指定标量返回值的数据类型，除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。 .scalar_expression:指定标量型用户自定义函数返回的标量值表达式。 .function_body:指定一系列的Transact_SQL语句它们决定了函数的返回值。例：创建学生补助计算函数use educgo - 创建函数 create function studentwelfare( hiredate datetime , today datetime , per_sal money ) - hiredate表示雇佣期,today表示当前日期，par_wage表示每一年的工龄应得的工资额 returns money as begin declare studentwelfare money set studentwelfare = ( year ( today ) - year ( hiredate ) * per_sal return ( studentwelfare ) end - 结束函数定义 go - 调用函数 select educ.dbo.studentwelfare( 1999-7-1 , getdate (), 15 ) as student_welfare注意：以上的函数体可简写为:as begin return ( year ( today ) - year ( hiredate ) * per_ sal )end 说明：DBO: Database Owner,意思是数据库的所有者。具体含义将在数据库安全性中介绍。 内嵌表值函数：以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由begin - end语句括起来的函数体，其返回的表由一个位于return子句中的select命令段从数据库中筛选出来。内嵌表值型函数功能相当于一个参数化的视图。创建函数语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =default ,n )returns table with,n as return ( select - stmt)其中： . table :批定返回值为一个表。 . select - stmt:单个select语句，确定返回的表的数据。例：创建返回所有订购某类产品的公司信息函数use educgo create function coursename( cid varchar ( 30 ) - productid表示产品代号 returns table as return ( select * from course where cid = cid ) go select * from educ.dbo.coursename(80012) 注意：因为是表值函数，返回的是一个表，因此调用时，要把该函数作为表来使用。 多语句表值型函数：可以看作标量型和内嵌表值型函数的结合体，它的返回值是一个表，但它和标量型函数一样有一个用begin - end语句括起来的函数体。语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =defau