SQL FUNCTION 建立

SQL 用户自定义函数的建立

--*********************************************************************************************

-- ABOUT SQL USER DEFINE FUNCTION,
-- 关于SQL用户自定义函数的介绍和使用方法
--*********************************************************************************************
use TESTDB01
go


-- 创建SQL用户自定义函数的格式
-- CREATE FUNCTION dbo.FuntionName(@p01 dataType [= 默认值],@p02 dataType [= 默认值][，参数列表])
-- RETURNS DataType
-- AS
-- BEGIN
-- ...
-- END 
----------------------------------------------------------------------------------------------
-- 删除自定义函数
-- DROP FUNCTION [dbo.]FunctionName
----------------------------------------------------------------------------------------------
-- 使用有两种情况:1,返回单(列)值 2,返回列表值,注意加 dbo. 所有者,是系统函数是函数名称前加两个冒号(::)
-- 1,如果是返回单(列)值时: select [dbo.]functionName(参数) as [列名]
-- 2,如果是返回列表值时: select * from [dbo.]FunctionName(参数)
----------------------------------------------------------------------------------------------
-- 判断函数的存在:
-- 用户自定义的函数信息保存在本库的系统表 sysobject 中,查询该表中name=函数名称 和 xtype='FN'便知函数的有无


--********************************************************************************************
-- EXAMPLE 01, 功能:将表 Table01 的数据查询出来，并以字符串形式返回
----------------------------------------------------------------------------------------------
if exists(select * from sysobjects where xtype='FN' and lower([name])='showdata')
drop function showData
go


CREATE FUNCTION showData()
returns varchar(8000)
AS
BEGIN
declare @str varchar(8000)
set @str=''
select @str=@str+convert(varchar(10),[ID])+'|'+[DepartmentID]+'|'+[DepartmentName]+'|'+char(10) from Table01
return @str
END 
go


--使用函数
select dbo.showData() as [returnValue01]
print dbo.showData()
go


--********************************************************************************************
--EXAMPLE 02, 功能: 查询数据库中任意表中的数据并以字符串的形式返回数据
--实现描述:
-----------------------------------------------------------------------------------------------


if exists(select * from sysobjects where xtype='FN' and lower([name])='showdata2')
drop function showData2
go


create function showData2(@tableName varchar(50))
returns varchar(8000)
as
begin
declare @x varchar(8000)
declare @clun varchar(8000)
set @x=''
set @clun='select '
if exists(select * from sysobjects where xtype='U' and lower([name])=lower(@tableName))
begin
-----------------------------------------------------------------------------------
--找出指定表对应的字段名并构建SQL查询语句
select @clun=@clun+(
case lower([name])
when 'id' then 'convert(varchar(50),['+[name]+'])'+',+''|''+' --针对int型数据进行的转换
else 'convert(varchar(255),['+[name]+']),+''|''+' --对字符型数据可以不转换,但防其它数据类型
end
)+',' from syscolumns where id=object_id(@tableName)
set @clun=left(@clun,len(@clun)-2)
set @clun=@clun+' from '+@tableName


--exec(...)
set @x=@clun
-----------------------------------------------------------------------------------
end
else
set @x='The table : '+@tableName+' is not exist in current database.'
return @x
end
go


--使用函数
print dbo.showData2('Table02')+char(10)
print dbo.showData2('Table030')+char(10)
select dbo.showData2('Table02') as [returnValue02]
select dbo.showData2('Table030') as [returnValue03]
go




--最后不用时删除释放资源
drop function showData
drop function showData2
--------------------------------------------------------------------------------------------------




--************************************************************************************************
--EXAMPLE 03, 功能: 用户自定义Split函数的功能,即用指定字符(串)去分割给定的字符串,结果以表格形式返回
--实现描述: 首先判断字符的位置可用charIndex函数来完成，然后利用substring函数按要求从某个起始位置
            开始截取合理的长度的字符串,把得到的数据保存进表格,然后更改起始位置，循环这个过程到完成;
--------------------------------------------------------------------------------------------------
--返回表值的函数:
--http://blog.csdn.net/smile2me27/archive/2004/07/08/37107.aspx
--select * from userFun(String,separateChar)


(1)，参数定义和说明:
---------------------
输出参数：@splitString varchar(8000) -- 需要Split的字符串
          @separate varchar(10)  ---分隔符


返回：@returnTable table(col_Value varchar(20))  --单个字符长度可以按照需要修改




(2)存在性检验:
-----------------
if exists(select * from sysobjects where xtype='FN' and lower([name])='usersplit')
    drop function userSplit
go






(3),函数体定义
------------------
CREATE FUNCTION userSplit(@splitString varchar(8000), @separate varchar(10))
RETURNS @returnTable table(col_Value varchar(20))   --//以表格变量的形式返回数据
AS
BEGIN
declare @thisSplitStr varchar(20)
declare @thisSepIndex int
declare @lastSepIndex int


set @lastSepIndex = 0
if Right(@splitString ,len(@separate)) <> @separate set @splitString = @splitString + @separate
set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)


while @lastSepIndex <= @thisSepIndex
begin
         set @thisSplitStr = SubString(@splitString ,@lastSepIndex,@thisSepIndex-@lastSepIndex)
         set @lastSepIndex = @thisSepIndex + 1
         set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)
         insert into @returnTable values(@thisSplitStr)
end
return
END
go




(4)，函数使用
-----------------
select * from dbo.userSplit('123,456,789,abc',',')  --//用逗号去分割前面的字符
输出结果如下
123
456
789
abc
--------------------------------------------------------------------------------------------------