简介

和C语言、Java、Python等一样，数据库也可以自定义函数，同样可以传参，拥有返回值。在工作中可能会遇到一些业务，需要反复执行某些sql，可以自定义一个函数，非常的方便。当然，还有很多业务可以用自定义函数快速完成。

讲解

函数分类

  1. 标量函数
    标量函数返回一个确定类型的标量值，其返回值类型为除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE类型外的其它数据类型。函数体语句定义在BEGIN-END语句内。在 RETURNS 子句中定义返回值的数据类型，并且函数的最后一条语句必须为Return语句。
  2. 内联表值函数
    内联表值型函数以表的形式返回一个返回值，即它返回的是一个表。内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表是由一个位于 RETURN 子句中的 SELECT 命令从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。
  3. 多语句表值函数
    多语句表值函数可以看作标量函数和内联表值函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用 BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值函数的不足。

创建语句

--标量函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型，注意是 RETURNS
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;

--内联表值函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS table    --返回一个表（类似视图，无须指定列）
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
RETURN  一条查询SQL语句

--多语句表值函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS @Table_Variable_Name table (Column_1 culumn_type,Column_2 culumn_type)    
--RETURNS @表变量 table 表的定义(即列的定义和约束)
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
     函数体(即 Transact-SQL 语句)
 RETURN

修改语句

个人没有遇到相关需要修改函数的业务场景。
只需把创建语句的 create 改为 alter 即可。

删除语句

drop function 函数名

实例

--示例表test
CREATE TABLE test(
    column_one VARCHAR(128) null,
    column_two VARCHAR(128) null,
    column_three VARCHAR(128) NULL
)

--插入数据
INSERT INTO test VALUES('column_one_1','column_two_1','column_three_1');
INSERT INTO test VALUES('column_one_2','column_two_2','column_three_2');
INSERT INTO test VALUES('column_one_3','column_two_3','column_three_3');

--查询一下数据
SELECT * FROM test;

--创建标量函数scalar_valued_functions
CREATE FUNCTION scalar_valued_functions(@in varchar(128))
    RETURNS VARCHAR(128)
AS
    BEGIN
        DECLARE @out VARCHAR(128)
        SELECT @out=column_two FROM test WHERE column_one=@in
        RETURN @out
    END

--调用函数，注意加上模式名(dbo)，查询结果（column_two_2）
SELECT dbo.scalar_valued_functions('column_one_2');

--删除标量函数scalar_valued_functions。
DROP FUNCTION dbo.scalar_valued_functions;

--创建多参数标量函数，
CREATE FUNCTION scalar_valued_functions(@in varchar(128), @var2 VARCHAR(128))
    RETURNS VARCHAR(128)
AS
    BEGIN
        DECLARE @out VARCHAR(128)
        SELECT @out=column_two,@var2=column_three FROM test WHERE column_one=@in
        RETURN @out+@var2
    END

--调用多参数标量函数scalar_valued_functions，查询结果(column_two_2column_three_2)
SELECT dbo.scalar_valued_functions('column_one_2','');

--创建内联表值函数inline_table_values_function
CREATE FUNCTION inline_table_values_function(@var VARCHAR(128))
    RETURNS TABLE
AS
    RETURN SELECT column_two,column_three FROM test WHERE column_one LIKE @var

--调用内联表值函数，查询结果(test表第二列和第三列)
SELECT * FROM inline_table_values_function('column_one%');

--创建多语句表值函数multi_statement_table_value_function
CREATE FUNCTION multi_statement_table_value_function(@var VARCHAR(128))
    RETURNS @table_test TABLE(first_column VARCHAR(128),second_column VARCHAR(128))
AS
    BEGIN
    INSERT INTO @table_test(first_column,second_column) SELECT column_two,column_three FROM test WHERE column_one LIKE @var
    RETURN
    END

--调用多语句表值函数，查询结果(test表第二列和第三列)
SELECT * FROM multi_statement_table_value_function('column_one%');

结束语

注意

函数名之后的是 RETURNS
调用函数加上模式名

查询自定义函数的函数体

exec sp_helptext 函数名