第6章4 T-SQL.ppt

6 用户自定义函数 在实际编程过程中 除了可以直接使用系统提供的内置函数以外 SQLServer2005还允许用户使用自定义的函数 在用户自定义函数中可以包含0个或多个参数 函数的返回值可以是数值 还可以是一个表 用户自定义函数 用户创建函数时可以有0个 1个或多个参数 必须返回单一的值 返回值可以是标量 也可以是一个表 1 1 标量函数 1 标量函数的定义CREATEFUNCTION函数名称 形式参数名称AS数据类型 RETURNS返回数据类型BEGIN函数内容RETURN表达式END 例 编写一个程序 输入一个日期后 返回该日期所在的季度值 CREATEFUNCTIONfn QuarterString dtmDatedatetime RETURNSchar 6 ASBEGINReturn datename q dtmdate Q datename yyyy dtmDate END 例 编写一个函数 输入学号后得到该学号所对应的学生姓名 CREATEFUNCTIONfn stuName stuIDint RETURNSchar 10 ASBEGINDECLARE namechar 10 SET name SELECTstudent nameFROMstudent infoWHEREstudent id stuID RETURN nameEND 标量函数的调用 语法 1 所有者名 函数名 实参1 实参n 2 所有者名 函数名实参1 实参n或者所有者名 函数名形参1 实参1 形参n 实参n其中 第一种调用方法很类似于平常使用函数的方法 所以可以select和set联合使用 第二种调用方法需结合EXEC来使用 例 usestudentGoDeclare namechar 10 Exec name dbo fn stuname stuID 200020102Print name或者 usestudentGoDeclare namechar 10 Exec name dbo fn stuname200020102Print name CREATEFUNCTIONage namevarchar 20 RETURNSintASBEGINusestudentDECLARE ageintselect age SELECTyear getdate year born Date fromstudent infoWHEREstudent name name Return ageGO 调用print 张一夫age 张一夫 GO 内嵌表值函数 语法 CREATEFUNCTION函数名称 形式参数名称AS数据类型 RETURNSTABLEASRETURN select stmt 例 CREATEFUNCTIONXK VIEW C NOCHAR 10 RETRUNSTABLEASRETURN SELECTstudent name class no tele numberFROMstudent info result infoWHEREstudent info student id result info student idANDcourse no C NO 内嵌表值函数只能通过select语句来调用 例 select fromXK VIEW 12 1 通过企业管理器来创建用户自定义函数 2 用户自定义函数的删除 DROPFUNCTION从当前数据库中删除一个或多个用户定义的函数 用户定义的函数通过CREATEFUNCTION创建 通过ALTERFUNCTION修改 语法DROPFUNCTION owner name function name n 例 Dropfunctiondbo xk 6 流程控制语句流程控制语句是指那些用来控制程序执行和流程分支的命令 在SQLServer2005中 流程控制语句主要用来控制SQL语句 语句块或者存储过程的执行流程 1 BEGIN END语句BEGIN END语句能够将多个Transact SQL语句组合成一个语句块 并将它们视为一个单元处理 在条件语句和循环等控制流程语句中 当符合特定条件便要执行两个或者多个语句时 就需要使用BEGIN END语句 其语法形式为 BEGIN sql statement statement block 一条语句 一组语句END 2 IF ELSE语句 1 语法 IF布尔表达式SQL语句块 ELSESQL语句块 2 执行过程IF ELSE语句是条件判断语句 其中 ELSE子句是可选的 最简单的IF语句没有ELSE子句部分 IF ELSE语句用来判断当某一条件成立时执行某段程序 条件不成立时执行另一段程序 SQLServer允许嵌套使用IF ELSE语句 而且嵌套层数没有限制 3 例 编写程序完成如下功能 查看有无班级号为200001的班级 有则将这个班的学生信息全部输出 否则输出提示用户没有找到该信息 IFEXISTS SELECT FROMclass infoWHEREclass no 200001 BEGINSELECT FROMstudent infoWHEREclass no 200001ENDELSEPRINT NOstudentinformationforclass200001 3 CASE语句CASE函数可以计算多个条件式 并将其中一个符合条件的结果表达式返回 CASE函数按照使用形式的不同 可以分为简单CASE函数和搜索CASE函数 1 简单CASE语句简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果 语法 CASEinput expressionWHENwhen expressionTHENresult expression n ELSEelse result expression END执行过程 计算input expression 然后按指定顺序对每个WHEN子句的input expression when expression进行计算 返回第一个取值为TRUE的 input expression when expression 的result expression 如果没有取值为TRUE的input expression when expression 则当指定ELSE子句时SQLServer将返回else result expression 若没有指定ELSE子句 则返回NULL值 2 搜索式CASE语句语法 CASEWHENboolean expressionTHENresult expression n ELSEelse result expression END执行过程 按指定顺序为每个WHEN子句的Boolean expression求值 返回第一个取值为TRUE的Boolean expression的result expression 如果没有取值为TRUE的Boolean expression 则当指定ELSE子句时SQLServer将返回else result expression 若没有指定ELSE子句 则返回NULL值 例 编写程序查询学号为20000101的学生各门课程的成绩 并将成绩转换为不同的等级 SELECTcourse no CASEWHENresult 89THEN 优秀 WHENresult 79ANDresult69ANDresult59ANDresult 70THEN 及格 ELSE 不及格 ENDAS 成绩等级 FROMresult infoWHEREstudent id 20000101 4 WHILE BREAK CONTINUE语句 1 语法 WHILE布尔表达式SQL语句块 BREAK SQL语句块 CONTINUE 2 执行过程设置重复执行SQL语句或语句块的条件 只要指定的条件为真 就重复执行语句 其中 CONTINUE语句可以使程序跳过CONTINUE语句后面的语句 回到WHILE循环的第一行命令 BREAK语句则使程序完全跳出循环 结束WHILE语句的执行 3 例 计算1到100中奇数的和 DECLARE NUMINT SUMINTSET NUM 0SET SUM 0WHILE NUM 100BEGINSET NUM NUM 1IF NUM 2 0CONTINUEELSESET SUM SUM NUMENDPRINT SUM 5 无条件转移goto语句GOTO语句可改变流程执行的顺序 可将执行流程转移到标号处继续执行流程 从而跳过GOTO语句后至标号前的Transact SQL语句 例如将流程跳转到标号label处执行程序 语法 GOTOLabel Label Label标号必须出现在同一个存储过程或批处理中 6 RETURN语句RETURN语句用于无条件地终止一个查询 存储过程或者批处理 此时位于RETURN语句之后的程序将不会被执行 语法形式 RETURN 整数表达式 其中 整数表达式 为返回的整型值 存储过程可以给调用过程或应用程序返回整型值 6 WAITFOR语句 1 语法形式为 WAITFOR DELAY time TIME time 2 参数说明 WAITFOR语句