sql自定义函数

SQL Server 2005 自定义函数语汇小结

 

由于工作的需要，了解下SQL Server 2005 函数的写法，现在总结一下：

对于SQL Server 2005 数据库而言，函数与存储过程在语法方面是有很大的相同点，

最大的不同就是函数有返回值，直接使用returns ,而存储过程则使用output来声明输出变量

一、下面先说明下，如何创建函数

1、创建没有返回值与没有参数的函数

CREATE FUNCTION my_function()

AS

BEGIN

    DECLARE @variable varchar(255) --声明字符型变量

    DECLARE @variable int --声明整形型变量

    ...(do something)

SET @variable = '12345' --对变量variable赋值

END

2、创建没有返回值有参数的函数

CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))

AS

BEGIN

    DECLARE @variable_1 varchar(255) --声明字符型变量

    ...(do something)

SET @variable_1 = @user_Name + convert(varchar(255),@password) --将变量@user_Name与@password连接赋给@variable_1，其中convert()函数是将int型转为varchar型

END

3、创建有返回值与有参数的函数

CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))

returns varchar(255)--设置返回值，记住是returns 而不是return

AS

BEGIN

    DECLARE @result varchar(5)

    DECLARE @fagle varchar(5)

SET @result = select users.user_Name from USERS as users where users.user_Name = @user_Name and users.password = @password

IF @result = ''

    BEGIN

       SET @fagle = 'NO'

    END

ELSE

    BEGIN

       SET @falge = 'YES'

    END

return @result --返回结果

END

二、删除一个函数语法

DROP FUNCTION my_function

三、执行一个函数语法

select dbo.my_function(...) --根据有没参数来处理

go

--注：在SQL Server 2005 中，有内部函数与外部函数，数据库系统自带函数，如sum()，count()等等，这些称为内部函数，而我们自定义的函数称为外部函数。

--在执行函数语法中，也有些区别，如执行内部函数：select sum(total) from ...，那么执行外部函数则需要在函数名前加dbo. + 自定义函数名，如

--select dbo.my_function()

--go

四、下面提供二个例子

1、日期判断，判断传进来的日期是否在上个月日到本月日之间

--请注意convert()函数的用法

create function isNewContract(@date varchar(32))

returns varchar(32)

AS

begin

    declare @begin_time varchar(32)

    declare @end_time varchar(32)

    declare @temp varchar(4)

    declare @result varchar(32)

    declare @month varchar(2)

set @begin_time=convert(varchar,DATEPART(year, getdate())) + '-'

set @end_time=convert(varchar,DATEPART(year, getdate())) + convert(varchar, DATEPART(month, getdate())) + convert(varchar, '14')

set @temp=convert(varchar(2),(month(getDate())-1 ))

if(@temp < 10 )

begin

    set @begin_time = @begin_time + convert(varchar(1),'0') + @temp + '-' + convert(varchar, '15')

end

if(@date >= @begin_time and @date <= @end_time)

begin

set @result = '是'

end

else

begin

set @result = @begin_time

end

return @result

end

2、查询多条记录合并成一条记录返回，并写入EXECL表中，进行分行换行显示，其中使用游标进行循环处理

--在数据库中，EXECL默认换行符ACSII码为'10',在合并字符串之前，需要先转为EXECL识别的换行符，具体使用chat()内部函数处理

create function getExportReportCollect(@projectId int ,@month varchar(64))

returns varchar(4096) --设置返回值

AS

begin

    declare @num varchar(2)

    declare @end varchar(10)

    declare @Result varchar(4096) --用于返回查询结果

    declare city_cursor cursor for --声明游标变量

    select reported.id

    from dbo.Investment_Budget_Reported as reported

    left join dbo.Investment_Budget_Contract as con

    on reported.contractId = con.id

    where reported.[month] = @month and reported.projectId = @projectId

set @Result=''

set @num = '1'

set @end = '10' --导入EXECL表中，换行符的ACSII码

declare @Field int --声明临时存放CityID的变量

open city_cursor --打开游标

fetch next from city_cursor into @Field --将实际ID赋给变量

while(@@fetch_status = 0) --循环开始

begin

    if((select [content] from dbo.Investment_Budget_Reported where id = @Field )is not null)

    BEGIN

       if(@Result = '')

           select @Result = @Result + @num + '、' + [content] from dbo.Investment_Budget_Reported where id = @Field

       else

           select @Result = @Result + @num + '、' + [content] from dbo.Investment_Budget_Reported where id = @Field

    set @Result = @Result + char(@end) --让换行符转为EXECL认识的换行符

set @num = @num + 1

    END

       fetch next from city_cursor into @Field --下一个reportId

end

close city_cursor --关闭游标

deallocate city_cursor --释放游标引用

return @Result

end