第5章 Transact-SQL语言基础 95.ppt

2020年1月30日星期四 数据库技术 第5章SQL编程 教学内容 5 1变量5 2流程控制语句5 3用户自定义函数5 4游标 2020年1月30日星期四 数据库技术 4 1变量 全局变量 由系统定义和维护 名称以两个 字符开始 SQL使用全局变量来记录SQLSERVER服务器的活动状态 Transact SQL全局变量为函数形式 现在作为函数引用 局部变量 名称以一个 字符开始 有用户自己定义和赋值 2020年1月30日星期四 数据库技术 全局变量 例 使用 ERROR变量检验SQL语句是否执行成功select学号fromxswhere姓名 周迅 if error 0print 你的SQL语句执行成功 注 可以在master库sysmessages系统表中查看与 ERROR错误代码相关的文本信息 2020年1月30日星期四 数据库技术 全局变量 例 执行UPDATE语句并用 ROWCOUNT来检测是否有发生更改的行 UPDATEauthorsSETau lname Jones WHEREau id 999 888 7777 IF ROWCOUNT 0print Warning Norowswereupdated 例 返回当前安装的日期 版本和处理器类型 SELECT VERSION 2020年1月30日星期四 数据库技术 局部变量 局部变量声明DECLARE变量名称数据类型例1 DECLARE temp countINT注 可同时等名多个局部变量 逗号分隔 2020年1月30日星期四 数据库技术 局部变量 局部变量赋值 1 SET变量名称 变量值例2 SET temp count 0 2 使用SELECT语句为局部变量赋值例3 DECLARE max fenintSELECT max fen max 总学分 FROMxs 2020年1月30日星期四 数据库技术 局部变量 例4 声明两个整型的局部变量 i1和 i2 对 i1赋初值 10 i2的值为 i1乘以5 再显示 i2的结果值 Declare i1int i2intSet i1 10Set i2 i1 5Print i2Select i2 2020年1月30日星期四 数据库技术 5 2流程控制语句 BEGIN ENDIF ELSECASE函数WHILE CONTINUE BREAKWAITFORGOTO 2020年1月30日星期四 数据库技术 1BEGIN END BEGIN END语句能够将多个SQL语句组合成一个语句块 并将它们视为一个单元处理 在条件语句和循环等控制流程语句中 当符合特定条件便要执行两个或者多个语句时 就需要使用BEGIN END语句 其语法形式为 BEGIN sql statement statement block END 2020年1月30日星期四 数据库技术 2 IF ELSE语句 IF ELSE语句是条件判断语句 其中 ELSE子句是可选的 最简单的IF语句没有ELSE子句部分 IF ELSE语句用来判断当某一条件成立时执行某段程序 条件不成立时执行另一段程序 SQLServer允许嵌套使用IF ELSE语句 而且嵌套层数没有限制 2020年1月30日星期四 数据库技术 2 IF ELSE语句 IF ELSE语句的语法形式IF逻辑表达式 sql statement statement block ELSE sql statement statement block 语句中的ELSE子句是可选的 2020年1月30日星期四 数据库技术 2 IF ELSE语句 例1DECLARE xINT yINT zINTSELECT x 1 y 2 z 3IF x yprint x y 打印字符串 x y ELSEIF y zPRINT y z ELSEPRINT z y 2020年1月30日星期四 数据库技术 2 IF ELSE语句 例2 如果有员工在办公室工作 则输出这些于是员工姓名 否则输出相应说明信息 Declare messageVARchar 200 IFexists select from员工表where所属部门 办公室 Beginset message 下列人员在办公室工作 PRINT messageSelect员工姓名from员工表where所属部门 办公室 ENDELSEBeginset message 没有人员在办公室工作 PRINT messageEND 2020年1月30日星期四 数据库技术 3 CASE函数 CASE函数可以计算多个条件式 并将其中一个符合条件的结果表达式返回 CASE函数按照使用形式的不同 可以分为简单CASE函数和搜索CASE函数 2020年1月30日星期四 数据库技术 3 CASE函数 1 简单CASE函数的语法形式简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果 CASEinput expressionWHENwhen expressionTHENresult expression n ELSEelse result expression END 2020年1月30日星期四 数据库技术 3 CASE函数 例1 声明变量 x y为字符型 长度均为6 为 x赋初值 abc 分情况判断 当 x a 时 y abc 1 当 x b 时 y bc 2 当 x abc 时 y abc 3 否则 y no 2020年1月30日星期四 数据库技术 3 CASE函数 declare xchar 6 ychar 6 select x abc select y case xwhen a then abc 1 when a then abc 2 when a then abc 3 else no endprint y 2020年1月30日星期四 数据库技术 3 CASE函数 例2 使用case函数对学生按性别分类UsexscjSelect学号 sex case性别when1then 男 when0then 女 endFromxs 2020年1月30日星期四 数据库技术 3 CASE函数 2 搜索CASE函数的语法形式CASE搜索函数计算一组布尔表达式以确定结果 CASEWHENBoolean expressionTHENresult expression n ELSEelse result expression END 2020年1月30日星期四 数据库技术 3 CASE函数 例1 声明变量 i为整型 s为字符型 长度为6 为 i赋初始值85 分情况判断 当 i在90到100范围内时 s 优 当 i在80到89范围内时 s 良 当 i在70到79范围内时 s 中 当 i在60到69范围内时 s 及格 其他 s 不及格 2020年1月30日星期四 数据库技术 3 CASE函数 declare iint schar 6 select i 85set s casewhen i 90and i 80and i 70and i 60and i 69then 及格 else 不及格 endprint s 2020年1月30日星期四 数据库技术 3 CASE函数 例2 根据学生的年龄范围显示相应信息UsexscjSelect学号 年龄 CasewhenDATEDIFF yy 出生时间 getdate 26then 年龄适中 else 年龄偏大 EndFromxs 2020年1月30日星期四 数据库技术 4WHILE CONTINUE BREAK 语法 WHILEBEGIN BREAK CONTINUE 命令行或程序块 ENDWHILE命令在设定的条件成立时会重复执行命令行或程序块 CONTINUE命令可以让程序跳过CONTINUE命令之后的语句 回到WHILE循环的第一行命令 BREAK命令则让程序完全跳出循环 结束WHILE命令的执行 WHILE语句也可以嵌套 2020年1月30日星期四 数据库技术 4WHILE CONTINUE BREAK SELECT x 1 y 1WHILE x 3BEGINPRINT x 打印变量x的值WHILE y 3BEGINSELECT c 100 x yPRINT c 打印变量c的值SELECT y y 1ENDSELECT x x 1SELECT y 1END 2020年1月30日星期四 数据库技术 4WHILE CONTINUE BREAK 例 显示字符串 China 中每个的ASCII字符和字符Declare positionint stringchar 8 Set position 1Set string China While position len string beginselectascii substring string position 1 char ascii substring string position 1 set position position 1end 2020年1月30日星期四 数据库技术 5 WAITFOR语句 WAITFOR语句用于暂时停止执行SQL语句 语句块或者存储过程等 直到所设定的时间已过或者所设定的时间已到才继续执行 语法形式为 WAITFORDELAY时间间隔或WAITFORTIME时间值 2020年1月30日星期四 数据库技术 5 WAITFOR语句 等待1小时2分零3秒后才执行SELECT语句WAITFORDELAY 01 02 03 SELECT FROMxs等到晚上11点零8分后才执行SELECT语句WAITFORTIME 23 08 00 SELECT FROMxs 2020年1月30日星期四 数据库技术 6GOTO语句 GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行 GOTO语句和标识符可以用在语句块 批处理和存储过程中 标识符可以为数字与字符的组合 但必须以 结尾 语法格式 GOTOlabel label 2020年1月30日星期四 数据库技术 6GOTO语句 利用GOTO语句求出从1加到5的总和 declare sumint countintselect sum 0 count 1label 1 select sum sum countselect count count 1if count 5gotolabel 1select count sum 2020年1月30日星期四 数据库技术 5 3用户自定义函数 标量型函数标量型函数返回一个确定类型的标量值其返回值类型为除TEXT NTEXT IMAGE CURSOR TIMESTAMP和TABLE类型外的其它数据类型 函数体语句定义在BEGIN END语句内 其中包含了可以返回值的Transact SQL命令 内联表值型函数多声明表值型函数 2020年1月30日星期四 数据库技术 5 3用户自定义函数 CREATEFUNCTION owner name unction name parameter name AS scalar parameter data type default n RETURNSscalar return data type WITH n AS BEGINfunction bodyRETURNscalar expressionEND 2020年1月30日星期四 数据库技术 5 3用户自定义函数 例 计算全体学生某门功课的平均成绩Createfunctionaverage cnumchar 20 returnsintAsBeginDeclare averintSelect aver selectavg 成绩 fromxs kcwhere课程号 cnumGroupby课程号 Return averend 2020年1月30日星期四 数据库技术 5 3用户自定义函数 在select语句中调用UsexscjDeclare course1char 20 Declare aver1intSelect course1 101 Select aver1 dbo average course1 Select aver1as 101课程的平均成绩 2020年1月30日星期四 数据库技术 5 3用户自定义函数 利用exec语句执行函数UsexscjDeclare course1char 20 Declare aver1intexec aver1 dbo average cnum 101 Select aver1as 101课程的平均成绩 2020年1月30日星期四 数据库技术 5 4游标 从某一结果集中逐一地读取一条记录 一般的SELECT语句不能解决这类问题 游标就是来解决这类问题 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制 游标总是与一条T SQL选择语句相关联因为游标由结果集 可以是零条 一条或由相关的选择语句检索出的多条记录 和结果集中指向特定记录的游标位置组成 2020年1月30日星期四 数据库技术 5 4游标 游标的使用声明 DECLARE 游标打开 OPEN 游标读取 FETCH