（精选）第7章2 T-SQL程序设计基础.ppt

第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 je 5 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 vag 90PRINT 1210024课程成绩优秀 ELSEIF vag 80PRINT 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 END USEjxglGOSELECTCASEcour 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 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的时候才会执行print 8 RETURN语句语法格式 RETURN integer expression 作用 无条件地从过程 批或语句块中退出 在return之后的其他语句不会被执行 说明 Return可以在过程 批和语句块中的任何位置使用 Return与break很相似 不同的是return可以返回一个整数 如果没有指定返回值 SQLServer系统会根据程序执行的结果返回一个内定值 返回值含义如下所示 表7 4RETURN命令返回的内定状态值 28 介绍SQL批处理 批处理 输出结果 9 批处理 一个批处理是 条或多条Transact SQL语句的集合 SQLServer服务器对批处理的处理分为四个阶段 分析阶段 检查语法 验证合法性 优化阶段 确定最有效的方法 编译阶段 生成执行计划 运行阶段 执行语句 批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行 SQLServer有以下几种指定批处理的方法 应用程序 存储过程或触发器 由EXECUTE语句执行的字符串是一个批处理 并编译为一个执行计划 例如 EXEC SELECT FROMteachers 由sp executesql系统存储过程执行的字符串是一个批处理 并编译为一个执行计划 例如 executesp executesqlN SELECT fromjxgl dbo teachers 1 批处理的指定 GO是批处理的结束标志 把GO前面的所有语句当成一个批处理来执行 GO命令和T SQL语句不可处在同一行上 在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字 局部 用户定义 变量的作用域限制在一个批处理中 不可在GO命令后引用 RETURN可在任何时候从批处理中退出 而不执行位于RETURN之后的语句 2 批处理的结束与退出 USEjxglGO 批处理结束标志CREATEVIEWstudents infoASSELECT FROMstudentsGO CREATEVIEW语句与其他语句隔离SELECT FROMstudents infoGO 补例创建一个视图 使用GO命令将CREATEVIEW语句与批处理中的其他语句 如USE SELECT语句等 隔离 去掉go declare iintselect i 1while i 500beginif i 3 0or i 7 0print iset i i 1end 课堂练习1 计算500以内能被3或7整除的数 本节小结 流程控制语句概述begin end语句if else ifexists语句while break和continue语句waitfor语句return语句 习题4 5 7 6游标 游标 Cursor 是一种处理数据的方法 为了查看或者处理结果集中的数据 游标提供了在结果集中向前或者向后浏览数据的能力 可以把游标看成一种指针 可以指向结果集中的任意位置 它允许用户对指定位置的数据进行处理 可以把结果集中的数据放在数组 应用程序中或其它地方 游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来 才使两个数据处理方式能够进行沟通 7 6 1游标概述 根据游标的用途不同 SQLServer2005将游标分成三种类型 1 Transact SQL游标 主要用在服务器上 不支持提取数据块或多行数据 2 API游标 支持在OLEDB ODBC以及DB library中使用游标函数 主要用在服务器上 3 客户游标 当在客户机上缓存结果集时才使用 常常仅被用作服务器游标的辅助 API游标和Transact SQL游标被称为服务器游标 也被称为后台游标 而客户端游标被称为前台游标 在本节中我们主要讲述服务器 后台 游标 7 6 1游标概述 根据Transact SQL服务器游标的处理特性 SQLServer2005将游标分为4种 2 动态游标 与静态游标相对 当滚动游标时 动态游标反映结果集中所做的所有更改 1 静态游标 在打开游标时在tempdb中建立SELECT结果集的快照 总是按照打开游标时的原样显示结果集 并不反应它在数据库中对任何结果集成员所做的更新 3 只进游标 不支持滚动 它只支持游标从头到尾顺序提取数据 游标从数据库中提取一条记录并进行操作 操作完毕后 再提取下一条记录 4 键集游标 该游标中各行的成员身份和顺序是固定的 由一组唯一标识符 键 控制 这组键称为键集 键集是打开游标时来自符合SELECT语句要求的所有行中的一组键值 键集驱动的游标对应的键集是打开该游标时在数据库tempdb中生成的 7 6 2游标的定义与使用 使用游标 CURSOR 的步骤如下 声明游标 在使用游标之前 首先需要声明游标 打开游标 打开一个游标意味着在游标中输入了相关的记录信息 获取记录信息 首先将游标当前指向的记录保存到一个局部变量中 然后游标将自动移向下一条记录 将一条记录读入某个局部变量后 就可以根据需要对其进行处理了 关闭游标 释放游标锁定的记录集 释放游标 释放游标自身所占用的资源 1 声明游标 DECLARE 在内存中创建游标结构 是游标语句的核心 语法格式 Declarecursor name 指定游标名 insensitive scroll cursor 指定游标类型 insensitive表示静态游标 FORSELECT statement 指定查询语句 for readonly update ofcolumn name n 表7 10Declare命令中SCROLL的取值 例7 32 定义游标Teach CUR 以便查询教师名字和所讲授的课程名 学分 DECLARETeach CURCURSORFORSELECTteachers teach name courses cour name courses creditFROMteachers courses course arrangeWHEREteachers teach Id course arrange teach idandcourse arrange cour Id courses cour Id 2 打开游标 OPEN 产生游标定义的结果集 语句格式 OPEN GLOBAL cursor name cursor variable name 其中 GLOBAL选项指定cursor name为全局游标 cursor name为游标名称 cursor variable name为游标变量名称 该变量引用一个游标 注意 当游标打开成功时 游标位置指向结果集的第一行之前 只能打开已经声明但尚未打开的游标 例7 33 打开前面所创建的游标Teach CUR OPENTeach CUR 例7 34 显示游标结果集合中数据行数SELECT数据行数 CURSOR ROWS 3 读取游标 一旦游标被成功打开 就可以从游标中逐行地读取数据 以进行相关处理 从游标中读取数据主要使用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变量 注意 游标位置决定了结果集中哪一行的数据可以被提取 如果游标方式为FORUPDATE 则可决定哪一行数据库可以更新或者删除 FETCH STATUS变量报告上一个FETCH语句的状态 其取值和含义如下 OPENTeach CURFETCHNEXTFROMTeach CUR 取第一个数据行 WHILE fetch status 0 检查 fetch status是否还有数据可取 BEGINFETCHNEXTFROMTeach CUREND 例7 35 打开前面所创建的游标Teach CUR 读取游标中的数据 4 关闭游标 CLOSE 格式为 CLOSE游标名例如 关闭Teach CUR游标如下描述 CLOSETeach CUR关闭已打开的游标 之后不能对游标进行读取等操作 但可以使用OPEN语句再次打开该游标 5 删除游标 DEALLOCATE DEALLOCATE语句格式 DEALLOCATE游标名作用 DEALLOCATE命令删除游标与游标名或游标变量之间的联系 并且释放游标占用的所有系统资源 删除后不可再用 必须重新声明方可 例如 删除Teach CUR游标 DEALLOCATETeach CUR 6 利用游标修改数据 通过游标修改或删除游标基表中的当前数据行 UPDATE语句的格式为 UPDATEtable nameSET列名 表达式 n WHERECURRENTOFcursor nameDELETE语句的格式为 DELETEFROMtable nameWHERECURRENTOFcursor name说明 CURRENTOFcursor name 表示当前游标指针所指的当前行数据 CURRENTOF只能在UPDATE和DELETE语句中使用 注意 使用游标修改基表数据的前提是声明的游标是可更新的 对相应的数据库对象 游标的基表 有修改和删除权限 例7 39 通过游标将教师表teachers记录号为3的 吕加国 老师的职称由 讲师 改为 副教授 USEjxglGODECLARETEACH CURSOR2SCROLLCURSORFORSELECTteach name teach professionalFROMteachersFORUPDATEOFteach professionalOPENTEACH CURSOR2FETCHABSOLUTE3FROMTEACH CURSOR2UPDATEteachersSETteach professional 副教授 WHERECURRENTOFTEACH CURSOR2SELECTteach name teach professionalFROMteacherswhereteach name 吕加国 CLOSETEACH CURSOR2DEALLOCATETEACH CURSOR2 7 6 3使用存储过程管理游标 可以使用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