自定义函数
自定义函数是由一个或多个 Transact-SQL 语句组成的子程序，目的是封装公共部分代码以便重复使用。自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回。
在数据量庞大的时候，需谨慎使用自定义函数，因为其在效率上有所欠缺。
自定义函数的类型
标量函数：返回一个标量值
表格值函数{内联表格值函数、多表格值函数}：返回行集（即返回多个值）
标量函数语法
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
GO
例子
-- 从身份证号获取年龄
CREATE FUNCTION fnGetAgeByIdCardNo (
@IDCardNo VARCHAR (20)
)
RETURNS INT
AS
BEGIN
IF LEN(@IDCardNo)<>18 AND LEN(@IDCardNo)<>15
BEGIN
RETURN NULL
END
DECLARE @dateofbirth VARCHAR(20)
IF LEN(@IDCardNo)=18
SET @dateofbirth=SUBSTRING(@IDCardNo,7,4)+'-'+SUBSTRING(@IDCardNo,11,2)+'-'+SUBSTRING(@IDCardNo,13,2)
ELSE
SET @dateofbirth=SUBSTRING(@IDCardNo,7,2)+'-'+SUBSTRING(@IDCardNo,9,2)+'-'+SUBSTRING(@IDCardNo,11,2)
RETURN DATEDIFF(YEAR,CONVERT(DATETIME,@dateofbirth), GETDATE())
END
GO
-- 使用
SELECT dbo.GetAgeFromIdCardNo('441823199507070000')
表值函数语法
内联表值函数：
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO
例子
CREATE FUNCTION fnGetUserInfoById(@sId varchar(36))
RETURNS TABLE
AS
RETURN (SELECT
UserId,
RealName,
Email,
Mobile
FROM [User]
WHERE UserId = @sId)
多语句表值函数：
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable here
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>,
<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
RETURN
END
GO
例子
-- 从第一个汉字开始分割字符串
CREATE FUNCTION fnSplitNchinese
(
@str_one NVARCHAR(100)
)
RETURNS @result TABLE (colone NVARCHAR(20),coltwo NVARCHAR(20))
AS
BEGIN
INSERT @result
SELECT
LEFT(@str_one, PATINDEX('%[^_@0-9a-z]%', @str_one) - 1),
RIGHT(@str_one, LEN(@str_one) - PATINDEX('%[^_@0-9a-z]%', @str_one) + 1)
RETURN
END
GO
-- 使用
SELECT * FROM fnSplitNchinese('Chinese中国rew城')
-- 运行结果
-- colone coltwo
-- Chinese 中国rew城
常见自定义函数整理
从身份证号获取年龄：
CREATE FUNCTION fnGetAgeByIdCardNo (
@IDCardNo VARCHAR (20)
)
RETURNS INT
AS
BEGIN
IF LEN(@IDCardNo)<>18 AND LEN(@IDCardNo)<>15
BEGIN
RETURN NULL
END
DECLARE @dateofbirth VARCHAR(20)
IF LEN(@IDCardNo)=18
SET @dateofbirth=SUBSTRING(@IDCardNo,7,4)+'-'+SUBSTRING(@IDCardNo,11,2)+'-'+SUBSTRING(@IDCardNo,13,2)
ELSE
SET @dateofbirth=SUBSTRING(@IDCardNo,7,2)+'-'+SUBSTRING(@IDCardNo,9,2)+'-'+SUBSTRING(@IDCardNo,11,2)
RETURN DATEDIFF(YEAR,CONVERT(DATETIME,@dateofbirth), GETDATE())
END
GO
从身份证号获取性别：
-- 18位身份证号是看第17位，奇数为男，偶数为女。
-- 15位身份证号是看第15位，同理，也是奇数为男，偶数为女。
-- 1-男
-- 2-女
-- 0-未知
CREATE FUNCTION fnGetSexByIdCardNo
(
@sIdCardNo VARCHAR(18)
)
RETURNS INT
AS
BEGIN
DECLARE @iSex INT
IF (LEN(@sIdCardNo)=18 AND ISNUMERIC(SUBSTRING(@sIdCardNo,17,1))=1 )
SET @iSex = (CASE
WHEN SUBSTRING(@sIdCardNo, 17, 1) IN (1, 3, 5, 7, 9) THEN 1
WHEN SUBSTRING(@sIdCardNo, 17, 1) IN (2, 4, 6, 8, 0) THEN 2
ELSE 0
END) ELSE IF (LEN(@sIdCardNo) = 15 AND ISNUMERIC(SUBSTRING(@sIdCardNo, 15, 1)) = 1) SET @iSex = (CASE
WHEN SUBSTRING(@sIdCardNo, 15, 1) IN (1, 3, 5, 7, 9) THEN 1
WHEN SUBSTRING(@sIdCardNo, 15, 1) IN (2, 4, 6, 8, 0) THEN 2
ELSE 0
END) ELSE SET @iSex = 0
RETURN (@iSex)
END
GO
从身份证号获取省份
CREATE FUNCTION fnGetCityByIdCardNo (@sIdCardNo VARCHAR(18))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @sCity VARCHAR(1000)
SET @sCity = '____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,西藏__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,'
SELECT
@sCity = REPLACE(@sCity, ' ', '');
SET @sIdCardNo = UPPER(@sIdCardNo)
IF (LEN(@sIdCardNo) <> 18 OR PATINDEX('%[^0-9x]%', @sIdCardNo) > 0) RETURN '这不是合法的身份证'
IF SUBSTRING(@sCity, CAST(LEFT(@sIdCardNo, 2) AS INT) * 5 + 1, 4) = '' RETURN '这身份证的地区码不存在'
RETURN '您的户籍是：' + REPLACE(SUBSTRING(@sCity, CAST(LEFT(@sIdCardNo, 2) AS INT) * 5 + 1, 4), '_', '')
END
