《数据库原理及应用2》课件第6章

第6章Transact-SQL6.1常量6.2变量6.3流程控制语句6.4函数6.5用户定义函数6.6游标6.7本章小结

6.1常量

1．字符串常量

2．整型常量

3．实型常量

4．日期时间型常量

5．货币型常量

6．Uniqueidentifier常量

6.2变量

变量对于一种语言来说是必不可少的组成部分。Transact-SQL语言允许使用两种变量：一种是用户自己定义的局部变量(LocalVariable)，另一种是系统提供的全局变量(GlobalVariable)。

1．局部变量

1)局部变量的声明

declare@变量名变量类型[,@变量名变量类型…]

其中，变量类型可以是SQLServer支持的所有数据类型，也可以是用户自定义的数据类型。

2)局部变量的赋值

局部变量不能使用“变量=变量值”的格式进行初始化，必须使用select或set语句来设置其初始值。

(1)通过set来赋值。

格式为：

set@局部变量=变量值

【例1】declare@a1int，@a2char(10)

Set@a1=2012

Set@a2=‘china’

(2)通过select来赋值。

格式为：

select@局部变量=变量值

【例2】declare@namechar(10)

Select@name=姓名from学生表where学号=‘001’

【例3】将字符串“sqlserver”和“2000”做连接，并输出。

declare@anvarchar(10),@bnvarchar(10)set@a=‘sqlserver’

set@b=′2000′

select@a+@b

2．全局变量

全局变量是SQLServer2000系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQLServer2000的配置设置值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或者Transact-SQL命令执行后的状态值。引用全局变量时，全局变量的名字前面要有两个标记符“@@”。不能定义与全局变量同名的局部变量。从SQLServer7.0开始，全局变量就以系统函数的形式使用。

【例4】select*from学生表。

Print@@rowcount

【例5】select@@version

6.3流程控制语句

1．Print语句

Print语句用于向客户端返回用户定义的消息。使用Print可以帮助我们排除Transact-SQL代码中的故障，检查数据值或生成报告。

【例6】Print'ABCDEFG'

2．begin…end语句

begin…end语句包括一系列Transact-SQL语句，从而可以执行一组Transact-SQL语句。begin和end是控制流语言的关键字。

语法：begin

SQL语句1

SQL语句2

…

end

3．if…else语句

1)语法

ifBoolean_expression

{sql_statement|statement_block}

[else

{sql_statement|statement_block}]

2)参数

(1)Boolean_expression：返回True或False的表达式。如果布尔表达式中含有Select语句，则必须用括号将Select语句括起来。

(2) {sql_statement|statement_block}：任何Transact-SQL语句或用语句块定义的语句组。除非使用语句块，否则if或else条件只能执行其后的一条Transact-SQL语句。

若要定义语句块，必须使用控制流关键字begin和end。

【例7】判断a是正数还是负数。

declare@aint

set@a=10

if@a>0

print‘a为正数’

else

print'a为负数'

【例8】判断成绩表里的成绩是否大于90分。

ifexists(select*from成绩表where成绩>90)

begin

Select*from成绩表where成绩>90

end

else

Print'nostudent'

4．While语句

While语句用于设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用Break和Continue关键字在循环内部控制While语句的执行。

1)语法

WhileBoolean_expression

{sql_statement|statement_block}

[Break]

{sql_statement|statement_block}

[Continue]

{sql_statement|statement_block}

2)参数

(1) Boolean_expression：

(2) {sql_statement|statement_block}：

(3) Break：

(4) Continue：

【例9】计算1～100的奇数和。

【例10】计算和小于10的那些数的和。

5．Case函数

Case函数用于计算条件列表并返回多个可能结果表达式之一。

格式1：格式2：

【例11】从成绩表查询学号为2013001号的成绩，并将成绩转换为等级。

6．Waitfor延迟语句

在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。

【例12】要求5秒钟之后执行select语句。

waitfordelay‘00：00：05’

select*from学生表

6.4函数

SQLServer2000提供了一些内置函数，用户可以使用这些函数方便地实现一些功能。以下举例说明一些常用的函数。

1．转换函数

(1) cast()：将一种数据类型的表达式转换为另一种数据类型的表达式。

语法：

cast(expressionasdata_type[(length)])

【例13】select姓名+‘(’+cast(成绩asvarchar(10))+‘)’from学生表

下列代码将成绩为null值的数据项显示为0分。

select姓名+‘(’+(

case

when成绩isnullthen‘0’

elsecast(成绩asvarchar(10))

end)

+')'from学生表

(2) convert()：将一种数据类型的表达式转换为另一种数据类型的表达式。

语法：

convert(data_type[(length)],expression[,style])

【例14】select姓名+'('+convert(nvarchar(10),出生日期,20)+')'from学生表

2．字符串函数

(1) len(character_expression)：返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。len对相同的单字节和双字节字符串返回相同的值。

【例15】selectmax(len(姓名))from学生表

(2) datalength(expression)：返回用于表示任何表达式的字节数。

【例16】declare@anvarchar(10)

set@a=‘abc’

selectdatalength(@a)/*显示6*/

(3) left(character_expression,integer_expression)：返回字符串中从左边开始指定个数的字符。

【例17】declare@stringtestchar(10)

set@stringtest=‘robin’

selectleft(@stringtest,3)/*显示rob*/

(4) right(character_expression,integer_expression)：返回字符表达式中从起始位置(从右端开始)到指定字符位置(从右端开始计数)的部分。

【例18】declare@stringtestchar(10)

set@stringtest=‘robin’

selectright(@stringtest,3)/*显示in*/

(5) substring(value_expression,start_expression,length_expression)：返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分。

【例19】selectx=substring(‘abcdef’,2,3)/*显示bcd*/

(6) upper(character_expression)：返回将小写字符转换为大写字符后得到的字符表达式。

【例20】selectupper('hello')/*显示HELLO*/

(7) lower(character_expression)：返回将大写字符转换为小写字符后得到的字符表达式。

【例21】declare@stringtestchar(10)

set@stringtest=‘ROBIN’

selectlower(left(@stringtest,3))/*显示rob*/

(8) space(integer_expression)：返回由重复的空格组成的字符串。

【例22】select姓名+space(3)+性别from学生表

(9) replicate(character_expression,times)：返回多次复制后的字符表达式。times参数的计算结果必须为整数。

【例23】declare@aint

set@a=3

printreplicate('*',@a)/*显示****/

【例24】以“*”方式输出菱形。

(10) stuff(character_expression,start,length,character_expression)：将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符，然后将第二个字符串插入到第一个字符串的开始位置。

【例25】selectstuff(‘axyzfg’,2,3,‘bcde’)/*结果为‘abcdefg’*/

(11) reverse(character_expression)：按相反顺序返回字符表达式。

【例26】printreverse('mountainbike')/*输出ekibniatnuom*/

(12) ltrim(characterexpression)：返回删除了前导空格的字符表达式。

【例27】declare@stringtestchar(10)

set@stringtest=‘robin’

select‘start-’+ltrim(@stringtest),‘start-’+@stringtest/*显示start-robinstart-robin*/

(13) rtrim(characterexpression)：返回删除了尾随空格的字符表达式。

【例28】declare@stringtestchar(10)

set@stringtest=‘robin’

select@stringtest+'-end',rtrim(@stringtest)+'-end'/*显示robin-endrobin-end*/

(14) trim(character_expression)：返回删除了前导空格和尾随空格的字符表达式。

(15) charindex(expression1,expression2[,start_location])：在expression2中搜索expression1并返回其起始位置(如果能找到)。搜索的起始位置为start_location。

【例29】declare@documentvarchar(64)

(16) patindex(‘%pattern%’,expression)：返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回0。

【例30】selectpatindex(‘%李%’,姓名)from学生表

(17) str(float_expression[,length[,decimal]])：返回由数字数据转换来的字符数据。其中，float_expression表示带小数点的近似数字(float)数据类型的表达式；length表示总长度，它包括小数点、符号、数字以及空格，默认值为10；decimal表示小数点后的位数。

【例31】selectstr(123.45,6,1)/*输出123.5*/

(18) char(integer_expression)：将intASCII代码转换为字符。其中，integer_expression为介于0和255之间的整数。如果该整数表达式不在此范围内，将返回null值。

(19) replace(character_expression,searchstring,

replacementstring)：将表达式中的一个字符串替换为另一个字符串或空字符串后，返回一个字符表达式。

【例32】printreplace('mountainbike','mountain','allterrain')/*返回allterrainbike*/

3．日期函数

(1) getdate()：返回系统的当前日期，getdate函数不使用参数。

(2) datepart(datepart,date)：返回一个表示日期的指定日期部分的整数。其中，datepart参数指定需要对日期中的哪一部分返回新值，它可取下列值：year、quarter、month、dayofyear、day、week、weekday、hour、minute、second、millisecond等。

【例33】selectdatepart(year,getdate())

(3) datename(datepart,date)：返回表示指定date的指定datepart的字符串。

【例34】select姓名,datepart(month,出生日期)from学生表

(4) dateadd(datepart,number,date)：将指定number时间间隔(有符号整数)与指定date的指定datepart相加后，返回该date。

【例35】selectdateadd(month,1,‘2006-08-30’)/*显示2006-09-3000:00:00.000*/

(5) datediff(datepart,startdate,enddate)：返回指定的startdate和enddate之间所跨的指定datepart边界的计数(带符号的整数)。

【例36】declare@startdatetime,@enddatetime

set@start=‘2007-05-0812:10:09’

set@end=‘2007-05-0712:10:09’

selectdatediff(day,@start,@end)/*显示-1*/

(6) day(date)、month(date)、year(date)：返回一个整数，该整数表示指定date的日、月、年。

【例37】printyear(getdate())

4．数学函数

(1) abs(numeric_expression)：返回数值表达式的绝对值。

(2) ASCII(character_expression)：返回字符表达式中最左侧的字符的ASCII代码值。

【例38】declare@stringtestchar(10)

set@stringtest=ASCII(‘robin’)

select@stringtest/*显示82*/

(3) ceiling(numeric_expression)：返回大于或等于指定数值表达式的最小整数。

(4) floor(numeric_expression)：返回小于或等于指定数值表达式的最大整数。

(5) power(numeric_expression,power)：返回对数值表达式进行幂运算的结果。power参数的计算结果必须为整数。

(6) pi()：返回pi的常量值。

(7) sqrt(float_expression)：返回指定浮点值的平方根。

(8) square(float_expression)：返回指定浮点值的平方。

(9) rand([seed])：返回从0到1之间的随机float值。

(10) round(numeric_expression,length[,function])：返回一个数值，四舍五入到指定的长度或精度。

5．其他常用函数

(1) isdate(expression)：如果expression是datetime或smalldatetime数据类型的有效日期或时间值，则返回1；否则，返回0。

【例39】selectisdate(‘2009/2/29’)/*显示0*/

(2) isnull(check_expression,replacement_value)：使用指定的替换值替换null。

【例40】select姓名,isnull(成绩,0)from学生表

(3) nullif(expression,expression)：如果两个指定的表达式相等，则返回空值。

(4) isnumeric(expression)：确定表达式是否为有效的数值类型。

(5) coalesce(expression[,...n])：返回其参数中第一个非空表达式。

6.5用户定义函数

与编程语言中的函数类似，SQLServer的用户定义函数用于接受参数、执行操作(例如复杂计算)并将操作结果以值的形式返回。返回值可以是单个标量值或结果集。

在SQLServer中使用用户定义函数有以下优点：

(1)允许模块化程序设计。

(2)执行速度更快。

(3)减少网络流量。

1．标量函数

(1)创建标量函数。

【例41】

【例42】

(2)调用标量函数。

可以在Transact-SQL语句中允许使用标量表达式的任何位置调用返回标量值(与标量表达式的数据类型相同)的用户定义函数。必须使用至少由两部分组成名称的函数来调用标量值函数，即架构名.对象名。

【例43】selectdbo.f1(23,28)

2．表值函数

(1)创建内联表值函数。

【例44】

(2)调用内联表值函数。

【例45】select*fromf1(‘男’)

注：调用时不需指定架构名。

3．多语句表值函数

(1)创建多语句表值函数。

【例46】

(2)调用多语句表值函数。

【例47】select*fromf1(1)

6.6游标

游标的作用如下：

(1)允许定位在结果集的特定行。

(2)从结果集的当前位置检索一行或一部分行。

(3)支持对结果集中当前位置的行进行数据修改。

(4)为由其他用户对显示在结果集中的数据库数据所做的更改提供不同