SQL Server数据库自定义函数

目录

一、序言

二、介绍

1、标量函数

2、表格值函数（内联表格值函数和多语句表格值函数）

1.内联表格值函数和多语句表格值函数区别：

三、语法

1、标量函数

2、表值函数

内联表值函数（ITVF）的语法：

多行表值函数（MTVF）的语法：

四、具体使用

1、标量函数

2、表值函数

（1）、内联表值函数

（2）、多行表值函数

四、总结

---

一、序言

数据库中的函数分为内置函数和自定义函数。我们在日常的数据库使用中，我们经常会用到数据库中原有的内置函数，如聚合函数，数值函数，日期时间函数，字符串函数等。自定义函数就是用户根据自己的需求，在数据库中自定义的函数。这些函数可以被用于执行特定的计算、操作和数据处理任务，以满足特定的业务需求。

---

二、介绍

用户自定义函数的类型为标量函数和表值函数

1、标量函数

  标量函数是指用户根据自己的需求和逻辑编写的自定义函数，用于执行特定的计算或操作，并返回一个单一的值作为结果。

  与数据库中内置的标量函数类似，SQL自定义函数的标量函数也接受一个或多个参数，并返回一个单一的结果。不同之处在于，自定义函数是用户根据自己的需求编写的，可以实现更复杂的逻辑和操作。

  SQL自定义函数的标量函数可以用于各种操作，例如计算、转换、过滤和聚合数据。它们可以在SELECT语句的列列表中使用，也可以在WHERE或HAVING子句中用作条件。

2、表格值函数（内联表格值函数和多语句表格值函数）

  表值函数它可以接受一个或多个参数，并返回一个表作为结果。表值函数可以用于查询中的各种操作，例如产生结果集、过滤数据、联接表等。

  表值函数可以在FROM子句中使用，作为一个表的源。它可以像表一样被查询，可以与其他表进行联接、过滤和排序等操作。表值函数的返回结果可以被SELECT语句的其他部分使用。

  表值函数可以返回单个列或多个列，每个列可以有不同的数据类型。它可以根据特定的参数值和逻辑来生成结果集，可以是静态的，也可以是基于输入参数的动态的。

1.内联表格值函数和多语句表格值函数区别：

  (1)、内联表值函数是一种表达式，它在查询中的位置类似于一个表。它的定义是一个单一的 SELECT 语句，返回一个表格结果集。内联函数在查询执行期间被展开，并且可以直接与其他表进行关联、筛选等操作。由于其内联性质，内联函数通常执行速度较快。

  (2)、多语句表值函数是由多个 SQL 语句组成的代码块，通过 BEGIN 和 END 关键字定义。在函数的主体中，可以包含多个 SELECT 语句及其他 T-SQL 语句，可以进行逻辑判断、循环等复杂的处理。多语句函数的返回结果是通过在函数中定义的表变量来保存，并在最后通过 SELECT 语句返回。由于其包含了多个语句，多语句函数可能会有较复杂的逻辑和较长的执行时间。

  (3)、总的来说，内联表值函数适合于简单的查询和计算，且对性能要求较高。多语句表值函数通常用于需要复杂逻辑和多步骤计算的场景，虽然其定义和处理可能更加复杂，但可以提供更大的灵活性和功能。

三、语法

1、标量函数

  标量函数（Scalar Function）的语法如下：

CREATE FUNCTION <函数名称>
(
    <参数1> <数据类型>,
    <参数2> <数据类型>,
    ...
)
RETURNS <返回值数据类型>
AS
BEGIN
    DECLARE <变量1> <数据类型>;
    DECLARE <变量2> <数据类型>;
    ...
    
    <函数体逻辑>
    
    RETURN <返回值>;
END

说明：

• <函数名称>：自定义函数的名称。
• <参数1> <数据类型>：函数的输入参数及其数据类型。
• <返回值数据类型>：函数返回值的数据类型。
• <变量1> <数据类型>：函数内部可用的变量及其数据类型。
• <函数体逻辑>：函数的计算逻辑，可以包含任意有效的T-SQL语句。在函数体中，可以使用DECLARE语句声明变量，并使用SET语句给变量赋值。
• <返回值>：使用RETURN关键字返回函数的计算结果。

 需要注意的是，标量函数只能返回一个值，因此在函数体逻辑中需要确保最终计算出的结果可以通过RETURN关键字进行返回。

2、表值函数

自定义函数表值函数分为内联表值函数（Inline Table-Valued Function，简称ITVF）和多行表值函数（Multi-Statement Table-Valued Function，简称MTVF）。它们的语法有一些不同。、

1. 内联表值函数（ITVF）的语法：

CREATE FUNCTION <函数名称> 
(
    <参数1> <数据类型>,
    <参数2> <数据类型>,
    ...
)
RETURNS TABLE
AS
RETURN
(
    <查询语句>
)

说明：

• <函数名称>：自定义函数的名称。
• <参数1> <数据类型>：函数的输入参数及其数据类型。
• <查询语句>：函数的查询逻辑，可以包含任意有效的SELECT语句。

1. 多行表值函数（MTVF）的语法：

CREATE FUNCTION <函数名称> 
(
    <参数1> <数据类型>,
    <参数2> <数据类型>,
    ...
)
RETURNS @表变量 TABLE
(
    <列1> <数据类型>,
    <列2> <数据类型>,
    ...
)
AS
BEGIN
    <函数体逻辑>
    RETURN
END

说明：

• <函数名称>：自定义函数的名称。
• <参数1> <数据类型>：函数的输入参数及其数据类型。
• <列1> <数据类型>：函数返回的表变量的列名及其数据类型。
• <函数体逻辑>：函数的查询逻辑，可以包含任意有效的T-SQL语句。在函数体中，可以使用INSERT INTO语句将数据插入到表变量中。

需要注意的是，内联表值函数（ITVF）使用RETURN关键字返回查询结果集，而多行表值函数（MTVF）使用INSERT INTO语句将数据插入到表变量中。此外，多行表值函数（MTVF）还需要使用BEGIN和END关键字将函数体逻辑包裹起来。 

四、具体使用

自定义函数的使用需要根据具体情况不同需求编写不同的代码，我会在下面讲述几个例子。

1、标量函数

（1）、第一步，要先建一个测试表

--建表
IF OBJECT_ID('学生表', 'U') IS NOT NULL--判断是否已有学生表，有的话删除，没有的话新建。
    DROP TABLE 学生表;
ELSE
CREATE TABLE 学生表 (
    学号 INT PRIMARY KEY,
    姓名 NVARCHAR(50),
    年龄 INT,
    性别 NVARCHAR(10),
    成绩 INT
);

--插入测试数据
INSERT INTO 学生表 (学号, 姓名, 年龄, 性别, 成绩)
VALUES (1, '张三', 20, '男', 80),
       (2, '李四', 22, '男', 90),
       (3, '王五', 21, '女', 85),
       (4, '赵六', 19, '男', 70),
       (5, '刘七', 23, '女', 95);

（2）、按照之前说过的语法创建一个标量函数

CREATE FUNCTION 获取男生平均成绩 (@chengji AS DECIMAL(4, 1))--先定义一个参数@chengji，这里表示要去平均值的字段。
RETURNS FLOAT--表示要返回的数值类型
AS
BEGIN
    DECLARE @平均成绩 FLOAT;--定义一个变量平均成绩，用来存放结果
    
    SELECT @平均成绩 = AVG(@chengji);--这里利用AVG将字段取平均数，然后赋值给变量@平均成绩
    
    RETURN @平均成绩;--输出结果
END;

这个例子是一个很简单的例子，用来求字段的平均值，因为这里我只用来求分数的平均值，所以@chengji变量的数据类型我设置的是小数类型，这里只是举个例子，用于学习，在实际运用中需要按需求编写语句。函数参数可以定义多个，实现不同的效果，活学活用。

2、表值函数

表值函数分为内联表值函数和多行表值函数

（1）、内联表值函数

首先，创建一个测试表格Orders，包含OrderID、OrderDate和TotalAmount三个列。

--创建表格
CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);
--插入一些数据
INSERT INTO Orders (OrderID, OrderDate, TotalAmount)
VALUES (1, '2022-01-01', 100.00),
       (2, '2022-01-02', 200.00),
       (3, '2022-01-03', 150.00),
       (4, '2022-01-04', 300.00),
       (5, '2022-01-05', 250.00);

接下来，创建一个内联表值函数GetOrdersByDateRange，接收两个日期参数@StartDate和@EndDate，返回指定日期范围内的订单信息。

CREATE FUNCTION GetOrdersByDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate
);

最后，使用内联表值函数GetOrdersByDateRange查询指定日期范围内的订单信息：

SELECT *
FROM GetOrdersByDateRange('2022-01-02', '2022-01-04');

查看返回结果如下：

 

可以看到 这个内联表值函数通过接收日期范围参数并在内部查询中使用它们来过滤订单数据，然后将结果作为表返回。

（2）、多行表值函数

首先，我们创建一个测试表Employees，用于存储员工信息，并插入测试数据：

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'IT'),
       (2, 'Jane', 'Smith', 'HR'),
       (3, 'Michael', 'Johnson', 'Sales'),
       (4, 'Emily', 'Williams', 'Marketing'),
       (5, 'David', 'Brown', 'IT');

接下来，我们创建一个多行表值函数GetEmployeesByDepartment，该函数接受一个部门名称作为参数，并返回该部门下的所有员工信息。

CREATE FUNCTION GetEmployeesByDepartment (@Department VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @Department
);

最后，我们可以使用该函数来查询特定部门的员工信息。：

SELECT * FROM GetEmployeesByDepartment('IT');

可以看到，我们通过这个函数获取了所有IT人员的信息。

四、总结

这里的案例只是示范，具体还要结合线下业务和需求，定义不同变量和内嵌不同语句。也可以直接使用内置的函数，灵活运用不断优化。