第7章2 T-SQL程序设计基础

1、第7章T-SQL程序设计基础,案例-乘车卡消费问题,你的乘车卡上有50.3元人民币，当余额低于5元时，显示友好提示信息“金额低于5元,请尽快充值!”，当余额低于0.9元时，显示友情提示“余额不足，请投币！”。,该案例中包含了变量及计算等问题，那么如何实现呢？,DECLAREiINT,jeDECIMAL(5,1)SETi=0SETje=50.3WHILE(je=0.9)BEGINSETje=je-0.9SETi=i+1IF(je5)PRINT金额低于5元,请允值!ENDSELECTjeAS余额,iAS消费次数,i*0.9AS消费额PRINT金额不足,请投币!,本节教学重点及要求,掌握流程控制语句

2、（包括：BEGINEND,IFELSE,WHILEBREAKCONTINUE,WAITFOR,CASE等）。掌握游标的概念和声明方法，以及使用游标进行数据的查询、修改、删除等操作。能够根据实际需要，进行综合运用编写程序代码。,7.4T-SQL流程控制语句,流程控制语句是指那些用来控制程序执行和流程分支的命令。在SQLServer2005中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。注释符:语句块：beginend条件语句：ifelse、ifexists循环语句：while、break、continue等待语句：waitfor返回语句：return转移语句：goto（了解

3、）,1.注释符,注释语句:程序中不被执行的正文，它有二个作用：说明代码的含义，增强代码的可读性；可以把程序中暂时不用的语句注释掉，使它们暂时不被执行，等需要这些语句时再将它们恢复。有两种方法来声明注释：-（两个减号）：用于注释单行。从此开始的整行或者行的一部分就成为了注释，注释在行的末尾结束。/*/：可以注释大块跨越多行的代码，它必须用一对分隔符“/*/”将余下的其他代码分隔开。,补例1单行注释,-Thisisacomment.Wholelinewillbeignored.SELECTemployee_name,address-查询所有姓钱的员工FROMemployeeWHEREemploye

4、e_nameLIKE钱%,补例2多行注释,/*ThisisacommnetAlltheselineswillbeignored.*/*Listallemployees.*/SELECT*FROMemployee注释并没有长度限制。SQLServer文档禁止嵌套多行注释，但单行注释可以嵌套在多行注释中。/*-Listallemployees.SELECT*FROMemployee*/,2.BEGINEND语句,语法形式：BEGINsql_statement|statement_blockEND作用可以将多条T-SQL语句封装起来，构成一个独立的语句块。Begin和end必须成对出现。,控制语句,

5、顺序执行,程序,控制语句,顺序执行,流程改变,程序,3.IFELSE语句,语法形式：IFBoolean_expression/*条件表达式*/sql_statement/state_block/*条件表达式为TRUE时执行*/ELSE/*条件表达式为FALSE时执行*/sql_statement/state_block作用：按条件控制程序的执行。当条件满足时，则在执行条件之后的T-SQL语句。否则，就执行else后的T-SQL语句（若ELSE部分存在）,3.IFELSE语句,【例7-6】根据course_score表的成绩状况来判断：如果1210024课程的平均成绩大于90分，显示“12100

6、24课程成绩优秀”；若大于80分，显示“1210024课程成绩良好”；否则显示“1210024课程及格”。,DECLAREvagintSELECTvag=AVG(score)FROMcourse_scoreWHEREcourse_score.cour_id=1210024PRINT1210024课程平均成绩为PRINTvagIFvag90PRINT1210024课程成绩优秀ELSEIFvag80PRINT1210024课程成绩良好ELSEPRINT1210024课程成绩及格,补例3：求两个整数的最大数。DECLAREmaxINT,aINT,bINTSELECTa=32,b=56IF(ab)SE

7、LECTmax=aELSESELECTmax=bSELECTmaxAS最大数,4.CASE函数,作用：可以计算多个条件式，并将其中一个符合条件的结果表达式返回。分类：按照使用形式的不同，可以分为简单CASE函数和搜索CASE函数。(1)简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。其语法形式为：CASEinput_expressionWHENwhen_expressionTHENresult_expression.nELSEelse_result_expressionEND,USEjxglGOSELECTCASEcour_nameWHEN大学语文THEN中文系教师讲授WHEN

8、马克思主义基本原理THEN政治历史系教师讲授WHEN中国近现代史纲要THEN政治历史系教师讲授WHEN大学英语THEN外国语系教师讲授WHEN线性代数THEN数学系教师讲授ELSE本系教师讲授ENDAS开课教师,cour_nameFROMcoursesGO,【例7-7】根据cour_name列，判断course表的各个课程由哪系的老师讲授。,(2)CASE搜索函数，CASE计算一组逻辑表达式以确定结果。语法：CASEWHENBoolean_expressionTHENresult_expression.nELSEelse_result_expressionEND注意事项：（1）按指定顺序对每个

9、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表中根据se

10、mester列，判断courses表的各个课程分别在几年级开设，使之更易理解。,USEjxglGOUPDATEteachersSETteach_telephone=CASEWHENdept_id=D001THEN3785947WHENdept_id=D001THEN3785940WHENdept_id=D002THEN3785938WHENdept_id=D004THEN3785926ELSE3786946ENDGO,【例7-9】在UPDATE语句中使用CASE函数：根据所在的系确定teachers表的电话号码。,5.WHILECONTINUEBREAK循环控制语句,语法形式为：WHILEBo

11、olean_expressionsql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUE,作用：用于设置重复执行SQL语句或语句块的条件。条件为真，就重复执行语句。说明：Break用于退出最内层的while循环。Continue用于重新开始一次while循环。,USEjxglGOWHILE(SELECTmin(year(getdate()-year(stu_birth)FROMstudents)=all(SELECTstu_birthFROMstudents)ENDEND,【例7-10】查询students表

12、，只要有年龄小于20岁的学生，就将年龄最小的那个学生删掉，如此循环下去，直到所有的学生的年龄都不小于20岁，或是学生的总人数小于20就退出循环。,DECLAREsint,nint,tint,cintSETS=0SETn=1WHILEn=10BEGINSETc=1SETt=1WHILEc=nBEGINSETt=t*cSETc=c+1ENDSETs=s+tSETn=n+1ENDSELECTsas10阶乘之和,nasn,补例计算s=1!+2!+10!。,6.GOTO语句GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行。标识符可以为数字与字符的组合，但必须以“：”结尾。语法格式是：GOT

13、Olabel；,DECLAREsumint,countintSELECTsum=0,count=1label_1:SELECTsum=sum+countSELECTcount=count+1IFcount=100GOTOlabel_1SELECTcount,sum操作结果如下面所示：1015050(1行受影响),【例7-10】利用GOTO语句求出从1加到100的总和。,7.调度执行语句WAITFOR,WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。语法形式为：WAITFORDELAYtime|TIMEtime（1）DELA

14、Y：指示SQLServer一直等到指定的时间过去，最长可达24小时。（2）time：要等待的时间。可以按datetime数据可接受的格式指定time，也可以用局部变量指定此参数，但不能指定日期。（3）TIME：指示SQLServer等待到指定时间。,【例7-12】用WAITFOR语句设置延迟操作。waitfordelay00:00:05print延迟5秒执行！-5秒后执行print,【例7-13】用WAITFOR语句指定从何时起执行。waitfortime08:57print08:57执行-到08:57的时候才会执行print,8.RETURN语句语法格式：RETURNinteger_expr

15、ession作用:无条件地从过程、批或语句块中退出，在return之后的其他语句不会被执行。说明:Return可以在过程、批和语句块中的任何位置使用。Return与break很相似，不同的是return可以返回一个整数。如果没有指定返回值，SQLServer系统会根据程序执行的结果返回一个内定值，返回值含义如下所示:,表7-4RETURN命令返回的内定状态值,介绍SQL批处理,批处理,输出结果,9.批处理,一个批处理是条或多条Transact-SQL语句的集合。SQLServer服务器对批处理的处理分为四个阶段：分析阶段，检查语法，验证合法性;优化阶段，确定最有效的方法；编译阶段，生成执行计划

16、；运行阶段，执行语句。批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。,SQLServer有以下几种指定批处理的方法。应用程序。存储过程或触发器。由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。例如，EXEC(SELECT*FROMteachers)由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如，executesp_executesqlNSELECT*fromjxgl.dbo.teachers,（1）批处理的指定,GO是批处理的结束标志。把GO前面的所有语句当成一个批处理来执行。GO命令和T-SQL语句不可处在

17、同一行上。在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部(用户定义)变量的作用域限制在一个批处理中，不可在GO命令后引用。RETURN可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。,(2)批处理的结束与退出,USEjxglGO-批处理结束标志CREATEVIEWstudents_infoASSELECT*FROMstudentsGO-CREATEVIEW语句与其他语句隔离SELECT*FROMstudents_infoGO,补例创建一个视图，使用GO命令将CREATEVIEW语句与批处理中的其他语句(如USE、SELECT语句等)隔离。,去掉go？,

18、declareiintselecti=1whilei500beginifi%3=0ori%7=0printiseti=i+1end,课堂练习1：计算500以内能被3或7整除的数。,本节小结,流程控制语句概述beginend语句ifelse、ifexists语句while、break和continue语句waitfor语句return语句,习题4、5,7.6游标,游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数

19、组、应用程序中或其它地方。游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，才使两个数据处理方式能够进行沟通。,7.6.1游标概述,根据游标的用途不同，SQLServer2005将游标分成三种类型：1.Transact_SQL游标.主要用在服务器上，不支持提取数据块或多行数据。2.API游标：支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务器上。3.客户游标：当在客户机上缓存结果集时才使用。常常仅被用作服务器游标的辅助。API游标和Transact-SQL游标被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本节中我们主要讲述服务器

20、（后台）游标。,7.6.1游标概述,根据Transact_SQL服务器游标的处理特性，SQLServer2005将游标分为4种：2.动态游标：与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。1.静态游标：在打开游标时在tempdb中建立SELECT结果集的快照。总是按照打开游标时的原样显示结果集，并不反应它在数据库中对任何结果集成员所做的更新。,3.只进游标：不支持滚动，它只支持游标从头到尾顺序提取数据，游标从数据库中提取一条记录并进行操作，操作完毕后，再提取下一条记录。4.键集游标：该游标中各行的成员身份和顺序是固定的。由一组唯一标识符（键）控制，这组键称为键集。键集是打开

21、游标时来自符合SELECT语句要求的所有行中的一组键值。键集驱动的游标对应的键集是打开该游标时在数据库tempdb中生成的。,7.6.2游标的定义与使用,使用游标(CURSOR)的步骤如下。声明游标。在使用游标之前，首先需要声明游标。打开游标。打开一个游标意味着在游标中输入了相关的记录信息。获取记录信息。首先将游标当前指向的记录保存到一个局部变量中，然后游标将自动移向下一条记录。将一条记录读入某个局部变量后，就可以根据需要对其进行处理了。关闭游标。释放游标锁定的记录集。释放游标。释放游标自身所占用的资源。,1.声明游标（DECLARE）：在内存中创建游标结构，是游标语句的核心。,语法格式：De

22、clarecursor_name/*指定游标名*/insensitivescrollcursor/*指定游标类型,insensitive表示静态游标*/FORSELECT_statement/*指定查询语句*/forreadonly|updateofcolumn_name,n,表7-10Declare命令中SCROLL的取值,【例7-32】定义游标Teach_CUR，以便查询教师名字和所讲授的课程名，学分。,DECLARETeach_CURCURSORFORSELECTteachers.teach_name,courses.cour_name,courses.creditFROMteacher

23、s,courses,course_arrangeWHEREteachers.teach_Id=course_arrange.teach_idandcourse_arrange.cour_Id=courses.cour_Id,2.打开游标（OPEN），产生游标定义的结果集。,语句格式：OPENGLOBALcursor_name|cursor_variable_name其中：GLOBAL选项指定cursor_name为全局游标；cursor_name为游标名称；cursor_variable_name为游标变量名称，该变量引用一个游标。注意：当游标打开成功时，游标位置指向结果集的第一行之前。只能打

24、开已经声明但尚未打开的游标。,【例7-33】打开前面所创建的游标Teach_CUR。OPENTeach_CUR【例7-34】显示游标结果集合中数据行数SELECT数据行数=CURSOR_ROWS,3.读取游标,一旦游标被成功打开，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH命令。其语法格式为：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,.n,表9-14fetch_statu

25、s变量,注意：游标位置决定了结果集中哪一行的数据可以被提取，如果游标方式为FORUPDATE，则可决定哪一行数据库可以更新或者删除。FETCH_STATUS变量报告上一个FETCH语句的状态，其取值和含义如下：,OPENTeach_CURFETCHNEXTFROMTeach_CUR/*取第一个数据行*/WHILEfetch_status=0/*检查fetch_status是否还有数据可取*/BEGINFETCHNEXTFROMTeach_CUREND,【例7-35】打开前面所创建的游标Teach_CUR，读取游标中的数据。,4.关闭游标（CLOSE）,格式为：CLOSE游标名例如：关闭Teac

26、h_CUR游标如下描述：CLOSETeach_CUR关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用OPEN语句再次打开该游标。,5.删除游标（DEALLOCATE）。DEALLOCATE语句格式：DEALLOCATE游标名作用：DEALLOCATE命令删除游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。删除后不可再用，必须重新声明方可。例如，删除Teach_CUR游标，DEALLOCATETeach_CUR,6.利用游标修改数据。通过游标修改或删除游标基表中的当前数据行。UPDATE语句的格式为：UPDATEtable_nameSET列名=表达式,nWHERECU

27、RRENTOFcursor_nameDELETE语句的格式为：DELETEFROMtable_nameWHERECURRENTOFcursor_name说明：CURRENTOFcursor_name：表示当前游标指针所指的当前行数据。CURRENTOF只能在UPDATE和DELETE语句中使用。注意：使用游标修改基表数据的前提是声明的游标是可更新的。对相应的数据库对象（游标的基表）有修改和删除权限。,【例7-39】通过游标将教师表teachers记录号为3的“吕加国”老师的职称由“讲师改为“副教授”。USEjxglGODECLARETEACH_CURSOR2SCROLLCURSORFORSEL

28、ECTteach_name,teach_professionalFROMteachersFORUPDATEOFteach_professionalOPENTEACH_CURSOR2FETCHABSOLUTE3FROMTEACH_CURSOR2UPDATEteachersSETteach_professional=副教授WHERECURRENTOFTEACH_CURSOR2SELECTteach_name,teach_professionalFROMteacherswhereteach_name=吕加国CLOSETEACH_CURSOR2DEALLOCATETEACH_CURSOR2,7.6.3

29、使用存储过程管理游标,可以使用Sp_cursor_list系统存储过程来获得对当前连接可见的游标列表，使用Sp_describe_cursor、Sp_describe_cursor_columns和Sp_describe_cursor_tables来确定游标的特性。1.Sp_describe_cursor：返回描述游标属性（例如游标的作用域、名称、类型、状态和行数）的游标。2.Sp_describe_cursor_columns：返回描述游标中每一列的属性（例如列的名称、位置、大小和数据类型）的游标。3.Sp_describe_cursor_tables：报告游标被引用基表。,7.6.3使用存储过程管理游标,【例7-40】定义并打开一个全局游标，使用Sp_describe_cursor报告游标的属性。DECLARETeach_CUR3SCROLLCURSORFORSELECTteach_name,teach_prof