MSSQL自定义函数

自定义函数
自定义函数是由一个或多个 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