SQL Server 数据库实践教程-管理与维护篇课件第2章 T-SQL 程序设计基础

1本章概述2.1概述

2.2注释

2.3变量

2.4运算符

2.5SQL函数

2.6流程控制语句22.1概述T-SQL就是Transact-SQL，是标准SQL在MSSQL环境下程式设计语言的增强版，它是用来让应用程式与SQLServer沟通的主要语言。T-SQL提供标准SQL的DDL和DML功能，加上延伸的函数、系统预存程序以及程式设计结构(例如IF和WHILE)让程式设计更有弹性，T-SQL的功能随著新版的SQLServer而持续成长。T-SQL的批处理是一组的扩展SQL语句，在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。两个GO之间的SQL语句作为一个批处理。这样的语句组从应用程序一次性地发送到SQLServer服务器进行执行。SQLServer服务器将批处理编译成一个可执行单元，称为执行计划。3示例--该批处理执行打开数据库UsesampleGo/*批处理结束标志*/--该批处理检索项目部员工Select*From员工数据表Where所属部门=‘项目部’

and工资>2000Orderby姓名Go--下面的批处理创建视图CreateviewPM-ViewAsSelect*from员工数据表where所属部门=‘项目部’Go

42.2格式化的TSQL代码在SQLServer中，有两种类型的注释字符：单行注释：使用两个连在一起的减号“--”作为注释符；多行注释：使用“/**/”作为注释符。2.2.1注释2.2.2结束TSQL语句的标准TSQL标准规定在每个命令行末尾使用分号结束该部分的批处理TSQL语句，但需要注意的是，TSQL技术分号是可选择的，但是请不要在下面的位置使用分号：不要在tryend后面添加分号不要在if语句后面添加分号必须在CET之前添加分号52.3变量2.3.1全局变量全局变量在整个SQLServer系统内使用。存储的通常是一些SQLServer的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“@@”开头。全局变量对用户来说是只读的。局部变量的名称不能与全局变量的名称相同有关全局变量的全部内容和解释参见帮助文档6举例SQL-sever提供的全局变量共有33个，但是并不是每一个都会用到。下面举例说明全局变量@@ERROR的用法：UsesampleGo--将项目部的工资更新为Update员工数据表Set工资=3000where所属部门='项目部'--检查是否出现限制检查冲突If@@ERROR=547print'出现限制检查冲突，请检查需要更新的数据‘

@@ERROR全局变量将返回最后执行的T-SQL语句的错误代码，数据类型为integer，如果成功@@ERROR返回0，否则返回错误代码。有关错误代码的信息请在sysmessages系统表中查找。本例子中检测限制检查冲突。7举例下面举例说明全局变量@@RowCount的用法：该变量用以判别查询是否成功，并返回查询到的行数。updatestudentsetsname='叮当'wheresname='老叮当';if@@rowcount=0beginprint'没有修改任何行'end82.3变量2.3.2局部变量利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。1．局部变量的定义

必须先用DECLARE命令定义后才可以使用。DECLAER{@local_variabledata_type}[…n]

2．局部变量的赋值方法

SET{{@local_variable=expression}或者SELECT{@local_variable=expression}[,...n]

91、通过SET进行附值declare@char1float,@numchar(40)select@char1=max(degree)fromscore,coursewherecname='高等数学'ando=oset@num='高等数学的最高成绩是:'print@num+STR(@char1)2、通过SELECT进行附值注意事项：(1)如果查询之中select返回多个值，则仅仅将最后一个数值附值给变量(2)如果没有返回值则保持当前的变量值案例见下10举例：UsesampleGo--开始声明局部变量Declare@max_salaryint--将其赋值为全体员工的工资最大值Select@max_salary=max(工资)from员工数据表Go（注意：局部变量的作用范围是从声明该局部变量的地方开始到声明局部变量的批处理或存储过程的结尾。在局部变量的作用范围以外引用该局部变量将引起语法错误。）案例见下112.4临时表和全局表2.4.1局部临时表

SQL

Server

支持临时表。临时表就是那些名称以井号

(#)

开头的表。如果当用户断开连接时没有除去临时表，SQL

Server

将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库

tempdb

内。

局部临时表的创建方法与用户表相同，但是临时表的名称前面必须打上#。实际上，临时表是在tempdb建立的。其生命周期自批处理开始生成，自该批处理结束终止，该临时表将从tempdb库中被删除。局部临时表往往在存储过程开发中被设计，至该存储过程结束终止。例子：createtable#stupass(snointprimarykey,snamevarchar(20),sdegreeint,cnamevarchar(20));122.4临时表和全局表2.4.2全局临时表

以两个井号

(##)

开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。

例子：createtable##stupass(snointprimarykey,snamevarchar(20),sdegreeint,cnamevarchar(20));132.4临时表和全局表2.4.3全局临时表

全局表和临时表的差异：二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号

(#)

打头；它们仅对当前的用户连接是可见的；当用户从

Microsoft

SQL

Server实例断开连接时被删除。全局临时表的名称以数学符号

(##)

打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL

Server断开连接时被删除。

例如，如果创建名为employees的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。如果创建名为#employees的本地临时表，只有您能对该表执行操作且在断开连接时该表删除。如果创建名为##employees的全局临时表，数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在您创建后有其他用户使用，则

SQL

Server在所有用户断开连接后删除该表。142.4运算符

算术运算符

算术运算符包括加（+）、减（－）、乘（*）、除（/）和取模（%）

赋值运算符Transact-SQL中只有一个赋值运算符，即等号（=）4.2.3位运算符位运算符包括按位与（&）、按位或（|）、按位异或（^）和求反（~）。位运算符用来对整型数据或者二进制数据（image数据类型除外）之间执行位操作。要求在位运算符左右两侧的操作数不能同时是二进制数据。152.4运算符2.4.4比较运算符比较运算符包括：等于（=）、大于（>）、大于或等于（>=）、小于（<）、小于或等于（<=）、不等于（<>或!=）、不小于（!<）、不大于（!>）。

2.4.5逻辑运算符逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。逻辑运算返回布尔值，值为TRUE或FALSE。

162.4运算符2.2.6

字符串连接运算符

（+）2.4.7运算符的优先级括号：（）；乘、除、求模运算符：*，/，%；加减运算符：＋，－；比较运算符：=，>，<，>=，<=，<>，!=，!>，!<；位运算符：^，&，|；逻辑运算符：NOT；逻辑运算符：AND；逻辑运算符：OR。172.6流程控制语句2.6.1BEGIN…END语句

BEGIN{sql_statement|statement_block}END2.6.2IF…ELSE语句

IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]

182.6流程控制语句举例例子1：UsesampleGo--声明用于发布消息的变量Declare@messagevarchar(200)--判断是否在办公室工作Ifexist(Select*from员工数据表where所属部门=‘办公室’)--如果有，则列出姓名Beginset@message=“下列人员在办公室：”

print@messageselect员工姓名from员工数据表where所属部门=‘办公室’End--否则，输出没有人的在工作的消息Else192.6流程控制语句举例BeginSet@message=“抱歉，没有人在办公室”Print@messageEndGo注意：begin和end分别表示语句块的开始和结束，而且他们必须成对使用。例子2：UsesampleGoDeclare@messagevarchar(200)--判断是否存在工资超过5000的员工Ifexist(Select*员工姓名，工资from员工数据表where工资>5000)End--否则，输出没有工资以上的员工202.6流程控制语句举例(if..else)ElseBeginSet@message=“抱歉，并没有员工的工资在5000元以上”PrintmessageEnd例子3(下面的例子中，如果平均书价大于15元，则输出一个消息，否则输出另外的一个消息)If(select*avg(price)fromtitle)>$15print‘我们可以打折！’Elseprint‘我们将要提价！’例子3：（如果select语句不止是一个值，就可以使用一个特殊的if语句，即ifexists）Ifexists(select*fromtitlewherepub_id=‘9933’)212.6流程控制语句举例(ifexists)BeginPrint“包含如下图书：”Select*fromtitleswherepub_id=‘9933’EndElsePrint“暂时没有您要找的图书”222.6流程控制语句2.6.3WHILE…CONTINUE…BREAK语句

WHILEBoolean_expression{sql_statement|statement_block}[BREAK]{sql_statement|statement_block}[CONTINUE]23WHILE…CONTINUE…BREAK语句例子(下面的这个例子将判断是否有员工的奖金少于300元，如果有则将所有的员工的工资增加300，否则将所有员工增加500元，直到所有的员工的奖金都是多于300或者是有的员工的工资已经超过了10000元)UsesampleGo--根据是否存在员工的平均工资少于3000而确定循环是否继续执行While(selectavg(工资)from员工数据表)<3000--循环开始beginUpdate员工数据表Set工资=工资+5--如果有的员工的工资已经超过了10000元，则跳出循环print'没有的员工的工资已经超过了10000元，继续循环'If(selectmax(工资)from员工数据表)>10000Breakelsecontinueend--循环结束print'有的员工的工资已经超过了10000元，停止循环'24declare@salarymoney,@min_moneymoney,@max_moneymoney,@avg_moneymoneyselect@min_money=MIN(工资),@max_money=MAX(工资),@avg_money=AVG(工资)FROM员工数据表while@avg_money<4000beginupdate员工数据表

set工资=工资+500if@max_money>5000breakelsecontinueend例子2252.6.4CASE语句2.6.4CASE语句简单CASE语句的语法形式为：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expression]END

搜索CASE语句的语法形式为：CASEWHENBoolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]END262.6.4CASE语句举例例子：UsesampleGoSelect姓名，“部门说明”=--分别为各个部门说明情况Case所属部门

when‘办公室’

then‘在办公室工作，该部门主要负责办公室的工作。’

when‘项目部’

then‘在项目部工作，该部门主要负责项目部的工作。’

when‘录入部’

then‘在录入部工作，该部门主要负责录入部的工作。’

when‘技术部’

then‘在技术部工作，该部门主要负责技术部的工作。’

……….EndFrom员工数据表--按照员工的数据表排序Orderby姓名272.6.5waitfor语句Waitfordelay时间间隔……（期间的时间间隔将指定Waitfor

语句执行之前需要等待的时间，最多是24小时）Waitfortime时间值……（期间的时间值将指定Waitfor

语句执行的时间）例子：UsesalpleGo--指定在执行select语句之前需要等待3秒Waitfordelay’00:00:02’--执行查询(select姓名，性别from员工数据表where所属部门=‘项目部’)282.6.6GOTO语句语法结构：GOTOlabel（标签名称）……label:例子：UsesampleGoIf(selectmax(工资)from员工数据表)>10000Gotodecrease_salary…….Decrease_salary:……(注意：人们认为GOTO语句是影响可读性的严重因素，在使用的时候尽可能避免使用GOTO语句，因为过多的GOTO语句可能会造成T-SQL的逻辑混乱而难以理解。另外，标签仅仅标示了跳转的目标，它并不隔离其前后的语句。只要标签前面的语句本身不是流程控制语句，标签前后的语句将按照顺序正常执行，就如同没有使用标签一样。)292.6.7Print语句语法格式如下：Print{‘anyASCIItext’|@local_variable|@@global_variable}既可以输出“字符串”或者“字符串常量”或者“字符串变量”举例：Print”你好吗？”Print@@version302.6.8错误处理1.Try…Catch

这是MSSQL2005特有的一种标准的捕获和处理错误的方法，其基本理念是，尝试执行一个代码块，如果发生错误，则在Catch代码块之中捕获错误。基本用法

BEGINTRY

{sql_statement|statement_block};

ENDTRY

BEGINCATCH

{sql_statement|statement_block}

ENDCATCH312.6.8错误处理1.Try…Catch

和普通语言的异常处理用法差不多，但要注意的是，SQLSERVER只捕捉那些不是严重的异常，当比如数据库不能连接等这类异常时，是不能捕捉的。BEGINTRYDECLARE@XINT--0为除数错误SET@X=1/0PRINT'CommandaftererrorinTRYblock'ENDTRYBEGINCATCHPRINT'ErrorDetected'ENDCATCHPRINT'CommandafterTRY/CATCHblocks'例子1：322.6.8错误处理1.Try…Catchtrycatch可以嵌套的案例例子2：2.错误函数错误函数返回值Error_Message()错误的消息文本Error_Number()错误编号Error_Procedure()发生错误的存储过程或触发器的名称Error_Serverity()错误的严重程度Error_State()错误的状态例子3：332.6.8错误处理3.Catch块一般处理的工作如果批处理使用了逻辑结构（begintran/committran），则错误处理程序应回滚事务。建议首先会滚事务，以释放该事务执行的锁定。如果错误是存储过程逻辑检测到的，则系统将自动引发错误消息。如果有必要，将错误记录到错误表中。结束批处理，如果它是存储过程、用户定义函数或触发器，可使用return命令结束它。344.5函数4.5.1系统函数

获取SQL-sever的系统信息。1、COL_LENGTH（expression）返回列的定义长度（以字节为单位）。2、DATALENGTH(expression)返回任何表达式所占用的字节数。举例：UsesampleGoSelectcol_length(‘员工数据表’，‘员工姓名’)asname_data_length,Datalength(‘员工姓名’)asname_data_lengthfrom员工数据表（注解：col_length（）函数可以返回列的长度）3、ISNUMERIC(expression)/返回类型int确定表达式是否为一个有效的数字类型。举例：USEschoolSELECTISNUMERIC(sno)FROMstudentGO354、USER_ID（）返回用户的数据库标识号。5、USER_NAME（）返回给定标识号的用户数据库用户名。USER_NAME([id])id用来返回用户名的标识号。id的数据类型为int。注释当省略id

时，则假定为当前用户。必须加上圆括号。举例子：SELECTUSER_ID('MY-TOMATO'),USER_NAME('MY-TOMATO')364.5.2字符串函数使用字符传函数对字符串输入值执行操作，以获得字符串或者所需要的数字值。1、CHAR（数字变量）功能：将ASC码转换成为字符串；注意：ASC码是指0——255之间的整数2、LEFT（字符串表达式，整数）功能：返回从字符串左边开始多少个字符3、LTRIM函数功能：删除字符串的前导空格4、REPLACE（’第一个字符串’，’第二个字符串’，’第三个字符串’）用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式5、SUBSTRING(表达式，开始点，结束点)返回字符、binary、text或image表达式的一部分。6、CAST与CONVERT函数实现数据的格式转化。将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能使用CAST：CAST(expression

ASdata_type)使用CONVERT：CONVERT(data_type[(length)],expression[,style])7、LEN(string_expression)返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格378.LOWER（）将大写字符数据转换为小写字符数据后返回字符表达式9.UPPER（）返回将小写字符数据转换为大写的字符表达式。例子：createtabletitles(titlevarchar(24),pricemoney)insertintotitlesvalues('PPd',3.63)SELECTLOWER(SUBSTRING(title,1,20))ASLower,UPPER(SUBSTRING(title,1,20))ASUpper,LOWER(UPPER(SUBSTRING(title,1,20)))AsLowerUpperFROMtitlesWHEREpricebetween1.00and200.003810、CHARINDEX(expression1,expression2[,start_location])返回字符串中指定表达式的起始位置。SELECTCHARINDEX('丽',sname)FROMstudent11、REPLICATE(character_expression,integer_expression)以指定的次数重复字符表达式。declare@cvarchar(12)set@c='我是谁'SELECTREPLICATE(@c,4)12、REVERSE(character_expression)返回字符表达式的反转。declare@cvarchar(12)set@c='我是谁'SELECTREVERSE(@c)13、STUFF(character_expression,start,length,character_expression)

删除指定长度的字符并在指定的起始点插入另一组字符。SELECTSTUFF('请问你谁是周星驰啊',6,3,'大笨蛋')394.5.3日期函数1、DATEADD(datepart,number,date)在向指定日期加上一段时间的基础上，返回新的datetime值。2、DATEDIFF(datepart,startdate,enddate)返回跨两个指定日期的日期和时间边界数。3、DATENAME(datepart,date)返回代表指定日期的指定日期部分的字符串。4、DATEPART(datepart,date)返回代表指定日期的指定日期部分的整数。5、year(),month(),day()返回年月日6、GETDATE（）函数返回今天的日期404.5.4

数学函数可以使用数学函数执行各种算术或函数运算1.ABS()函数（绝对值）精确数字或近似数字数据类型类别的表达式（bit数据类型除外）。例子：SELECTABS(-2147483648)2.CEILING()(取整函数)返回大于或等于所给数字表达式的最小整数。例子SELECTCEILING($123.45),CEILING($-123.45),CEILING($0.0)GO3.FLOOR()(取整函数)返回小于或等于所给数字表达式的最大整数。示例SELECTFLOOR(123.45),FLOOR(-123.45),FLOOR($123.45)比较CEILING和FLOORCEILING函数返回大于或等于所给数字表达式的最小整数。FLOOR函数返回小于或等于所给数字表达式的最大整数。例如，对于数字表达式12.9273，CEILING将返回13，FLOOR将返回12。FLOOR和CEILING返回值的数据类型都与输入的数字表达式的数据类型相同414.ROUND()(四舍五入函数)返回数字表达式并四舍五入为指定的长度或精度。语法ROUND(numeric_expression,length[,function])例子1.使用ROUND和估计值下例显示两个表达式，说明使用ROUND函数且最后一个数字始终是估计值。SELECTROUND(123.9994,3),ROUND(123.9995,3)GO例子2.使用ROUND和四舍五入的近似值SELECTROUND(123.4545,2)SELECTROUND(123.45,-2)5.sign(n),当n>0,返回1，n=0,返回0，n<0,返回-1DECLARE@valuerealSET@value=-1WHILE@value<2BEGINSELECTSIGN(@value)SELECT@value=@value+1END425.RAND([seed])返回0到1之间的随机float值。DECLARE@countersmallintSET@counter=1WHILE@counter<5BEGINSELECTRAND(@counter)Random_NumberSET@counter=@counter+1END434.5.6用户自定义函数

CreateFunction函数名称（形式参数名称

AS数据类型）Returns返回数据类型Begin函数内容Return表达式End调用用户自定义函数的基本语法为：变量=用户名.函数名称（实际参数列表）注意：在调用返回数值的用户自定义函数时，一定要在函数名称的前面加上用户名

SQLSERVER创建了用户自定义的函数，它同时具备了视图和存储过程的优点，但是却牺牲了可移植性。44一、标量函数1、标量函数是返回单个值的函数。这类函数可以接收多个参数，但是返回的值只有一个，所有的函数将使用return返回一个值。2、在用户定义的函数中，return命令应当是最后一条执行的命令。3、用户定义的函数不能够对于数据进行更新，但是可以使用视图操作。语法标量函数CREATEFUNCTION[用户名.]定义的函数名

([{@变量名

[AS]变量类型

[,...n]])RETURNS返回值的数据类型[AS]BEGIN

declare@返回值变量

function_body

RETURN@返回值变量

END45用户自定义函数举例：1、CREATEfunctionaverc(@cnovarchar(12))returnsintasbegindeclare@averintselect@aver=(selectavg(degree)fromscorewherecno=@cnogroupbycno)return@averenddeclare@eeintdeclare@wwvarchar(12)set@ww='3-105‘exec@ee=dbo.averc@wwselect@ee=dbo.averc(@ww)print@ee46用户自定义函数举例(标量函数)学生问题：见下面的例子47内嵌表值函数CREATEFUNCTION[用户名.]用户定义的函数名

([{@局部变量名

[AS]局部变量数据类型

}[,...n]])RETURNSTABLE

[AS]RETURN(

select-stmt

)二、内嵌表值函数内嵌表值用户定义函数没有由begin/end标识的程序体。取而代之的是将select语句作为table数据类型加以返回。48用户自定义函数举例：内嵌表值函数--创建函数,查询选修了某门课程的学生姓名createfunctionfn_view(@cnamevarchar(20))returnstableasreturn(selectsnamefromstudentwheresnoin(selectsnofromscorewherecnoin(selectcnofromcoursewherecname=@cname)))declare@eevarchar(20)set@ee='高等数学'select*fromfn_view(@ee)49用户自定义函数举例：--建立函数,输入一个学生的学号就可以知道他的姓名,选修课程名--以及该门课程的成绩createfunctionstu_avg_table(@snovarchar(20))returns@stu_avgtable(snovarchar(12),snamevarchar(20),cnamevarchar(20),degreeint)asbegininsert@stu_avgselectstudent.sno,sname,cname,degreefromstudent,score,coursewherestudent.sno=score.snoando=oandstudent.sno=@snoreturnenddeclare@rrvarchar(20)set@rr='103'select*fromstu_avg_table(@rr)50用户自定义函数举例(内嵌表值函数)商品销售问题：见下面的例子512.6游标一、简介到目前为止，所有的SQL语句是采用面向集合的方法来处理和操作数据的，是针对行的集合进行的操作。应用WHERE子句指出的行包含在该集合之中，必须对每一行进行同样的工作。例如：返回集合的条件行（SELECT），改变集合中的每一行的同一个列（UPDATE），删除集合中的每一个行（DELETE）等。二、关于游标的引入面向集合的一种替代方法就是游标的概念的引入。它允许每次一行的操作，基于行的内容，可以决定采取的下一步行为。注意：不得不按照每次一行的方式来处理行（游标）时候的性能，开销相对于面向集合的方式来处理行的性能是巨大的。在解决使用游标之前，应该确定已经仔细分析了需求，并且无法通过别的编程方法来解决问题。某些情况下面，游标是非常有用的，但是它应当是程序设计员最后的手段，而并不应当成为第一的选择！522.6游标概念：游标实际上是用户在系统中开设的一个数据缓冲区，存放SQL语句的执行结果。游标的使用：游标中存放查询结果的一组记录，用户可以通过移动游标指针逐一访问记录，获得结果，并赋给主变量，交由主语言进一步处理。532.6游标使用游标的五个步骤：声明游标；打开游标；利用游标读取、修改或删除所取的行；关闭游标；释放游标。542.6游标1、声明游标DECLAREcursor_nameCURSORFORselect_statement2、打开游标：opencursor_name3、关闭游标Closecursor_name关闭游标后可释放其所占用的资源。若要再次使用，可再打开4、释放游标 deallocatecursor_name5、使用游标取数:FETCH(NEXT|PRIOR|FIRST|LAST)FROMcursor_name552.6游标如下例中声明并打开游标useschool--定义游标阶段DECLAREstu_cursorCURSORFORSELECTsnameFROMstudent--打开游标阶段OPENstu_cursor--获取游标阶段[游标移动阶段]FETCHNEXTFROMstu_cursor--关闭游标阶段closestu_cursor--释放游标阶段deallocatestu_cursor562.6游标在简单的游标中使用FETCH介绍几个在游标使用过程中有用的全局变量1、@@cursor_rows返回连接上最后打开的游标中当前存在的合格行的数量。2、@@FETCH_STATUS回被FETCH语句执行的最后游标的状态.0表示FETCH语句成功。572.6游标在简单的游标中使用FETCHUSEschoolGODECLARE@snamevarchar(40)DECLAREstu_cursorCURSORFORSELECTsnameFROMstudentOPENstu_cursorFETCHNEXTFROMstu_cursorINTO@snameWHILE@@FETCH_STATUS=0BEGINPRINT'学生姓名:'+@snameFETCHNEXTFROMstu_cursorINTO@snameENDCLOSEstu_cursorDEALLOCATEstu_cursorGO582.6游标刚才我们看到关键词：FETCHNEXT是推动游标向前移动的关键。除此以外，还包括以下的移动：FETCH关键字移动位置FIRST数据集第一条记录LAST数据集末一条记录PRIOR前一条记录NEXT后一条记录RELATIVE按照相对位置决定移动的位置ABSOLUTE按照绝对位置决定移动的位置592.6游标DECLARE@snamevarchar(40)DECLAREstu_cursorCURSORFORSELECTsnameFROMstudent

OPENstu_cursordeclare@snamevarchar(20)FETCHNEXTFROMstu_cursorINTO@snameprint@snamedeclare@snamevarchar(20)FETCHpriorFROMstu_cursorINTO@snameprint@sname系统将提示：fetch:提取类型prior不能用于只进游标。这是为什么？602.6游标游标分类游标可以按照它所支持的提取选项进行分类：只进必须按照从第一行到最后一行的顺序提取行。FETCHNEXT是唯一允许的提取操作。可滚动性[scroll]可以在游标中任何地方随机提取任意行。允许所有的提取操作（但动态游标不支持绝对提取）。612.6游标语法改进:DECLARE@snamevarchar(40)DECLAREstu_cursorCURSORscrollFORSELECTsnameFROMstudent

OPENstu_cursordeclare@snamevarchar(20)FETCHNEXTFROMstu_cursorINTO@snameprint@snamedeclare@snamevarchar(20)FETCHpriorFROMstu_cursorINTO@snameprint@sname622.6游标使用FETCH将值存入变量DECLARE@snamevarchar(30),@svarchar(400)DECLAREstu_cursorCURSORFORSELECTsnameFROMstudentOPENstu_cursorFETCHNEXTFROMstu_cursorINTO@sWHILE@@FETCH_STATUS=0BEGINFETCHNEXTFROMstu_cursorINTO@sname

set@s=ltrim(rtrim(@s))+','+ltrim(rtrim(@sname))ENDprint@sCLOSEstu_cursorDEALLOCATEstu_cursorGO为什么最后一行总是重复出现！632.6游标使用游标修改或删除数据DECLAREabcCURSORFORSELECTsNameFROMStudentOPENabcGOFETCHNEXTFROMabcGOUPDATEstudentSETsName='猪头'WHERECURRENTOFabcGOCLOSEabcDEALLOCATEabcGO642.6游标复杂的游标查询问题：

——使用递归游标遍历树算法解决家族树的问题问题提出：如何解决某个人所有的子孙后代的显示呢？基本算法思路：生成一棵树，用游标来获取每个孩子的编号和姓名，而后以这些孩子的编号为递归的标准，再次查询孩子的孩子，直到游标置底。652.6游标复杂的游标查询问题：

——使用递归游标遍历树算法解决家族树的问题注意：当游标较少时候，使用该算法解决遍历问题已经足够；但是当数据量较大时候，就会出现问题。主要由两个原因造成：1、MSSQL限制存储过程循环次数最多32层。2、性能问题。基于行的游标比基于集合的查询性能上面将慢50到70倍。设定：将500万行数据组织为12个层次，需要进行游标调用500万次，开辟500万个缓冲空间，执行500万次的select查询66一、从递归的SQL查询说开去1、一个问题创建一个存储过程，查询“福建省”的所有用户，并且将他们的电子邮件连接起来，中间用“；”分割后附值给一个内存变量，以便调用。67一、从递归的SQL查询说开去2、递归调用的基本语法Select@variable=@variable+d.columnFromtableasd68二、关于游标的基础知识1、简介到目前为止，所有的SQL语句是采用面向集合的方法来处理和操作数据的，是针对行的集合进行的操作。应用WHERE子句指出的行包含在该集合之中，必须对每一行进行同样的工作。例如：返回集合的条件行（SELECT），改变集合中的每一行的同一个列（UPDATE），删除集合中的每一个行（DELETE）等。2、关于游标的引入面向集合的一种替代方法就是游标的概念的引入。它允许每次一行的操作，基于行的内容，可以决定采取的下一步行为。69注意：1、游标是有害的！！在实际测试中，游标的效率是正常SQLselect查询的1/50到1/70。2、不得不按照每次一行的方式来处理行（游标）时候的性能，开销相对于面向集合的方式来处理行的性能是巨大的。在解决使用游标之前，应该确定已经仔细分析了需求，并且无法通过别的编程方法来解决问题。某些情况下面，游标是非常有用的，但是它应当是程序设计员最后的手段，而并不应当成为第一的选择！703、概念：游标实际上是用户在系统中开设的一个数据缓冲区，存放SQL语句的执行结果。4、游标的使用：游标中存放查询结果的一组记录，用户可以通过移动游标指针逐一访问记录，获得结果，并赋给主变量，交由主语言进一步处理。二、关于游标的基础知识715、游标的生命周期游标的生命周期包含5个阶段：1、声明游标：为游标指定获取数据时候的select语句。声明的游标并不会检索任何的数据，仅仅为游标指定select的查询范围

Declare游标名称cusorcursoroptionsForselect语句2、打开游标

Open游标名称3、取出游标中的信息

Fetch游标名称into@变量1，@变量2

该操作将使得游标移动到下一行记录，并将游标返回的每个列的数据分别赋值给本地变量，这些变量必须预先予以声明。4、关闭游标，释放数据，但是保留select语句，以后还可以通过open方法打开（open命令与close相似）

Close游标名称5、释放游标，释放相关内存，并删除游标定义

Deallocate游标名称通常配合@@fetch_status形成循环：@@fetch_status标量的意义：0——最近一次fetch命令成功的获取到一行数据1——最近一次fetch命令到达结果集的尾部2——最近一次获取的行不可用；改行已经被删除72在使用游标之前，需要声明游标。具体格式如下：DECLARE游标的名称CURSOR

/*用于指定T-SQL服务器游标的名称*/[LOCAL/GLOBAL]

/*用于指定该游标的名称是全局的还是局部的*/[FORWARD_ONLY|SCROLL]/*游标只能从第一行滚动到最后一行。SCROLL

语句见后*/[READ_ONLY]/*禁止游标进行数据更新*/FOR选择语句/*这里是标准的select语句，它将定义游标将要处理的结果集合*/[FORUPDATE[OF字段名称1，字段名称2，字段名称3，….]]/*UPDATE定义游标内可以更新的列*/5、游标的定义73关键字移动位置FIRST数据集第一条记录LAST数据集末一条记录PRIOR前一条记录NEXT后一条记录RELATIVE按照相对位置决定移动的位置ABSOLUTE按照绝对位置决定移动的位置74UsesampleGo--声明游标Declarepm_cursorcursorreadonlyFORselect*from员工数据表where所属部门=‘项目部’Orderby姓名75三、SQLServer全文索引

在一个产品介绍网站中查询产品时，由于产品的介绍性文字可能会很长，如果使用对产品介绍字段使用like进行模糊查询，性能肯定会是问题。那么如何解决这个问题呢？第一个想法就是使用全文索引。那么全文索引是什么、应该如何应用、在应用的过程中又应该注意哪些事情呢？76三、SQLServer全文索引

1、什么是全文检索

[摘录自SQLServer2000联机从书]

全文索引为在字符串数据中进行复杂的词搜索提供有效支持。全文索引存储关于重要词和这些词在特定列中的位置的信息。全文查询利用这些信息，可快速搜索包含具体某个词或一组词的行。

全文索引包含在全文目录中。每个数据库可以包含一个或多个全文目录。一个目录不能属于多个数据库，而每个目录可以包含一个或多个表的全文索引。一个表只能有一个全文索引，因此每个有全文索引的表只属于一个全文目录。

全文目录和索引不存储在它们所属的数据库中。目录和索引由Microsoft搜索服务分开管理。

全文索引必须在基表上定义，而不能在视图、系统表或临时表上定义。77三、SQLServer全文索引

可以做这样一个比喻。大家大概都见过档案柜，档案柜是将各种档案按照分类登记在档案索引卡上，这个档案柜中的索引卡就象建立的全文索引，通过这些档案索引卡可以迅速定位你要查找的卷宗所在的位置。如果不建立这些索引卡，如果卷宗数量不多还好，一旦档案数量很多的时候显然很难找到期望的卷宗，这就类似使用Like的情形。78三、SQLServer全文索引

全文索引和普通索引的区别：普通SQL索引

全文索引

存储时受定义它们所在的数据库的控制存储在文件系统中，但通过数据库管理每个表允许有若干个普通索引每个表只允许有一个全文索引当对作为其基础的数据进行插入、更新或删除时，它们会自动更新将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来请求，也可以在添加新数据时自动发生不分组在同一个数据库内分组为一个或多个全文目录使用SQLServer企业管理器、向导或Transact-SQL语句创建和除去使用SQLServer企业管理器、向导或存储过程创建、管理和除去79三、SQLServer全文索引

2、配置全文检索服务实验

首先建立检索表的全文检索，全文检索要求唯一索引，故需要在相关表建立唯一聚集索引。这一步实验可以使用SQLDDL或者SQLServerManagementStudio建立表的全文检索；或者通过键入命令EXECSP_FULLTEXT_DATA