工程数据管理(8)

工程数据管理西安工程大学机电工程学院西安工程大学机电工程学院工程数据管理工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计函数在程序的应用设计中是必不可少的，在SQL语言中提供了大量功能强大的内置函数，这些函数的使用大大的提高了SQL语句操纵信息的能力，使得SQL语句的使用有了更大的灵活性，以前我们学过的函数有，聚合函数，“print”函数，同时也用过“raiserror”函数等等，另外还有很多函数，如，日期函数、删除空格函数、获取子串函数等，不能全部讲完，现在下来我们看一下常用函数的应用。聚合函数我们以前碰到过，像sum()、avg()、min()等，就不做具体介绍了。看以下函数：1、day(date),month(date),year(date),如selectyear('78-9-28')，另外两个格式一样。2、getdate()如selectgetdate()得到当时的具体日期3、Str(number)将一数值型的数据转换为字符型数据。Str(459)返回“459”。Str(459.001)返回459.001“4、删除空格函数LTRIM(expression)，删除字符串左侧的空格。如selectLTRIM('你好')RTRIM(expression)，删除字符串右侧的空格。5、获取子串函数工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计1）left函数：返回从字符串最左边起到第Num个字符的部分子串。具体如下：Left(expression,Num)如：usemy_db1GoSelectleft((selectdzfromstu_infowherexh=960811),4)Go2）right函数：返回从字符串最右边起到第Num个字符的部分子串。具体如下：right(expression,Num)如：usemy_db1GoSelectright((selectdzfromstu_infowherexh=960811),4)Go3）substring函数：返回的子串是从字符左边第start个字符起Num个字符的部分。Substring(expression,start,Num)如：selectSubstring('我是中华人民共和国公民',3,4)下来再看一下数据函数，主要有以下几种：工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计Sin()正弦函数，Cos()余弦函数，返回以弧度表示角的对应值。Tan()正切函数，Cot()余切函数，Asin()反正弦函数，Acos()反余弦函数，返回弧度表示的角。Atan()反余切函数，Exp()表达式的指数值，Log()对数函数Log10()以10为底的对数Sqrt()表达式的平方根Abs()表达式的绝对值Pi()π值有时在函数里要用到运算符，使得表达式更加的方便灵活。所以再看一下有哪些运算符工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计运算符1、算术运算符SQL支持的算术运算符有：+：加或正号+在有时也叫做连接符，在字符串里也经常用到。-：减或负号*：乘法/：除法%：取模，返回的是余数2、赋值运算符：即等号（=）3、位运算符可以在任意两个整型数据或二进制数据之间执行位操作。主要有以下几个&，也就是按位and︱，按位or^，按位互斥，表示按位异或～，按位not，表示按位取反4、比较运算符=，,,=,=,,!=,!,!,!=工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计为了更加适应实际的应用，有时系统自带的函数无法满足客户的需求，这时就要我们自己创建一个函数，即要求用户自定义一个函数，如，我们要求出1+2+3+……+100的值，此时如何让系统计算出这个值，就要我们自定义一个函数。在SQL中，为了加快开发速度，可以将一个或多个SQL语句组成的子程序定义成函数，而不是限制用户只能使用自带的函数，它允许用户自定义函数，以便补充和扩展系统不支持的一些函数。用户定义函数采用0个或更多输入参数并返回标量值或表，函数最多可以有1024个输入参数，当函数的参数有默认值时，调用该函数必须指定默认的default关键字才能获取默认值，用户定义的函数不支持输出参数。不像存储过程，输入与输出参数都支持。与任何函数一样，用户定义的函数是可返回值的例程。根据返回值的类型，可分为三类：A、返回可更新数据表的函数，如果用户定义的函数包含单个select语句且该语句可更新，则该函数返回的表格式结果也可以更新。（内嵌表值函数）B、返回不可更新的表的函数：如果用户定义函数不止一个select语句，或包含一个不可更新的select语句，则该函数返回的表格式结果也不可更新。（多语句表值函数）C、返回标量值的函数：标量函数返回在return子句中定义的类型的单个数据值。可以使用所有标量数据类型。（标量函数）工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计用户自定义函数的创建一个是用企业管理器；另一个是用查询分析器。这个和存储过程的创建一样，虽然是在企业管理器里创建，但也要编SQL语句，所以像这种类型，一般我们都有查询分析器处理。下来看一下具体的操作：一：企业管理器二：查询分析器，具体语法：Createfunctionfunc_name------自定义的函数名(@parameter_nameparameter_type[=default])------自定义函数的参数，参数的数据类型，有默认值时写出=默认值Returnsparameter_type------------返回参数值的类型AsBeginSQL_expression--------------具体函数内容Returnscalar_expression--------------返回值的表达式end工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计创建方法知道了，现在根据上面学过的有三种自定义函数（标量函数、内嵌表值函数、多语句表值函数），分别介绍如何创建函数：如：要给学生成绩表所在库创建一个函数，函数名为my_func1，来实现数学成绩在80分以上的，返回字符串‘良’，否则‘差’Usemy_db1GoCreatefunctionmy_func1(@mathcjnumeric(4,1))returnsnvarchar(5)---------------返回值只有单个数据值，所以这个函数是标量函数。BeginDeclare@returnstrnvarchar(5)If@mathcj80Set@returnstr='良'ElseSet@returnstr='差'Return@returnstrEnd以上自定义的函数创建完了，下来看一下函数的使用工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计Usemy_db1Goselectxh,mathcj,dbo.my_func1(mathcj)as'良或差'fromstu_cj下面这个例子就是把查询结果插入到一个新表，但我们这个是用函数的查询结果，以前的是直接把列名插入。现在相当于再复习一下。Usemy_db1Goselectxh,mathcj,dbo.my_func1(mathcj)as'良或差'intonew_Tfromstu_cjwhereyear=1996-------------只把学年为1996年的信息查出插入新表Usemy_db1GoInsertnew_T1(xh,mathcj,良或差)selectxh,mathcj,dbo.my_func1(mathcj)as'良或差'fromstu_cjWhereyear=1998上面这个例子就是把查询结果插入到另一个表，这种方法叫做“插入查询结果集”因为是自定义函数，所以要识别用户，就要把DBO加上这个是查询结果工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计再看一个例子：declare@sint,@iintset@s=0set@i=1while@i101beginset@s=@s+@iset@i=@i+1endprint'和是'+str(@s)这个函数可以看出来是做什么的吗？这是用while语句求1+2+3+……+100之和。工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计再看一个例子，现在要创建如下一个函数，函数名为my_func3,其中变量@X与@Y为浮点型数据，且函数返回值A也为浮点型。A=(X－Y)/2,当XYA=(X+Y)/2，当XYA=1,当X=YCreatefunctionmy_func3(@Xfloat,@Yfloat)Returnsfloat--------这也是一个标量函数AsBeginDeclare@AfloatIf@X@Yset@A=(@X-@Y)/2If@X@Yset@A=(@X+@Y)/2If@X=@Yset@A=1Return@Aend工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计再看一个例子：Usemy_db1GoCreatefunctionmy_func6(@sushenvarchar(10))ReturnstableAsReturn(selectxh,xm,dzfromstu_infowheresushe=@sushe)现在调用一下这个函数，找出sushe为机电楼3号楼的学生信息select*fromdbo.my_func6('机电楼3号楼')上面的例子可以看出Return子句中只有一条单独的select语句，这个语句的结果构成了内嵌表值函数返回的表，所以这个例子就是内嵌表值函数的例子工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计再看一个例子：Usemy_db1GoCreatefunctionmy_func7(@sushenvarchar(10))Returns@T_ttable(xhint,xmnvarchar(5),dznvarchar(20))Asbegininsert@T_tselectxh,xm,dzfromstu_infowheresushe=@susheReturnEnd函数的调用：select*fromdbo.my_func7('紫荆公寓8号楼')以上的例子就是多语句表值函数的例子，它和内嵌表值函数类似，但不同的是，它由begin和end限定函数体，并且在returns子句里必须定义表的名称和表的格式。工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计自定义函数创建完后，我们也可以进行修改和删除，具体的语法：修改语法:alterfunctionfunc_nameSQL_state-----------这和创建函数的参数的意义一样。删除珸法：dropfunctionfunc_name工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计最后，我们再看一下在SQL中SQL语句的控制。即就是我们平常如何编程SQL语言。下来再看以下具体的SQL语言：1）数据库定义：createdatabasedatabase_name2）表定义：要求：1、NOTNULL字段（学号，姓名，性别）2、检查约束：性别（‘男’或‘女’）3、包含外键关系（注意建表顺序）工程数据管理西安工程大学机电工程学院第10章用户自定义函数的应用与程序设计usemy_db1goCreatetableT_xsxx(xhintnotnullprimarykey,Xmchar(10)notnull,Xbchar(10)notnullconstraintxb_chkcheck(xb='男'orxb='女'),Csrqdatetimenull,Sfzhchar(8)null)usemy_db1GoCreatetableT_xscj(xmchar(10)notnullprimaryke