函数与用户自定义函数.doc

函数在Transact - SQL语言中，函数被用来执行一些特殊的运算以支持SQLServer的标准命令。( 1 ).行集函数：行集函数可以在transact - SQL语句中当作表引用。( 2 ).聚合函数：用于一组值执行计算并返回一个单一的值。( 3 ).标量函数：用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值.(一)、标量函数的分类1 .配置函数：返回当前的配置信息2 .游标函数：返回有关游标的信息3 .日期和时间函数：用于对日期和时间类型的输入值进行操作，返回一个了子符串，数字或日期和时间值4 .数学函数：用于对作为函数参数提供的输入值执行操作，返回一个数字值5 .元数据函数：返回有关数据库和对象和信息6 .字符串函数：对字符串输入值执行操作，并返回一个字中或数字值7 .系统函数：执行系统操作8 .系统统计函数：返回系统的统计信息9 .文本和图像函数：对于文本或图像输入值或列执行操作，返回有关这些值的信息。(二)、具体讲解：1 .系统函数用于返回有关SQLServer系统，用户，数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在select语句的select和where子句经及表达式中使用系统函数。例：返回taihang数据库的yuan表中的第二列的名称。use taihangselect col_name ( object_id ( yuan ), 2 )注：col_name为系统函数， object_id :返回对象的id。2 .日期和时间类型日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串，数字值或日期和时间值。dateadd ( datepart , number ,date)datediff ( datepart ,date1,date2)datename ( datepart ,date)datepart ( datepart ,date)day (date)getdate ()month (date)year (date)例1：从getdate函数返回的日期中提取月份数select datepart ( month , getdate () as monthnumber 注:datepart为系统函数例2：从03 / 12 / 1998中返回月份、天数和年份数select month ( 03/12/1998 ), day ( 03/12/1998 ), year ( 03/12/1998 )3 .、字符串函数可对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用binary和varbinary数据类型。此外，果些字符串还可以处理text, ntext ,image数据类型的数据。1 .字符串函数的分类：( 1 ).基本字符串函数： upper , lower , space , replicate , stuff , reverse , ltrim , rtrim .( 2 ).字符串查找函数： charindex , patindex .( 3 ).长度和分析函数： datalength , substring , right .( 4 ).转换函数：asch, char , str , soundex , difference 4 .数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQLServer提供的数字数据( decimal , integer , float , real , money ,samllmoney,smallint和tinyint)进行处理。例：在一表达式中使用ceiling(向上取整), floor (向下到整), round (四舍五入，N位小数)函数。select ceiling ( 13.4 ), floor ( 13.4 ), round ( 13.4567 , 3 )结果为14， 13 ， 13.4570 。5 .转换函数一般情况下，SQLServer会自动处理某些数据类型的转换。例如，如果比较char和datetime表达式、smallint和int表达式、或不同长度的char表达式，SQLServe可以将它们自动转换，这种自动转换称为隐性转换。但是无法由SQLServe自动转换的或者是SQLServe自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数有两个：convert和cast.( 1 ). cast (expression as data_type)( 2 ).couvert函数允许用户反表达式从一钟数据类型转换成另一钟数据类型，还允许反日期转换成不同的样式convert (data_type length ,expression ,style )例：use bubsselect title,ytd_sales from titleswhere cast (ytd_sales as char ( 20 ) like 15% and type = trad_cook 用户自定义函数自定义函数不能执行一系列改变数据库状态的操作，可以像系统函数在查询或存储过程等的程序中使用，也可以像过程一样能过execute命令来执行。自定义函数中存储了一个Transact - SQL例程可以返回一定的值。根据函数返回值形式的不同，将用户看定义函数分为三种类型：1.标量型函数：标量型函数返回一个确定类型的标量值，其返回值类型为除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。函数体语句定义在begin - end语句内，其中包含了可以返回值的Transact - SQL命令。语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =default ,n )returns scalar_return_data_type with,n as begin function_bodyreturn scalar_expression end 其中： .function_option有两个可选值：encryption | schemabindingencryption:加密选项，让SQLServer对系统表中有关createfunction的声明加密，以防止用户自定义函数作为SQLServer复制的一部分被发布。schemabinding，计划绑定选项。将用户自定义函数绑定到它所引用的数据库对象，则函数所涉及的的数据库对象从此将不能被删除或修改，除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。 .owner_name:指定用户自定义函数的所有者。 .function_name:指定用户自定义函数的名称。 .database_name.owner_name.function_name应是唯一的。 . parameter_name :定义一个式多个参数的名称，一个函数最多可以定义1024个参数，每个参数前用 符号标明 ，参数的作用范围是整个函数，参数只能替代常量，不能替代表名，列名或其它数据库对象名称，用户自定义函数不支持输出参数。 .scalar_parameter_data_type:指定标量参数的数据类型，除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。 .scalar_return_data_type:指定标量返回值的数据类型，除了text, ntext , image , cursor ,timestampt和table类型外的其它数据类型。 .scalar_expression:指定标量型用户自定义函数返回的标量值表达式。 .function_body:指定一系列的Transact_SQL语句它们决定了函数的返回值。例：创建工龄工资计算函数use taihanggo - 创建函数 create function workyearwage( hiredate datetime , today datetime , per_wage money )- hiredate表示雇佣期,today表示当前日期，par_wage表示每一年的工龄应得的工资额 returns money as begin declare workyearwage money set workyearwage = ( year ( today ) - year ( hiredate ) * per_wage return ( workyearwage )end - 结束函数定义 go - 调用函数 select taihang.dbo.workyearwage( 1999-7-1 , getdate (), 1 )as work_year_wage注意：以上的函数体可简写为:as begin return ( year ( today ) - year ( hiredate ) * per_wage )end 2.内嵌表值函数：以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由begin - end语句括起来的函数体，其返回的表由一个位于return子句中的select命令段从数据库中筛先出来。内嵌表值型函数功能相当于一个参数化的视图。创建函数语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =default ,n )returns table with,n as return ( select - stmt)其中： . table :批定返回值为一个表。 . select - stmt:单个select语句，确定返回的表的数据。例：创建返回所有订购某类产品的公司信息函数use taihanggo create function orderfirms( productid varchar ( 30 )- productid表示产品代号 returns table as return ( select * from productsp where p.p_id = productid )go 3.多语句表值型函数：可以看作标量型和内嵌表值型函数的结合体，它的返回值是一个表，但它和标量型函数一样有一个用begin - end语句括起来的函数体。返回值表中的数据是由函数体语法：create function owner_name function_name( parameter_nameas scalar_parameter_data_type =default ,n )returns return_variable table with,