1、 第6章 T-SQL程序设计 主要学习内容变量逻辑控制语句循环语句批处理语句 学习目标 了解变量的定义和种类熟练掌握逻辑控制语句熟练掌握循环语句熟练掌握批处理语句 导入案例导入案例程序设计语言拥有变量、流程结构设计等等，这使得程序设计有明显的算法块，应用非常灵活。微软为了使得SQL Server的T-SQL语言不仅拥有标准SQL语言的定义，还能拥有灵活方便的程序设计能力，使得有程序设计语言基础的数据库使用人员，能够迅速开发出性能良好的算法。使得SQL Server数据库的存储和管理数据能力更加强大，应用程序开发人员的程序前台开发工作与后台开发分离，加快开发速度，提高开发效率，更加易于管理。6.
2、1 变量SQL Server 的的 Transact-SQL（简简 称称 T-SQL）语语言言的的特特点点之之一一是是加加入入了了变变量量的的使使用用，这这使使得得T-SQL程程序序设设计计非非常常灵灵活活。变变量量是是SQL Server中中由由系系统统或或用用户户定定义义并并可可对对其其赋赋值值的的实实体体，变变量量分分为为局局部部变变量量(Local Variable)和全局变量和全局变量(Global Variable)。6.1.1全局变量 全局变量又称系统变量，是由SQL Server系统提供并赋值的变量，用来记录SQL Server服务器活动状态数据 常用变量表 变量变量含义含义E
3、RROR最后一个最后一个T-SQL错误的错误号错误的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQL SERVER的版本信息的版本信息 常用变量表注意：全局变量必须以标记作为前缀，如version。全局变量由系统定义和维护，我们只能读取，不能修改
4、全局变量的值。全局变量全局变量【例6.1】演示全局变量的使用？USE Studentprint SQL Server的版本+VERSION print 服务器的名称:+SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge)VALUES(刘乐乐,2019000005,男,23)-如果大于0表示上一条语句执行有错误print 当前错误号+convert(varchar(5),ERROR)print 刚才报名的学员，座位号为:+convert(varchar(5),IDENTITY)UPDATE stuinfo SET stuAge=2
5、5 WHERE stuName=刘乐乐print 当前错误号+convert(varchar(5),ERROR)GO 全局变量全局变量注：Print语句表示把字符串打印在屏幕上。后面必须跟字符串，而ERROR是整型变量，所以要把它强制转换成字符串类型。6.1.2局部变量局部变量局部变量是用户自定义的变量。它不像全局变量那样由系统提供，用户可以随时随地直接使用，而必须由用户自己定义，定义后赋初值了，才能使用，而且生命周期仅限于用户定义它的那个程序块，出了这个程序块，它的生命就结束了。局部变量必须以标记作为前缀，如age。局部变量局部变量声明局部变量 DECLARE 变量名 数据类型例如：DECL
6、ARE name varchar(8)DECLARE seat int局部变量的赋值 SET 变量名=值 SELECT 变量名=值例如：SET name=张三SELECT name=stuName FROM stuInfo WHERE stuNo=2019000001 局部变量局部变量注：1.先声明再赋值；2.赋值有两种方式：1）使用set 2）使用select set用于普通的赋值。select用于从表中查询数据并赋值。3.使用select语句赋值时，必须保证筛选的记录只有1条，否则取最后一条。所以TSQL语句后面一般接WHERE筛选条件。4.变量定义的同时可以初始化例：declare n
7、int=5 局部变量局部变量【例6.2】编写T-SQL在学生信息表中查找刘乐乐同学的同桌？使用数据库Student中的数据表StuInfo(stuNo,stuName,stuAge,stuSex,stuSeat,stuAddress):具体表结构信息如下：USE studentCREATE TABLE dbo.stuInfo(stuNo varchar(10)primary key,stuName varchar(10)NOT NULL,stuSex varchar(2)NOT NULL,stuSeat int IDENTITY(1,1)NOT NULL,stuAge int NULL,stu
8、Address varchar(50)NULL,)GO 插入记录insert into stuInfo(stuNo,stuName,stuSex,stuAge)select 2019000001,x,男,20 union select 2019000005,刘乐乐,女,25 union select 2019000006,y,女,20 go 局部变量局部变量分析：同桌，即是上课时坐在左右的同学。座位号是横向排列的，所以可以通过座位号来定位同桌。第一步，找出刘乐乐的座位号；第二步，刘乐乐的座位号减1或加1 即是他的左右同学了。局部变量局部变量Use Student/*-查找刘乐乐的信息-*/DE
9、CLARE name varchar(10)-学员姓名SET name=刘乐乐 -使用SET赋值SELECT*FROM stuInfo WHERE stuName=name/*-查找刘乐乐的左右同桌-*/DECLARE seat int -座位号SELECT seat=stuSeat FROM stuInfo -使用SELECT赋值 WHERE stuName=nameSELECT*FROM stuInfo WHERE(stuSeat=seat+1)OR(stuSeat=seat-1)GO 6.2逻辑控制语句逻辑控制语句在T-SQL程序设计中，逻辑控制语句有单分支条件语句IF-ELSE和多分支
10、条件语句CASE-END。6.2.1 IFELSE语句语句SQL中的IF-ELSE语句语法如下：IF（条件）BEGIN 语句1 语句2 ENDELSE BEGIN 语句1；语句2；END注：ELSE是可选部分；如果有多条语句，才需要BEGIN-END语句块。IFELSE语句语句【例6.3】统计并班级考试情况，如果班级平均分在85以上，显示“成绩优秀”，并显示前三名学员的考试信息；如果在85以下，显示“本人成绩较差”，并显示后三名学员的考试信息。数据库为jsj2019数据表为stuInfo、courseInfo和scores原数据 分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判
11、断；答：declare avg floatselect avg=avg(score)from scores if avg=85begin print 成绩优秀 select top 3 stuInfo.stuNo,stuName,cName,score from stuInfo inner join scores on stuInfo.stuNo=scores.stuNo inner join courseInfo on scores.cNo=courseInfo.cNo order by score descendelsebegin print 成绩较差 select top 3 stuIn
12、fo.stuNo,stuName,cName,score from stuInfo inner join scores on stuInfo.stuNo=scores.stuNo inner join courseInfo on scores.cNo=courseInfo.cNo order by score endgo案例执行 6.2.2 CASEEND多分支语句多分支语句T-SQL中CASE语句的语法如下：CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果END CASEEND多分支语句多分支语句【例6.4】采用美国的ABCDE五级打分制来
13、显示笔试成绩。A级:90分以上 B级：8089分 C级:7079分 D级：6069分 E级：60分以下 CASEEND多分支语句多分支语句答：print ABCDE五级显示成绩如下：SELECT stuNo,cNo,成绩=CASE WHEN score60 THEN E WHEN score BETWEEN 60 AND 69 THEN D WHEN score BETWEEN 70 AND 79 THEN C ElSE A ENDFROM SCORES CASEEND多分支语句多分支语句注：CASEEND的语法比较难于理解，与C语言中switch-case还是有较大区别的：1.CASEEND
14、作为一个整体使用，它是有唯一返回值的，如：SELECT stuNo,cNo,成绩=某个值 FROM scores。2.成绩是别名，等价使用AS。3.成绩后的值不确定，它的值由CASEEND多分支决定。CASE语句根据每条记录中的成绩，进行判断，结果是A还是B等。CASEEND多分支语句多分支语句【例6.5】请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成绩 优：90分以上 良：8089分 中：7079分 差：6069分 不及格：60分以下数据表：stuMarks(stuNo,ExamNo,writtenExam,labExam)答：CASEEND多分支语句多分支语句USE stud
15、entDBSelect*from stuInfoSelect*from stuMarksSELECT 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,平均分=(writtenExam+labExam)/2,等级=CASE WHEN(writtenExam+labExam)/260 THEN 不及格 WHEN(writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN 差 WHEN(writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN 中 WHEN(writtenExam+labExa
16、m)/2 BETWEEN 80 AND 89 THEN 良 ElSE 优 END FROM stuMarksGO 6.3循环语句循环语句SQL中的WHILE语句的语法如下：WHILE（条件）BEGIN 语句1 语句2 BREAK END注：BREAK表示退出循环，如果有多条语句，才需要BEGIN-END语句块。循环语句循环语句【例6.6】某次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。数据表：stuMarks(stuNo,ExamNo,writtenExam,la
17、bExam)分析：第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。循环语句循环语句答：USE studentDB-加分前-select*from stuMarksDECLARE n intWHILE(1=1)-条件永远成立 BEGIN SELECT n=COUNT(*)FROM stuMarks WHERE writtenExam0)UPDATE stuMarks -每人加2分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环 ENDSELECT*FROM stuMarks-加分后的成绩如下：-6.4批处理语句批处理语句批处
18、理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQL Server执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。批处理语句批处理语句批处理语句语法如下：语句1语句2GO注：GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率。批处理语句批处理语句假设SQL Server是网络数据库，一台服务器可能有很多远程客户端，如果在客户端一次发送1条SQL语句，然后客户返回结果；然后再发送1条SQL语句，再返回，效率太低。因此批处理语句可提高效率。一般是将一些逻辑相关的业务操作语句，放
19、置在同一批中，这完全由业务需求和代码编写者决定 SQL Server规定，如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志。批处理语句批处理语句【例6.7】根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写批处理语句实现。数据表：stuMarks(stuNo,ExamNo,writtenExam,labExam)90分以上：不加分8089分：加1分7079分：加2分6069分：加3分60分以下：加5分 SELECT*FROM stuMarks -原始成绩SELECT 上机平均分=avg(labExam)FROM stuMarks
20、 -原始平均分DECLARE labAvg INTWHILE(1=1)BEGIN UPDATE stuMarks SET labExam=CASE WHEN labExam=85 BREAK ENDSELECT*FROM stuMarks -加分后成绩SELECT 上机平均分=avg(labExam)FROM stuMarks-加分后平均分成绩GO 小结小结在T-SQL程序设计中，局部变量是用户可自定义的变量，它的作用范围仅在程序内部，作用域局限在一定范围的T-SQL对象，而全局变量是系统内部使用的由系统提供并赋值、用来记录服务器活动状态数据的一组变量，任何程序均可随时调用。IFELSE语句和CASE语句通过条件的的选择判断来决定程序的执行，根据不同的实际情况选择使用IF-ELSE语句或CASE-END语句。循环语句While可以根据条件循环执行语句块，直到条件不满足为止。在T-sql语言中，是用Begin End来把多条语句写在一个语句复合体中的。以GO为结束标志的一串SQL语句称为批处理，是最基本的算法块，但还不是数据库的物理对象，无法持久保持。
