标量函数

　　函数（function）表示每个输入值对应唯一输出值的一种对应关系。函数f中对应输入值的输出值x的标准符号为f(x)。包含某个函数所有的输入值的集合被称作这个函数的定义域，包含所有的输出值的集合被称作值域。若先定义映射的概念，可以简单定义函数为，定义在非空数集之间的映射称为函数。在一个变化过程中，发生变化的量叫变量，有些数值是不随变量而改变的，我们称它们为常量。

　　自变量，函数一个与它量有关联的变量，这一量中的任何一值都能在它量中找到对应的固定值。因变量(函数)，随着自变量的变化而变化，且自变量取唯一值时，因变量(函数)有且只有唯一值与其相对应。函数值，在y是x的函数中，x确定一个值，Y就随之确定一个值，当x取a时，Y就随之确定为b，b就叫做a的函数值。

　　SQL Server 2000 允许用户创建自定义函数，自定义函数可以有返回值。

　　自定义函数分为：标量值函数或表值函数

　　如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。

　　如果 RETURNS 子句指定 TABLE，则函数为表值函数。 

　   设数集D包含于R，则称映射f：D→R为定义在D上的函数，通常简记为y=f（x），x∈D

　　其中x称为自变量，y称为因变量，D称为定义域。

　　函数定义：函数是预先定义的功能块（由代码组成）。 

　　函数过程中的这些语句用于完成某些有意义的工作——通常是处理文本，控制输入或计算数值。通过在程序代码中引入函数名称和所需的参数，可在该程序中执行（或称调用）该函数。

　　类似过程，不过函数一般都有一个返回值。它们都可在自己结构里面调用自己，称为递归。

　　大多数编程语言构建函数的方法里都含有Function关键字（或称保留字）。

　　与数学上的函数类似，函数多用于一个等式，如y= f(x)（f由用户自己定义）。

　　标量函数返回一个确定类型的标量值。其返回值类型为除TEXT、NTEXT 、IMAGE、CURSOR、 TIMESTAMP 和TABLE 类型外的其它数据类型。

　　函数体语句定义在BEGIN-END语句内，其中包含了可以返回值的Transact-SQL 命令。

     所谓标量函数指的就是此函数只返回一个值。表值函数返回值是一个表。聚合函数是在select语句中使用的，用来聚合一个结果集， 类似于Sum()或是Count()等内置的函数，而且真正的自定义聚合函数目前只能用CLR来实现。 创建标量值函数: create FUNCTION [dbo].[mytest]() RETURNS varchar(60) AS BEGIN declare @uu varchar(60) set @uu='中国历史' return @uu END 调用方法: print dbo.mytest() 带参数的: create FUNCTION mytest2( @u int ) RETURNS varchar(60) AS BEGIN declare @uu varchar(60) set @uu=case when @u=1 then '中国历史111' when @u=0 then '中国历史000' end return @uu END 调用方法1: print dbo.mytest2(1) 调用方法2: declare @ui varchar(30) select @ui=dbo.mytest2(1) select @ui as '测试' 参考: 6.5 用户定义的函数用户自己定义的函数可以分为如下三个类别： （1） 返回值为可更新表的函数，这样的函数称为内嵌表值函数； （2） 返回不可更新表的函数，这样的函数称为多语句表值函数； （3） 返回标量值的函数 注意：用户自定义函数不支持输出函数。 6.5.1 标量函数的定义 Create function owname_name, function_name (@parameter_name as data_type,….) Returns data_type [with encryption | schemabinding.] [as] Begin … End 例子：/*计算全体学生某们功课的平均成绩*/ create function average (@cnum char(20)) returns int as begin declare @aver int select @aver= (select avg(score) from xs_kc where kc_id=@cnum group by kc_id) return @aver end 6.5.2标量函数的调用（1）在select语句中调用 例子：/*在select语句中调用*/ declare @course1 char(20) declare @aver1 int select @course1='101' select @aver1=dbo.average(@course1) select @aver1 as '101课程的平均成绩' （2）利用EXEC语句执行 例子：/*exec语句中调用*/ declare @course1 char(20) declare @aver1 int select @course1='101' exec @aver1=dbo.average @course1 select @aver1 as '101课程的平均成绩' 例子：/*新建course表*/ use xscj create table course (cno int ,cname nchar(20) ,credit int ,aver as (dbo.average(cno)) ) 6.5.3 内嵌表值函数内嵌表值函数可用于实现参数化视图。 /*内嵌表值函数的定义*/ create function fn_view1 (@para nvarchar(30)) returns table as return ( select stu_id,stu_name from xscj.dbo.xs where stu_major=@para ) /*内嵌表值函数的调用*/ select * from fn_view1(N'计算机') 6.5.4 多语句表值函数内嵌表值函数和多语句表值函数都返回表，二者不同之处在于：内嵌表值函数没有函数主体，返回的表是单个select语句的结果集； 而多语句表值函数在begin…end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表中，最后返回表。 例子：/*创建返回table的函数，通过学号作为实参调用该函数，可显示该学生各门功课的成绩和学分*/ Create function score_table (@student_id char(6)) returns @score table (xs_id char(6) ,xs_name char(8) ,kc_name char(16) ,cj tinyint ,xf tinyint ) as begin insert @score select s.stu_id,s.stu_name,p.kc_name,p.kc_credit,o.score from xs as s inner join xs_kc as o on (s.stu_id=o.stu_id) inner join kc as p on (o.kc_id=p.kc_id) where s.stu_id=@student_id return end /*多语句表值函数的调用*/ select * from score_table('001103')

　　创建标量函数的语法如下：

　　CREATE FUNCTION [ owner_name.] function_name

　　( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

　　RETURNS scalar_return_data_type

　　[ WITH < function_option> [ [,] ...n] ] 标量函数相关书籍

　　[ AS ]

　　BEGIN

　　function_body

　　RETURN scalar_expression

　　END

　　创建一个标量函数，在一个字符串中用字符串1来替换所有的字符串2，并且把结果反转，显示为大写字符结果。

　　CREATE FUNCTION NoOfChar (@char1 varchar(20),@char2 varchar(20),@char3 varchar(20))

　　RETURNS varchar(20)

　　AS

　　BEGIN

　　DECLARE @newchar varchar(20)

　　select @newchar=REPLACE(@char1,@char2,@char3)

　　, @newchar=REVERSE(@newchar),

　　@newchar=UPPER(@newchar)

　　RETURN(@newchar)

　　END 

　　说明：varchar要加上长度，否则只存储一个字符！   

　　写sql存储过程经常需要调用一些函数来使处理过程更加合理，也可以使函数复用性更强，不过在写sql函数的时候可能会发现，有些函数是在表值函数下写的有些是在标量值下写的，区别是表值函数只能返回一个表，标量值函数可以返回基类型。

　　举个例子，当用户删除一个节点的时候，是需要将当前节点下的所有子节点都删掉，如果程序只传一个当前节点，那就需要写一个函数来得到当前节点下的所有子节点，这些子节点的信息就可以放到一个表中返回。 ALTER 　　　　FUNCTION testGetSubNodes(

 　　-- Add the parameters for the function here

 　　@nodeId int

　　)

　　RETURNS

　　 @t TABLE

　　(

　　 -- Add the column definitions for the TABLE variable here

 　　id bigint identity(1,1) not null,

　　nodeIds int ,

　　nodeName varchar(500)

　　)

　　AS

　　BEGIN

 　　-- Fill the table variable with the rows for your result set

 　　insert into @t values(@nodeId,'header');

　　while exists(

　　select nodeid from dbo.Tree where parentid

　　in (select nodeIds from @t) and nodeid not in(select nodeIds from @t))

　　begin

　　insert into @t select nodeid, nodename from dbo.Tree where parentid

　　in (select nodeIds from @t)

　　end    

　　RETURN

　　END   这个函数的主要功能就是返回当前节点下的所有子节点，在存储过程中写 select * from testGetSubNodes　　　　　　　　

　　(nodeId)就可以返回表中的数据了。   再写一个标量值函数 ALTER FUNCTION [dbo].[testGetSubNodes_]

　　(

 　　@nodeId int

　　)

　　RETURNS int

　　AS BEGIN

　　declare @nodeCount int

　　select @nodeCount=5 from MenuTree

　　return @nodeCount

　　END

　　这个函数很简单返回一个整型值，然后就可以在存储过程中调用了，不过调用的方式有所不同，象上面的表值函数调用是不需要所有者的，只要写函数名称就可以，对于标量值函数来说，是需要加上所有者的，比如所有者是dbo select dbo.testGetSubNodes_,这样就可以返回5，如果不加dbo，那sql会不认识这个函数。

SELECT {fn   [ (     ,.... n) ] }

以下各表列出了未在 Transact-SQL 中重复的 ODBC 标量函数。

时间、日期和时间间隔函数

示例

A.在存储过程中使用 ODBC 函数

下例在存储过程中使用了 ODBC 函数：

复制

CREATE PROCEDURE dbo.ODBCprocedure ( @string_exp nvarchar(4000) )ASSELECT {fn OCTET_LENGTH( @string_exp )};

B.在用户定义函数中使用 ODBC 函数

下例在用户定义函数中使用了 ODBC 函数：

复制

CREATE FUNCTION dbo.ODBCudf ( @string_exp nvarchar(4000) )RETURNS intASBEGINDECLARE @len intSET @len = (SELECT {fn OCTET_LENGTH( @string_exp )})RETURN(@len)END ;SELECT dbo.ODBCudf('Returns the length.');--Returns 38

C.在 SELECT 语句中使用 ODBC 函数

下面的 SELECT 语句使用了 ODBC 函数：

复制

DECLARE @string_exp nvarchar(4000) = 'Returns the length.';SELECT {fn BIT_LENGTH( @string_exp )};-- Returns 304SELECT {fn OCTET_LENGTH( @string_exp )};-- Returns 38SELECT {fn CONCAT( 'CONCAT ','returns a character string')};-- Returns CONCAT returns a character stringSELECT {fn TRUNCATE( 100.123456, 4)};-- Returns 100.123400SELECT {fn CURRENT_DATE( )};-- Returns 2007-04-20SELECT {fn CURRENT_TIME(6)};-- Returns 10:27:11.973000DECLARE @date_exp nvarchar(30) = '2007-04-21 01:01:01.1234567';SELECT {fn DAYNAME( @date_exp )};-- Returns SaturdaySELECT {fn DAYOFMONTH( @date_exp )};-- Returns 21SELECT {fn DAYOFWEEK( @date_exp )};-- Returns 7SELECT {fn HOUR( @date_exp)};-- Returns 1 SELECT {fn MINUTE( @date_exp )};-- Returns 1SELECT {fn MONTHNAME( @date_exp )};-- Returns AprilSELECT {fn QUARTER( @date_exp )};-- Returns 2SELECT {fn WEEK( @date_exp )};-- Returns 16

标量函数对一个或多个参数进行操作以产生一个单独的值。定义函数时，参数必须放置在括号中间。标量函数可以返回除了text、ntext、cursor和timestamp的任何数据类型。如果函数语句不止一条，必须使用BEGIN和END。

标量函数可以用于许多位置，包括：

SELECT语句

列的列表

WHERE子句

ORDER BY

GROUP BY

UPDATE语句的SET子句

INSERT语句

CHECK约束

DEFAULT约束

计算列

流控制语句

函数和存储过程内

下面的例子是标量用户定义的函数，返回给定日期的季度和年份：

USE AdventureWorks2008;  

GO  

CREATE FUNCTION dbo.Qtr  

 (@InDate datetime)   

RETURNS char(9)  

AS  

BEGIN   

  RETURN 'FY' + CAST(YEAR(@InDate) As varchar) +  

  '-Q' + CAST(DATEPART(qq, @InDate) AS varchar);  

END  

GO 

这个函数以一种通常用于报表的方式格式化了返回值。日期格式将被转化，以便财政2008年第一季度的日期显示为FY2008-Q1。在PRINT语句中使用函数的例子如下所示：

PRINT dbo.Qtr('3/20/2008'); 

标量函数可以在允许标量表达式的任何地方使用。例如，下面的查询将使用函数来显示数据和，按季度：

SELECT dbo.Qtr(OrderDate) As OrderQuarter,   

  SUM(TotalDue) As TotalSales   

FROM Sales.SalesOrderHeader   

GROUP BY dbo.Qtr(OrderDate)  

ORDER BY dbo.Qtr(OrderDate); 

标量用户定义的函数不止是能够格式化日期。任何不修改数据的Transact-SQL语句集都可以用来计算标量值然后返回。

下面的函数是AdventureWorks2008数据库中的一个例子。它用来计算给定产品的存货总量。

USE AdventureWorks2008;  

GO  

CREATE FUNCTION dbo.ufnGetTotalInventoryStock   

(@ProductID int)  

RETURNS int  

AS  

BEGIN  

 DECLARE @ret int;   

SELECT @ret = SUM(p.Quantity)   

FROM Production.ProductInventory p  

 WHERE p.ProductID = @ProductID;  

 IF (@ret IS NULL)    

SET @ret = 0;  

 RETURN @ret;  

END; 

下面这个例子是使用上面这个函数：

SELECT ProductNumber, Name,   

  dbo.ufnGetTotalInventoryStock(ProductID) As InventoryCount  

FROM Production.Product  

WHERE ProductNumber LIKE 'EC%'