Sql server 的表值函数是返回一个Table类型,table类型相当与一张存储在内存中的一张虚拟表。
实现表值函数很简单：
下面是一个不带输入参数的表值函数
create function tvpoints()
returns table
as 
return
(
select * from tb_users
);

 
这个表值函数数查询所有用户表的数据
对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中。
以下示例创建了一个表值函数.
create function tvpoints()
returns @points table (x float, y float)
as begin
insert @points values(1,2);
insert @points values(3,4);
return;
end


 
查询表值函数跟查询普通表一样
select * from tvpoints()
返回的是一张表
 
 
带输入参数的表值函数
create function tvpoints2(@x AS int,@y as int)
returns @points table (x float, y float)
as begin
insert @points values(@x,@y);
return;
end


SQL_标量值函数
2009-03-02 20:47
SQL NOW() 函数取得当日的数据

在NOW （ ）函数返回当前系统日期和时间。



SQL 标量函数----->日期函数 day() 、month()、year()、DATEADD()、ATEDIFF()、DATENAME()、DATEPART() GETDATE()



执行实例(表:life_unite_product 有createtime时间字段)

select day(createtime) from life_unite_product     --取时间字段的天值

select month(createtime) from life_unite_product   --取时间字段的月值

select year(createtime) from life_unite_product    --取时间字段的年值



select datepart(yy,createtime) from life_unite_product     --取时间字段的年值

select datepart(qq,createtime) from life_unite_product     --取时间字段的季度值

select datepart(mm,createtime) from life_unite_product     --取时间字段的月值

select datepart(dy,createtime) from life_unite_product     --取时间字段是那年的第几天

select datepart(dd,createtime) from life_unite_product     --取时间字段的天值

select datepart(wk,createtime) from life_unite_product       --取时间字段是那年的第几个星期

select datepart(dw,createtime) from life_unite_product       
--取时间字段是那年的那个星期的第几个工作日（工作日从星期日开算）

select datepart(hh,createtime) from life_unite_product     --取时间字段的小时值

select datepart(mi,createtime) from life_unite_product     --取时间字段的分钟值

select datepart(ss,createtime) from life_unite_product     --取时间字段的秒值

select datepart(ms,createtime) from life_unite_product     --取时间字段的毫秒值



select dateadd(yy,-1,createtime) from life_unite_product   ----取时间字段(年份被减1了)

select dateadd(mm,3,createtime) from life_unite_product   ----取时间字段(月份被加3了)

select dateadd(dd,1,createtime) from life_unite_product   ----取时间字段(日被加1了)



select DATEDIFF(yy,createtime,getdate()) from life_unite_product --与当前日期的年份差

select DATEDIFF(mm,createtime,getdate()) from life_unite_product --与当前日期的月份差

select DATEDIFF(dd,createtime,getdate()) from life_unite_product --与当前日期的日数差

select DATEDIFF(mi,createtime,getdate()) from life_unite_product --与当前日期的分钟数差



select datename(yy,createtime) from life_unite_product   --取时间字段的年值

select datename(mm,createtime) from life_unite_product   --取时间字段的月值

select datename(dd,createtime) from life_unite_product   --取时间字段的天值


select getdate()   --取当前时间

日期函数
1、day(date_expression) 
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD (， ， )
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。参数“datepart” 取值如下：
5、DATEDIFF()
DATEDIFF (， ， )
返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (， )
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART ( datepart , date )
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd， date) 等同于DAY (date)
DATEPART (mm， date) 等同于MONTH (date)
DATEPART (yy， date) 等同于YEAR (date)
下表列出了 datepart 选项以及 SQL Server Compact Edition 所识别的缩写：
日期部分        缩写 
年份            yy、yyyy 
季度            qq、q 
月份            mm、m 
每年的某一日    dy、y 
日期            dd、d 
星期            wk、ww 
工作日*         dw
小时            hh 
分钟            mi、n 
秒              ss、s 
毫秒            ms 
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间
日期函数用来操作DATETIME 和SMALLDATETIME 类型的数据，执行算术运算。与其它函数一样，可以在Select 语句的Select 
和Where 
子句以及表达式中使用日期函数。其使用方法如下：
日期函数参数，其中参数个数应不同的函数而不同。
·DAY（）
DAY（） 函数语法如下：
DAY （<date_expression>）
DAY（） 函数返回date_expression 中的日期值。
·MONTH（）
MONTH（） 函数语法如下：
MONTH (<date_expression>)
MONTH（） 函数返回date_expression 中的月份值。
与DAY（） 函数不同的是，MONTH（） 函数的参数为整数时，一律返回整数值1，即SQL Server 认为其是1900 年1 月。
·YEAR（）
YEAR（） 函数语法如下：
YEAR （<date_expression>）
YEAR（） 函数返回date_expression 中的年份值。
提醒：在使用日期函数时，其日期值应在1753年到9999年之间，这是SQL Server系统所能识别的日期范围，否则会出现错误。
·DATEADD（）
DATEADD（） 函数语法如下：
DATEADD （<datepart>， <number>， <date>）
DATEADD（） 函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期。参数“datepart” 
在日期函数中经常被使用，它用来指定构成日期类型数据的各组件，如年、季、月、日、星期等。其取值如表4-9 所示：
·DATEDIFF（）
DATEDIFF（） 函数语法如下：
DATEDIFF（） （<datepart>， <date1>， <date2>）
DATEDIFF（） 函数返回两个指定日期在datepart 方面的不同之处，即date2 
超过date1的差距值，其结果值是一个带有正负号的整数值。针对不同的datepart， 
DATEDIFF（）函数所允许的最大差距值不一样，如：datepart 为second 时，DATEDIFF（） 函数所允许的最大差距值为68： 
年datepart 为millisecond 时，DATEDIFF（） 
函数所允许的最大差距值为24 天20 小时30 分23 秒647 毫秒。
·DATENAME（）
DATENAME（） 函数语法如下：
DATENAME （<datepart>， <date）>
DATENAME（） 函数以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
·DATEPART（）
DATEPART（） 函数语法如下：
DATEPART （<datepart>， <date>）
DATEPART（） 函数以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART （dd， date） 等同于DAY （date）
DATEPART （mm， date） 等同于MONTH （date）
DATEPART （yy， date） 等同于YEAR （date）
·GETDATE（）
GETDATE（） 函数语法如下：
GETDATE（）
GETDATE（） 函数以DATETIME 的缺省格式返回系统当前的日期和时间，它常作为其它函数或命令的参数使用。
相关链接：http://blog.csdn.net/hongsejiaozhu/archive/2009/05/08/4160621.aspx

Sqlserver表类型和表变量

表类型可以用在存储过程中，用于批量增加


sqlserver2008表类型定义：

[sql]
use Customer
GO
if exists(select 1
from systypes 
where name='Type_TB_Biz_Account')
    drop 
typeType_TB_Biz_Account
GO

----------------------------------------------------------
-- TypeName: Type_TB_Biz_Account 
----------------------------------------------------------
CREATE TYPE
Type_TB_Biz_Account  AS TABLE

(
    Account 
int,
    Balance 
decimal(19,2),
    Credit 
decimal(19,2),
    [Readonly] 
int
);
GO

if exists(select 1
from sysobjects 
where name='SP_Biz_Account_BatchUpdateBalance')
    drop 
proc SP_Biz_Account_BatchUpdateBalance
GO
----------------------------------------------------------
-- ProcedureName: 批量更新账户余额、信用额
----------------------------------------------------------
create proc
SP_Biz_Account_BatchUpdateBalance
@TBAccount  Type_TB_Biz_Account  readonly
as
    update 
TB_Biz_Account
    set 
Balance=New.Balance,Credit=New.Credit
    FROM 
dbo.TB_Biz_Account
as Cur 
    INNER 
JOIN @TBAccount AS
New  ON Cur.Account
= New.Account;
    
    return 
@@rowcount
GO


sqlserver2005好像不支持表类型，不过支持表变量

表变量定义： 

[sql]

declare  
@mtTable  table(
    id int,

    [name]varchar(10)
)

表值函数有两种形式：

1.内联表值函数

Create FUNCTION Funtion_name
(  
    --这里定义传入参数以及类型
)
RETURNS TABLE
AS
RETURN
(
    --这里直接写查询语句，返回结果就是查询语句的结果集
)
GO

示例：

CREATE FUNCTION F_BConversion
(  
    @NUM1 INT,
    @NUM2 INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT @NUM1+@NUM2 Addition,@NUM1-@NUM2 Subtration,@NUM1*@NUM2 Multiplication,@NUM1/@NUM2 Division
)
GO


 


这个函数通过传入两个数字分别计算这两个数字加减乘除的结果，表值函数的使用调用方式就相当于表一样，可以直接select * from，也可以和其他表做关联， 只不过后面多个传入参数罢了！


 

2.多语句表值函数

CREATE FUNCTION Funtion_name
(
    --这里定义传入参数及类型
)
RETURNS
@table_name TABLE
(
    --这里定义@table_name的列名
)
AS
BEGIN
    --这里写sql语句并且将最终需要返回的结果集塞到@table_name 这张表里面
    RETURN 
END
GO

示例：

ALTER FUNCTION F_TConversion
(
    @NUM INT
)
RETURNS
@t_table TABLE
(
    [Binary] varchar(64),
    Octal varchar(16),
    Hexadecimal varchar(8)
)
AS
BEGIN
    DECLARE @RESULT2 VARCHAR(500)='',@RESULT8 VARCHAR(500)='',@RESULT16 VARCHAR(500)=''; 
    WITH CTE AS( 
        SELECT @NUM/2 D2,@NUM%2 S2,@NUM/8 D8,@NUM%8 S8,@NUM/16 D16,@NUM%16 S16,1 [INDEX] 
        UNION ALL 
        SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16%16,[INDEX]+1 FROM CTE WHERE D2>0 
    ) 
    SELECT @RESULT2+=CAST(S2 AS VARCHAR(1))
          ,@RESULT8+=CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
          ,@RESULT16+=CASE WHEN D16=0 AND S16=0 THEN ''
                           ELSE CASE CAST(S16 AS VARCHAR(5))
                                WHEN '10' THEN 'A' 
                                WHEN '11' THEN 'B' 
                                WHEN '12' THEN 'C' 
                                WHEN '13' THEN 'D' 
                                WHEN '14' THEN 'E' 
                                WHEN '15' THEN 'F' 
                                ELSE CAST(S16 AS VARCHAR(5))
                            END
                        END
    FROM CTE ORDER BY [INDEX] DESC
    INSERT INTO @t_table
    SELECT @RESULT2,@RESULT8,@RESULT16
    RETURN 
END
GO






这个函数是根据我前段时间写的一个关于进制转换的列子改编的，这里通过传入一个十进制的数字，分别返回对应的二进制、八进制、十六进制。


 

标量值函数：

CREATE FUNCTION function_name
(
	--这里定义传入参数及类型
)
RETURNS 
	--这里需要定义返回值的类型
AS
BEGIN
	--定义返回值
	--写sql逻辑
	--返回返回值
END
GO


示例：

CREATE FUNCTION F_Conversion
(
    @NUM INT,
    @TYPE INT
)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @RESULT VARCHAR(500)=''; 
    WITH CTE AS( 
    SELECT @NUM/@type D,@NUM%@type S,1 [INDEX] 
    UNION ALL 
    SELECT D/@type , D%@type,[INDEX]+1 FROM CTE WHERE D>0 
    ) 
    SELECT @RESULT+=CASE CAST(S AS VARCHAR(5)) WHEN '10' THEN 'A' 
            WHEN '11' THEN 'B' 
            WHEN '12' THEN 'C' 
            WHEN '13' THEN 'D' 
            WHEN '14' THEN 'E' 
            WHEN '15' THEN 'F' 
            ELSE CAST(S AS VARCHAR(5)) END 
    FROM CTE ORDER BY [INDEX] DESC 
    RETURN @RESULT
END
GO





这个函数传入两个参数都是int型，分别是整数和返回的进制类型，这个例子将正整数10转换成对应的二进制。

顾名思义：表值函数返回的是表，而标量值函数可以返回基类型
一、表值函数
用户定义表值函数返回 table 数据类型，表是单个 SELECT 语句的结果集。
以下示例：

CREATE FUNCTION F3_GetEmployeeSalary
(
	@EmployeeID VARCHAR(50) --参数
)
RETURNS TABLE  --返回类型为表
AS
RETURN
(
	SELECT * FROM dbo.TEmployee
	WHERE EmployeeID = @EmployeeID   --通过一条sql查询语句获取表中数据
)

 --函数调用
SELECT * FROM F3_GetEmployeeSalary('1')
注意：

（1）只能返回Table，returns后边一定是table
（2）as后面没有begin/end，只有一个return语句返回特定的记录


对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中。
以下示例：
CREATE FUNCTION [dbo].[F_SQLSERVER_SPLIT](@Long_str varchar(8000),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID int IDENTITY PRIMARY KEY,      
    short_str varchar(8000)    
)    
AS   
BEGIN   
    DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int   
    SET @split_str_length = LEN(@split_str)    
    IF CHARINDEX(@split_str,@Long_str)=1 
         SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
    ELSE
         SET @long_str_Tmp=@Long_str
    IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1    
        SET @long_str_Tmp=@long_str_Tmp+@split_str    
    ELSE   
        SET @long_str_Tmp=@long_str_Tmp    
    IF CHARINDEX(@split_str,@long_str_Tmp)=0
        Insert INTO @tmp select @long_str_Tmp 
    ELSE
        BEGIN
            WHILE CHARINDEX(@split_str,@long_str_Tmp)>0    
                BEGIN   
                    SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)    
                    DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int   
                    SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)    
                    SET @split_str_Position_END = LEN(@short_str)+@split_str_length    
                    SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
                    IF @short_str<>'' Insert INTO @tmp select @short_str    
                END           
        END
    RETURN     
END

--函数调用
select * from F_SQLSERVER_SPLIT('a,b,c',',')
注意：

（1）returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。(实质上是定义了一个表变量)

（2）在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

（3）最后只需要return，return后面不跟任何变量。(返回的是表变量里面的数据)

二、标量值函数

以下示例：

CREATE FUNCTION dbo.F3_GetMax
(
	@a INT,   --函数的两个参数
	@b INT
)
RETURNS INT --返回类型为INT
AS
BEGIN
	DECLARE @max INT;
	IF(@a>=@b)
	BEGIN
		SET @max = @a;
	END
	ELSE
	BEGIN
		SET @max = @b
	END

	RETURN @max;  --最后return返回@max中的值
END

--调用函数
SELECT dbo.F3_GetMax(2,3);
注意：


（1）必须使用两部分组成函数的名字来调用函数，即所有者.对象名，如dbo.F3_GetMax(2,3)


（2）所有的传入参数前必须加@


（3）不要写漏和写错关键字，如as，returns，return


（4）returns后面不是跟一个变量，而是跟变量的返回值类型


（5）在begin/end语句块中，使用的是return