SQLServer的程序设计.doc

《SQLServer的程序设计.doc》由会员分享，可在线阅读，更多相关《SQLServer的程序设计.doc（23页珍藏版）》请在装配图网上搜索。

第10章 Transact-SQL程序设计 教学目标 终极目标： 会利用变量、函数、流程控制语句编写Transact-SQL程序实现对数据库系统的操作。 促成目标：1. 能正确理解和使用变量 2. 能正确理解和使用函数 3. 能正确理解和使用流程控制语句 相关知识 什么是Transact-SQL? 前面我们已经学习了有关SQL的知识，SQL是关系型数据库系统的标准语言，标准的SQL语句几乎可以在所有的关系型数据库上可以不加修改地使用。但标准的SQL不支持流程控制，仅仅是一些简单的语句，有时候使用起来不方便。为此，大型的关系型数据库系统都在标准的SQL基础上，结合自身的特点推出了可以编程的、结构化的SQL编程语言，比如SQL Server2000的Transact-SQL、Oracle9i的PL/SQL。 可能有同学会问了，SQL不是很好用吗，我们什么时候需要使用Transact-SQL呢？ 如果你的工作仅仅是简单地查询SQL Server2000数据库的数据，那么学习SQL也就够了。对于一些复杂的查询，SQL就鞭长莫及了，尤其是如果在查询过程中需要变量、判断、循环等控制时就必须使用Transact-SQL。 建立SQL Server2000数据库的目的还是为了开发各种应用系统，要开发C/S模式的数据库应用系统，往往会用到存储过程、触发器、函数这样的编程对象，这时就必须使用Transact-SQL。 Transact-SQL程序的结构 其实我们前面学习的SQL范例都可以称为是Transact-SQL程序。下面我们通过一个实例来说明一个稍复杂的Transact-SQL。 /*Transact-SQL程序的实例*/ use school go declare @avgrade int --判断成绩 set @avgrade=60 if(select avg(grade) from sc where cno=c01)>=@avgrade --输出结果 select 课程平均成绩超过+convert(varchar(3),@avgrade) else --输出结果 select 课程平均成绩不超过+convert(varchar(3),@avgrade) --执行批处理 go 由此可见，Transact-SQL程序主要的语法要素如下： 变量 注释符 运算符 函数 流程控制语句 批处理 10.1 变量 Transact-SQL 中可以使用两种变量：局部变量和全局变量。 10.1.1局部变量 局部变量是用户可自定义的变量，它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。 局部变量必须以@ 开头，而且必须先用DECLARE 命令声明后才可使用。其说明形式如下： DECLARE @变量名 变量类型[，@变量名变量类型…] 在使用DECLARE命令声明以后，所有的变量都被赋予初值NULL 例：声明一个变量 Declare @i char(5) 可以在一个declare 语句中声明多个变量 例: declare @i int,@j int,@k int 在Transact-SQL 中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT 或SET 命令来设定变量的值。其语法如下： SELECT @变量名= 变量值 SET @变量名= 变量值 【注意】：SET与SELECT的区别 下表列出SET与SELECT的区别: set select 同时对多个变量同时赋值 不支持 支持 表达式返回多个值时 出错 将返回的最后一个值赋给变量 表达式未返回值 变量被赋null值 变量保持原值 下面以具体示例来说明问题： create table chinadba1( userid int , addr varchar(128) ) insert into chinadba1(userid,addr) values(1, addr1) insert into chinadba1(userid,addr) values(2, addr2) insert into chinadba1(userid,addr) values(3, addr3) 1.同时对多个变量同时赋值, 使用set赋值 declare @addr1 varchar(60),@addr2 varchar(60) set @addr1=aaa,@addr2=bbb , 附近有语法错误。 同时对多个变量同时赋值 , 使用select赋值 declare @addr1 varchar(60),@addr2 varchar(60) select @addr1=aaa,@addr2=bbb 2.表达式返回多个值时，使用SET赋值 declare @addr varchar(128) set @addr=(select addr from chinadba1) /* --出错信息为 服务器: 消息 512，级别 16，状态 1，行 2 子查询返回的值多于一个。当子查询跟随在 =、!=、 <、 <=、 >、 >= 之后，或子查询用作表达式时，这种情况是不允许的。 */ 表达式返回多个值时，使用SELECT赋值 declare @addr varchar(128) select @addr=addr from chinadba1 print @addr --结果集中最后一个 addr 列的值 --结果: addr3 3.表达式未返回值时，使用SET赋值 declare @addr varchar(128) set @addr=初始值 set @addr= (select addr from chinadba1 where userid=4) print @addr 表达式未返回值时，使用SELECT赋值 declare @addr varchar(128) set @addr=初始值 select @addr= addr from chinadba1 where userid=4 print @addr --保持原值 例：声明一个长度为 8个字符的变量id，并赋值。 declare @id char（8） 或者 declare @id char（8） select @id =‘10010001’ set @id =‘10010001’ declare @id char(8) declare @id char(8) Select @id=sno set @id=(select sno From student From student Where sname=‘张三’ Where sname=‘张三’) 不能将SELECT语句的赋值功能和查询功能同时混合使用，否则系统会产生错误。 Declare @i int,@j int select @i=max(grade),@j=min(grade) From sc Declare @i int,@j int select @i=max(grade),@j=min(grade),max(grade)-min(grade) from 产品 10.1.2全局变量 全局变量是SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。 全局变量通常存储一些SQL Server 的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL 命令执行后的状态值。 使用全局变量注意： （1） 全局变量不是由用户的程序定义的，它们是在服务器级定义的 （2） 用户只能使用预先说明及定义的全局变量。 （3）引用全局变量时必须以“@@”开头。 （4）局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。 如: @@CONNECTIONS:返回自SQL SERVER最近一次启动以来连接或企图连接到SQL SERVER的连接数目。 @@ERROR:返回执行T-SQL语句的错误代码。在SQL Server执行完一条语句后，如果执行成功，则返回@@ERROR的值为0，如果该语句在执行过程中发生错误，则将返回错误信息，而@@ERROR将返回相应的错误编号，该编号一直保持下去，直到下一条语句得到执行为止。 @@IDENTITY:返回最后插入行的标识列的列值. 10.2注释符 - -用于单行注释 与C 语言相同的程序注释符号，即“/*……*/”，/* 用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。 10.3运算符 10.3.1算数运算符 两个数相加。这个加法算术运算符也可以将一个以天为单位的数字加到日期中。 A. 使用加法运算符计算客户总的可订购量 下面的示例将 产品表中当前库存量和当前已定购的以及再订购的所有产品的数量相加。 select 产品名称, 库存量+订购量+再订购量 总的可订购量 from 产品 B. 使用加法运算符将天数加到日期和时间值中 下面的示例将若干天数加到 datetime 日期上。 DECLARE @startdate datetime, @adddays int SET @startdate =2007-3-12 SET @adddays = 5 select @startdate startdate,@startdate+@adddays adddate C. 将字符和整型数据类型相加 本示例通过将字符数据类型转换为 int，将 int 数据类型值与字符值相加。如果在 char 字符串中有无效的字符，则 SQL Server 将返回错误。 DECLARE @addvalue int SET @addvalue = 15 SELECT 125127 + @addvalue 下面是结果集：125142 10.3.2赋值运算符 赋值运算符只有一个，即=（等号），用于为字段或变量赋值。 例：下面的语句先定义一个int变量@xyz，然后将其值赋为123。 declare @xyz int set @xyz=123 10.3.3位运算符 位运算符用于在两个数之间执行位操作，T-SQL的位运算符如表所示。 位运算符的操作数可以是整型或二进制数据类型（binary和varbinary，但不包括image数据类型）的任何数据，并且，两个操作数不能同时是二进制数据。下表列出了位运算支持的操作数数据类型。 位运算支持的操作数数据类型 10.3.4比较运算符 比较运算符用于测试两个表达式是否相等，除了text、ntext或image数据类型的表达式外，比较运算符还可用于其他所有类型的表达式。比较运算符运算结果为布尔数据（TRUE或FALSE）下表列出了比较运算符及其含义。 10.3.5逻辑运算符 逻辑运算符用于对某个条件进行测试，和比较运算符一样，逻辑运算的运算结果为布尔数据（TRUE或FALSE）。下表列出了逻辑运算符及其含义。 10.3.6字符串串联连接符 字符串连接运算是指使用加号（+）将两个字符串连接成一个字符串，加号作为字符串连接符。例：abc +123结果为abc123。 10.3.7运算符的优先顺序 如果一个表达式中使用了多种运算符，则运算符的优先顺序决定计算的先后次序。计算时，从左向右计算，先计算优先级高的运算，再计算优先级低的运算。 下面列出了运算符的顺序。 括号 算术运算符 比较运算符 按位运算符 逻辑运算符 赋值运算符 10.4 函数 10.4.1数学函数 数学函数通常对作为参数提供的输入值执行计算，并返回一个数字值。 常用的数学函数 （1）取近似值函数 CEILING 返回>=表达式的最小整数,返回的数据类型与表达式相同. 语法: CEILING (numeric表达式) 例： ceiling(123.45) 124 ceiling(-123.45) -123 ceiling(123) 123 FLOOR 返回<=表达式的最大整数,返回的数据类型与表达式相同. 语法: FLOOR(numeric表达式) floor(123.45) 123 floor(-123.45) -124 floor(123) 123 ROUND 返回数字表达式并四舍五入为指定的长度或精度. 返回与表达式相同的类型。 语法: ROUND (numeric表达式 , length [ , function ] ) 参数: length 是 numeric表达式将要四舍五入的精度。当 length 为正数时，numeric表达式四舍五入为 length 所指定的小数位数。当 length 为负数时，numeric表达式则按 length 所指定的在小数点的左边四舍五入。 function 是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0（默认），numeric表达式 将四舍五入。当指定 0 以外的值时，将截断 numeric表达式。 例： ROUND(748.58,1) 748.60 ROUND(748.58,2) 748.58 ROUND(748.58,3) 748.58 ROUND(748.58,-1) 750.00 ROUND(748.58,-2) 700.00 ROUND(748.58,-3) 1000.00 如果 length 是负数且大于小数点前的数字个数，ROUND 将返回 0。 例: ROUND(748.58,-4) 0.00 ROUND(534.56, 1) --结果为 ROUND(534.56, -1) --结果为 ROUND(534.56, -2) --结果为 ROUND(534.56, -3) --结果为 ROUND(534.56, -4) --结果为 ROUND(123.9995, 3) --结果为 使用 ROUND 截断 下例使用两个 SELECT 语句说明四舍五入和截断之间的区别。 例: ROUND(150.75,1) 150.80 ROUND(150.75,1,0) 150.80 --四舍五入结果 ROUND(150.75,1,1) 150.70 --截断结果 （2）取绝对值函数 ABS 返回给定数字表达式的绝对值。返回与表达式相同的类型。 语法: ABS (表达式 ) 例： ABS(-1.0) 1.0 ABS(0.0) 0.0 ABS(1.0) 1.0 （3）随机函数 RAND 返回 0 到1 之间的随机float 值。不包括0和1。 语法: RAND ( [seed] ) Seed 是给出种子值整型表达式（tinyint、smallint 或 int）。 随机数种子是用来打乱随机数的，没有它，你的随机数并不真正随机数. 种子与结果的关系是： 对于不同的种子，有不同的随机数 对于相同的种子，具有相同的随机数 产生指定范围的随机整数的公式为： Y = FLOOR(RAND() * (上限 － 下限 ＋ 1 )) ＋ 下限 例如：产生10～30之间的随机整数（包括10和30）的公式为： Y = FLOOR(RAND() *(30-10+1)) ＋ 10 （4）其它函数 POWER 返回给定表达式乘指定次方的值。返回类型与 numeric表达式相同。 语法： POWER ( numeric表达式 , y ) y 可以是精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）。 例： power(2,2) 4 power(2,3) 8 SQUARE 返回给定表达式的平方。返回float类型。 语法： SQUARE ( float表达式) 例: 下例返回半径为 1 英寸、高为 5 英寸的圆柱容积。 DECLARE @h float, @r float SET @h = 5 SET @r = 1 SELECT PI()* SQUARE(@r)* @h 容积 SQRT 返回给定表达式的平方根。 语法： SQRT ( float_表达式 ) 例：完成 PI 返回 PI 的常量值。返回float类型。 语法： PI ( ) 10.4.2字符串函数 （1）字符转换函数 ASCII（） ASCII（）函数返回字符表达式最左端字符的ASCII 码值。 语法: ASCII（character_expression ) 在ASCII 函数中，纯数字的字符串可不用 ‘ ’ 括起来，但含其它字符的字符串必须用 ‘ ’ 括起来使用，否则会出错。 例: ASCII(123) 等价于 SELECT ASCII(123) SELECT ASCII(ABC) 会提示 列名 ‘ABC’ 无效的错误 例: ASCII(abc) 97 ASCII(ABC) 65 CHAR（） CHAR（）函数用于将ASCII 码转换为字符。 语法： CHAR（character_expression） 如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（）函数会返回一个NULL 值。 CHAR(65) A CHAR(97) a 注释: CHAR 可用于将控制字符插入字符串中。下表显示了一些常用的控制字符。 换行符和回车的区别? 回车是回到本行最前面, 换行是换到下一行去。一般情况下, 回车换行同时用, 就是到下一行的头上去. 这个东西的说法来自打字机,以前的打字机要新起一行的时候有两步: 1. 打字的机头回到开始位置,这就是回车 2. 纸张往上推进一行,这就是换行 现在电脑上的 回车换行 其实已经是个整体的概念了 例： declare @x int, @y int set @x=4 set @y=5 if @x<@y print X@y print X>y+char(13)+ 你不好 LOWER（） LOWER（）函数把字符串全部转换为小写。 语法： LOWER（character_expression） 例: lower(djjAAAAjdj) UPPER（） UPPER（）函数把字符串全部转换为大写。 语法： UPPER（character_expression） 例: upper(djjAAAAjdj) STR（） STR（）函数把数值型数据转换为字符型数据。 语法： STR（[ ,length,[ ]]） length 指定返回的字符串的长度。是总长度，包括小数点、符号、数字或空格。如果没 有指定长度,默认值为 10。必须是非负值。 指定长度应大于或等于数字的符号位数+小数点前的位数+小数点位数+小数点后的位数。 decimal 指定返回的小数位数,即小数点右边的位数 ，默认为0，必须是非负值。 小数位数大于decimal 值时，STR（）函数将其下一位四舍五入。 如果小数点前的位数超过了指定的长度，则返回指定长度的‘*’。 --普通的转换 declare @i float set @i=12.1245 print 看看结果+str(@i) 结果:12 declare @i float set @i=12.45 print 看看结果+str(@i,6,4) 结果:12.450 --小数的四舍五入 declare @i float set @i=12.456789 print 看看结果+str(@i,6,4) 结果:12.457 declare @i float set @i=12345.45789 print 看看结果+str(@i,3,4) 结果:*** 小数点前的位数有5位,超过的总长度3,*的个数由总长度3决定 （2）去空格函数 LTRIM（） LTRIM（）函数把字符串头部的空格去掉。 语法: LTRIM (character_expression ) 例: select ltrim( kdjj jjjjj) 例: DECLARE @string_to_trim varchar(60) SET @string_to_trim = Hello world! print The beginning:+LTRIM(@string_to_trim) RTRIM（） RTRIM（）函数把字符串尾部的空格去掉。 语法: RTRIM (character_expression ) （3）取子串函数 LEFT（） LEFT（）函数返回部分字符串。 语法： LEFT（character_expression,integer_expression） LEFT（）函数返回的子串是从字符串最左边起到第integer_expression 个字符的部分。 若integer_expression 为负值，则返回错误。 例： declare @i varchar(100),@j int set @i=从查询或过程中无条件退出后的语句 set @j=5 print left(@i,@j) RIGHT（） RIGHT（）函数返回部分字符串。 语法： RIGHT,） RIGHT（）函数返回的子串是从字符串右边第integer_expression 个字符起到最后一个字符的部分。 若integer_expression 为负值，则返回错误。 SUBSTRING SUBSTRING（）函数返回部分字符串。 语法： SUBSTRING (expression ,starting_ position, length) SUBSTRING（）函数返回的子串是从字符串左边第starting_ position 个字符起length个字符的部分。 其中表达式可以是字符串或二进制串或含字段名的表达式。 SUBSTRING（）函数不能用于TEXT 和IMAGE 数据类型。 例： select substring(abcdefghijk,2,5) 返回字符串“abcdefghijk”的左边第2个字符起5个字符的部分 (4)字符串比较函数 CHARINDEX（） CHARINDEX（）函数返回字符串中某个指定的子串出现的开始位置。返回类型为int. 语法： CHARINDEX （，） 其中，substring _expression 是所要查找的字符串表达式。expression 为字符串也可为列名表达式。 如果没有发现子串，则返回0 值。 例： charindex(w,jhhwhhw) 4 例： declare @i varchar(500),@j varchar(10),@k int set @i=从查询或过程候用于从过程、批处理或语句块中退出 set @j=中 set @k=charindex(@j,@i) print @k print substring(@i,@k,2) PATINDEX（） PATINDEX（）函数返回字符串中某个指定的子串出现的开始位置。 语法： PATINDEX （<%substring _expression%>，） 其中,子串表达式前后必须有百分号“%” ，否则返回值为0。 与CHARINDEX（）函数不同的是，PATINDEX（）函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT 数据类型。 例： patindex(w,jhhwhhw) 0 patindex(%w%,jhhwhhw) 4 (5)字符串操作函数 QUOTENAME（） QUOTENAME（）函数返回被特定字符括起来的字符串。其语法如下： 语法： QUOTENAME （<’character_expression’>[，quote_ character]） 特定字符可以是单引号,左括号(或右括号)，中括号或者双引号。如果未指定 quote_character，则使用中括号。 quotename(abc) quotename(abc,<) quotename(abc,>) quotename(abc,<>) quotename(abc,() quotename(abc,)) quotename(abc,()) quotename(abc,") quotename(abc,"") quotename(abc,) --单引号特殊,出现的时候必须成对出现 REPLICATE（） 以指定的次数重复字符表达式 。 语法： REPLICATE （character_expression，integer_expression） 如果integer_expression 值为负值，则REPLICATE 函数返回NULL 串。 例: replicate(重复,3) replicate(重复,-3) --结果: NULL REVERSE（） REVERSE（）函数将指定的字符串的字符排列顺序颠倒。 语法： REVERSE （character_expression） 其中，character_expression 可以是字符串常数或一个列的值。 例: reverse(abcdef) REPLACE（） REPLACE（）函数返回被替换了指定子串的字符串。其语法如下： 语法： REPLACE ，，） REPLACE（）函数用string_expression3 替换在string_expression1 中的子串string_expression2。 例： replace(acdeicde,cde,xxx) - -用 xxx 替换 acdeicde中的字符串 cde。 STUFF（） STUFF（）函数用另一子串替换字符串指定位置长度的子串。 语法： STUFF（，，，） 如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则STUFF（）函数返回NULL 值。 如果length 长度大于character_表达式1 的长度，则character_表达式1 只保留首字符。 例： STUFF(abcdef, 2, 3, ijklmn) --用’ijklmn’替换第一个字符串 (abcdef) 中从第二个位置（字符 b）开始的三个字符,并返回一个字符串。 start_position是一个整数，指定删除和插入的开始位置。如果 start_position 或 length 是负数，则返回空字符串。如果 start_position 比第一个 character_表达式长，则返回空字符串。 STUFF(abcdef,-2, 3, ijklmn) --结果为：NULL STUFF(abcdef,7, 3, ijklmn) --结果为：NULL SPACE（） SPACE（）函数返回由指定空格组成的字符串。 语法： SPACE （int_表达式） 如果int_表达式的值为负值，则SPACE 函数返回NULL 串。 abc+space(2)+def’ --结果：abc def abc+space(-2)+def’ --结果：NULL 例: 把作者的姓氏串联一个逗号、两个空格和作者的名字。 USE pubs GO SELECT RTRIM(au_lname) + , + SPACE(2) + LTRIM(au_fname) FROM authors LEN（） 返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。 语法： LEN ( 字符串表达式 ) 例: len(dccv ) len( dccv ) len( dc cv ) 10.4 流程控制语句 Transact-SQL 语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。 10.5.1　BEGIN…END 语法： BEGIN <程序块> END BEGIN…END关键词对用来将一个SQL语句块组织在一起，以达到一起执行的目的。任何时候当要执行一个包含两条或两条以上的语句时，就可以使用BEGIN…END。 BEGIN…END 用来设定一个程序块，将在BEGIN…END 内的所有程序视为一个单元执行。 它可以和许多其他的流控制关键词一起使用, BEGIN…END 经常在条件语句（如IF…ELSE）中使用。 在BEGIN…END 中可嵌套另外的BEGIN…END 来定义另一程序块。 10.5.2 IF … ELSE 语法： IF <条件表达式式> <命令行或程序块块> [ELSE [条件表达式式] <命令行或程序块块>] 其中: 条件表达式用来判别条件是否满足，接下来根据条件，或是执行IF语句后的语句，或是执行ELSE后的语句。如果没有ELSE语句而且IF条件不成立，SQL Server将跳过IF语句后的代码段，继续执行后面的程序 <条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。 ELSE 子句是可选的，最简单的IF 语句没有ELSE 子句部分。 如果不使用程序块，IF 或ELSE 只能执行一条命令。 IF ELSE 可以进行嵌套，在Transact-SQL 中最多可嵌套32 级。 例1： 从SC数据表中求出学号为S1同学的平均成绩，如果此平均成绩大于或等于60分，则输出“pass”信息。 if (select avg(grade) from sc where sno=95001)>=60 begin print pass end 例2 ： 声明两个int型的变量,变量名分别为X,Y,给变量X和Y分别赋值为4和5,如果X小于Y,则输出“XY”,并且输出“你不好” declare @x int, @y int set @x=4 set @y=5 if @x<@y begin print X@y begin print X>y print 你不好 end 10.5.3 CASE CASE结构提供比IF……ELSE结构更多的选择和判断的机会。使用CASE表达式可以很方便的实现多重选择的情况，从而可以避免编写多重的IF……ELSE嵌套循环 CASE具有两种格式： 1.简单CASE表达式将某个表达式与一组简单表达式进行比较以确定结果. 2.CASE搜索表达式计算一组逻辑表达式以确定结果. 格式1： CASE <表达式> WHEN <表达式> THEN <表达式> … WHEN <表达式> THEN <表达式> [ELSE <表达式>] END 该语句的执行过程是： 将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。 ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。 例1：从学生表Student中，选取SNO，SEX，如果SEX为“男”则输出“M”，如果为“女”输出“F”。 SELECT Sno, Ssex, CASE Ssex WHEN 男 THEN M WHEN 女 THEN F END FROM Student 例2 ：使用Northwind的中文数据库,从产品表中选取产品ID,产品名称,类别ID,如果类别ID为1时,则输出“饮料”;如果类别ID为2时,则输出“调味品”;如果类别ID为3时,则输出“点心”;如果类别ID为4时,则输出“日用品”;如果类别ID为5时,则输出“谷类/麦片”;如果类别ID为6时,则输出“肉/家禽”;如果类别ID为7时,则输出“特制品”;如果类别ID为8时,则输出“ 海鲜” select 产品ID,产品名称,类别ID, case 类别ID when 1 then 饮料 when 2 then 调味品 when 3 then 点心 when 4 then 日用品 when 5 then 谷类/麦片 when 6 then 肉/家禽 when 7 then 特制品 when 8 then 海鲜 end from 产品 例3：使用Northwind的中文数据库,从运货商表中选取运货商编号,运货商编号为”1”时,输出”急速快递”;运货商编号为”2”时,输出”统一包裹”;运货商编号为”3”时,输出”联邦货运 格式2： CASE WHEN <条件表达式> THEN <表达式> … WHEN <条件表达式> THEN <表达式> [ELSE <表达式>] END 该语句的执行过程是： 首先测试WHEN后的表达式的值，如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值 如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值。 如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。 例1：从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。 SELECT SNO,CNO,SCORE, SCORE=CASE WHEN SCORE IS NULL THEN 未考 WHEN SCORE<60 THEN 不及格 WHEN SCORE>=60 AND SCORE<70 THEN 及格 WHEN SCORE>=70 AND SCORE<90 THEN 良好 WHEN SCORE>=90 THEN 优秀 END FROM SC 例2：使用Northwind的中文数据库，从产品表中选取产品ID，产品名称，供应商ID，库存量，如果库存量大于或等于30时为输出"安全库存";如果库存量大于或等于20并且小于30时输出"临危库存";小于20时输出"危险库存" select 产品ID,产品名称,供应商ID,库存量, case when 库存量>=30 then 安全库存 when 库存量>=20 and 库存量<30 then 临危库存 when 库存量<20 then 临危库存 end from 产品 10.5.4 WHILE…CONTINUE…BREAK 其语法如下： WHILE <条件表达式> [BEGIN] <程序块> [BREAK] [CONTINUE] [程序块] [END] WHILE 命令在设定的条件成立时，会重复执行命令行或程序块。 CONTINUE 命令可以让程序跳过CONTINUE 命令之后的语句，回到WHILE 循环的第一行，继续进行下一次循环。 BREAK 命令则让程序完全跳出循环，结束WHILE 命令的执行。 WHILE 语句也可以嵌套。 例1：编程求1到100的和 declare @i int declare @sum int set @i=1 set @sum=0 while @i<=100 begin set @sum=@sum+@i set @i=@i+1 end select @sum as 合计 , @i as 循环数 例2：以下程序计算1－100之间所有能被3整除的数的个数及总和。 DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT SET @S=0 SET @I=1 SET @NUMS=0 WHILE (@I<=100) BEGIN IF (@I%3=0) BEGIN SET @S=@S+@I SET @NUMS=@NUMS+1 END SET @I=@I+1 END PRINT @S PRINT @NUMS 例3：在下例中，如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。 WHILE (SELECT AVG(price) FROM titles) < $30 BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUE END PRINT Too much for the market to bear 10.5.5 WAITFOR 语法： WAITFOR {DELAY <‘时间’> | TIME <‘时间’>} WAITFOR 命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。 其中‘时间’必须为DATETIME 类型的数据，但不能包括日期。 各关键字含义如下： DELAY：用来设定等待的时间，最多可达24 小时 TIME：用来设定等待结束的时间点 例：等待1 小时2 分零3 秒后才执行SELECT 语句。 waitfor delay ‘01:02:03’ select * from 雇员 例：在早晨7点执行UPDATE语句。 waitfor time ‘7:00’ update 产品 set 单价=单价+1 10.5.6 GOTO 语法： GOTO 标识符 GOTO 命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。 作为跳转目标的标识符可为数字与字符的组合。但必须以“：”结尾。 在GOTO 命令行，标识符后不必跟“：” 例：求1＋2＋3＋…＋10的总和。 DECLARE @S SMALLINT,@I SMALLINT SET @I=1 SET @S=0 BEG: IF (@I<=10) BEGIN SET @S=@S+@I SET @I=@I+1 GOTO BEG END PRINT @S --等同于下面的循环语句 DECLARE @S SMALLINT,@I SMALLINT SET @I=1 SET @S=0 While (@I<=10) begin SET @S=@S+@I SET @I=@I+1 end 10.6批处理 批处理是包含一个或多个Transact-SQL语句的组，它将一次性地发送到SQL Server中执行，用GO来通知SQL Server一批Transact-SQL语句的结束。一些SQL语句不可以放在一个批中进行处理，它们需要遵守以下规则：大多数Create命令要在单个批处理命令中进行，但create database,create table,create index例外。 23