《第7章2-T-SQL程序设计基础.ppt》由会员分享，可在线阅读，更多相关《第7章2-T-SQL程序设计基础.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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(je90PRINT1210024课程成绩优秀
2、课程成绩优秀ELSEIFvag80PRINT1210024课程成绩良好课程成绩良好ELSEPRINT1210024课程成绩及格课程成绩及格补例补例3：求两个整数的最大数。：求两个整数的最大数。DECLAREmaxINT,aINT,bINTSELECTa=32,b=56IF(ab)SELECTmax=aELSESELECTmax=bSELECTmaxAS最大数最大数4.CASE函数函数作用作用：可以计算多个条件式，并将其中一个符合条件的结果表达式返回。分类：分类：按照使用形式的不同，可以分为简单CASE函数和搜索CASE函数。(1)简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。
3、其语法形式为：CASEinput_expressionWHENwhen_expressionTHENresult_expression.nELSEelse_result_expressionENDUSEjxglGOSELECTCASEcour_nameWHEN大学语文大学语文THEN中文系教师讲授中文系教师讲授WHEN马克思主义基本原理马克思主义基本原理THEN政治历史系教师讲授政治历史系教师讲授WHEN中国近现代史纲要中国近现代史纲要THEN政治历史系教师讲授政治历史系教师讲授WHEN大学英语大学英语THEN外国语系教师讲授外国语系教师讲授WHEN线性代数线性代数THEN数学系教师讲授数学系
4、教师讲授ELSE本系教师讲授本系教师讲授ENDAS开课教师开课教师,cour_nameFROMcoursesGO【例7-7】根据cour_name列，判断course表的各个课程由哪系的老师讲授。(2)CASE搜索函数，搜索函数，CASE计算一组逻辑表达式以确定结果计算一组逻辑表达式以确定结果。语法：语法：CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END注意事项：（1）按指定顺序对每个WHEN子句的逻辑表达式逻辑表达式进行计算。返回第一个计算结果为TRUE的result_
5、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表的各个课程分别在几年级开设，使之
6、更易理解。USEjxglGOUPDATEteachersSETteach_telephone=CASEWHENdept_id=D001THEN3785947WHENdept_id=D001THEN3785940WHENdept_id=D002THEN3785938WHENdept_id=D004THEN3785926ELSE3786946ENDGO【例例7-9】在在UPDATE语句中使用语句中使用CASE函数：根据所函数：根据所在的系确定在的系确定teachers表的电话号码。表的电话号码。5.WHILECONTINUEBREAK循环控制语句循环控制语句语法形式为：WHILEBoolean_e
7、xpressionsql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUE作用：用于设置重复执行用于设置重复执行SQL语句或语句块的条件。条件为真，语句或语句块的条件。条件为真，就重复执行语句。就重复执行语句。说明：Break用于退出最内层的用于退出最内层的while循环。循环。Continue用于重新开始一次用于重新开始一次while循环。循环。USEjxglGOWHILE(SELECTmin(year(getdate()-year(stu_birth)FROMstudents)20BEGINIF(SELE
8、CTcount(*)FROMstudents)=all(SELECTstu_birthFROMstudents)ENDEND【例例7-10】查询查询students表，只要有年龄小于表，只要有年龄小于20岁的学生，就将年龄最岁的学生，就将年龄最小的那个学生删掉，如此循环下去，直到所有的学生的年龄都不小于小的那个学生删掉，如此循环下去，直到所有的学生的年龄都不小于20岁，岁，或是学生的总人数小于或是学生的总人数小于20就退出循环。就退出循环。DECLARE s int,n int,t int,c intSET S=0SET n=1WHILE n=10 BEGIN SET c=1 SET t=1
9、WHILE c=n BEGIN SET t=t*c SET c=c+1 END SET s=s+t SET n=n+1 ENDSELECT s as 10 阶阶乘之和乘之和,n as n补例补例 计算计算s=1!+2!+10!。6.GOTO语语句句 GOTO GOTO语语句可以使程序直接跳到指定的句可以使程序直接跳到指定的标标有有标识标识符的位置符的位置处处继续执继续执行。行。标识标识符可以符可以为为数字与字符的数字与字符的组组合，但必合，但必须须以以“：”结结尾。尾。语语法格式是：法格式是：GOTO label；DECLARE sum int,count intSELECT sum=0,co
10、unt=1label_1:SELECT sum=sum+count SELECT count=count+1IF count=100 GOTO label_1SELECT count,sum操作结果如下面所示：操作结果如下面所示：101 5050(1行受影响行受影响)【例【例7-10】利用利用GOTO语句求出从语句求出从1加到加到100的总和。的总和。7.调度执行语句调度执行语句WAITFORWAITFOR语句用于语句用于暂时停止执行暂时停止执行SQL语句、语句块或者存语句、语句块或者存储过程储过程等，直到所设定的时间已过或者所设定的时间已到等，直到所设定的时间已过或者所设定的时间已到才继续执
11、行。才继续执行。语法形式为：语法形式为：WAITFORDELAYtime|TIMEtime（1）DELAY：指示：指示SQLServer一直等到指定的时间过去，一直等到指定的时间过去，最长可达最长可达24小时。小时。（2）time：要等待的时间。可以按：要等待的时间。可以按datetime数据可接受的数据可接受的格式指定格式指定time，也可以用局部变量指定此参数，但，也可以用局部变量指定此参数，但不能指不能指定日期定日期。（3）TIME：指示：指示SQLServer等待到指定时间。等待到指定时间。【例例7-12】用用WAITFOR语句设置延迟操作。语句设置延迟操作。waitfor delay
12、 00:00:05 print 延延迟迟5秒秒执执行！行！-5秒后秒后执执行行print【例例7-13】用用WAITFOR语句指定从何时起执行。语句指定从何时起执行。waitfortime08:57print08:57执行执行-到到08:57的时候才会执行的时候才会执行print8.RETURN语语句句语语法格式法格式：RETURNinteger_expression作用作用:无条件地从过程、批或语句块中退出，在无条件地从过程、批或语句块中退出，在return之后的之后的其他语句不会被执行。其他语句不会被执行。说明说明:lReturn可以在过程、批和语句块中的任何位置使用。可以在过程、批和语句
13、块中的任何位置使用。lReturn与与break很相似，不同的是很相似，不同的是return可以返回一个整可以返回一个整数。数。如果没有指定返回值如果没有指定返回值，SQLServer系统会根据程序执系统会根据程序执行的结果返回一个内定值，返回值含义如下所示行的结果返回一个内定值，返回值含义如下所示:返回值返回值 含义含义返回值返回值含义含义0 0程序执行成功程序执行成功-7-7资源错误资源错误-1-1找不到对象找不到对象-8-8非致使的内部错误非致使的内部错误-2-2数据类型错误数据类型错误-9-9已经达到系统的权限已经达到系统的权限-3-3死锁死锁-10-10、-11 致使的内部不一致错误
14、致使的内部不一致错误-4-4违反权限原则违反权限原则-12-12表或指针破坏表或指针破坏-5-5语法错误语法错误-13-13数据库破坏数据库破坏-6-6用户造成的一般错用户造成的一般错误误-14-14硬件错误硬件错误表表7-4RETURN命令返回的内定状态值命令返回的内定状态值28介绍SQL批处理批处理单个 SQL 命令组成一批组成一批作为单个作为单个执行计划执行计划进行编译进行编译输出结果输出结果9.批处理批处理一个一个批处理批处理是是条或多条条或多条Transact-SQL语句的集合。语句的集合。SQL Server服务器对批处理的处理分为四个阶段：服务器对批处理的处理分为四个阶段：分析阶
15、段分析阶段，检查语法，验证合法性，检查语法，验证合法性;优化阶段优化阶段，确定最有效的方法；，确定最有效的方法；编译阶段编译阶段，生成执行计划；，生成执行计划；运行阶段运行阶段，执行语句。，执行语句。批处理最重要的特征就是它批处理最重要的特征就是它作为一个不可分的实体在服务器上作为一个不可分的实体在服务器上解释和执行。解释和执行。SQL Server有以下几种指定批处理的方法。有以下几种指定批处理的方法。l应用程序。应用程序。l存储过程或触发器。存储过程或触发器。l由由EXECUTE语句执行的字符串是一个批处理，并编译语句执行的字符串是一个批处理，并编译为一个执行计划。例如，为一个执行计划。例
16、如，EXEC(SELECT*FROM teachers)l由由sp_executesql系统存储过程系统存储过程执行的字符串是一个批处执行的字符串是一个批处理，并编译为一个执行计划。例如，理，并编译为一个执行计划。例如，execute sp_executesql NSELECT*from jxgl.dbo.teachers（1）批处理的指定GO是是批处理的结束标志批处理的结束标志。把。把GO前面的所有语句前面的所有语句当成一个批当成一个批处理来执行。处理来执行。GO命令和命令和T-SQL语句不可处在同一行上语句不可处在同一行上。在在批处理的第一条语句后批处理的第一条语句后执行任何存储过程必须包
17、含执行任何存储过程必须包含EXECUTE关键字。关键字。局部局部(用户定义用户定义)变量的作用域变量的作用域限制在一个批处理中，不可在限制在一个批处理中，不可在GO命令后引用。命令后引用。RETURN可在任何时候从批处理中退出，而不执行位于可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。之后的语句。(2)批处理的结束与退出USE jxglGO -批处理结束标志批处理结束标志CREATE VIEW students_infoAS SELECT*FROM studentsGO-CREATE VIEW语句与其他语句隔离语句与其他语句隔离SELECT*FROM students_inf
18、oGO 补例 创建一个视图，使用GO命令将CREATE VIEW语句与批处理中的其他语句(如USE、SELECT语句等)隔离。去掉go？declare i intselect i=1while i 500 begin if i%3=0 or i%7=0 print i set i=i+1 end课堂练习课堂练习1：计算：计算500以内能被以内能被3或或7整除的数。整除的数。本节小结u流程控制语句概述ubeginend语句uifelse、if exists语句uwhile、break和continue语句uwaitfor语句ureturn语句习题4、57.6游标游标游标(Cursor)是一种处理
19、数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，才使两个数据处理方式能够进行沟通。7.6.1游标概述根据游标的用途不同，SQLServer2005将游标分成三种类型：1.Transact_SQL游标游标.主要用在服务器上，不支持提取数据块或多行数据。2.API游标：游标：支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务
20、器上。3.客户游标：客户游标：当在客户机上缓存结果集时才使用。常常仅被用作服务器游标的辅助。API游标和Transact-SQL游标被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本节中我们主要讲述服务器（后台）游标。7.6.1游标概述根据Transact_SQL服务器游标的处理特性，SQLServer2005将游标分为4种：2.动态游标动态游标：与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。1.静态游标静态游标：在打开游标时在tempdb中建立SELECT结果集的快照。总是按照打开游标时的原样显示结果集，并不反应它在数据库中对任何结果集成员所做的更新。3
21、.只进游标只进游标：不支持滚动，它只支持游标从头到尾顺序提取数据，游标从数据库中提取一条记录并进行操作，操作完毕后，再提取下一条记录。4.键集游标键集游标：该游标中各行的成员身份和顺序是固定的。由一组唯一标识符（键）控制，这组键称为键集。键集是打开游标时来自符合SELECT语句要求的所有行中的一组键值。键集驱动的游标对应的键集是打开该游标时在数据库tempdb中生成的。7.6.2游标的定义与使用游标的定义与使用使用游标(CURSOR)的步骤如下。声明游标。在使用游标之前，首先需要声明游标。打开游标。打开一个游标意味着在游标中输入了相关的记录信息。获取记录信息。首先将游标当前指向的记录保存到一个
22、局部变量中，然后游标将自动移向下一条记录。将一条记录读入某个局部变量后，就可以根据需要对其进行处理了。关闭游标。释放游标锁定的记录集。释放游标。释放游标自身所占用的资源。1.声明游标（声明游标（DECLARE）：在内存中创建游标结构，）：在内存中创建游标结构，是游标语句的核心。是游标语句的核心。语法格式：语法格式：Declarecursor_name/*指定游标名指定游标名*/insensitivescrollcursor/*指定游标类型指定游标类型,insensitive表示静态游标表示静态游标*/FORSELECT_statement/*指定查询语句指定查询语句*/forreadonly|
23、updateofcolumn_name,n表7-10Declare命令中SCROLL的取值SCROLL选项选项含义含义FIRST提取游标中的第一行数据提取游标中的第一行数据LAST提取游标中的最后一行数据提取游标中的最后一行数据PRIOR提取游标当前位置的上一行数据提取游标当前位置的上一行数据NEXT提取游标当前位置的下一行数据提取游标当前位置的下一行数据RELATIVE n提取游提取游标标当前位置之前或之后的第当前位置之前或之后的第n行数据行数据(n正，往下，正，往下，n负负，往上往上)ABSULUTE n 提取游提取游标标中的第中的第n行数据行数据【例7-32】定义游标Teach_CUR，
24、以便查询教师名字和所讲授的课程名，学分。DECLARETeach_CURCURSORFORSELECTteachers.teach_name,courses.cour_name,courses.creditFROMteachers,courses,course_arrangeWHEREteachers.teach_Id=course_arrange.teach_idandcourse_arrange.cour_Id=courses.cour_Id2.打开游标（打开游标（OPEN），产生游标定义的结果集。，产生游标定义的结果集。语句格式：OPENGLOBALcursor_name|cursor_
25、variable_name其中：GLOBAL选项指定cursor_name为全局游标；cursor_name为游标名称；cursor_variable_name为游标变量名称，该变量引用一个游标。注意：当游标打开成功时，游标位置指向结果集的第一行之前。只能打开已经声明但尚未打开的游标。【例7-33】打开前面所创建的游标Teach_CUR。OPENTeach_CUR【例7-34】显示游标结果集合中数据行数SELECT数据行数=CURSOR_ROWS3.读取游标读取游标一旦游标被成功打开，就可以从游标中逐行地读取数据，一旦游标被成功打开，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数
26、据主要使用以进行相关处理。从游标中读取数据主要使用FETCH命令命令。其。其语法格式语法格式为：为：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvar FROM GLOBAL cursor_name|cursor_variable_name INTO variable_name ,.n 表9-14 fetch_status变量返回返回值值描描 述述0FETCH命令已成功命令已成功执执行行-1FETCH命令失命令失败败或者行数据己超出了或者行数据己超出了结结果果集集-2所所读读取的数据已取的数据已经经不存在。不存在。注意：游标位
27、置决定了结果集中哪一行的数据可以被提取，如果游标方式为FOR UPDATE，则可决定哪一行数据库可以更新或者删除。FETCH_STATUS变量报告上一个FETCH语句的状态，其取值和含义如下：OPEN Teach_CURFETCH NEXT FROM Teach_CUR /*取第一个数据行取第一个数据行*/WHILE fetch_status=0 /*检查检查fetch_status是否还有数据可取是否还有数据可取*/BEGIN FETCH NEXT FROM Teach_CUREND【例【例7-35】打开前面所创建的游标打开前面所创建的游标Teach_CUR，读取游标中的读取游标中的数据。数
28、据。4.关闭游标（关闭游标（CLOSE）格式为：格式为：CLOSE游标名游标名例如：关闭例如：关闭Teach_CUR游标如下描述：游标如下描述：CLOSETeach_CUR关闭已打开的游标，之后不能对游标进行读取等操作，但可关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用以使用OPEN语句再次打开该游标。语句再次打开该游标。5.删除游标（删除游标（DEALLOCATE）。）。DEALLOCATE语句格式：DEALLOCATE游标名作用：DEALLOCATE命令删除游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。删除后不可再用，必须重新声明方可。例如，删除Teach_
29、CUR游标，DEALLOCATETeach_CUR6.利用游标修改数据。利用游标修改数据。通过游标修改或删除游标基表中的当前数据行。UPDATE语句的格式为：UPDATEtable_nameSET列名=表达式,nWHERECURRENTOFcursor_nameDELETE语句的格式为：DELETEFROMtable_nameWHERECURRENTOFcursor_name说明：CURRENTOFcursor_name：表示当前游标指针所指的当前行数据。CURRENTOF只能在UPDATE和DELETE语句中使用。注意：使用游标修改基表数据的前提是声明的游标是可更新的。对相应的数据库对象（游
30、标的基表）有修改和删除权限。【例7-39】通过游标将教师表teachers记录号为3的“吕加国”老师的职称由“讲师改为“副教授”。USEjxglGODECLARETEACH_CURSOR2SCROLLCURSORFORSELECTteach_name,teach_professionalFROMteachersFORUPDATEOFteach_professionalOPENTEACH_CURSOR2FETCHABSOLUTE3FROMTEACH_CURSOR2UPDATEteachersSETteach_professional=副教授WHERECURRENTOFTEACH_CURSOR2S
31、ELECTteach_name,teach_professionalFROMteacherswhereteach_name=吕加国CLOSETEACH_CURSOR2DEALLOCATETEACH_CURSOR27.6.3使用存储过程管理游标可以使用Sp_cursor_list系统存储过程来获得对当前连接可见的游标列表，使用Sp_describe_cursor、Sp_describe_cursor_columns和Sp_describe_cursor_tables来确定游标的特性。1.Sp_describe_cursor：返回描述游标属性（例如游标的作用域、名称、类型、状态和行数）的游标。2.
32、Sp_describe_cursor_columns：返回描述游标中每一列的属性（例如列的名称、位置、大小和数据类型）的游标。3.Sp_describe_cursor_tables：报告游标被引用基表。7.6.3使用存储过程管理游标【例7-40】定义并打开一个全局游标，使用Sp_describe_cursor报告游标的属性。DECLARETeach_CUR3SCROLLCURSORFORSELECTteach_name,teach_professionalFROMteachers/*定义一个滚动游标*/OPENTeach_CUR3/*定义一个游标变量report，以存储来自sp_describ
33、e_cursor的游标信息*/DECLAREreportCURSOREXECmaster.dbo.sp_describe_cursorcursor_return=reportOUTPUT,cursor_source=Nglobal,cursor_identity=NTeach_CUR3FETCHNEXTfromreportWHILE(fetch_status-1)BEGINFETCHNEXTfromreportENDCLOSEreportDEALLOCATEreportGOCLOSETeach_CUR3DEALLOCATETeach_CUR3小结本章介绍了T-SQL的常量与变量，函数，运算符，
34、程序控制语句，游标等内容，还介绍了程序设计的一些方法和技巧。（1）常量是表示一个特定数据值的符号。常量的格式取决于它所表示的值的数据类型。（2）变量分为局部变量和全局变量两种。局部变量由DECLARE语句声明，可以由SET、SELECT或UPDATE语句赋值；全局变量不可由用户定义。（3）T-SQL的运算符分为算术运算符、位运算符、比较运算符、逻辑运算符、连接运算符，每种运算符都有专门的数据类型或操作数，各运算符间遵循一定的优先级。（4）SQLServer2005提供了大量的系统内置函数。根据系统内置函数的不同,可以分为标量函数,聚集函数,排序函数和系统函数等。（5）程序控制流语句BEGIN和
35、END要一起使用，其功能是将语句块括起来。IFELSE语句根据条件来执行语句块。当程序有多个条件需要判断时，可以使用CASE函数实现。WHILE循环可根据条件多次重复执行语句。GOTO语句会破坏程序结构化的特点，尽量不要使用。（6）游标是应用程序通过行来管理数据的一种方法。有3种游标：T-SQL游标、API服务器游标和客户游标。游标声明使用DECLARECURSOR语句，游标的使用包括打开游标、读取数据、关闭游标、删除游标等，分别使用OPEN、FETCH、CLOSE、DEARLLOCATE语句。习题习题P1974、5、6、7、8、956实验实验1、例题验证2、p196实验8：T-SQL程序设计