SQL Server的程序设计

1第10章Transact-SQL程序设计教学目标终极目标：会利用变量、函数、流程控制语句编写Transact-SQL程序实现对数据库系统的操作。促成目标：1.能正确理解和使用变量2.能正确理解和使用函数3.能正确理解和使用流程控制语句相关知识什么是Transact-SQL?前面我们已经学习了有关SQL的知识，SQL是关系型数据库系统的标准语言，标准的SQL语句几乎可以在所有的关系型数据库上可以不加修改地使用。但标准的SQL不支持流程控制，仅仅是一些简单的语句，有时候使用起来不方便。为此，大型的关系型数据库系统都在标准的SQL基础上，结合自身的特点推出了可以编程的、结构化的SQL编程语言，比如SQLServer2000的Transact-SQL、Oracle9i的PL/SQL。可能有同学会问了，SQL不是很好用吗，我们什么时候需要使用Transact-SQL呢？如果你的工作仅仅是简单地查询SQLServer2000数据库的数据，那么学习SQL也就够了。对于一些复杂的查询，SQL就鞭长莫及了，尤其是如果在查询过程中需要变量、判断、循环等控制时就必须使用Transact-SQL。建立SQLServer2000数据库的目的还是为了开发各种应用系统，要开发C/S模式的数据库应用系统，往往会用到存储过程、触发器、函数这样的编程对象，这时就必须使用Transact-SQL。Transact-SQL程序的结构其实我们前面学习的SQL范例都可以称为是Transact-SQL程序。下面我们通过一个实例来说明一个稍复杂的Transact-SQL。/*Transact-SQL程序的实例*/useschoolgodeclare@avgradeint--判断成绩set@avgrade=60if(selectavg(grade)fromscwherecno='c01')=@avgrade--输出结果select'课程平均成绩超过'+convert(varchar(3),@avgrade)else--输出结果select'课程平均成绩不超过'+convert(varchar(3),@avgrade)--执行批处理2go由此可见，Transact-SQL程序主要的语法要素如下：变量注释符运算符函数流程控制语句批处理10.1变量Transact-SQL中可以使用两种变量：局部变量和全局变量。10.1.1局部变量局部变量是用户可自定义的变量，它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。局部变量必须以@开头，而且必须先用DECLARE命令声明后才可使用。其说明形式如下：DECLARE@变量名变量类型[，@变量名变量类型…]在使用DECLARE命令声明以后，所有的变量都被赋予初值NULL例：声明一个变量Declare@ichar(5)可以在一个declare语句中声明多个变量例:declare@iint,@jint,@kint在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT或SET命令来设定变量的值。其语法如下：SELECT@变量名=变量值SET@变量名=变量值【注意】：SET与SELECT的区别下表列出SET与SELECT的区别:setselect同时对多个变量同时赋值不支持支持表达式返回多个值时出错将返回的最后一个值赋给变量表达式未返回值变量被赋null值变量保持原值下面以具体示例来说明问题：createtablechinadba1(useridint,addrvarchar(128))insertintochinadba1(userid,addr)values(1,'addr1')insertintochinadba1(userid,addr)values(2,'addr2')3insertintochinadba1(userid,addr)values(3,'addr3')1.同时对多个变量同时赋值,使用set赋值declare@addr1varchar(60),@addr2varchar(60)set@addr1='aaa',@addr2='bbb'','附近有语法错误。同时对多个变量同时赋值,使用select赋值declare@addr1varchar(60),@addr2varchar(60)select@addr1='aaa',@addr2='bbb'2.表达式返回多个值时，使用SET赋值declare@addrvarchar(128)set@addr=(selectaddrfromchinadba1)/*--出错信息为服务器:消息512，级别16，状态1，行2子查询返回的值多于一个。当子查询跟随在=、!=、、=、、=之后，或子查询用作表达式时，这种情况是不允许的。*/表达式返回多个值时，使用SELECT赋值declare@addrvarchar(128)select@addr=addrfromchinadba1print@addr--结果集中最后一个addr列的值--结果:addr33.表达式未返回值时，使用SET赋值declare@addrvarchar(128)set@addr='初始值'set@addr=(selectaddrfromchinadba1whereuserid=4)print@addr表达式未返回值时，使用SELECT赋值declare@addrvarchar(128)set@addr='初始值'select@addr=addrfromchinadba1whereuserid=4print@addr--保持原值例：声明一个长度为8个字符的变量id，并赋值。declare@idchar（8）或者declare@idchar（8）select@id=‘10010001’set@id=‘10010001’4declare@idchar(8)declare@idchar(8)Select@id=snoset@id=(selectsnoFromstudentFromstudentWheresname=‘张三’Wheresname=‘张三’)不能将SELECT语句的赋值功能和查询功能同时混合使用，否则系统会产生错误。Declare@iint,@jintselect@i=max(grade),@j=min(grade)FromscDeclare@iint,@jintselect@i=max(grade),@j=min(grade),max(grade)-min(grade)from产品10.1.2全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQLServer的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。使用全局变量注意：（1）全局变量不是由用户的程序定义的，它们是在服务器级定义的（2）用户只能使用预先说明及定义的全局变量。（3）引用全局变量时必须以“@@”开头。（4）局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。如:@@CONNECTIONS:返回自SQLSERVER最近一次启动以来连接或企图连接到SQLSERVER的连接数目。@@ERROR:返回执行T-SQL语句的错误代码。在SQLServer执行完一条语句后，如果执行成功，则返回@@ERROR的值为0，如果该语句在执行过程中发生错误，则将返回错误信息，而@@ERROR将返回相应的错误编号，该编号一直保持下去，直到下一条语句得到执行为止。@@IDENTITY:返回最后插入行的标识列的列值.10.2注释符--用于单行注释与C语言相同的程序注释符号，即“/*……*/”，/*用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。10.3运算符10.3.1算数运算符5两个数相加。这个加法算术运算符也可以将一个以天为单位的数字加到日期中。A.使用加法运算符计算客户总的可订购量下面的示例将产品表中当前库存量和当前已定购的以及再订购的所有产品的数量相加。select产品名称,库存量+订购量+再订购量总的可订购量from产品B.使用加法运算符将天数加到日期和时间值中下面的示例将若干天数加到datetime日期上。DECLARE@startdatedatetime,@adddaysintSET@startdate='2007-3-12'SET@adddays=5select@startdatestartdate,@startdate+@adddaysadddateC.将字符和整型数据类型相加本示例通过将字符数据类型转换为int，将int数据类型值与字符值相加。如果在char字符串中有无效的字符，则SQLServer将返回错误。DECLARE@addvalueintSET@addvalue=15SELECT'125127'+@addvalue下面是结果集：12514210.3.2赋值运算符赋值运算符只有一个，即=（等号），用于为字段或变量赋值。例：下面的语句先定义一个int变量@xyz，然后将其值赋为123。declare@xyzintset@xyz=12310.3.3位运算符位运算符用于在两个数之间执行位操作，T-SQL的位运算符如表所示。6位运算符的操作数可以是整型或二进制数据类型（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运算符的优先顺序如果一个表达式中使用了多种运算符，则运算符的优先顺序决定计算的先后次序。计算时，从左向右计算，先计算优先级高的运算，再计算优先级低的运算。下面列出了运算符的顺序。括号算术运算符比较运算符按位运算符7逻辑运算符赋值运算符10.4函数10.4.1数学函数数学函数通常对作为参数提供的输入值执行计算，并返回一个数字值。常用的数学函数（1）取近似值函数CEILING返回=表达式的最小整数,返回的数据类型与表达式相同.语法:CEILING(numeric表达式)例：ceiling(123.45)124ceiling(-123.45)-123ceiling(123)123FLOOR返回=表达式的最大整数,返回的数据类型与表达式相同.语法:FLOOR(numeric表达式)floor(123.45)1238floor(-123.45)-124floor(123)123ROUND返回数字表达式并四舍五入为指定的长度或精度.返回与表达式相同的类型。语法:ROUND(numeric表达式,length[,function])参数:length是numeric表达式将要四舍五入的精度。当length为正数时，numeric表达式四舍五入为length所指定的小数位数。当length为负数时，numeric表达式则按length所指定的在小数点的左边四舍五入。function是要执行的操作类型。function必须是tinyint、smallint或int。如果省略function或function的值为0（默认），numeric表达式将四舍五入。当指定0以