T-SQL编程与应用
6.1T-SQL语言基础
6.1.1T-SQL语言的编程功能1.基本功能支持ANSISQL-92标准：DDL数据定义,DML数据操纵,DCL数据控制,DD数据字典2.扩展功能加入程序流程控制结构加入局部变量，系统变量等6.1.2标识符1.标识符分类常规标识符Regularidentifer（严格遵守标识符格式规则）界定标识符Delimitedidentifer（引号’或方括号[]）2.标识符格式规则SQLserver7.0以前的版本，标识符长度限制在30个字符以内。SQLserver2000的标识符：1~128个字符；临时表名1~116个字符。标识符的第一个字符必须是：大、小写字母、下划线、@、#。其中，@和#在TSQL中有专门的含义。接下来的字符必须是符合Unicode2.0(统一码)标准的字母,或者是十进制数字,或是特殊字符@,#,_,$。标识符不能与任何SQLServer保留字匹配。标识符不能包含空格，或别的特殊字符。*不符合规则的标识符必须加以界定（双引号””或方括号[]）注意：数据库名、表名必须符合标识符规范。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
如果对象驻留在SQLServer的本地实例中，则指定SQLServer数据库的名称。如果对象在链接服务器中，则database_name将指定OLEDB目录。
schema_name
如果对象在SQLServer数据库中，则指定包含对象的架构的名称。如果对象在链接服务器中，则schema_name将指定OLEDB架构名称。
object_name
对象的名称。说明：从SQLServer2005开始，每个对象都属于一个数据库架构。数据库架构是一个独立于数据库用户的非重复命名空间。您可以将架构视为对象的容器。可以在数据库中创建和更改架构，并且可以授予用户访问架构的权限。任何用户都可以拥有架构，并且架构所有权可以转移。在SQLServer2000中架构和用户是没有多大的区别，我们在2000中一般是指所有者。2005后，用户和架构开始明确的分开，架构可以理解为对象的容器或者命名空间。2000中服服务器名.数据库名拥有者名务器名数据库名.拥有者名对象名数据库名拥有者名.对象名6.1.3注释1.注释多行/*fshjhfjkshfjsdhfsdjffsjdkfljskdlfjkldsfjkdslfjfjfj*/2.注释单行--ghjfghkfdjhgkfhgjfdhgkgjfdh
6.2表达式
6.2.1常量常量是指在程序运行中值不变的量。根据常量的类型不同分为字符型常量，整型常量，日期时间型常量、实型常量、货币常量、全局唯一标识符。1字符串常量字符串常量分为ASCII字符串常量、UNICODE字符串常量。ASCII常量：用单引号括起来，由ASCII构成的字符串。如，'abcde'UNICODE常量：前面有一个N，如N'abcde'。（N在SQL92规范中表示国际语言，必须大写）
字符串常量必须放在单引号或双引号中。由字母、数字、下划线、特殊字符（!,@,#）组成。当单引号括住的字符串常量中包含单引号时，用2个单引号表示字符串中的单引号。如，I'mZYT写作'I''mZYT'。T-SQL中设置SETQUOTED_IDENTIFIER{ON|OFF}设置。SETQUOTED_IDENTIFIER当为ON时，标识符可以由双引号分隔，而文字必须由单引号分隔。不允许用双括号括住字符串常量因为双括号括的是标识符。SETQUOTED_IDENTIFIER为OFF时，标识符不可加引号，且必须遵守所有Transact-SQL标识符规则。允许用双括号括住字符串常量。MicrosoftSQL客户端和ODBC驱动程序自动使用ON。
说明：说明：Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。
强烈建议：用单括号括住字符串常量，别被双引号括的到底是标识符还是字符串搞糊涂。强烈建议：用单括号括住字符串常量，别被双引号括的到底是标识符还是字符串搞糊涂。建议2整型常量二进制整型常量，0，1组成，如111001。十进制整型常量，如1982十六进制整型常量，用0x开头，如0x3e，0x，只有0x表示空十六进制数。3日期时间型常量用单引号将日期时间字符串扩起来。如'july22,2007''22-july-2007'
'06-24-1983''06/24/1983''1981-05-23''19820624''1982年10月1日'
4实型常量实型常量有定点和浮点。如165.234，10E235货币常量用货币符号开头。如￥542324432.25。SQLServer不强制分组，也就是每隔三个数字插一个逗号之类的。6全局唯一标识符全局唯一标识符(GloballyUniqueIdentificationNumbers,GUID)是16字节长的二进制数据类型，是SQLServer根据计算机网络适配器地址和主机时钟产生的唯一号码生成的全局唯一标识符。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF即为有效的GUID值。世界上的任何两台计算机都不会生成重复的GUID值。GUID主要用于在拥有多个节点、多台计算机的网络或系统中，分配必须具有唯一性的标识符。Windows平台上，在GUID
应用非常广泛：注册表、类及接口标识、数据库、甚至自动生成的机器名、目录名等。6.2.2数据类型在SQLServer2005中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。第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命令赋值，执行脚本
USEeducGODECLARE@var1varchar(8)SELECT@var1='学生'SELECT@var1=SnameFROMstudentWHERESID='bj10001'SELECT@var1as'学生'执行结果：--显示局部变量结果--声明局部变量--为局部变量赋初始值--查询结果赋值给变量
例6-2：SELECT命令赋值，多个返回值中取最后一个USEeducgoDECLARE@var1varchar(8)SELECT@var1='读者'SELECT@var1=Sname--查询结果赋值，返回的是整个列的全部值，但最后一个给变量FROMstudentSELECT@var1AS'读者'执行结果：--显示局部变量的结果
例6-3：SET命令赋值USEeducgoDECLARE@novarchar(10)SET@no='Bj10001'SELECTSID,SnameFROMstudentWHERESID=@no执行结果：--变量赋值
2.全局变量
系统全局变量是SQLServer系统提供并赋值的变量。用户不能建立全局变量，也不能用SET语句改变全局变量的值。格式：@@变量名记录SQLServer服务器活动状态的一组数据，系统提供的30个全局变量。以下是几个全局变量介绍：@@ERROR@@IDENTITY@@LANGUAGE@@MAX_CONNECTIONS@@ROWCOUNT@@SERVERNAME@@SERVICENAME@@TIMETICKS@@TRANSCOUNT@@VERSION最后一个T－SQL错误的错误号最后一个插入的标识值当前使用语言的名称可以创建的同时链接的最大数目受上一个SQL语言影响的行数本地服务器的名称该计算机上的SQL服务的名称当前计算机上每刻度的微秒数当前连接打开的事务数SQLServer的版本信息
注意：全局变量由@@开始，由系统定义和维护，用户只能显示和读取，不能修改；局部变量由一个@开始，由用户定义和赋值。全局变量总共有33个。例6-4：显示SQLServer的版本。
select@@version
select@@servername
--本地服务器名
6.2.4函数SQLServer2005提供了一些内置函数，用户可以使用这些函数方便的实现一些功能。以下举例说明一些常用的函数，其他函数请参考联机手册。
1.聚合函数聚合函数对一组值执行计算并返回单一的值。除COUNT函数之外，聚合函数忽略空值。聚合函数经常与SELECT语句的GROUPBY子句一同使用。所有聚合函数都具有确定性就是任何时候用一组给定的输入值调用它们时，都返回相同的值。所有聚合函数都具有确定性仅在下列项中聚合函数允许作为表达式使用：
???
SELECT语句的选择列表（子查询或外部查询）。COMPUTE或COMPUTEBY子句。HAVING子句。
例：查询出最高分的和最高分USEeducGOSELECTsid,gradeFROMscwheregrade=(selectmax(grade)fromsc)
想想为啥用子查询。2.日期时间函数日期时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。下表列出日期和时间函数以及它们的确定性属性。
(1)DATEADD()：在向指定日期加上一段时间的基础上，返回新的datetime值语法：DATEADD(datepart,number,date)参数说明：参数说明：
datepart
是规定应向日期的哪一部分返回新值的参数。下表列出了Microsoft?SQLServer?识别的日期部分和缩写
number
是用来增加datepart的值。如果指定一个不是整数的值，则将废弃此值的小数部分。
date
是返回datetime或smalldatetime值或日期格式字符串的表达式。DECLARE@OLDTimedatetime--定义日期时间型数据
SET@OLDTime='12-02-200406:30pm'SELECTDATEADD(hh,4,@OldTime)
(2)DATEDIFF()：两时间之差语法DATEDIFF(datepart,startdate,enddate)参数
datepart
是规定了应在日期的哪一部分计算差额的参数。下表列出了Microsoft?SQLServer?识别的日期部分和缩写。
DECLARE@FirstTimedatetime,@SecondTimedatetimeSET@FirstTime='03-24-20066:30pm'SET@SecondTime='03-24-20066:33pm'SELECTDATEDIFF(ms,@FirstTime,@SecondTime)astime1--第一个参数表示毫秒
(3)DATENAME()：返回年月日星期等字符串。语法DATENAME(datepart,date)参数
datepart
是指定应返回的日期部分的参数。下表列出了Microsoft?SQLServer?识别的日期部分和缩写。
DECLARE@StatementDatedatetimeSET@StatementDate='2006-3-143:00PM'SELECTDATENAME(dw,@StatementDate)
3.字符函数这些函数对字符串输入值执行操作，返回字符串或数字值。
(1)ASCII()返回字符表达式最左端字符的ASCII代码值。DECLARE@StringTestchar(10)SET@StringTest=ASCII('RobinSELECT@StringTest')
(2)CHAR()将intASCII代码转换为字符的字符串函数。DECLARE@StringTestchar(10)SET@StringTest=ASCII('Robin')
SELECTCHAR(@StringTest)
(3)LEFT()返回从字符串左边开始指定个数的字符。
DECLARE@StringTestchar(10)SET@StringTest='Robin'
SELECTLEFT(@StringTest,3)
(4)LOWER()将大写字符数据转换为小写字符数据后返回字符表达式
DECLARE@StringTestchar(10)SET@StringTest='Robin'
SELECTLOWER(LEFT(@StringTest,3))
(5)LTRIM()删除起始空格后返回字符表达式
DECLARE@StringTestchar(10)SET@StringTest='Robin'
SELECT'Start-'+LTRIM(@StringTest),'Start-'+@StringTest
(6)RIGHT()返回字符串中从右边开始指定个数的integer_expression字符。
DECLARE@StringTestchar(10)SET@StringTest='Robin'
SELECTRIGHT(@StringTest,3)
(7)RTRIM()截断所有尾随空格后返回一个字符串。
DECLARE@StringTestchar(10)SET@StringTest='Robin'SELECT@StringTest+'-End',RTRIM(@StringTest)+'-End'
(8)STR()由数字数据转换来的字符数据。SELECT‘A’+82
SELECT'A'+STR(82)
SELECT'A'+LTRIM(STR(82))
(9)SUBSTRING()求子串函数
DECLARE@StringTestchar(10)SET@StringTest='Robin'
SELECTSUBSTRING(@StringTest,3,LEN(@StringTest))
(10)UPPER()返回将小写字符数据转换为大写的字符表达式。
DECLARE@StringTestchar(10)SET@StringTest='RobinSELECTUPPER(@StringTest)'
4.空值置换函数ISNULL（空值，指定的空值），用指定的值代替空值。USELibraryGOSELECTLendnum,ISNULL(Lendnum,0)AS空值置换FROMReaderWHEREISNULL(Lendnum,0)=0查询结果：
6.2.5运算符SQLServer2005的运算符和其他高级语言类似，用于指定要在一个或多个表达式中执行的操作，将变量、常量和函数连接起来。
优先级123456789运算符类别一元运算符算术运算符算术字符串运算符比较运算符按位运算符逻辑运算符逻辑运算符逻辑运算符赋值运算符所包含运算符+（正）、-（负）、~（取反）*（乖）、/（除）、%（取模）+（加）、-（减）、+（连接）=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（或!=不等于）、!<（不小于）、!>（不大于）&;（位与）、|（位或）、^（位异或）not（非）and（与）all（所有）、any（任意一个）、between（两者之间）、exists（存在）、in（在范围内）、like（匹配）、or（或）、some（任意一个）=（赋值）
6.3批处理与脚本
6.3.1批处理？1概念批处理是指包含一条或多条T-SQL语句的语句组，被一次性的执行。是作为一个单元发出的批处理一个和多个SQL语句的集合。SQLServer将批处理编译成一个可执行单元，称为执行计划。批中如果某处发生编译错误，整个执行计划都无法执行。1.批处理：指包含一条或多条T-SQL语句的语句组，这组语句从应用程序一次性地发送到SQLserver服务器执行。2.执行单元：SQLserver服务器将批处理语句编译成一个可执行单元，这种单元称为执行单元。3.若批处理中的某条语句编译出错，则无法执行。若运行出错，则视情况而定。4.书写批处理时,GO语句作为批处理命令的结束标志，当编译器读取到GO语句时，会把GO语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身不是T-SQL语句的的组成部分，只是一个表示批处理结束的前端指令。2批处理有以下规则：批处理有以下规则有以下规则：(1).createdefault,createrule,createtrigger,createprocedure和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批处理的结束和推出批处理结束语句：GO作为批处理的结束标志。也就是说当编译器执行到GO时会把GO之前的所有语句当作一个批处理来执行。GO不是T-SQL语句。GO命令和T-SQL语句不可在同一行，在批处理中的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部（用户定义）变量的作用域限制在一个批处理中，不可在GO命令后引用。在联机帮助里，解释为在联机帮助里，GO解释SignalstheendofabatchofTransact-SQLstatementstotheMicrosoft?SQLServer?utilities.就是一个语句的结束信号。也可以理解为一个分段执行命令（有些
地方如此解释，不太好）。
GOisnotaTransact-SQLstatement;itisacommandrecognizedbytheosqlandisqlutilities
andSQLQueryAnalyzerEXECUTE功能：执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持Transact-SQL批处理内的字符串的执行联机帮助里解释为ExecutesacommandstringorcharacterstringwithinaTransact-SQLbatch,oroneofthefollowingmodules:systemstoredprocedure,user-definedstoredprocedure,scalar-valueduser-definedfunction,orextendedstoredprocedure.批处理退出语句：RETURN[整型表达式]无条件中止查询、存储过程或批处理的执行。存储过程或批处理不执行位于RETURN之后的语句。当存储过程使用该语句，则可用该语句指定返回给调用应用程序、批处理或过程的整数值。若return语句未指定值，则存储过程的返回值是0。注意：当用于存储过程时，RETURN不能返回空值。例：返回状态，该过程检查在SC表中是否存在选修了80012课程的学生。存在则返回1，不存在返回2
createprocedurechecksid@paramchar(20)asif(selectsidfromscwherecid=@param)='bj10001'return1elsereturn2
6.3.2脚本1什么是脚本？脚本是存储在文件中一系列T-SQL语句。可包含一个或多个批处理，GO作为批处理结束语句，如果脚本中无GO语句，则作为单个批处理。脚本文件扩展名为.sql
6.4流程控制语句
T-SQL语言支持基本的流控制逻辑，它允许按照给定的某种条件执行程序流和分支，T-SQL提供的控制流有：IF…ELSE分支，CASE多重分支，WHILE循环结构，GOTO语句，WAITFOR语句和RETURN语句。6.4.1IF…ELSE语句制定T-SQL语句的执行条件。如果满足条件，则在IF关键字及其条件之后执行T-SQL语句：布尔表达式返回TRUE。可选的ELSE关键字引入另一个T-SQL语句，当不满足IF条件时就执行该语句：布尔表达式返回FALSE。
语法：IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]例6-5：IF查询课程中有没有计算机课。
USEeducGO--如果课程中有计算机课程，统计其数量，否则显示没有计算机课程IFexists(SELECT*FROMcourseWHEREcnamelike'计算机%')SELECTCOUNT(*)AS计算机课程数量FROMcourseWHEREcnamelike'计算机%'ELSEPRINT'数据库中没有计算机课程'
/*条件表达式*//*条件表达式为TRUE时执行*/
/*条件表达式为FALSE时执行*/
执行结果：
例6-6：嵌套IF课程查询
USEeducGOIFexists(SELECT*FROMcourseWHEREcname='计算机组成原理')SELECTCOUNT(*)AS计算机组成原理FROMcourseWHEREcname='计算机组成原理'ELSEIFexists(SELECT*FROMcourseWHEREcname='编译原理')SELECTCOUNT(*)AS编译原理FROMcourseWHEREcname='编译原理'ELSEPRINT'计算机组成原理和编译原理都没开！'
执行结果：
在实际程序中，IF…ELSE语句中不止包含一条语句，而是一组的SQL语句。为了可以一次执行一组SQL语句，这时就需要使用BEGIN…END语句将多条语句封闭起来。其语法格式为：BEGIN
{sql_statement|statement_block}END说明：BEGIN...END语句块允许嵌套。例6-7：BEGINGEND图书查询
USEeducGO
/*语句块*/
IFexists(SELECT*FROMcourseWHEREcname='计算机组成原理')beginSELECTCOUNT(*)AS计算机组成原理FROMcourseWHEREcname='计算机组成原理'endELSEbeginIFexists(SELECT*FROMcourseWHEREcname='编译原理')SELECTCOUNT(*)AS编译原理FROMcourseWHEREcname='编译原理'ELSEPRINT'计算机组成原理和编译原理都没开！'End
6.4.2WHILE语句设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。语法：WHILE逻辑表达式BeginT-SQL语句组[break]/*终止整个语句的执行*/[continue]/*结束一次循环体的执行*/END备注：如果嵌套了两个或多个WHILE循环，则内层的BREAK将退出到下一个外层循环。将首先运行内层循环结束之后的所有语句，然后重新开始下一个外层循环。例6-8：一个小循环程序
DECLARE@Xint
SET@X=0WHILE@x<3BEGINSET@x=@X+1PRINT'x='+convert(char(1),@x)--类型转换函数convertEND
执行结果：x=1x=2x=3BREAK语句退出while或if…else语句中最内层的循环。退出本次循环continue语句退出本层循环。6.4.3GOTO语句GOTO语句将执行语句无条件跳转到标签处，并从标签位置继续处理。GOTO语句和标签可在过程、批处理或语句块中的任何位置使用。其语法格式为：GOTOlabel6.4.4WAITFOR语句WAITFOR语句，称为延迟语句。就是暂停执行一个指定的时间间隔或者到一个指定的时间。其语法格式为：WAITFOR{DELAY'time_to_pass'|TIME'time_to_execute'}注意：执行WAITFOR语句时，事务正在运行，并且其他请求不能在同一事务下运行。WAITFOR不更改查询的语义。如果查询不能返回任何行，WAITFOR将一直等待，或等到满足TIMEOUT条件（如果已指定）。'time'：要等待的时间。可以按datetime数据可接受的格式指定time，也可用局部变量指定此参数。不能指定日期，只能指定时间。例6-9：延迟30秒执行查询。
USEeducGOWAITFORDELAY'00:00:30'
/*设定等待时间*//*设定等待到某一时刻*/
SELECT
*FROM
student
执行结果：
例6-10：在时刻21:20:00执行查询。USEeducGOWAITFORTIME'21:20:00'SELECT*FROMstudent
执行结果：（用合适的时刻，上机体会）
6.5CASE表达式
计算条件列表并返回多个可能结果表达式之一。CASE具有两种格式：简单CASE表达式将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索表达式计算一组逻辑表达式以确定结果。两种格式都支持可选的ELSE参数。语法：1.简单式CASE表达式WHEN表达式的值1THEN返回表达式1WHEN表达式的值2THEN返回表达式2…ELSE返回表达式nEND例6-11：显示学生选课的数量。
USEeducGOselectsid,'课程数量'=casecount(*)when1then'选修了一门课'when2then'选修了两门课'when3then'选修了三门课'
endfromscgroupbysid
执行结果：
2.搜索式CASEWHEN逻辑表达式1THEN返回表达式1WHEN逻辑表达式2THEN返回表达式2…ELSE返回表达式nEND例6-12：上例
USEeducGOselectsid,count(*)as数量,课程数量=casewhencount(*)=1then'选修了一门课'whencount(*)=2then'选修了两门课'whencount(*)=3then'选修了三门课'endfromscgroupbysid
6.6自定义函数User-DefinedFunction(UDF)
6.6.1概述1什么是UDF？用户定义函数（UDF）是执行计算并返回一个值（标量值或表）的一段程序。MicrosoftSQLServer2000引入了UDF，你可以用T-SQL编写UDF并应用于查询、计算列（computedcolumn）和约束。SQLServer2005又引入了公共语言运行库（commonlanguageruntimeCLR）集成，你可以使用任意.NET语言编写UDF及其他程序和对象。
2UDF分类SQLServer所支持的两种UDF：（内返回单个值的标量值UDF和返回一个表的表值UDF返回一个表的表值联和多语句）。标量型函数(Scalarfunction)返回一个确定类型的标量。函数体用BEGIN-END括起来。内联表值型函数(inlinetable–valuedfunction)以表的形式返回一个返回值。即它返回的是一个表。没有用BEGIN-END括起来的函数体。其返回的表由一个位于return子句中的Select命令从数据库中得到的。多语句表值型函数(Multi-sataementtable-valuedfunction)可以看作是标量型和内联表值型函数的结合体。返回值是一个表，但和标量函数一样有一个用Begin-End括起来的函数体，返回值表中的数据是由函数体中的语句插入的。3注意UDF可以嵌入到查询、约束和计算列中。定义UDF的代码不能影响函数范围之外的数据库状态，也就是说，UDF代码不能修改表中的数据或调用会产生副作用（sideeffect）的函数（例如，RAND）。UDF的代码只能创建表变量，不能创建或访问临时表，也不允许使用动态执行。在MicrosoftSQLServer2005系统中，可以分别使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION语句来实现用户定义函数的创建、修改和删除。在创建用户定义函数时，每个完全限定用户函数名称(schema_name.function_name)必须惟一。用户自定义函数不能用于执行一系列可以改变数据库状态的操作。6.6.2创建用户自定义函数1用T-SQL创建用户自定义函数用户自定义函数分为：标量型函数(Scalarfunction)内联表值型函数(inlinetable–valuedfunction)多语句表值型函数(Multi-sataementtable-valuedfunction)标量型函数：标量型函数：标量型函数返回一个确定类型的标量值，其返回值类型为除了text,ntext,image,cursor,timestampt和table类型外的其它数据类型。函数体语句定义在begin-end语句内，其中包含了可以返回值的Transact-SQL命令。语法：createfunction[owner_name]function_name
([{@parameter_name[as]scalar_parameter_data_type[=default]}[,n]])returnsscalar_return_data_type[with
[,n]][as]beginfunction_bodyreturnend其中：<1>.function_option有两个可选值：{encryption|schemabinding}encryption:加密选项，让SQLServer对系统表中有关createfunction的声明加密，以防止用户自定义函数作为SQLServer复制的一部分被发布。schemabinding，计划绑定选项。将用户自定义函数绑定到它所引用的数据库对象，则函数所涉及的的数据库对象从此将不能被删除或修改，除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。<2>.owner_name:指定用户自定义函数的所有者。<3>.function_name:指定用户自定义函数的名称。<4>.database_name.owner_name.function_name应是唯一的。<5>.@parameter_name:定义一个式多个参数的名称，一个函数最多可以定义1024个参数，每个参数前用@符号标明，参数的作用范围是整个函数，参数只能替代常量，不能替代表名，列名或其它数据库对象名称，用户自定义函数不支持输出参数。<6>.scalar_parameter_data_type:指定标量参数的数据类型，除了text,ntext,image,cursor,timestampt和table类型外的其它数据类型。<7>.scalar_return_data_type:指定标量返回值的数据类型，除了text,ntext,image,cursor,timestampt和table类型外的其它数据类型。<8>.scalar_expression:指定标量型用户自定义函数返回的标量值表达式。<9>.function_body:指定一系列的Transact_SQL语句它们决定了函数的返回值。例：创建学生补助计算函数
useeducgo
[scalar_expression]
--创建函数createfunction@per_salmoney)
studentwelfare(@hiredate
datetime,@today
datetime,
--hiredate表示雇佣期,today表示当前日期，par_wage表示每一年的工龄应得的工资额returnsmoneyassetbegin@studentwelfaremoney@studentwelfare=(year(@today)-year(@hiredate))*@per_saldeclare
return(@studentwelfare)end--结束函数定义go--调用函数selecteduc.dbo.studentwelfare('1999-7-1',getdate(),15)asstudent_welfare
注意：以上的函数体可简写为:asbegin
return((year(@today)-year(@hiredate))*@per_sal)end
说明：说明：DBO:DatabaseOwner,意思是数据库的所有者。具体含义将在数据库安全性中介绍。内嵌表值函数：内嵌表值函数：以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由begin-end语句括起来的函数体，其返回的表由一个位于return子句中的select命令段从数据库中筛选出来。内嵌表值型函数功能相当于一个参数化的视图。创建函数语法：createfunction[owner_name]function_name
([{@parameter_name[as]scalar_parameter_data_type[=default]}[,n]])returnstable
[with[,n]][as]return(select-stmt)其中：<1>.table:批定返回值为一个表。<2>.select-stmt:单个select语句，确定返回的表的数据。例：创建返回所有订购某类产品的公司信息函数
usegocreatefunctioncoursename(@cidvarchar(30))--productid表示产品代号returnstableasreturn(selectgo*fromcoursewherecid=@cid)educ
select*fromeduc.dbo.coursename('80012')
注意：因为是表值函数，返回的是一个表，因此调用时，要把该函数作为表来使用。多语句表值型函数：多语句表值型函数：可以看作标量型和内嵌表值型函数的结合体，它的返回值是一个表，但它和标量型函数一样有一个用begin-end语句括起来的函数体。语法：createfunction[owner_name]function_name
([{@parameter_name[as]scalar_parameter_data_type[=default]}[,n]])returns@return_variabletable
[with[,n]][as]
beginfunction_bodyreturnend其中：<1>.:({column_definition|table_constraint}[,n])<2>.@return_variable:一个table类型的变量，用于存储和累积返回的表中的数据行。例：输入一个学生查询选课情况。
usegocreatefunctionchoisecourse(@stnamevarchar(30))returns@choiseinfotable(char(10),学生char(20),所选课程号char(10),成绩numeric(5,1))asbegininsert@choiseinfoselectstudent.sid,sname,cid,gradefromstudent,scwherestudent.sid=sc.sidandstudent.sname=@stnamereturnendgo--下面调用该函数select*fromeduc.dbo.choisecourse('王国明')educ
2用SSMS创建用户自定义函数打开对象资源管理器－>打开数据库->打开可编程性－>打开函数－>右键点击“表值函数”或。“标量值函数”－>选择“新建内联表值函数”或“新建标量函数”
3调用自定义函数调用自定义函数（用户定义的函数）和调用内置函数方式基本相同，
??
当调用标量值函数时，必须加上“所有者”，通常是dbo。当调用表值函数时，可以只使用函数名称。
4.修改用户自定义函数alterfunction:此命令语法与createfunction相同，相当于重建。
5.删除用户自定义函数dropfunction{[owner_name]function_name}[,n]
例：删除用户自定义函数TotalAmountDROPFUNCTIONTotalAmount
6.7游标
一些概念6.7.1一些概念概念：游标概念：游标(cursor)是一种数据访问机制，它允许用户单独的访问数据行，而不是对整个行集进行操作。T-SQL游标类似于C语言指针。组成：组成：在SQLServer2005中游标主要包括以下两个部分：(1)游标结果集由定义游标的SELECT语句返回的行的集合。
(2)游标位置指向这个结果集中的某一行的指针。特点：在SQLServer2005中游标具有以下特点：特点游标返回一个完整的结果集，但允许程序设计语言只调用集合中的一行。允许定位在结果集中的特定行。从结果集的当前位置检索一行或多行支持对结果集中在当前位置的行进行数据修改。可以为其他用户对显示在结果集中的数据库数据所作的更改提供不同级别的可见性支持。
提供脚本、存储过程和触发器中使用的访问结果集中数据的T-SQL语句6.7.2操作游标在使用游标之前首先声明游标。操作游标操作游标（四个组成部分）游标(1).declare游标：声明游标(2).open游标：打开游标(3).从一个游标中fetch信息：读取游标中的数据(4).close或deallocate游标：释放游标一、声明游标主要内容：(1).游标名字(2).数据来源表和列(3).选取条件(4).属性仅读或可修改1.语法格式：declare游标名称[insensitive][scroll][local|global][forward_only|scroll]forSelect选择语句[for[read_only|update[of字段名称1，字段名称2，其中：1>.local|global指定该游标的作用域是局部的还是全局的。2>.如果把forward_only选择为forward_only,则游标只能从第一行滚动到最后一行。3>.scroll表明所有的提取操作，如first,last,prior,next,relative,absolute都可用。如不使用该保留字，那么只能进行next提取操作。Insensitive，定义一个游标，以创建由该游标使用的数据的临时副本（临时表）。4>.选择语句：是定义结果集的select语句，应该注意的是在游标中不能使用compute,compute5>.readbyforbrowseinto语句.]]]cursor
only:表明不允许游标内的数据被更新。
6>.update[of字段名1[,n]]:定义在游标中可被修改的列。例1：标准游标
declarefirstcurcursorforselectid,name,address,city,statefromtablename
例2：只读游标declarefirstcurcursorforselectid,name,address,city,atatefromtablename
forreadonly例3：更新游标declarefirstcurcursorforselectname,addressfromfortablenameupdate
二、打开游标1.在使用游标之前，必须打开游标，使用open命令。语法：open注意:1>.在打开游标时，如果游标声明语句中使用了insensitive保留字，则open产生一个临时表来存放结果集。如果在结果集中任何一行数据的大小超过SQLServer定义的最大行尺寸时，open命令将失败。2>.insensitive:表明SQLServer会将游标定义所选取出来的数据记录存放在一临时表内，建（立在tempdb数据库下）对该游标的操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取数据，即游标不会随着基本表内容的改变面改变，同时也不会通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新，删除都会反映到游标中。3>打开了游标，则可用@@cursor_rows全局变量来检索游标中的行数。游标名称
三、读取游标中的数据－fetch1.fetch命令取出游标中的数据当游标被成功打开以后就可以从游标中逐行地读取数据以时行相关处理。从游标中读取数据主要使用fetch命令。语法：fetch[[next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]from]cursor_name[into@variable_name1,@variable_name2注：1>.next:返回结果集中当前行的下一行，并增加当前行递增为返回行。如果fetchnext是第一次读取游标中数据，则返回结果集中的第一行而不是第二行。2>.prior:返回结果集中当前行的前一行，并将当前行递减为返回行。如果fetchprior是第一次读取游标中的数据，刚无数据记录返回，并把游标位置设为第一行。3>.first:返回游标中的第一行。4>.last:返回游标中的最后一行。5>.absolute{n|@nvar}:若N或@nvar为=0，则不返回行；如果N或@nvar为正数，则表示返回从游标头开始的第n行。如果N或@nvar为负数，则返回从游标最后一行数据算起的第N或@nvar行数据。若N或@nvar超过游标的数据子集范畴，则@@fetch_status返回-1。在该情况下，如果N或@nvar为负数，则再执行fetchnext命令会得到第一行数据；如果N或@nvar为正值，如执行fetchprior命令刚会得到最后一行数据。N或@nvar可以是固定值，也可以是smallint,tinyint或int类型的变量。6>.relative{N|@nvar}：若N或@nvar为=0，则返回当前行；若N或@nvar为正数，则读取游标当前位置起向后的第N或@nvar行数据。如果N或@nvar为负数，则返回游标当前位置起向前的第N或@nvar行数据。若N或@nvar超过游标的数据子集范畴，则@@fetch_status返回-1。在该情况下，如果N或@nvar为负数，则再执行fetchnext命令会得到第一行数据；如果N或@nvar为正值，如执行fetchprior命令刚会得到最后一行数据。N或@nvar可以是固定值，也可以是smallint,tinyint或int类型的变量。]
7>.into@variable_name[,n]：允许使用fetch命令读取的数据存放在多个变量中；在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中的数据列的数据类型相匹配。.检查游标状态2.检查游标状态@@fetch_status：全局变量，返回上次执行fetch命令的状态，在每次用fetch从游标中读取数据时，都应检查该变量以确定上次fetch操作是否成功，来决定如何进行下一步处理。@@fetch_status变量有三个不同返回值。0：表示成功取出了一行。－1表示未取到数据。游标位置超出结果集。－2表示返回的行已经不再是结果集的一个成员，这种情况只有在游标不是insensitive的情况下出现，即其它进程已删除了行或改变了游标打开的关键值。.编辑当前游标行3.编辑当前游标行通常，用游标来从基础表中检索数据，以实现对数据行处理，在修改游标中的数据，即进行定位更新或删除游标所包含的数据，所以必须执行另外的更新或删除命令，并在where子句中重新给定条件才能修改到该行数据，但是如果在声明游标时使用了forupdate语句那么就可以在update或delete命令中以wherecurrentof关键字直接修改或删除当前游标中所存储的数据，而不必使用where子句重新给出指定条件。当改变游标中数据时，这种变化会自动地影响到游标的基础表。但是如果在声明游标时选择了insensitive选项时，该游标中的但是如果在声明游标时选择了选项时，数据不能被修改。数据不能被修改。进行定位修改或删除游标中的数据的语法规则语法：进行定位修改或删除游标中的数据的语法规则语法：更新updatetable_namesetcolumn_name1={expression1|null(select_statement)}[,column_name2={expression2|null(select_statement)}]wherecurrentofcursor_name
删除deletewhere其中：1>.table_name：是update或delete的表名。fromtable_namecurrentofcursor_name
2>.column_name：uqdate的列名3>.cursor_name:游标名例1：该例子并不是一个完整的例子。--首先声明一个游标declareauthors_curscrollcursorforselect*fromauthorsforupdateofau_lname,au_fname--更新authors表中的au_lname和au_fname列updateauthorssetau_lname='china',au_fname='asia'wherecurrentofauthors_cur--删除authors表中的一行数据deletefromauthorswherecurrentofauthors_cur
注：以上的更新或删除操作总是在游标的当前位置当前位置例1：下面是一个完整的定位的例子。
useeducgodeclare@stunamechar(10)--定义游标学生信息_cursordeclare学生信息_curcursorlocalscrollforselectsnamefromstudent--打开游标open学生信息_cur--取游标中的数据whilebegin@@fetch_status=0/*循环开始，系统默认@@fetch_status的初始值是0*/
fetchnextfrom学生信息_curinto@stunameprint@stunameif@@fetch_status=-1fetchabsolute1from学生信息_curinto@stunameendclose学生信息_curdeallocate学生信息_cur
死循环了。同学们运行体会一下。例2
useeducgodeclare@stuname4char(10)declare@iint--定义游标学生信息_cursordeclare学生信息_cur4cursorlocalscrollforselectsnamefromstudent--打开游标open学生信息_cur4--取游标中的数据set@i=0while@i<=10beginfetchnextfrom学生信息_cur4into@stuname4print@iprint@stuname4set@i=@i+1endprint@@fetch_status
执行结果：例3useeduc
godeclare@stunamechar(10)--定义游标学生信息_cursordeclare学生信息_curcursorlocalscroll
forselectsnamefromstudent--打开游标open学生信息_cur--取游标中的数据whilebegin@@fetch_status=0/*循环开始，系统默认@@fetch_status的初始值是*/
fetchnextfrom学生信息_curinto@stunameprint@stuname--if@@fetch_status=-1--fetchabsolute1from学生信息_curinto@stunameendclose学生信息_curdeallocate学生信息_cur
执行结果：
例4
useeducgodeclare@stuname1char(10)declare@stuname2char(10)declare@stuname3char(10)declare@stuname4char(10)--定义游标学生信息_cursordeclare学生信息_cur1cursorlocalscrollforselectsnamefromstudent--打开游标open学生信息_cur1fetchabsolute8from学生信息_cur1into@stuname1print@@fetch_statusprint@stuname1print'0000000000000000000000000000000000'fetchnextfrom学生信息_cur1into@stuname2print@stuname2print@@fetch_status
print'1111111111111111111111111111111111'fetchnextfrom学生信息_cur1into@stuname1print@stuname1print@@fetch_statusprint'2222222222222222222222222222222222'fetchnextfrom学生信息_cur1into@stuname3print@stuname3print@@fetch_statusprint'3333333333333333333333333333333333'fetchrelative-1from学生信息_cur1into@stuname3print@stuname3print@@fetch_statusprint'4444444444444444444444444444444444'fetchabsolute1from学生信息_cur1into@stuname4print@stuname4--close学生信息_cur1--deallocate学生信息_cur1
执行结果
说明：从上面的例子可以看出，在表头部还有一个开始标记，尾部有一个结束标记。说明：从上面的例子可以看出，在表头部还有一个开始标记，尾部有一个结束标记。记录指针为定位器。指向记录尾部的结束标记时，并不传给变量，针为定位器。当fetchnext指向记录尾部的结束标记时，NULL并不传给变量，因此变量中仍是最后一条记录的值，这在例子中可以看到。仍是最后一条记录的值，这在例子中可以看到。最后一条记录的值四、释放游标1.关闭游标使用close命令关闭游标，在处理完游标中数据之后，发布关闭游标来释放数据结果集和定位于数据记录上的锁，close语句关闭游标但不释放游标占用的数据结构。如果准备在随后的使用中再次打开游标，则应使用open命令。语法：close游标名称2.释放游标
在使用游标时各种针对游标的操作或者引用游标各或者引用指向游标的游标变量，当close命令关闭游标时并没有释放游标占用的数据结构。因此常使用deallocate命令删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。语法：deallocate游标名称
6.8事务
6.8.1概念为什么引入事务？为什么引入事务？一般情况下，同一时刻很少只有一个用户访问数据库。在同一时刻可能会有多个不同类型的用户访问数据库，这样就必然会发生一些冲突。例如，一个用户查看数据表中的数据，作一些基本的查询操作，另一个用户在第一个用户查询期间更新了表，那么就造成第一个用户第二次查询会看到不同的数据，造成第一次查询失败。因此SQLServer引入了事务来解决这类问题。什么是事务？什么是事务？事务是指一个不可分割的单元的工作，要么全做，要么全不做。一个事务由一个或多个完成一组相关行为的SQL语句组成，每个SLQ语句都用来完成特定的任务。相当于原语事务必须具备四个属性：事务必须具备四个属性：原子性：是指一个事务中要么操作都执行，要么都不执行。一致性：是指当事务完成时，所有的数据必须都具有一致的状态。独立性：是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务看到的数据要
么是另一个事务修改之前的状态，要么是修改之后的状态，本质上是一种串行性。
持久性：是指当一个事务提交完成，则事务所完成的工作对数据库的影响是永久保存。事务工作原理：事务工作原理：只有完成了事务才提交，否则数据要恢复到原始状态，只有完成事务提交，才能表示当前事务执行完毕。其实就是，事务在对数据库进行操作时，数据库中被事务操作的数据是被锁定的，只有当正在操作的事务提交后，才能再供其他事务操作。使用事务的考虑：使用事务的考虑：原则上事务尽可能短、尽量避免事务嵌套。6.8.2事务控制语句1BEGINTRANSACTION[事务名]：开始一个事务
2.COMMIT提交：完成一个事务单元
3.ROLLBACK[TRAN|TRANSACTION][事务名｜保存点名]即抛弃自最近一条begintransaction语句之后的所有修改。4.SAVE
回滚：回滚一个事务单元，
［TRANSACTION］保存位置：设置保存点，允许部分提交一部分提交一
个事务，同时仍能退回这个事务的其余部分。个事务例：定义一个简单的事务并设置保存点
begintransactionexampletransuseeducgoupdatestudentsetsex='女'wheresid='bj10001'gosavetransactionchangeupdatestudentsetsname='李小丽'wheresid='bj10001'gorollbacktransactionchangeprint'programgoon!'committransaction/*该例有两个更新操作，第一次更新完成后设置一个保存点；第二次更新完成后，程序执行回滚到保存点，使得第二次更新取消。但第一次更新有效；程序继续从回滚处执行，打印一行字*/--回滚到保存点--设置保存点--第二次更新--第一次更新--开始一个事务
原来bj10001是男生，现在被改为女生
同学们再执行下面的脚本。看看结果，思考一下用法
begintransactionexampletransuseeducgoupdatestudentsetsex='男'wheresid='bj10001'gosavetransactionchangeupdatestudent--设置保存点--第二次更新--第一次更新--开始一个事务
setsname='刘伟箭'wheresid='bj10001'go--rollbacktransactionchangeprint'programgoon!'committransactionbegintransactionexampletransuseeducgoupdatestudentsetsex='女'wheresid='bj10001'gosavetransactionchangeupdatestudentsetsname='王小丽'wheresid='bj10001'gorollbacktransactionexampletransprint'programgoon!'--回滚到保存点--设置保存点--第二次更新--第一次更新--开始一个事务--回滚到保存点
