CASE

CASE函数可以算多个条件式,并将其中一个符合条件的果表达式返回。CASE函数按照使用形式的不同,可以分简单CASE函数和搜索CASE函数

简单CASE 函数将某个表达式与一组简单表达式行比以确定果。

CASE 搜索函数算一表达式以确定果。

简单CASE函数:

简单CASE表达式将一个测试表达式与一组简单表达式行比,如果某个简单表达式与测试表达式的相等,返回相应结果表达式的When子句提供测试值,并将When子句所指定的果表达式返回。测试表达式与测试值的数据一致。找不到足条件的返回NULL

法:

CASEinput_expression

WHENwhen_expression THEN

result_expression[ ...n ]

[ELSE else_result_expression]

END

或者:

Case入表达式

When表达式1 then 果表达式

...... ...

When表达式n then 果表达式

[else]

[果表达式(n+1]

end

CASE搜索函数

在搜索CASE表达式中,CASE关键字后面不跟任何表达式,在When子句后面跟的都是布表达式。

法:

CASE

WHENBoolean_expression

THENresult_expression[ ...n ]

[ELSEelse_result_expression]

END

或者:

Case

When表达式1 then 果表达式

...... ...

When表达式n then 果表达式

[else]

[果表达式(n+1]

End

 

简单case句和搜索case句之的区

简单case 句只能是when 后面的表达式完全匹配case 后的表达式,相当于=,所以也不能匹配null

搜索case 句可以作条件,那可以使用like!=,between..and,<,=is null,is not

null 等,比简单case 句的使用更加广泛,完全可以替代简单case

Case 句中可以使用子查询,但是必返回一行,不可以是多行!

Case 表达式返回的是一个确定的value,如果没有else,若前面的都不匹配,返回null<else 不是必,都没有匹配返回nullPL/SQL 中的case 句不同,case句如果不写else,都没有匹配,则报case_not_found 异常>!

简单case 中的表达式,when 后面的表达式应该全部保持一致。

所有的then 后面的return_value 型要保持一致!

简单case 表达式,也就是case 表达式 when…when null 是取不到。也就是case 后面的表达式如果值为null,不会与when null 匹配,只会与else 匹配。点,如果case 后面的表达式有可能null,如果需要匹配null,那可以使用decodesearched case

于搜索 case 句来,有自动类转换,只要条件成立就可以。如:selectcase when1='1' then 1 end from dual;--1=‘1‘条件成立!

参数最高限制255 个。包括case exp 中的exp else 中的,以及when exp1 value 1 算两个参数。如果复杂,超过这个限制,可以考使用嵌套case

DEMO

1 、使用简单CASE 函数的 SELECT

SELECT 句中,简单CASE 函数仅检查是否相等,而不行其他比。以下

示例使用 CASE 函数更改品系列类别示,以使类别更易理解。

USE AdventureWorks;

GO

SELECTProductNumber, Category =

CASEProductLine

WHEN'R' THEN 'Road'

WHEN'M' THEN 'Mountain'

WHEN'T' THEN 'Touring'

WHEN'S' THEN 'Other sale items'

ELSE'Not for sale'

END,

Name

FROMProduction.Product

ORDERBY ProductNumber;

GO

selectProductNumber,ProductLine,name

fromProduction.Product

whereProductLine <> 'null'

andProductNumber = 'BK-M18B-40'

返回:

分析:

以上示例用于出三列分ProductNumberCategoryName

其中Category后跟一个简单CASE函数,用于比表Production.Product中的ProductLine.

ProductLine输值为RCategory返回Road,值为M返回Mountain,

值为T,返回Touring,值为S,返回Other sale items

若不匹配以上数值时返回 Not for sale

CASEProductLine

WHEN'R' THEN 'Road'

WHEN'M' THEN 'Mountain'

WHEN'T' THEN 'Touring'

WHEN'S' THEN 'Other sale items'

ELSE'Not for sale'

END,

2 、使用CASE 搜索函数的 SELECT

SELECT 句中,CASE 搜索函数允根据比较值果集内对值进行替

下面的示例根据品的价格范文本注

USE AdventureWorks;

GO

SELECTProductNumber, Name, 'Price Range' =

CASE

WHENListPrice = 0 THEN 'Mfg item - not for resale'

WHENListPrice < 50 THEN 'Under $50'

WHENListPrice >= 50 and ListPrice <250 THEN 'Under $250'

WHENListPrice >= 250 and ListPrice <1000 THEN 'Under $1000'

ELSE'Over $1000'

END

FROMProduction.Product

ORDERBY ProductNumber ;

GO

selectProductNumber,listprice,name

fromProduction.Product

whereProductNumber = 'BB-7421'

返回果:

分析:

以上示例用于ProductNumber, Name, 'Price Range'三列数

其中'Price Range'后跟一个CASE 搜索函数句,用于比表Production.Product中的listprice列中的数值进行判断.listprice=0'Price Range'返回'Mfg item - not

forresale';若<50,返回'Under $50';>=50 and <250,返回'Under $250';>=250 and <1000,返回'Under $1000';剩余的返回'Over $1000'.

CASE

WHENListPrice = 0 THEN 'Mfg item - not for resale'

WHENListPrice < 50 THEN 'Under $50'

WHENListPrice >= 50 and ListPrice <250 THEN 'Under $250'

WHENListPrice >= 250 and ListPrice <1000 THEN 'Under $1000'

ELSE'Over $1000'

END

DEMO3使用简单CASE函数,根据状用于判断是否尚有.

USEMARKET

Select品名称,供商,存量,状=

CASE

when0 then ‘存无

when1 then ‘存有

END

FROMgoods

DEMO4使用CASE 搜索函数,根据商品价格判商品档次

USEMARKET

Select品名称,供商,价格=

CASE

when(价格>=5000then‘高档商品

when(价格>=1500then‘中档商品

when(价格>=900then‘中档商品

when(价格>=100then‘低档商品

ElSE‘小商品

END

FROMgoods

 

print句:

它主要用于把消息传递用程序的消息理程序,通常在屏幕上.

法如下:

print '任何ASCII文本' | @SQL局部| @@SQL全局| 字符串表达式 ...

:

print句向客端返回一个用自定的消息,示一个字符串(其最长为255哥字符

).SQL局部.SQL全局...

如果量的不是字符串,需先用数据转换函数(convert()函数)将其转换为字符串.表达式的度可以超8000个字符,但超8000个的将不再被..

 

WHILECONTINUEBREAK

在程序中当需要多次重复处理某工作,就需使用WHILE环语句。WHILE句通表达式来置一个循条件,当条件,重复执行一个SQL句或,否退出循继续执行后面句只要指定的条件True WHILE 句就会重复语句或。下面两个 Transact-SQL 句通常和 WHILE 一起使用:REAK CONTINUEBREAK 句退出最内WHILE CONTINUE 重新WHILE

例如,如果没有其他行可以理,程序可能BREAK 句。例如,如果要继续执行代可以CONTINUE 句。

如果将 SELECT 句用作 WHILE 句的条件,SELECT 句必在括号中

法:

WHILEBoolean_expression --<条件表达式>

Begin

{sql_statement| statement_block } --<命令行或程序>

[BREAK ]

{sql_statement| 句序列 statement_block } --<命令行或程序>

[CONTINUE ]

句序列3 --<命令行或程序>

End

WHILE 命令在定的条件成立会重复执行命令行或程序

CONTINUE 命令可以程序跳CONTINUE 命令之后的句,回到WHILE 的第

一行命令。

BREAK 命令则让程序完全跳出循WHILE 命令的行。

WHILE 句也可以嵌套。

在嵌套循中,内BREAK 命令将控制权转移到外一的循继续

DEMO1在游中使用 WHILE

下面的示例使用 WHILE 句控制行的提取数。

USE AdventureWorks;

GO

DECLAREabc CURSOR FOR

SELECT* FROM Purchasing.ShipMethod;

OPENabc;

FETCHNEXT FROM abc

WHILE(@@FETCH_STATUS = 0)

FETCHNEXT FROM abc;

CLOSEabc;

DEALLOCATEabc;

GO

DEMO2在嵌套的 IF...ELSE WHILE 中使用 BREAK CONTINUE

在以下示例中,如果品的平均价小于 $300WHILE 将价格乘 2,然后选择最高价格。如果最高价格小于或等于 $500WHILE 重新始,并再次将价格乘 2不断地将价格乘 2,直到最高价格超$500,然后退出 WHILE ,并出一条消息。

USE AdventureWorks;

GO

WHILE( SELECT AVG(ListPrice)

FROMProduction.Product) < $300

BEGIN

UPDATEProduction.Product

SET ListPrice = ListPrice * 2

SELECTMAX(ListPrice) FROM Production.Product

IF (SELECT MAX(ListPrice) FROM Production.Product) > $500

BREAK

ELSE

CONTINUE

END

PRINT'Too much for themarket to bear';

DEMO3建一个数学运算表达式:

DECLARE@x INT, @y INT, @c INT

SELECT@x = 1, @y = 1

WHILE@x < 3

BEGIN

PRINT@x --打印x

WHILE@y < 3

BEGIN

SELECT@c = 100*@x+ @y

PRINT@c --打印c

SELECT@y = @y + 1

END

SELECT@x = @x + 1

SELECT@y = 1

END

返回果:

1101 102

2201 202

 

GOTO

GOTO句可以使程序直接跳到指定的标识符的位置处继续执行,而位于GOTO句和标识符之的程序将不会被行。GOTO句和标识符可以用在、批理和存储过程中,标识符可以数字与字符的合,但必‖结尾。

法:

GOTOlabel

……

label:

DEMO1分行打印字符12345

DECLARE@x INT

SELECT@x = 1

lab_1:PRINT @x

SELECT@x = @x + 1

WHILE@x < 6

GOTOlab_1

DEMO2以下示例示如何将 GOTO 用作分支机制

DECLARE@Counter int;

SET @Counter = 1;

WHILE@Counter < 10

BEGIN

SELECT@Counter

SET @Counter = @Counter + 1

IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.

IF @Counter = 5 GOTO Branch_Two --This will never execute.

END

Branch_One:

SELECT'Jumping To BranchOne.'

GOTOBranch_Three; --This will prevent Branch_Two fromexecuting.

Branch_Two:

SELECT'Jumping To BranchTwo.'

Branch_Three:

SELECT'Jumping To BranchThree.'

返回果:

DEMO3利用GOTO句求出从1加到100

declare@sum int, @count int

select@sum=0, @count=1

label_1:

select@sum=@sum+@count

select@count=@count+1

if @count<=100

gotolabel_1

select@count, @sum

返回:

 

WAITFOR

WAITFOR句用于暂时停止SQL句、或者存储过程等,直到所定的时间或者所定的时间已到才继续执行。

法:

WAITFOR{ DELAY 'time' | TIME 'time' }

其中,DELAY用于指定时间间隔,TIME用于指定某一刻,其数据datetime,在datetime数据中不允有日期部分,即采用hh:mm:ss‘格式。

DEMO

1 . 使用WAITFOR TIME

以下示例在10:20 (22:20) 行存储过sp_update_job

USE msdb;

EXECUTEsp_add_job @job_name = 'TestJob';

BEGIN

WAITFOR TIME'22:20';

EXECUTEsp_update_job @job_name = 'TestJob',

@new_name ='UpdatedJob';

END;

GO

2 、使用WAITFOR DELAY

以下示例在两小的延行存储过程。

BEGIN

WAITFOR DELAY'02:00';

EXECUTEsp_helpdb;

END;

GO

3 、等待1 2 分零3 秒后才SELECT

WAITFOR DELAY01:02:03

SELECT * FROMToys

4 、等到11 点零8 分后才SELECT

WAITFOR TIME23:08:00

SELECT * FROMToys

 

RETURN

RETURN句用于无条件地止一个查询、存储过程或者批理,此位于RETURN句之后的程序将不会被行,在括号内可指定一个返回

法:

RETURN[ integer_expression ]

其中:参数integer_expression返回的整型。存储过程可以给调程或用程序,返回整型

DEMO1:从过程返回

以下示例示如果在findjobs 没有指定用名作参数,RETURN 将使程向用屏幕送一条消息后退出。如果指定了用名,将从相的系表中索此用在当前数据库创建的所有象名。

CREATEPROCEDURE findjobs @nm sysname = NULL

AS

IF @nm IS NULL

BEGIN

PRINT'You must give auser name'

RETURN

END

ELSE

BEGIN

SELECTo.name, o.id, o.uid

FROMsysobjects o INNER JOIN master..syslogins l

ON o.uid = l.sid

WHEREl.name = @nm

END;

DEMO2返回状

以下示例将检查指定系人的 ID 的状。如果所在的州是 Washington (WA),将返回状1。在其他情况下(StateProvince WA 以外的,或者 ContactID 没有匹配的行),返回状2

USE AdventureWorks;

GO

CREATEPROCEDURE checkstate@param varchar(11)

AS

IF (SELECT StateProvince

FROMPerson.vAdditionalContactInfo

WHEREContactID = @param) = 'WA'

RETURN1

ELSE

RETURN2;

GO