在SQL Server中使用用户定义的函数

在本教程中，我们将讨论SQL Server中的用户定义函数。更具体地说，我们将讨论标量函数和表值函数。

在编写代码时，必须力求遵循DRY原则（Don't Repeat Yourself）。避免代码重复的一个方法是将大块的代码放在函数中，并根据需要调用它们。

SQL中函数的概念与Python等其他编程语言相似。主要的区别在于它们的实现方式。在SQL中，根据返回的数据，有两种主要的用户定义的函数类型。

标量函数：这些类型的函数返回一个单一的值，即float, int, varchar, datetime，等等。
表值型函数：这些函数返回表。

前提条件

对SQL的基本了解。
一个有数据库的SQL服务器。
连接到数据库的SQL Server Management Studio。

创建函数

标量函数

下面是一个简单函数的定义。它接收两个数字并返回它们的总和。由于这个函数返回一个数字，所以它是一个标量函数。

    CREATE FUNCTION scalar_func
    (
	    @a AS INT, -- parameter a
	    @b AS INT -- parameter b
    )
    RETURNS INT -- return type
    AS
    BEGIN
	    RETURN @a + @b -- return statement
    END;

我们使用Create function 命令来定义函数。它的后面是函数的名称。在上面的例子中，函数的名称是scalar_func 。
我们需要按照以下格式声明函数的参数。

@VariableName AS Data Type

在上面的例子中，我们定义了两个整数参数a 和b 。

结果的返回类型必须在参数的定义下面提到。在上面的例子中，我们要返回的是一个整数的总和。
在返回语句之后，我们创建了一个BEGIN ... END 块，其中包含了我们函数的逻辑。虽然在这个例子中，我们有一个单一的返回语句，但我们不需要BEGIN ... END 块。

表值函数

在创建一个表值函数之前，我们将创建一个简单的表。

    -- Creating new table
    CREATE TABLE TEST(
	    num1 INT,
	    num2 INT
    );
    
    -- Inserting values into new table
    INSERT INTO TEST
    VALUES
    (1,2),
    (2,3),
	(4,5);

该表包含2列。我们将创建一个函数，返回一个带有额外列的新表。这个额外的列将包含列num1 和列num2 的数字之和。

    CREATE FUNCTION table_valued_func()
    RETURNS TABLE
    AS
    RETURN
	    -- statement to calculate sum
	    SELECT num1 , num2, num1 + num2 AS 'SUM'
	    FROM TEST;

上面的函数没有接受任何参数。
该SQL语句简单地计算了总和，并将其存储在一个名为SUM 的新列中。

在语句中使用函数

标量函数

    -- invoking previously created scalar function
    SELECT dbo.scalar_func(1,2);

在语句中使用函数时，我们需要在函数前加上它所关联的数据库模式。在Microsoft SQL Server中默认的模式是dbo 。如果没有提到数据库模式，SQL将给出一个错误。

以表为值的函数

由于函数返回一个表，我们将需要选择我们感兴趣的列。

    -- invoking previously created table valued function
    SELECT * FROM dbo.table_valued_func();

和标量函数一样，我们也需要提到数据库模式。

更新/删除函数

更新/删除标量函数和表值函数的语法是相同的。

更新

我们将更新我们的表值函数，在现有的总和上增加10，并将列的名称改为New_Sum 。

    ALTER FUNCTION table_valued_func()
    RETURNS TABLE
    AS
    RETURN
	    -- updating statement to add 10 to sum
	    SELECT num1 , num2, num1 + num2 + 10 AS 'NEW_SUM'
	    FROM TEST;

Alter 关键字被用来更新该函数。

下降

    -- dropping previously created scalar function
    DROP FUNCTION dbo.scalar_func;
    -- dropping previously created tabular function
    DROP FUNCTION dbo.table_valued_func;

注意：不要在函数名称后面加括号。

在函数中使用变量和条件语句

变量

下面是声明和初始化变量的语法。

    -- declaring integer variable
    DECLARE @result AS INT;
    -- initializing created varaible
    SET @result = @a + @b;

DECLARE 关键字用于创建一个变量，SET 关键字用于初始化一个变量。

下面我们有一个使用变量的标量函数的例子。

    CREATE FUNCTION scalar_func
    (
	    @a AS INT,
	    @b AS INT
    )
    RETURNS INT
    AS
    BEGIN
	    -- using variables inside function
	    DECLARE @result AS INT
	    SET @result = @a + @b
	    RETURN @a + @b
    END;

IF...ELSE语句

IF...ELSE 语句的语法与Python或C++中的IF...ELSE 语句相似。

    DECLARE @num AS INT;
    SET @num = 4;
    -- if condition
    IF @num % 2 = 0    
	    BEGIN
		    SELECT 'Number is Even'
	    END
    -- else condition
    ELSE
	    BEGIN
		    SELECT 'Number is Odd'
	    END

上面这段代码检查变量num 中的值是偶数还是奇数。根据该值，执行IF 或ELSE 块。

下面列出的是一个使用IF...ELSE Block的函数。

    CREATE FUNCTION is_even(@num AS INT)
    RETURNS BIT
    AS
    BEGIN
	    DECLARE @result AS BIT
	    -- set variable to 1 if number is even
	    IF @num % 2 = 0
		    SET @result = 1
	    -- set variable to 0 if number is odd
	    ELSE
		    SET @result = 0
	    RETURN @result
    END;

案例语句

当你要处理多个if语句时，最好使用case语句。它们使你的代码更容易阅读。下面是case语句的一般语法。

	CASE
	    WHEN  condition1  THEN  result1  
		WHEN  condition2  THEN  result2  
		.
		.
		.  
		ELSE  result  
	END

像switch case一样，所有的case都会被检查，如果多个case都被满足，相应的代码块将被执行。

下面我们有一个使用case语句的函数。

    CREATE FUNCTION is_greater
	( 
		@a AS INT,
		@b AS INT
	)
	RETURNS VARCHAR(30)
	AS
	BEGIN
	RETURN( 'A is' + 
	CASE
		-- Case 1
		WHEN @a > @b THEN 'Greater than'
		-- Case 2
		WHEN @a < @b THEN 'Smaller than'
		ELSE 'Equal to'
	END
	+ 'B')
	END;

它对两个整数进行比较，并根据比较结果返回一个字符串。

总结

正如我在上面提到的，在编写SQL语句时要尽量遵循DRY原则。当你看到同一段代码被用在多个语句中时，可以考虑把它放在一个函数中。函数可以使你的语句看起来更简洁，更短。

如何在SQL Server中使用用户定义的函数

在SQL Server中使用用户定义的函数

目录

前提条件

创建函数

标量函数

表值函数

在语句中使用函数

标量函数

以表为值的函数

更新/删除函数

更新

下降

在函数中使用变量和条件语句

变量

IF...ELSE语句

案例语句

总结