搜索
第7章T-SQL程序设计基础案例--乘车卡消费问题你的乘车卡上有50.3元人民币，当余额低于5元时，显示友好提示信息“金额低于5元,请尽快充值!”，当余额低于0.9元时，显示友情提示“余额不足，请投币！”。该案例中包含了变量及计算等问题，那么如何实现呢？DECLARE@iINT,@jeDECIMAL(5,1)SET@i=0SET@je=50.3WHILE(@je=0.9)BEGINSET@je=@je-0.9SET@i=@i+1IF(@je5)PRINT'金额低于5元,请允值!'ENDSELECT@jeAS余额,@iAS消费次数,@i*0.9AS消费额PRINT'金额不足,请投币!'本节教学重点及要求•掌握流程控制语句（包括：BEGIN…END,IF…ELSE,WHILE…BREAK…CONTINUE,WAITFOR,CASE等）。•掌握游标的概念和声明方法，以及使用游标进行数据的查询、修改、删除等操作。•能够根据实际需要，进行综合运用编写程序代码。7.4T-SQL流程控制语句•流程控制语句是指那些用来控制程序执行和流程分支的命令。•在SQLServer2005中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。–注释符:–语句块：begin…end–条件语句：if…else、ifexists–循环语句：while、break、continue–等待语句：waitfor–返回语句：return–转移语句：goto（了解）1.注释符注释语句:程序中不被执行的正文，它有二个作用：说明代码的含义，增强代码的可读性；可以把程序中暂时不用的语句注释掉，使它们暂时不被执行，等需要这些语句时再将它们恢复。有两种方法来声明注释：--（两个减号）：用于注释单行。从此开始的整行或者行的一部分就成为了注释，注释在行的末尾结束。/*……*/：可以注释大块跨越多行的代码，它必须用一对分隔符“/**/”将余下的其他代码分隔开。补例1单行注释--Thisisacomment.Wholelinewillbeignored.SELECTemployee_name,address--查询所有姓钱的员工FROMemployeeWHEREemployee_nameLIKE'钱%'补例2多行注释/*Thisisacommnet．Alltheselineswillbeignored.*//*Listallemployees.*/SELECT*FROMemployee•注释并没有长度限制。SQLServer文档禁止嵌套多行注释，但单行注释可以嵌套在多行注释中。/*--Listallemployees.SELECT*FROMemployee*/2.BEGIN…END语句•语法形式：BEGIN{sql_statement|statement_block}END•作用–可以将多条T-SQL语句封装起来，构成一个独立的语句块。–Begin和end必须成对出现。10控制语句顺序执行程序控制语句顺序执行流程改变程序3.IF…ELSE语句•语法形式：IFBoolean_expression/*条件表达式*/{sql_statement/state_block}/*条件表达式为TRUE时执行*/ELSE/*条件表达式为FALSE时执行*/{sql_statement/state_block}•作用：–按条件控制程序的执行。–当条件满足时，则在执行条件之后的T-SQL语句。否则，就执行else后的T-SQL语句（若ELSE部分存在）3.IF…ELSE语句【例7-6】根据course_score表的成绩状况来判断：如果1210024课程的平均成绩大于90分，显示“1210024课程成绩优秀”；若大于80分，显示“1210024课程成绩良好”；否则显示“1210024课程及格”。DECLARE@vagintSELECT@vag=AVG(score)FROMcourse_scoreWHEREcourse_score.cour_id='1210024'PRINT'1210024课程平均成绩为'PRINT@vagIF@vag90PRINT'1210024课程成绩优秀'ELSEIF@vag80PRINT'1210024课程成绩良好'ELSEPRINT'1210024课程成绩及格'补例3：求两个整数的最大数。DECLARE@maxINT,@aINT,@bINTSELECT@a=32,@b=56IF(@a@b)SELECT@max=@aELSESELECT@max=@bSELECT@maxAS最大数4.CASE函数作用：可以计算多个条件式，并将其中一个符合条件的结果表达式返回。分类：按照使用形式的不同，可以分为简单CASE函数和搜索CASE函数。(1)简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。其语法形式为：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expression]ENDUSEjxglGOSELECTCASEcour_nameWHEN'大学语文'THEN'中文系教师讲授'WHEN'马克思主义基本原理'THEN'政治历史系教师讲授'WHEN'中国近现代史纲要'THEN'政治历史系教师讲授'WHEN'大学英语'THEN'外国语系教师讲授'WHEN'线性代数'THEN'数学系教师讲授'ELSE'本系教师讲授'ENDAS'开课教师',cour_nameFROMcoursesGO【例7-7】根据cour_name列，判断course表的各个课程由哪系的老师讲授。(2)CASE搜索函数，CASE计算一组逻辑表达式以确定结果。语法：CASEWHENBoolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]END注意事项：（1）按指定顺序对每个WHEN子句的逻辑表达式进行计算。返回第一个计算结果为TRUE的result_expression。（2）如果逻辑表达式计算结果不为TRUE，则在指定ELSE子句的情况下数据库引擎将返回else_result_expression；若没有指定ELSE子句，则返回NULL值。USEjxglGOSELECTcour_nameCASEWHENsemesterIN(1,2)THEN'一年级开设'WHENsemesterIN(3,4)THEN'二年级开设'WHENsemesterIN(5,6)THEN'三年级开设'ELSE'四年级开设'ENDAS'开课学期'FROMcourses【例7-8】在courses表中根据semester列，判断courses表的各个课程分别在几年级开设，使之更易理解。USEjxglGOUPDATEteachersSETteach_telephone=CASEWHENdept_id='D001'THEN'3785947'WHENdept_id='D001'THEN'3785940'WHENdept_id='D002'THEN'3785938'WHENdept_id='D004'THEN'3785926'ELSE'3786946'ENDGO【例7-9】在UPDATE语句中使用CASE函数：根据所在的系确定teachers表的电话号码。5.WHILE…CONTINUE…BREAK循环控制语句语法形式为：WHILEBoolean_expression{sql_statement|statement_block}[BREAK]{sql_statement|statement_block}[CONTINUE]作用：用于设置重复执行SQL语句或语句块的条件。条件为真，就重复执行语句。说明：Break用于退出最内层的while循环。Continue用于重新开始一次while循环。USEjxglGOWHILE(SELECTmin(year(getdate())-year(stu_birth))FROMstudents)20BEGINIF(SELECTcount(*)FROMstudents)20BREAKELSEBEGINDELETEstudentsWHEREstu_birth=all(SELECTstu_birthFROMstudents)ENDEND【例7-10】查询students表，只要有年龄小于20岁的学生，就将年龄最小的那个学生删掉，如此循环下去，直到所有的学生的年龄都不小于20岁，或是学生的总人数小于20就退出循环。DECLARE@sint,@nint,@tint,@cintSET@S=0SET@n=1WHILE@n=10BEGINSET@c=1SET@t=1WHILE@c=@nBEGINSET@t=@t*@cSET@c=@c+1ENDSET@s=@s+@tSET@n=@n+1ENDSELECT@sas'10阶乘之和',@nas'n'补例计算s=1!+2!+…+10!。6.GOTO语句GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行。标识符可以为数字与字符的组合，但必须以“：”结尾。语法格式是：GOTOlabel；DECLARE@sumint,@countintSELECT@sum=0,@count=1label_1:SELECT@sum=@sum+@countSELECT@count=@count+1IF@count=100GOTOlabel_1SELECT@count,@sum操作结果如下面所示：1015050(1行受影响)【例7-10】利用GOTO语句求出从1加到100的总和。7.调度执行语句WAITFOR•WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。语法形式为：WAITFOR{DELAY'time'|TIME'time'}（1）DELAY：指示SQLServer一直等到指定的时间过去，最长可达24小时。（2）‘time’：要等待的时间。可以按datetime数据可接受的格式指定time，也可以用局部变量指定此参数，但不能指定日期。（3）TIME：指示SQLServer等待到指定时间。【例7-12】用WAITFOR语句设置延迟操作。waitfordelay'00:00:05'print'延迟5秒执行！'--5秒后执行print【例7-13】用WAITFOR语句指定从何时起执行。waitfortime'08:57'print'08:57执行'--到08:57的时候才会执行print8.RETURN语句语法格式：RETURN[integer_expression]作用:无条件地从过程、批或语句块中退出，在return之后的其他语句不会被执行。说明:Return可以在过程、批和语句块中的任何位置使用。Return与break很相似，不同的是return可以返回一个整数。如果没有指定返回值，SQLServer系统会根据程序执行的结果返回一个内定值，返回值含义如下所示:返回值含义返回值含义0程序执行成功-7资源错误-1找不到对象-8非致使的内部错误-2数据类型错误-9已经达到系统的权限-3死锁-10、-11致使的内部不一致错误-4违反权限原则-12表或指针破坏-5语法错误-13数据库破坏-6用户造成的一般错误-14硬件错误表7-4RETURN命令返回的内定状态值28介绍SQL批处理批处理单个SQL命令组成一批作为单个执行计划进行编译输出结果9.批处理•一个批处理是—条或多条Transact-SQL语句的集合。•SQLServer服务器对批处理的处理分为四个阶段：–分析阶段，检查语法，验证合法性;–优化阶段，确定最有效的方法；–编译阶段，生成执行计划；–运行阶段，执行语句。•批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。•SQLServer有以下几种指定批处理