第6章4T-SQL

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,6,、,用户自定义函数,在实际编程过程中，除了可以直接使用系统提供的内置函数以外，,SQL Server 2005,还允许用户使用自定义的函数。在用户自定义函数中可以包含0个或多个参数，函数的返回值可以是数值，还可以是一个表。,用户自定义函数,用户创建函数时可以有,0,个、,1,个或多个参数，必须返回单一的值。返回值可以是标量，也可以是一个表。,1.1,、标量函数：,(1),标量函数的定义,CREATE FUNCTION,函数名称（形式参数名称,AS,数据类型）,RETURNS,返回数据类型,BEGIN,函数内容,RETURN,表达式,END,例：编写一个程序，输入一个日期后，返回该日期所在的季度值。,CREATE FUNCTION,fn_QuarterString(dtmDate,datetime,),RETURNS char(6),AS,BEGIN,Return(,datename(q,dtmdate)+Q+datename(yyyy,dtmDate,),END,例：编写一个函数：输入学号后得到该学号所对应的学生姓名。,CREATE FUNCTION,fn_stuName(stuID,int,),RETURNS char(10),AS,BEGIN,DECLARE name char(10),SET name=,(,SELECT,student_name,FROM,student_info,WHERE,student_id,=,stuID,),RETURN name,END,标量函数的调用,语法：,1,、所有者名,.,函数名,(,实参,1,，,实参,n),2,、,所有者名,.,函数名 实参,1,，,实参,n,或者,所有者名,.,函数名 形参,1=,实参,1,，,形参,n=,实参,n,其中：,第一种调用方法很类似于平常使用函数的方法。所以可以,select,和,set,联合使用。,第二种调用方法需结合,EXEC,来使用。,例：,use student,Go,Declare name char(10),Exec name=,dbo.fn_stuname,stuID,=200020102,Print name,或者：,use student,Go,Declare name char(10),Exec name=,dbo.fn_stuname,200020102,Print name,CREATE FUNCTION age(name varchar(20),RETURNS,int,AS,BEGIN,use student,DECLARE age,int,select age=(SELECT,year(getdate()-year(born_Date,),from,student_info,WHERE,student_name,=name),Return age,GO,-,调用,print,张一夫,age(,张一夫）,GO,内嵌表值函数,语法：,CREATE FUNCTION,函数名称（形式参数名,称,AS,数据类型）,RETURNS,TABLE,AS,RETURN,(,select-stmt,),例：,CREATE FUNCTION XK_VIEW(C_NO CHAR(10),RETRUNS TABLE,AS RETURN,(,SELECT,student_name,class_no,tele_number,FROM student_info,result_info,WHERE student_info.student_id=result_info.student_id,AND course_no=C-NO,),内嵌表值函数只能通过,select,语句来调用。,例：,select*from XK_VIEW(12),1,、通过企业管理器来创建用户自定义函数。,2,、用户自定义函数的删除：,DROP FUNCTION,从当前数据库中删除一个或多个用户定义的函数。用户定义的函数通过,CREATE FUNCTION,创建，通过,ALTER FUNCTION,修改。,语法,DROP FUNCTION ,owner_name,.,function_name,.,n,例：,Drop function,dbo.xk,6,、流程控制语句,流程控制语句是指那些用来控制程序执行和流程分支的命令，在,SQL Server 2005,中，流程控制语句主要用来控制,SQL,语句、语句块或者存储过程的执行流程。,1,）,BEGINEND,语句,BEGINEND,语句能够将多个,Transact-SQL,语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用,BEGINEND,语句，其语法形式为：,BEGIN,sql_statement|statement_block,-,一条语句,|,一组语句,END,2,）,IFELSE,语句,（,1,）语法：,IF,布尔表达式,SQL,语句块,ELSE,SQL,语句块,（,2,）,执行过程,IFELSE,语句是条件判断语句，其中，,ELSE,子句是可选的，最简单的,IF,语句没有,ELSE,子句部分。,IFELSE,语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。,SQL Server,允许嵌套使用,IFELSE,语句，而且嵌套层数没有限制。,（,3,）例：编写程序完成如下功能：查看有无班级号为,200001,的班级，有则将这个班的学生信息全部输出，否则输出提示用户没有找到该信息。,IF EXISTS,（,SELECT*FROM class_info WHERE class_no=200001,）,BEGIN,SELECT*FROM student_info WHERE class_no=200001,END,ELSE,PRINT NO student information for class200001,3,）,CASE,语句,CASE,函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。,CASE,函数按照使用形式的不同，可以分为简单,CASE,函数和搜索,CASE,函数。,（,1,）简单,CASE,语句,简单,CASE,函数将某个表达式与一组简单表达式进行比较以确定结果。,语法：,CASE input_expression,WHEN when_expression THEN result_expression,n,ELSE else_result_expression,END,执行过程：,计算,input_expression,，,然后按指定顺序对每个,WHEN,子句的,input_expression=when_expression,进行计算。,返回第一个取值为,TRUE,的,(input_expression=when_expression),的,result_expression,。,如果没有取值为,TRUE,的,input_expression=when_expression,，,则当指定,ELSE,子句时,SQL Server,将返回,else_result_expression,；,若没有指定,ELSE,子句，则返回,NULL,值。,（,2,）搜索式,CASE,语句,语法：,CASE,WHEN,boolean_expression,THEN result_expression,n,ELSE else_result_expression,END,执行过程：,按指定顺序为每个,WHEN,子句的,Boolean_expression,求值。,返回第一个取值为,TRUE,的,Boolean_expression,的,result_expression,。,如果没有取值为,TRUE,的,Boolean_expression,，,则当指定,ELSE,子句时,SQL Server,将返回,else_result_expression,；,若没有指定,ELSE,子句，则返回,NULL,值。,例：编写程序查询学号为,20000101,的学生各门课程的成绩，并将成绩转换为不同的等级。,SELECT course_no,CASE,WHEN result89 THEN,优秀,WHEN result79 AND result69 AND result59 AND result70 THEN,及格,ELSE,不及格,END AS ,成绩等级,FROM result_info,WHERE student_id=20000101,4,）,WHILE BREAK CONTINUE,语句,（,1,）语法：,WHILE,布尔表达式,SQL,语句块,BREAK,SQL,语句块,CONTINUE,（,2,）,执行过程,设置重复执行,SQL,语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，,CONTINUE,语句可以使程序跳过,CONTINUE,语句后面的语句，回到,WHILE,循环的第一行命令。,BREAK,语句则使程序完全跳出循环，结束,WHILE,语句的执行。,（,3,）例：计算,1,到,100,中奇数的和。,DECLARE NUM INT,SUM INT,SET NUM=0,SET SUM=0,WHILE NUM100,BEGIN,SET NUM=NUM+1,IF NUM%2=0,CONTINUE,ELSE,SET,SUM=SUM+NUM,END,PRINT SUM,5),无条件转移,goto,语句,GOTO,语句可改变流程执行的顺序。可将执行流程转移到标号处继续执行流程，从而跳过,GOTO,语句后至标号前的,Transact-SQL,语句。例如将流程跳转到标号,label,处执行程序：,语法：,GOTO Label,Label:,Label,标号必须出现在同一个存储过程或批处理中。,6,）,RETURN,语句,RETURN,语句用于无条件地终止一个查询、存储过程或者批处理，此时位于,RETURN,语句之后的程序将不会被执行。,语法形式：,RETURN,整数表达式,其中，,整数表达式,为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。,6,）,WAITFOR,语句,（,1,）语法形式为：,WAITFOR DELAY time|TIME time,（,2,）,参数说明：,WAITFOR,语句的作用是当进程执行到该语句时暂时停止程序执行，直到所设定的等待时间已过或到了所设定的时间，才继续向下执行程序。,时间必须为,datetime,类型的数据，如,14,：,25,：,36,，不能包括日期。,DELAY,用来设定等待的时间，,TIME,用来设定等待结束的时间点。,例：下面的程序的作用是系统等待,2,小时,30,分钟后执行,SELECT,操作。,WAITFOR DELAY 2:30:00,SELECT *FROM,student_info,