SQL-Server实用数据库技术课件第8章

课程回顾数据查询在数据库中的编程本章综述Transact-SQL(简称T-SQL)是SQLServer2008提供的一种交互式查询语言。使用Transact-SQL编写应用程序可以完成所有数据库管理工作。对用户来说，T-SQL语言是唯一可以和SQLServer2008的数据库管理系统进行交互的语言。任何应用程序，只要向数据库管理系统发出命令以获得数据库管理系统的响应，最终都必须体现为以T-SQL语句为表现形式的指令。函数丰富了数据库的信息处理功能，而游标则提供了对数据库中的数据灵活处理的方式，可实现对数据信息进行复杂处理的功能。本章结构8.1Transact–SQL语言基础8.2变量与运算符8.3流程控制语句8.4系统内置函数8.5用户自定义函数8.6游标的创建与使用技能展示：了解T-SQL语言的基本元素掌握T-SQL流程控制语句熟悉T-SQL系统内置函数熟练掌握用户自定义函数的创建与使用了解游标的创建与使用8.1Transact–SQL语言基础SQL与T-SQL的不同T-SQL的分类标识符的命名规则批处理的概念注释的使用方法8.1.1SQL与T-SQL概述

8.1.2T-SQL语言的基础知识

开封大学信息工程学院8.1.1SQL与T-SQL概述SQL语言：结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。T-SQL语言：T-SQL语言是微软在SQL语言基础上发展起来的扩充语言，除了提供标准的SQL命令之外，还对SQL语言做了许多补充，提供了如变量声明、流程控制语言、功能函数等功能。开封大学信息工程学院T-SQL语言分类：数据定义语言(DDL,DataDefinitionLanguage)数据操纵语言(DML,DataManipulationLanguage)数据控制语言(DCL,DataControlLanguage)T-SQL增加的语言元素数据定义语言(DDL)的主要语句及功能数据定义语言主要用于对数据库以及数据库中的各种的对象进行创建、删除、修改等操作语句功能CREATE创建数据库或数据库对象ALTER对数据库或数据库对象进行修改DROP删除数据库或数据库对象数据操纵语言(DML)主要语句及功能数据操作语言主要用于操作数据库中的各种对象，查询、添加、修改和删除数据中的数据。语句功能SELECT从表或视图中检索数据INSERT将数据插入到表或视图中UPDATE修改表或视图中的数据DELETE从表或视图删除数据数据控制语言(DCL)主要语句及功能数据控制语言就是用来对数据库进行安全管理的，它包括用来设置或更改数据库用户或角色权限的语句，以确保数据库中的数据和操作不被未经授权的用户使用和执行语句功能GRANT授予权限REVOKE收回权限DENY收回权限、并禁止从其他角色继承许可权限T-SQL增加的语言元素T-SQL增加的语言元素不是ANSISQL所包含的内容，而是微软为了用户编程的方便而增加的语言元素，这些语言元素包括变量、元素符、函数、流程控制语句、注释等。8.1.2T-SQL语言的基础知识标识符标识符是指用户在SQLServer中定义的服务器、数据库、数据库对象（如表、视图、索引、触发器等）、变量和列名等对象名称。分类：常规标识符分隔标识符 常规标识符的命名规则标识符长度可以为1-128个字符。但是，对于本地临时表，标识符最多只能有116个字符。标识符的首字符必须为Unicode3.2标准所定义的字母或下划线(_)、at符号(@)、数字符号(#)。标识符第一个字符的后续字符可以为Unicode3.2标准所定义的字母、数字或@、#、$、_符号。标识符内不能嵌入空格或其他特殊字符。标识符不能与SQLServer中的保留关键字同名。【注意】在SQLServer中，某些位于标识符开头位置的符号具有特殊意义。为了避免混淆，不应使用以这些特殊意义开头的名称。以“@”符号开头的标识符表示局部变量或参数。以“#”符号开头的标识符表示临时表或过程。以“##”符号开头的标识符表示全局临时对象。以“@@”符号开头的标识符为某些T-SQL函数的名称。分隔标识符分隔标识符允许在标识符中使用SQLServer保留关键字或常规标识符中不允许使用的一些特殊字符，这是由双引号（”）和方括号（[]）进行分隔的标识符。批处理批处理是同时从应用程序发送到SQLServer并得以执行的一组单条或多条T-SQL语句，用GO语句作为批处理的结束标志。若没有GO语句，默认所有的语句属于一个批处理。SQLServer的程序发送和编译以批处理为一个程序执行单元。如果一个批处理中任何一条语句有语法错误（例如引用不存在的对象），则整个批处理都不能执行。但是通过编译的批处理语句，若其中的某条语句运行时发生错误（例如违反约束），则错误语句之前所执行的语句正常执行（批处理位于事务中并且错误导致事务回滚的情况例外）。批处理的使用规则CREATEDEFAULT创建默认值、CREATERULE创建规则、CREATEVIEW创建视图、CREATEPROCEDURE创建存储过程、CREATETRIGGER创建触发器对象等，都必须单独作为一个批处理，不能与其他语句放在一个批处理中。CHECK检查约束不能在同一个批处理中既定义又使用。规则和默认不能在同一个批处理中既绑定到列又被使用。不能在修改表中的一个字段后，立即在同一个批处理中引用该字段。局部变量的作用域限制在一个批处理中，不能在GO语句之后再次引用该变量。【例8-1】查看设备表中所有的空调的信息，并创建视图“view_cond_inf”，然后查询“view_cond_inf”视图中的信息。USEbuildGOCREATEVIEWview_cond_infASSELECT*FROMT_FacilityWHEREFacilityNamelike'%空调%'GOSELECT*FROMview_cond_infGO注释单行注释：--注释内容多行注释：/*注释内容*/【例8-2】对完成【例8-1】的批处理语句添加注释，进行说明。/*以下内容完成

view_cond_inf视图的创建及查询操作*/USEbuild--打开build数据库GO--创建view_cond_inf视图CREATEVIEWview_cond_infASSELECT*FROMT_FacilityWHEREFacilityNamelike'%空调%'GO--查询view_cond_inf视图中的所有信息SELECT*FROMview_cond_infGO8.2变量与运算符

局部变量的声明与使用全局变量的使用常用运算符的使用运算符的优先级8.2.1变量

8.2.2运算符

开封大学信息工程学院8.2.1变量局部变量与全局变量局部变量(用户自定义变量)形式：@***作用：用于临时存储各种类型的数据全局变量形式：@@***作用：有系统提供的有确定值的变量，用户不能定义也不能修改全局变量局部变量声明语法格式：DECLARE{@变量名数据类型[(长度)]}[，…n]赋值语法格式：SET@局部变量=表达式SELECT{@局部变量=表达式}[，…n]显示语法格式：PRINT表达式SELECT表达式[，…n]作用域：局部变量的作用域是在一个批处理、一个存储过程或一个触发器内，其生命周期从定义开始到它遇到的第一个GO语句或者到存储过程、触发器的结尾结束，即局部变量只在当前的批处理、存储过程、触发器中有效【注意】局部变量必须以“@”开头。变量名必须符合标识符的命名规则。变量的数据类型可以是系统类型，也可以是用户自定义类型，但不允许是text、ntext和image类型。系统固定长度的数据类型不需要指定长度。例如：DECLARE@namechar（6）--定义@name长度为6的字符型变量DECLARE@iint，@addressvarchar（30）--定义@i整型变量和@address长度为30的变长字符型变量【注意】SET命令只能一次给一个变量赋值，而SELECT命令一次可以给多个变量赋值，两种格式可以通用，建议首选SET。用SET命令时，表达式中可以包含SELECT语句子查询，但只能是集合函数返回的单值，且必须用圆括号括起来。SELECT也可以直接使用查询的单值结果给局部变量赋值。如：SELECT@局部变量=表达式或字段名FROM表名WHERE条件【注意】使用PRINT只能有一个表达式，其值在查询后的“消息”窗口显示。使用SELECT相当于进行无数据源检索，可以有多个表达式，其结果在查询后的“网格”子窗口显示。在一个脚本中，最好不要混合使用这两种输出方式，因为这样的话需要切换两个窗口来查看数据结果。【例8-3】查询设备表，使用@price存放设备表中单价最高的物品价格，使用@name存放单价最高的设备的设备名，把这两个局部变量值输出。USEbuildDECLARE@namenvarchar(8),@priceintSET@price=(selectMAX(Price)FROMT_Facility)SELECT@name=FacilityNameFROMT_FacilityWHEREPrice=@pricePRINT'最高单价：'PRINT@pricePRINT'单价最高的设备名：'+@name方法一：USEbuildDECLARE@namenvarchar(8),@priceintSET@price=(selectMAX(Price)FROMT_Facility)SELECT@name=FacilityNameFROMT_FacilityWHEREPrice=@priceSELECT@priceAS最高单价,@nameAS设备名方法二：【例8-4】请找出下面程序中的错误DECLARE@iintSET@i=100PRINT@iGOPRINT@iGO全局变量特点：全局变量是由系统提供的有确定值的变量，用户不能自己定义全局变量，也不能用SET语句来修改全局变量的值格式：系统全局变量都是以@@开头的全局变量含义@@VERSION返回运行SQLServer数据库的服务器名称@@LANGUAGE返回当前所用语言的名称@@ROWCOUNT返回受前一条SQL语句影响的行数@@ERROR返回执行的上一个T-SQL语句的错误号【例8-5】使用全局变量@@VERSION查看当前数据库的版本信息。运算符算术运算符比较运算符逻辑运算符位运算符字符串连接运算符算术运算符算术运算符用于对表达式进行数学运算，表达式中的各项可以是任何数值数据类型。运算符含义+(加)加法-(减)减法*(乘)乘法/(除)除法%(取模)求模，返回一个除法运算的整数余数比较运算符比较运算符用于对两个表达式进行比较，可以用于除了text、ntext或image数据类型的所有表达式，比较结果是布尔数据类型，可取以下三个逻辑值之一：TRUE：真，条件成立。FALSE：假，条件不成立。UNKNOWN：不确定，是某个数据与NULL比较的结果。运算符含义=等于<小于>大于<=小于等于>=大于等于<>不等于!=不等于(非ISO标准)!<不小于(非ISO标准)!>不大于(非ISO标准)逻辑运算符逻辑运算符用于对某些条件进行测试，以获得真实情况，返回值为TRUE或FALSE。运算符含义NOT逻辑非，对任何布尔表达式的结果取反AND逻辑与，如果两个布尔表达式都为TRUE，则结果为TRUEOR逻辑或，如果两个布尔表达式中任何一个为TRUE，则结果为TRUEALL如果一组的比较都为TRUE，则结果为TRUEANY如果一组的比较中任何一个返回TRUE，则结果为TRUEBETWEEN如果操作数在指定范围内容，则结果为TRUEEXISTS如果子查询包含一些行，则结果为TRUEIN如果操作数等于表达式列表中的一个，则结果为TRUELIKE如果操作数与一种模式相匹配，则结果为TRUESOME如果在一组比较中，有些为TRUE，则结果为TRUEANY、ALL、SOMEANY、ALL、SOME运算符主要用于比较特定值和结果集中的所有值，一般情况下，要与比较运算符配合使用。其中ANY与SOME的作用相同。语法如下：例如：@i>ALL(SELECTPriceFROMT_Facility)@i>ANY(SELECTPriceFROMT_Facility)特定值{=|>|<|>=|<=|<>|…}{ANY|ALL|SOME}(SELECT语句)ININ运算符主要用于测试特定值是否在子查询的结果集或给定的一些值之中，只要与其中任何一个值相等，即返回TRUE。语法如下：例如：@iIN(SELECTPriceFROMT_Facility)@iNOTIN(5,2,3)特定值[NOT]IN(SELECT语句|值[,…n])BETWEENBETWEEN主要用于判断表达式的值是否在某个范围内，若在指定范围内结果为TRUE。语法如下：例如：SELECT*FROMT_FacilityWHEREPriceBETWEEN1000AND5000[NOT]BETWEEN表达式1AND表达式2LIKELIKE用于测试特定字符串是否与制定模式相匹配，若匹配，返回TRUE。模式可以包含普通字符和通配字符。语法如下：特定字符串[NOT]LIKE模式[ESCAPEescape_character]通配符含义%代表0或多个任意字符_(下划线)代表单个任意字符[]指定范围（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何单个字符[^]指定不属于范围（如[^a-f]、[^0-9]）或集合（如[^abcdef]）中的任何单个字符例如：select*fromD_DepartmentwhereDepartmentlike'%学院'

EXISTSEXISTS运算符主要用于测试一个子查询的结果集是否不为空，若结果集不为空，返回TRUE。语法如下：[NOT]EXISTS（SELECT语句）位运算符字符串连接运算符：+运算符运算规则&（与）两个位均为1时，结果为1，否则为0|（或）只要一个位为1时，结果为1，否则为0^（异或）两个位值不同时，结果为1，否则为0运算符优先级优先级运算符1+（正）、-（负）、~（位非）2*（乘）、/（除）、%（取模）3+（加）、-（减）、+（连接）4=、>、<、>=、<=、<>、!=、!>、!<5^（位异或）、&（位与）、|（位或）6NOT7AND8ALL、ANY、BETWEEN、IN、LIKE、OR、SOME9=（赋值）8.3流程控制语句8.3.1BEGIN…END语句

8.3.2IF…ELSE语句8.3.3CASE…END语句

顺序结构

分支结构

开封大学信息工程学院8.3.4WHILE语句

循环结构

8.3.1BEGIN…END语句语法：作用：不论多少个语句，放在BEGIN…END中间就构成一个独立的语句块，被系统当作一个整体单元来处理BEGIN

语句1

语句2…END8.3.2IF…ELSE条件语句语法：执行过程：先判断条件表达式的值，若为TRUE，则执行IF后面的语句块，若为FALSE，则执行ELSE后面的语句，没有ELSE子句时则顺序执行后续语句。IF逻辑条件表达式语句块1[ELSE

语句块2]【注意】条件表达式中可以包含SELECT子查询，但必须用圆括号括起来语句块1、语句块2可以是单个SQL语句，如果有两个以上语句必须放在BEGIN…END语句块中【例8-6】查询部门表中部门ID为1101的部门，若查到该部门则显示其名称，否则显示“没有该部门”。USEbuildGODECLARE@nameNVARCHAR(15)IFEXISTS(SELECT*FROMD_DepartmentWHEREDepartmentNO='1101')BEGINSELECT@name=DepartmentFROMD_DepartmentWHEREDepartmentNO='1101'PRINT'---1101部门的信息如下---'PRINT'部门名称：'+@nameENDELSEPRINT'没有该部门'

8.3.3CASE…END语句CASE…END语句根据不同的条件返回不同的值，它提供了比IF…ELSE语句更多的选择和判断机会，使用它可以在实现多分支判断时避免复杂的IF…ELSE语句嵌套。CASE…END语句有两种格式简单CASE…END语句搜索CASE…END语句。简单CASE…END表达式格式：功能：根据测试表达式的值得到一个对应值CASE测试表达式

WHEN常量值1THEN结果表达式1[{WHEN常量值2THEN结果表达式2}[…n]][ELSE结果表达式n]END执行过程：先计算测试表达式的值，将测试表达式的值按顺序依次与WHEN指定的各个常量值进行比较：如果找到了第一个相等的常量值，则整个CASE表达式取相应THEN指定的结果表达式的值，之后不再比较，跳出CASE…END语句。如果找不到相等的常量值，则取ELSE指定的结果表示n的值。如果找不到相等的常量值也没有使用ELSE，则返回NULL。【例8-7】查询设备表中设备的主要信息，把设备表中的设备类别编号转换为设备类别名称显示出来。SELECTFacilityName,Category=CASEClassificationWHEN1THEN'办公用品'WHEN2THEN'教室用品'WHEN3THEN'宿舍用品'END,--类别字段到此结束，后面还有其他字段，逗号不能丢

Model,Price,QuantityFROMT_Facility搜索CASE…END表达式格式：功能：根据某个条件得到一个对应值CASEWHEN条件表达式1THEN结果表达式1[{WHEN条件表达式2THEN结果表达式2}[…n]][ELSE结果表达式n]END执行过程：按顺序依次判断WHEN指定的条件表达式的值，遇到第一个为TRUE的条件表达式，则整个CASE表达式取相应THEN指定的结果表达式的值，之后不再比较，结束并跳出CASE…END语句。如果找不到为TRUE的条件表达式，则取ELSE指定的结果表达式n的值。如果找不到为TRUE的条件表达式也没有使用ELSE，则返回NULL。【例8-8】查询设备表，根据设备的使用时间显示不同的提示信息。SELECTFacilityName,Model,Price,Quantity,

使用时间=year(GETDATE())-year(BuyTime),

提示信息=CASEWHENyear(GETDATE())-year(BuyTime)>=8THEN'超期服役'WHENyear(GETDATE())-year(BuyTime)>=2THEN'可以维持'ELSE'刚刚购置,新的'ENDFROMT_Facility8.3.4WHILE语句格式：执行过程：先判断条件表达式的值，若为TRUE，则执行BEGIN…END之间的循环体语句，执行到END时返回到WHILE再次判断条件表达式的值，若为TRUE，则再次执行BEGIN…END之间的循环体语句，直到条件表达式为FALSE，则跳出循环体，继续执行后续语句。WHILE逻辑条件表达式

BEGIN

循环体语句系列…[BREAK]……[CONTINUE]……END【注意】条件表达式的值必须是TRUE或FALSE。如果条件表达式中含有SELECT语句，则必须用括号将SELECT语句括起来。

若在执行循环体时遇到BREAK语句，则无条件跳出BEGIN…END。若在执行循环体时遇到CONTINUE语句，则结束本轮循环，不再执行之后的循环体语句，返回到WHILE再次判断条件表达式的值【例8-9】计算1+2+3+……+100的和。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT'1到100的和为：'PRINT@sum8.3.5其它流程控制语句WAITFOR语句

功能：使程序暂停指定的时间后再继续执行格式：WAITFOR{DELAY‘时间’|TIME’时间’}注意：DELAY指定暂停的时间长短——相对时间。TIME指定暂停到什么时间再重新执行程序——绝对时间。‘时间’参数必须是datetime类型的时间部分，格式为“hh:mm:ss”，不能含有日期部分。【例8-10】在5秒钟之后对设备表进行查询，在23时对部门表进行查询。USEbuildGOBEGINWAITFORDELAY'00:00:5'SELECT*FROMT_FacilityWAITFORTIME'23:00:00'SELECT*FROMD_DepartmentEND8.4系统内置函数

8.4.1数学函数8.4.2字符串函数8.4.3日期和时间函数8.4.4类型转换函数系统内置函数

开封大学信息工程学院函数是指一组编译好的T-SQL语句，它们可以带有一个或多个参数，也可以不带参数。函数执行的结果为返回一个数值、数值集合，或者没有返回值仅仅是执行一些操作。SQLServer2008支持两种函数类型：系统内置函数和用户定义函数。在SQLServer2008中，数据库引擎提供了丰富的系统内置函数。可以通过SQLServerManagementStudio的【对象资源管理器】窗口，查看所有的系统内置函数，8.4.1数学函数函数功能说明Abs(x)返回x的绝对值Acos(x)返回x的反余弦值(弧度)Atn2(x1,x2)返回介于x1和x2之间的近似反正切值(弧度)Ceiling(x)返回不小于x的最小整数Cos(x)返回x(弧度)的余弦值Cot(x)返回x(弧度)的余切值Degrees(x)返回x(弧度)的对应的角度值Exp(x)返回ex的指数函数Floor(x)返回不大于x的最大整数Log(x)返回以e为底的自然对数Log10(x)返回以10为底的对数Power(x,y)返回x的y次方xyRand(x)返回0到1之间的随机值Round(x1,x2)返回x1四舍五入到x2指定精度后的数值Sin(x)返回x(弧度)的正弦值Square(x)返回x的平方Sqrt(x)返回x的平方根【说明】函数参数x可以是数值常量、变量、字段名、数值函数或算术表达式。x的数据类型可以是各种数值型或货币型的，有的函数值类型与x类型相同，有的需要将x转换成float，其结果也是float类型的。功能说明中得到的值是函数返回值，使用函数后参数x的值不变。8.4.2字符串函数函数功能说明Ascii(A)返回字符串A第一个字符的ASCII码Char(x)返回ASCII码为整数x的字符Left(A,x)从字符串A的左边(前端)取x个字符的子串Len(A)返回字符串A去掉尾部空格后所包含的字符个数(不是字节数)，如果是空串，函数返回0Lower(A)返回将字符串A中所有字母变为小写字母的字符串Ltrim(A[,‘B’])将字符串A左边字符B删掉，缺省为删掉空格Patindex(A,B)返回模式A在字符串B中第一次出现的起始位置Replace(A,B[,C])在字符串A中查找字符串B，并将其替换为字符串C，省略C或为NULL则在A中删掉BRight(A,x)从字符串A的右边(尾部)取x个字符的子串Rtrim(A[,’B’])将字符串A右边字符B删掉，缺省为删掉空格Soundex(A)返回由4个字符组成的代码，用于评估两个字符串的相似性Space(x)得到有x个空格的字符串Str(x[,len[,d]])将x的数值转换为数字字符串，包括符号和小数点Stuff(A,start,len,B)把字符串A从start开始长为len的字符串用字符串B替换Substring(A,x[,y])从字符串A的x字符位置开始取出y个字符的子串，如果省略y取到字符串A的最后一个字符，如果x取负值则从后向前数【说明】函数参数x一般是整型的数值常量、变量、数值函数或算术表达式。函数参数A是字符串常量、变量、字段名、字符串函数或字符串表达式。A的数据类型可以是各种字符型、宽字符型或二进制类型的，大部分只能处理char(n)、varchar(n)、nchar(n)、nvarchar(n)类型或者可以转换成这些类型的数据，只有少部分可以处理binary(n)、varbinary(n)、image、text、ntext类型的数据。功能说明中得到的字符串或子字符串是函数返回值，原字符串A的内容不变。【例8-11】使用Replace函数将字符串“Iamthebest”中的字符串“am”替换为“willbe”。PRINTReplace('Iamthebest','am','willbe')8.4.3日期和时间函数函数功能说明Dateadd(yy|mm|dd,x,D)返回按第一个参数指定的项目D+x的新值Datediff(yy|mm|dd,D1,D2)返回按第一个参数指定的项目D2-D1的新值Datepart(时间参数,日期)返回日期中时间参数指定部分的对应整数，如SECOND得到秒数Datename(时间参数,日期)返回日期中时间参数指定部分的对应字符串Day(D)返回D的日期数Getdate()返回系统的当前日期和时间Getutcdate()返回表示当前UTC时间(世界时间坐标或格林尼治标准时间)值Month(D)返回D的月份值Year(D)返回D的年份值【说明】

函数参数x一般是整型的数值常量、变量、数值函数或算术表达式。

D是日期时间型的常量、变量、字段名或日期时间函数。

D的格式应该符合SETDATEFORMAT()命令设定的格式。功能说明中得到的值是函数返回值，原日期时间D的内容不变。【例8-12】查询设备表，计算出设备号为“6”的设备的使用时间。SELECTFacilityName,Model,Price,Quantity,

使用时间=year(GETDATE())-YEAR(BuyTime)FROMT_FacilitywhereFacilityNo=68.4.4类型转换函数函数功能说明Cast(表达式

as数据类型[(长度)])将表达式的值转换为指定的“数据类型”Convert(数据类型[(长度)],表达式[,style])按style格式将表达式的值转换成指定的“数据类型”【说明】函数中的表达式可以是任何有效的SQLServer表达式，所指定的数据类型必须是系统的基本数据类型而不能是用户自定义的类型。(长度)用于需要指定长度的数据类型，不需要指定长度的类型可以省略。

Cast()函数只适用于转换后不需要指定格式的数据类型，如整数、普通字符串。Convert()函数可适合于任何类型，其中Style可设置转换后的格式：将datetime或smalldatetime型日期时间转换为字符串的日期格式；将Real或float(p)型浮点数转换为字符串的小数或指数格式；将Smallmoney或money货币型转换为字符串的货币格式。

style参数见表8-17。不需要指定格式的类型Style可以省略。style参数的有效值转换后返回字符串的格式8(2位年份)108(4位年份)只转换为时间：hh:mm:ss11(2位年份)111(4位年份)只转换为日期：[yy]yy/mm/dd120(4位年份)yyyy-mm-ddhh:mm:ss0(Real或Float型浮点数)默认值，最多6位数，必要时使用科学计数法1(Real或Float型浮点数)最多8位数，必要时使用科学计数法2(Real或Float型浮点数)最多16位数，必要时使用科学计数法0(货币型，转换为字符型)默认值，小数点左侧数字不以逗号分隔，右侧取两位小数1(货币型，转换为字符型)小数点左侧数字每三位逗号分隔，右侧取两位小数2(货币型，转换为字符型)小数点左侧数字不以逗号分隔，右侧取四位小数【例8-13】修改【例8-12】中该设备的使用时间的显示。SELECTFacilityName,Model,Price,Quantity,

使用时间=Cast(year(GETDATE())-YEAR(BuyTime)asvarchar(2))+'年'FROMT_FacilitywhereFacilityNo=6【例8-14】掌握Convert函数的使用。DECLARE@ddatetime,@rreal,@mmoney--定义局部变量SET@d='2010/12/2010:10:36AM'SET@r=268886SET@m=9635225.3685PRINTconvert(varchar(30),@d,108)--结果为：10:10:36PRINTconvert(varchar(30),@d,111)--结果为：2012/12/20PRINTconvert(varchar(30),@d,120)--结果：2012-12-2010:10:36PRINTconvert(varchar(20),@r,0)--结果为：268886PRINTconvert(varchar(20),@r,1)--结果为：2.6888600e+005PRINTconvert(varchar(22),@r,2)--结果：.688860000000000e+005PRINTconvert(varchar(25),@m,0)--结果为：9635225.37PRINTconvert(varchar(25),@m,1)--结果为：9,635,225.37PRINTconvert(varchar(25),@m,2)--结果为：9635225.36858.5用户自定义函数

CreateFunction语句函数的调用ALTERFUNCTION语句

DROPFUNCTION语句

8.5.1函数的定义与调用

8.5.2函数的修改与删除开封大学信息工程学院使用SQLServerManagementStudio创建、修改、删除自定义函数

8.5.3使用SSMS编辑自定义函数

8.5.1用户函数的定义与调用用户函数的定义

语法格式：CREATEFUNCTION[所有者名称.]函数名

[({@参数名称[AS]数据类型[=默认值][READONLY]}[,…n])]

RETURNS

返回值类型

[WITH<标量函数的选项>[,…n]][AS]BEGIN

函数体SQL语句

RETURN

数值表达式

END【说明】

自定义函数必须在当前数据库中定义。函数名：必须符合标识符构成规则，在数据库中名称必须惟一，省略所有者名称默认为系统管理员dbo。

@参数名称：用局部变量定义的形式参数，用于接收调用函数时传递过来的参数。默认值必须是常量，如果设定了默认值则调用函数时若不提供参数，形式参数自动取默认值。READONLY：指定不能在函数定义中更新或修改参数。如果参数类型为用户自定义的表类型，则应指定此选项。RETURNS指定返回值类型，RETURN指定返回值，注意这两个关键字的区别。标量函数的选项常用为ENCRYPTION，用于将CREATEFUNCTION语句的原始文本转换为模糊格式。模糊代码的输出在任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。用户函数的调用自定义函数的调用与系统标准函数的调用相同，但必须写出“所有者名称.函数名”并在圆括号内给出参数。调用形式：所有者名称.函数名（实参1,…,实参n）【例8-15】定义一个根据指定日期及当前日期求出相对年限的函数“func_rela_yea()”，调用该函数，查询设备表，创建一个包含设备基本信息的“view_faci_inf”视图，显示设备的使用年限。USEbuildGOCREATEFUNCTIONfunc_rela_yea(@dateDatetime,@datenowDatetime)RETURNSintBEGINRETURNyear(@datenow)-year(@date)ENDGOCREATEVIEWview_faci_infASSELECTFacilityName,Model,

购买时间=Convert(varchar(12),BuyTime,111),

使用年限=dbo.func_rela_yea(BuyTime,GETDATE())FROMT_FacilityGOSELECT*FROMview_faci_inf8.5.2用户自定义函数的修改与删除ALTERFUNCTION修改自定义函数DROPFUNCTION删除自定义函数语法如下：DROPFUNCTION所有者名称.函数名称[,…n]8.5.3使用SSMS编辑自定义函数在SQLServerManagementStudio中创建自定义函数【例8-16】定义显示2位小数的货币格式函数“func_curr_sty()”

CREATEFUNCTIONfunc_curr_sty( --Addtheparametersforthefunctionhere @curr_valueSmallmoney)RETURNSvarchar(12)ASBEGIN --Declarethereturnvariablehere DECLARE@moneyvarchar(12) --AddtheT-SQLstatementstocomputethereturnvaluehere SELECT@money=Convert(varchar(12),@curr_value,0) --Returntheresultofthefunction RETURN@moneyENDGO【例8-17】使用自定义函数“func_curr_sty()”查询设备表，显示每种设备的金额总价。USEbuildGOSELECTFacilityName,Model,Price,Quantity,总价=dbo.func_curr_sty(Amount)FROMT_Facility使用SQLServerManagementStudio修改或删除自定义函数8.6游标的创建与使用游标的用途游标的特点8.6.2声明游标8.6.3打开游标8.6.4读取数据8.6.5关闭游标8.6.6释放游标8.6.1游标的概念

游标的使用开封大学信息工程学院8.6.1游标的概念作用：在T-SQL脚本程序、存储过程、触发器中对SELECT语句返回的结果集进行逐行逐字段处理，把一个完整的数据表按行分开，一行一行的逐一提取记录，并从这一记录行中逐一提取各项数据。SQLServer2008中的游标具有以下几个特点：游标返回一个完整的结果集，但允许程序设计语言只调用集合中的一行。允许定位在结果集的特定行。可以从结果的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。可以为其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可能性支持。提供脚本、存储过程和触发器中用于访问结果集中的数据的T-SQL语句。使用过程：定义声明游标→打开游标→从游标中提取记录并分离数据→关闭游标→释放游标。8.6.2声明游标两种定义方式基于SQL-92标准的定义语句T–SQL扩展的游标声明语句

基于SQL-92标准的定义语句格式：DECLARE游标名[INSENSITIVE][SCROLL]CURSORFORSELECT语句

[FOR{READONLY|UPDATA[OF字段名[,…n]]}]【说明】INSENSITIVE定义游标时自动在系统tempdb数据库中创建一个临时表存储游标使用的数据，在游标使用过程中基表数据改变不影响游标的数据，但该游标的数据不允许修改。省略该项表示游标直接从基表中取得数据，即游标使用的数据将随基表数据的变化而动态变化。SCROLL表示该游标可以在FETCH语句中任意指定数据的提取方式，省略该项表示该游标仅支持NEXT顺序提取方式。SELECT指定该游标使用的结果集，不允许使用COMPUTE或INTO子句。READONLY表示只读，该游标中的数据不允许修改，即不允许在UPDATE或DELETE语句中引用该游标。UPDATA[OF字段名[,…n]]表示在该游标内可以更新基本表的指定字段，省略字段名列表表示可以更新所有字段。T–SQL扩展的游标声明语句DECLARE游标名CURSOR[FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|OPTIMISTIC][TYPE_WARNING]FORSELECT语句

[FORUPDATE[OF字段名[,…n]]]【说明】FORWARD_ONLY指定该游标为顺序结果集，只能用NEXT向后方式顺序提取记录。SCROLL指定该游标为滚动结果集，可以使用向前、向后、定位方式提取记录。STATIC与INSENSITIVE含义相同，在系统tempdb数据库中创建临时表存储游标使用的数据，即游标不会随基本表内容而变化，同时也无法通过游标来更新基本表。KEYSET指定游标中列的顺序是固定的，并且在tempdb内建立一个KEYSET表，基本表数据修改时能反映到游标中。如果基本表添加符合游标的新记录时该游标无法读取(但其他语句使用WHERECURRENTOF子句可对游标中新添加的记录数据进行修改)。如果游标中的一行被删除掉，则用游标提取时@@FETCH_STATUS的返回值为-2。DYNAMIC指定游标中的数据将随基本表而变化，但需要大量的游标资源。FAST_FORWARD指定FORWARD_ONLY而且READ_ONLY类型游标。使用FAST_FORWARD参数则不能同时使用FORWARD_ONLY、SCROLL、OPTIMISTIC或FORUPDATE参数。OPTIMISTIC指明若游标中的数据已发生变化，则对游标数据进行更新或删除时可能会导致失败。TYPE_WARNING指定若游标中的数据类型被修改成其他类型时，给客户端发送警告。若省略FORWARD_ONLY|SCROLL则不使用STATIC、KEYSET和DYNAMIC时默认为FORWARD_ONLY游标，使用STATIC、KEYSET或DYNAMIC之一则默认为SCROLL游标。若省略READ_ONLY|OPTIMISTIC参数，则默认选项为：如果未使用UPDATE参数不支持更新，则游标为READ_ONLY；STATIC和FAST_FORWARD类型游标默认为READ_ONLY；DYNAMIC和KEYSET类型游标默认为OPTIMISTIC。【注意】不能将SQL-92游标语法与T-SQL游标的扩展语法混合使用。若在CURSOR前使用了SCROLL或INSENSITIVE则为SQL-92游标语法，则不能再在CURSOR和FORSELECT语句之间使用任何保留字，反之同理。8.6.3打开游标格式：OPEN[GLOBAL]游标名【说明】如果全局游标与局部游标同名时，GLOBAL表示打开全局游标，省略为打开局部游标。打开游标后，可以使用全局变量@@ERROR判断该游标是否打开成功。@@ERROR为0则打开成功，否则打开失败。使用全局变量@@CURSOR_ROWS可得到打开游标中当前存在的记录行数，其返回值为：0：表示无符合条件的记录或该游标已经关闭或已释放-1：表示该游标为动态的，记录行经常变动无法确定n：正整数n表示指定结果集已从表中全部读入，总共n条记录-m：表示指定的结果集还没全部读入，目前游标中有m条记录8.6.4读取数据格式：

FETCH[next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]FROM[GLOBAL]游标名

[INTO@变量名

[,…n]]【说明】FETCH之后的参数为提取记录的方式，可以是以下方式之一：NEXT顺序向下提取当前记录行的下一行，并将其作为当前行。第一次对游标操作时取第一行为当前行，处理完最后一行，再用FETCHNEXT则CURSOR指向结果集最后一行之后，@@FETCH_STATUS的值为-1。PRIOR顺序向前提取当前记录的前一行，并将其作为当前行。第一次用FETCHPRIOR对游标操作时，没有记录返回，游标指针CURSOR仍指向第一行之前。FIRST提取游标结果集的第一条记录，并将其作为当前行。LAST提取游标结果集的最后一条记录，并将其作为当前行。ABSOLUTE{n|@nvar}按绝对位置提取游标结果集的第n或第@nvar条记录，并将其作为当前行。若n或@nvar为负值则提取结尾之前的倒数第n或第@nvar条记录。n为整数，@nvar为整数类型变量。RELATIVE{n|@nvar}按相对位置提取当前记录之后（正值）或之前（负值）的第n或第@nvar条记录，并将其作为当前行。【说明】在游标内有一个游标指针CURSOR指向游标结果集的某个记录行—称为当前行，游标刚打开时CURSOR指向游标结果集第一行之前。FROM指定提取记录的游标，global用于指定全局游标，省略为局部游标。NTO指定将提取记录中的字段数据存入对应的局部变量中。变量名列表的个数、类型必须与结果集中记录的字段的个数、类型相匹配。打开游标用FETCH提取记录后，可用@@FETCH_STATUS检测游标的当前状态。@@FETCH_STATUS的返回值为：0：FETCH语句提取记录成功-1：FETCH语句执行失败或提取的记录不在结果集内-2：被提取的记录已被删除或根本不存在

8.6.5关闭游标格式：CLOSE[GLOBAL]游标名说明：关闭游标，会释放当前结果集的内存空间，然后解除定位游标记录行上的游标指针。游标关闭后，其定义结果仍然存储在系统中，但不能提取记录和定位更新，需要时可用OPEN语句再次打开。

8.6.6释放游标格式：DEALLOCATE[GLOBAL]游标名说明：释放游标是指删除游标引用，释放该游标所占用的所有系统资源。【例8-18】使用游标逐条查看空调信息视图(view_cond_inf)中空调的主要信息，用变量输出各项主要数据，并统计空调总台数。USEbuildGODECLAREcurs_condcursorkeySET--定义游标名为curs_condFORSELECTFacilityName,Model,Price,Quantity,AmountFROMview_cond_infOPENcurs_cond--打开游标

IF@@error=0--判断游标打开成功

BEGINIF@@cursor_rows>0--判断游标结果集记录个数大于

BEGINPRINT'游标记录数为：'+CONVERT(varchar(2),@@cursor_rows)DECLARE@fnamenvarchar(30),@modvarchar(20),@prinumeric(8,2),@quaint,@amomoney,@sumint,@indexintFETCHabsolute2FROMcurs_condinto@fname,@mod,@pri,@qua,@amo--提取记录

PRINT'第条记录:'+'设备名:'+@fname+'规格:'+@mod+'单价:'+CAST(@priASvarchar(8))+'数量:'+CAST(@quaASvarchar(2))+'台'+'总价'+convert(varchar(25),@amo,0)FETCHrelative2FROMcurs_condinto@fname,@mod,@pri,@qua,@amoPRINT'后移条记录:'+'设备名:'+@fname+'规格:'+@mod+'单价:'+CAST(@priASvarchar(8))+'数量:'+CAST(@quaASvarchar(2))+'台'+'总价'++convert(varchar(25),@amo,0)SET@sum=0SET@index=0PRINT'全部记录为：'FETCHfirstFROMcurs_condinto@fname,@mod,@pri,@qua,@amo--先提取第一条记录

WHILE@@FETCH_STATUS=0BEGINPRINTCAST(@index+1ASchar(2))+':'+'设备名:'+@fname+'规格:'+@mod+'单价:'+CAST(@priASvarchar(8))+'数量:'+CAST(@quaASvarchar(2))+'台'+'总价'++convert(varchar(25),@amo,0)FETCHnextFROMcurs_condinto@fname,@mod,@pri,@qua,@amoSET@index=@index+1SET@sum=CAST(@quaASint)+@sumENDPRINT'实际记录数为：'+CASt(@indexASchar(2))+'空调总台数为：'+CASt(@sumASchar(6))ENDENDELSEPRINT'游标打开失败！'CLOSEcurs_cond--关闭游标

DEALLOCATEcurs_cond--删除游标小结在SQLServer中使用T-SQL进行程序设计时，通常是使用批处理来提交一个或多个T-SQL语句，一个