1、工程数据管理,西安工程大学机电工程学院,西安工程大学 机电工程学院 工程数据管理,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,函数在程序的应用设计中是必不可少的，在SQL语言中提供了大量功能强大的内置函数，这些函数的使用大大的提高了SQL语句操纵信息的能力，使得SQL语句的使用有了更大的灵活性，以前我们学过的函数有，聚合函数，“print”函数，同时也用过“raiserror”函数等等，另外还有很多函数，如，日期函数、删除空格函数、获取子串函数等，不能全部讲完，现在下来我们看一下常用函数的应用。 聚合函数我们以前碰到过，像sum()、avg()、min()
2、等，就不做具体介绍 了。看以下函数： 1、day(date), month(date), year(date), 如select year(78-9-28)，另外两个格式一样。 2、getdate() 如select getdate()得到当时的具体日期 3、Str(number)将一数值型的数据转换为字符型数据。 Str(459) 返回 “ 459”。 Str(459.001) 返回 “ 459.001“ 4、删除空格函数 LTRIM(expression)，删除字符串左侧的空格。 如select LTRIM( 你好 ) RTRIM (expression)，删除字符串右侧的空格。 5、获取
3、子串函数,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,1）left函数：返回从字符串最左边起到第Num个字符的部分子串。具体如下： Left(expression,Num) 如：use my_db1 Go Select left(select dz from stu_info where xh=960811),4) Go 2）right函数：返回从字符串最右边起到第Num个字符的部分子串。具体如下： right(expression,Num) 如：use my_db1 Go Select right(select dz from stu_info wher
4、e xh=960811),4) Go 3）substring函数：返回的子串是从字符左边第start个字符起Num个字符的部分。 Substring(expression,start,Num) 如：select Substring(我是中华人民共和国公民,3,4)下来再看一下数据函数，主要有以下几种：,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,Sin()正弦函数， Cos ()余弦函数， 返回以弧度表示角的对应值。 Tan ()正切函数， Cot ()余切函数， Asin ()反正弦函数， Acos ()反余弦函数， 返回弧度表示的角。 Atan ()反
5、余切函数， Exp ()表达式的指数值， Log ()对数函数 Log10 ()以10为底的对数 Sqrt ()表达式的平方根 Abs ()表达式的绝对值 Pi () 值有时在函数里要用到运算符，使得表达式更加的方便灵活。所以再看一下有哪些运算符,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,运算符 1、算术运算符 SQL支持的算术运算符有： +：加或正号 +在有时也叫做连接符，在字符串里也经常用到。 -：减或负号 *：乘法 /：除法 %：取模，返回的是余数2、赋值运算符：即等号（=） 3、位运算符 可以在任意两个整型数据或二进制数据之间执行位操作。主要有以
6、下几个 &，也就是按位and ，按位or ，按位互斥，表示按位异或 ，按位not，表示按位取反4、比较运算符 =，, =, , !=, !, !, !=,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,为了更加适应实际的应用，有时系统自带的函数无法满足客户的需求，这时就要我们自己创建一个函数，即要求用户自定义一个函数，如，我们要求出1+2+3+100的值，此时如何让系统计算出这个值，就要我们自定义一个函数。在SQL中，为了加快开发速度，可以将一个或多个SQL语句组成 的子程序定义成函数，而不是限制用户只能使用自带的函数，它允许用户自定义函数，以便补充和扩展系统
7、不支持的一些函数。用户定义函数采用0个或更多输入参数并返回标量值或表，函数最多可以有1024个输入参数，当函数的参数有默认值时，调用该函数必须指定默认的default关键字才能获取默认值，用户定义的函数不支持输出参数。不像存储过程，输入与输出参数都支持。 与任何函数一样，用户定义的函数是可返回值的例程。根据返回值的类型，可分为三类： A、返回可更新数据表的函数，如果用户定义的函数包含单个select语句且该语句可更新，则该函数返回的表格式结果也可以更新。（内嵌表值函数）B、返回不可更新的表的函数：如果用户定义函数不止一个select语句，或包含一个不可更新的select语句，则该函数返回的表格
8、式结果也不可更新。（多语句表值函数） C、返回标量值的函数：标量函数返回在return子句中定义的类型的单个数据值 。可以使用所有标量数据类型。（标量函数）,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,用户自定义函数的创建 一个是用企业管理器；另一个是用查询分析器。 这个和存储过程的创建一样，虽然是在企业管理器里创建，但也要编SQL语句，所以像这种类型，一般我们都有查询分析器处理。 下来看一下具体的操作： 一：企业管理器 二：查询分析器，具体语法： Create function func_name - - 自定义的函数名 (parameter_name
9、parameter_type=default ) -自定义函数的参数，参数的数 据类型，有默认值时写出=默认值Returns parameter_type -返回参数值的类型 As Begin SQL_expression -具体函数内容 Return scalar_expression -返回值的表达式 end,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,创建方法知道了，现在根据上面学过的有三种自定义函数（标量函数、内嵌表值 函数、多语句表值函数），分别介绍如何创建函数： 如：要给学生成绩表所在库创建一个函数，函数名为my_func1，来实现数学成绩在8
10、0分以上的，返回字符串良，否则差 Use my_db1 Go Create function my_func1 (mathcj numeric(4,1) returns nvarchar(5) -返回值只有单个数据值，所以这个函数是标量函数。 Begin Declare returnstr nvarchar(5) If mathcj80 Set returnstr=良 Else Set returnstr=差 Return returnstr End以上自定义的函数创建完了，下来看一下函数的使用,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,Use my_d
11、b1 Go select xh,mathcj,dbo.my_func1(mathcj) as 良或差 from stu_cj下面这个例子就是把查询结果插入到一个新表，但我们这个是用函数的查询结果，以前的是直接把列名插入。现在相当于再复习一下。 Use my_db1 Go select xh,mathcj,dbo.my_func1(mathcj) as 良或差 into new_T from stu_cj where year=1996 -只把学年为1996年的信息查出插入新表Use my_db1 Go Insert new_T1(xh,mathcj,良或差) select xh,mathcj,
12、dbo.my_func1(mathcj) as 良或差 from stu_cj Where year=1998 上面这个例子就是把查询结果插入到另一个表，这种方法叫做“插入查询结果集”,因为是自定义函数，所以要识别用户，就要把DBO加上,这个是查询结果,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,再看一个例子：declare s int,i int set s=0 set i=1 while i101 begin set s=s+i set i=i+1 end print 和是+str(s)这个函数可以看出来是做什么的吗？这是用while语句求1+2+3+
13、100之和。,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,再看一个例子，现在要创建如下一个函数，函数名为my_func3,其中变量X与Y为浮点型数据，且函数返回值A也为浮点型。A=(XY)/2, 当XYA=(X+Y)/2，当XY set A=(X-Y)/2 If XY set A=(X+Y)/2 If X=Y set A=1 Return A end,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,再看一个例子： Use my_db1 Go Create function my_func6 (sushe nvarchar(
14、10) Returns table As Return (select xh,xm,dz from stu_info where sushe=sushe)现在调用一下这个函数，找出sushe为机电楼3号楼的学生信息select * from dbo.my_func6(机电楼3号楼)上面的例子可以看出Return子句中只有一条单独的select语句，这个语句的结果构成了内嵌表值函数返回的表，所以这个例子就是内嵌表值函数的例子,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,再看一个例子： Use my_db1 Go Create function my_func
15、7 (sushe nvarchar(10) Returns T_t table (xh int,xm nvarchar(5),dz nvarchar(20) As begin insert T_t select xh,xm,dz from stu_info where sushe=sushe Return End函数的调用： select * from dbo.my_func7(紫荆公寓8号楼) 以上的例子就是多语句表值函数的例子，它和内嵌表值函数类似，但不同的是，它由 begin和end限定函数体，并且在returns子句里必须定义表的名称和表的格式。,工程数据管理,西安工程大学机电工程学院
16、,第10章 用户自定义函数的应用与程序设计,自定义函数创建完后，我们也可以进行修改和删除，具体的语法：修改语法:alter function func_nameSQL_state -这和创建函数的参数的意义一样。删除珸法：drop function func_name,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,最后，我们再看一下在SQL中SQL语句的控制。即就是我们平常如何编程SQL语言。下来再看以下具体的SQL语言： 1）数据库定义：create database database_name2）表定义：要求：1、NOT NULL字段（学号，姓名，性别）
17、2、检查约束：性别（男或女）3、包含外键关系（注意建表顺序）,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,use my_db1 go Create table T_xsxx (xh int not null primary key, Xm char (10) not null, Xb char(10) not null constraint xb_chk check(xb=男 or xb=女), Csrq datetime null, Sfzh char(8) null)use my_db1 Go Create table T_xscj (xm char(
18、10) not null primary key, Xh int not null foreign key(xh) references T_xsxx, Mathcj numeric(4,1) null, Huaxuecj numeric(4,1) null, Engcj numeric(4,1) null, Phycj numeric(4,1) null, Year int),工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,create table table1_name （列名1 类型 constraint constr_namenull 或 not nu
19、ll primary key列名2 类型 null 或 not null列名3 类型 null 或 not nullforeign key(列名n,列名) references table2_name . ）一般创建表都是按物理表来创建。,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,3）修改表：alter table table_name -SQL里的参数和创建表一样 4）删除表：drop table table_name 5）插入数据：insert into table_name(列名1，列名2) values(值1) -括号里值的顺序和上面的一样ins
20、ert into table_name(列名1，列名2) values(值2) -括号里值的顺序和上面的一样 6）修改数据：update table_name set col_name=new_expression where 7）删除数据：delete from table_name where 8）查询语句：select from . where以上是简单的且是必须要知道的SQL语句，当然在实际当中，还有一些复杂的SQL语句，还要借助于流程控制语句。 1、beginend像前面我们在创建函数时用到的。 2、if.else 3、goto语句，这个语句可改变流程执行的顺序，可将执行流程转移到标
21、号处继续执行流程。如，将流程跳转到标号为label处执行程序，则goto label。此处注意的是，在label语句处必须是label：（必须有冒号，且冒号后跟SQL句，label:beginend或其它）,工程数据管理,西安工程大学机电工程学院,第10章 用户自定义函数的应用与程序设计,4、while、break和coninue语句 While：是一种控制结构，用于重复执行一条语句或语句块的条件。当条件成立（也就是说条件是TRUE）是重复执行。 While 布尔表达式SQL 语句break -break语句可停止while语句，从while循环中退出。SQL 语句continue -continue语句跳过后面的任何语句，引起while重新循环。5、return这是无条件从一个查询或存储过程中退出，即返回语句，前面已用过，不做介绍。6、waitfor这个语句可触发一个语句块、存储过程或事务的执行的时间，时间间隔或事件。语法如：waitfor delay 时间常量 意思是要等待多长时间waitfor time 时间常量 意思是要等到什么时间waitfor delay 10:00:00 意思是要等待10个小时waitfor time 10:00:00 意思是要等到10点,
