SQL Server的程序设计Word文件下载.docx

1、 流程控制语句 批处理10.1 变量 Transact-SQL 中可以使用两种变量：局部变量和全局变量。10.1.1局部变量局部变量是用户可自定义的变量，它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。局部变量必须以 开头，而且必须先用DECLARE 命令声明后才可使用。其说明形式如下：DECLARE 变量名 变量类型，变量名变量类型在使用DECLARE命令声明以后，所有的变量都被赋予初值NULL例：声明一个变量Declare i char（5）可以在一个declare 语句中声明多个变量例: declare i int,j int,k in

2、t在Transact-SQL 中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT 或SET 命令来设定变量的值。其语法如下：SELECT 变量名= 变量值SET 变量名= 变量值【注意】：SET与SELECT的区别下表列出SET与SELECT的区别:setselect同时对多个变量同时赋值不支持支持表达式返回多个值时出错将返回的最后一个值赋给变量表达式未返回值变量被赋null值变量保持原值下面以具体示例来说明问题：create table chinadba1（ userid int , addr varchar（128） ） insert into china

3、dba1（userid,addr） values（1, addr1insert into chinadba1（userid,addr） values（2, addr2insert into chinadba1（userid,addr） values（3, addr31.同时对多个变量同时赋值, 使用set赋值 declare addr1 varchar（60）,addr2 varchar（60）set addr1=aaa,addr2=bbb , 附近有语法错误。 同时对多个变量同时赋值 , 使用select赋值 select addr1=2.表达式返回多个值时，使用SET赋值 declare

4、addr varchar（128） set addr=（select addr from chinadba1） /* -出错信息为 服务器: 消息 512，级别 16，状态 1，行 2 子查询返回的值多于一个。当子查询跟随在 =、!=、 、 、 = 之后，或子查询用作表达式时，这种情况是不允许的。*/ 表达式返回多个值时，使用SELECT赋值 select addr=addr from chinadba1 print addr -结果集中最后一个 addr 列的值 -结果: addr3 3.表达式未返回值时，使用SET赋值 set addr=初始值set addr= （select addr

5、from chinadba1 where userid=4） print addr 表达式未返回值时，使用SELECT赋值 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 F

6、rom student From student Where sname=张三 Where sname=张三）不能将SELECT语句的赋值功能和查询功能同时混合使用，否则系统会产生错误。Declare i int,j intselect i=max（grade）,j=min（grade）From scselect i=max（grade）,j=min（grade）,max（grade）-min（grade）from 产品10.1.2全局变量全局变量是SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQL Server 的配置

7、设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL 命令执行后的状态值。使用全局变量注意：（1） 全局变量不是由用户的程序定义的，它们是在服务器级定义的（2） 用户只能使用预先说明及定义的全局变量。（3）引用全局变量时必须以“”开头。（4）局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。如:CONNECTIONS:返回自SQL SERVER最近一次启动以来连接或企图连接到SQL SERVER的连接数目。ERROR:返回执行T-SQL语句的错误代码。在SQL Server执行完一条语句后，如果执行成功，则返回ERROR的值为0，如果该语句在执行

8、过程中发生错误，则将返回错误信息，而ERROR将返回相应的错误编号，该编号一直保持下去，直到下一条语句得到执行为止。IDENTITY:返回最后插入行的标识列的列值.10.2注释符- -用于单行注释与C 语言相同的程序注释符号，即“/*/”，/* 用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。10.3运算符10.3.1算数运算符两个数相加。这个加法算术运算符也可以将一个以天为单位的数字加到日期中。A. 使用加法运算符计算客户总的可订购量下面的示例将 产品表中当前库存量和当前已定购的以及再订购的所有产品的数量相加。 select 产品名称, 库存量+订购量+再订购量 总

9、的可订购量 from 产品B. 使用加法运算符将天数加到日期和时间值中下面的示例将若干天数加到 datetime 日期上。 DECLARE startdate datetime, adddays int SET startdate =2007-3-12 SET adddays = 5 select startdate startdate,startdate+adddays adddateC. 将字符和整型数据类型相加本示例通过将字符数据类型转换为 int，将 int 数据类型值与字符值相加。如果在 char 字符串中有无效的字符，则 SQL Server 将返回错误。DECLARE addva

10、lue int SET addvalue = 15 SELECT 125127 + addvalue 下面是结果集：125142 10.3.2赋值运算符赋值运算符只有一个，即=（等号），用于为字段或变量赋值。下面的语句先定义一个int变量xyz，然后将其值赋为123。 declare xyz int set xyz=12310.3.3位运算符位运算符用于在两个数之间执行位操作，T-SQL的位运算符如表所示。位运算符的操作数可以是整型或二进制数据类型（binary和varbinary，但不包括image数据类型）的任何数据，并且，两个操作数不能同时是二进制数据。下表列出了位运算支持的操作数数据类

11、型。位运算支持的操作数数据类型10.3.4比较运算符 比较运算符用于测试两个表达式是否相等，除了text、ntext或image数据类型的表达式外，比较运算符还可用于其他所有类型的表达式。比较运算符运算结果为布尔数据（TRUE或FALSE）下表列出了比较运算符及其含义。10.3.5逻辑运算符逻辑运算符用于对某个条件进行测试，和比较运算符一样，逻辑运算的运算结果为布尔数据（TRUE或FALSE）。下表列出了逻辑运算符及其含义。10.3.6字符串串联连接符字符串连接运算是指使用加号（+）将两个字符串连接成一个字符串，加号作为字符串连接符。abc +123结果为abc123。10.3.7运算符的优先

12、顺序 如果一个表达式中使用了多种运算符，则运算符的优先顺序决定计算的先后次序。计算时，从左向右计算，先计算优先级高的运算，再计算优先级低的运算。 下面列出了运算符的顺序。括号 算术运算符比较运算符按位运算符逻辑运算符赋值运算符10.4 函数10.4.1数学函数数学函数通常对作为参数提供的输入值执行计算，并返回一个数字值。常用的数学函数（1）取近似值函数CEILING 返回=表达式的最小整数,返回的数据类型与表达式相同.语法:CEILING （numeric表达式） 例：ceiling（123.45） 124ceiling（-123.45） -123ceiling（123） 123FLOOR 返

13、回=表达式的最大整数,返回的数据类型与表达式相同. FLOOR（numeric表达式） floor（123.45） 123 floor（-123.45） -124 floor（123） 123ROUND 返回数字表达式并四舍五入为指定的长度或精度. 返回与表达式相同的类型。ROUND （numeric表达式 , length , function ）参数:length是 numeric表达式将要四舍五入的精度。当 length 为正数时，numeric表达式四舍五入为 length 所指定的小数位数。当 length 为负数时，numeric表达式则按 length 所指定的在小数点的左边四舍

14、五入。function是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0（默认），numeric表达式 将四舍五入。当指定 0 以外的值时，将截断 numeric表达式。ROUND（748.58,1） 748.60 ROUND（748.58,2） 748.58ROUND（748.58,3） 748.58ROUND（748.58,-1） 750.00 ROUND（748.58,-2） 700.00ROUND（748.58,-3） 1000.00如果 length 是负数且大于小数点前的数字个数，

15、ROUND 将返回 0。ROUND（748.58,-4） 0.00ROUND（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.80ROUND（150.75,1,0） 150.80 -四舍五入结果ROUND（150.75,1,1） 150.70 -截断结果 （2）取绝对值

16、函数ABS 返回给定数字表达式的绝对值。返回与表达式相同的类型。ABS （表达式 ） ABS（-1.0） 1.0ABS（0.0） 0.0ABS（1.0） 1.0（3）随机函数RAND 返回 0 到1 之间的随机float 值。不包括0和1。 RAND （ seed ） Seed 是给出种子值整型表达式（tinyint、smallint 或 int）。随机数种子是用来打乱随机数的，没有它，你的随机数并不真正随机数.种子与结果的关系是： 对于不同的种子，有不同的随机数 对于相同的种子，具有相同的随机数产生指定范围的随机整数的公式为：Y = FLOOR（RAND（） * （上限 下限 1 ） 下限例

17、如：产生1030之间的随机整数（包括10和30）的公式为：Y = FLOOR（RAND（） *（30-10+1） 10（4）其它函数POWER 返回给定表达式乘指定次方的值。返回类型与 numeric表达式相同。语法： POWER （ numeric表达式 , y ） y 可以是精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）。power（2,2） 4power（2,3） 8SQUARE 返回给定表达式的平方。返回float类型。 SQUARE （ float表达式） 下例返回半径为 1 英寸、高为 5 英寸的圆柱容积。DECLARE h float, r floatSET h

18、= 5SET r = 1SELECT PI（）* SQUARE（r）* h 容积SQRT 返回给定表达式的平方根。 SQRT （ float_表达式 ） 完成PI返回 PI 的常量值。 PI （ ） 10.4.2字符串函数（1）字符转换函数ASCII（） ASCII（）函数返回字符表达式最左端字符的ASCII 码值。 ASCII（character_expression ） 在ASCII 函数中，纯数字的字符串可不用 括起来，但含其它字符的字符串必须用 括起来使用，否则会出错。ASCII（123） 等价于 SELECT ASCII（SELECT ASCII（ABC） 会提示 列名 ABC 无效

19、的错误ASCII（） 97ABC） 65CHAR（） CHAR（）函数用于将ASCII 码转换为字符。CHAR（character_expression）如果没有输入0 255 之间的ASCII 码值，CHAR（）函数会返回一个NULL 值。CHAR（65） ACHAR（97） a注释:CHAR 可用于将控制字符插入字符串中。下表显示了一些常用的控制字符。换行符和回车的区别? 回车是回到本行最前面, 换行是换到下一行去。一般情况下, 回车换行同时用, 就是到下一行的头上去.这个东西的说法来自打字机,以前的打字机要新起一行的时候有两步: 1. 打字的机头回到开始位置,这就是回车 2. 纸张往上推

20、进一行,这就是换行 现在电脑上的 回车换行 其实已经是个整体的概念了declare x int, y intset x=4set y=5if xy print XXy你不好LOWER（） LOWER（）函数把字符串全部转换为小写。LOWER（character_expression）lower（djjAAAAjdj）UPPER（） UPPER（）函数把字符串全部转换为大写。UPPER（character_expression）upper（STR（） STR（）函数把数值型数据转换为字符型数据。 STR（ ,length, ）length 指定返回的字符串的长度。是总长度，包括小数点、符号、数字

21、或空格。如果没 有指定长度,默认值为 10。必须是非负值。 指定长度应大于或等于数字的符号位数+小数点前的位数+小数点位数+小数点后的位数。decimal 指定返回的小数位数,即小数点右边的位数 ，默认为0，必须是非负值。 小数位数大于decimal 值时，STR（）函数将其下一位四舍五入。如果小数点前的位数超过了指定的长度，则返回指定长度的*。-普通的转换declare i floatset i=12.1245print 看看结果+str（i） 结果:12set i=12.45+str（i,6,4）结果:12.450-小数的四舍五入set i=12.45678912.457set i=123

22、45.45789+str（i,3,4）* 小数点前的位数有5位,超过的总长度3,*的个数由总长度3决定（2）去空格函数LTRIM（） LTRIM（）函数把字符串头部的空格去掉。LTRIM （character_expression ）select ltrim（ kdjj jjjjjDECLARE string_to_trim varchar（60）SET string_to_trim = Hello world!The beginning:+LTRIM（string_to_trim）RTRIM（） RTRIM（）函数把字符串尾部的空格去掉。RTRIM （character_expression

23、 ）（3）取子串函数LEFT（） LEFT（）函数返回部分字符串。LEFT（character_expression,integer_expression） LEFT（）函数返回的子串是从字符串最左边起到第integer_expression 个字符的部分。若integer_expression 为负值，则返回错误。declare i varchar（100）,j intset i=从查询或过程中无条件退出后的语句set j=5print left（i,j）RIGHT（）RIGHT（）函数返回部分字符串。RIGHT, RIGHT（）函数返回的子串是从字符串右边第integer_expressi

24、on 个字符起到最后一个字符的部分。SUBSTRINGSUBSTRING（）函数返回部分字符串。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 （su