SQL Server 函数编程详解

在 SQL Server 中，除了存储过程以外，函数编程也是一种非常有用的工具。函数可以让我们在查询中执行复杂的计算或返回表数据，提升代码的复用性和可维护性。SQL Server 中的函数分为两类：标量函数和表值函数。

本文将详细介绍 SQL Server 中函数编程的概念及其使用方法，提供大量示例代码，涵盖函数的所有属性和方法。

1. 什么是函数？

SQL Server 中的函数是预编译的代码块，接受输入参数并返回一个值或表。函数与存储过程的不同之处在于：

• 函数必须返回一个值（标量函数）或一个表（表值函数）。
• 函数不能修改数据库中的数据，只能查询数据。
• 函数可以在 SQL 查询语句中使用，例如 SELECT 或 WHERE 子句中。

2. 标量函数

标量函数是返回单一值的函数，返回的值可以是任何数据类型，如整数、字符或日期等。标量函数在 SQL 语句中可以直接使用，常用于对输入参数进行计算或格式化操作。

创建标量函数的语法

CREATE FUNCTION FunctionName (@parameter datatype, ...)
RETURNS return_datatype
AS
BEGIN
    -- SQL 逻辑
    RETURN result_value;
END;

• FunctionName：函数的名称。
• @parameter：输入参数，函数可以接收多个输入参数。
• return_datatype：函数返回的数据类型。

示例：创建一个计算年薪的标量函数

这个函数接受一个月薪，返回年薪。

CREATE FUNCTION CalculateAnnualSalary (@MonthlySalary DECIMAL(18, 2))
RETURNS DECIMAL(18, 2)
AS
BEGIN
    RETURN @MonthlySalary * 12;
END;

调用标量函数

可以在查询中直接调用标量函数，例如 SELECT 或 WHERE 语句中：

-- 查询员工的年薪
SELECT EmployeeID, Name, dbo.CalculateAnnualSalary(Salary) AS AnnualSalary
FROM Employees;

在这个查询中，dbo.CalculateAnnualSalary(Salary) 使用了 Salary 列的值作为输入，返回每个员工的年薪。

3. 表值函数

表值函数返回一个表，表值函数分为两类：

• 内联表值函数：返回简单的表查询结果。
• 多语句表值函数：返回多行 SQL 逻辑处理后的结果表。

内联表值函数

内联表值函数类似于视图，但具有更高的灵活性，可以接受输入参数并返回查询结果。

创建内联表值函数的语法

CREATE FUNCTION FunctionName (@parameter datatype, ...)
RETURNS TABLE
AS
RETURN
(
    -- 返回表的 SQL 查询
    SELECT columns FROM table WHERE condition
);

示例：创建一个根据部门 ID 获取员工的表值函数

CREATE FUNCTION GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID
);

调用表值函数

可以像查询普通表一样查询表值函数的结果：

-- 查询部门 ID 为 1 的所有员工
SELECT * FROM dbo.GetEmployeesByDepartment(1);

在这个查询中，GetEmployeesByDepartment(1) 返回一个包含所有属于部门 ID 为 1 的员工的表。

4. 多语句表值函数

多语句表值函数用于执行多步 SQL 逻辑，并将结果返回为表。与内联表值函数不同，多语句表值函数需要显式地定义返回的表结构。

创建多语句表值函数的语法

CREATE FUNCTION FunctionName (@parameter datatype, ...)
RETURNS @ReturnTable TABLE (column1 datatype, column2 datatype, ...)
AS
BEGIN
    -- 插入数据到返回表
    INSERT INTO @ReturnTable
    SELECT columns FROM table WHERE condition;
  
    RETURN;
END;

• @ReturnTable：返回的表的结构。
• INSERT INTO：将查询结果插入到返回表中。

示例：创建一个返回员工及其所在部门名称的多语句表值函数

CREATE FUNCTION GetEmployeeDetails ()
RETURNS @EmployeeDetails TABLE
(
    EmployeeID INT,
    EmployeeName NVARCHAR(100),
    DepartmentName NVARCHAR(100)
)
AS
BEGIN
    -- 将查询结果插入返回表
    INSERT INTO @EmployeeDetails
    SELECT e.EmployeeID, e.Name, d.DepartmentName
    FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID;
  
    RETURN;
END;

调用多语句表值函数

-- 查询所有员工及其所在部门的详细信息
SELECT * FROM dbo.GetEmployeeDetails();

在这个示例中，函数返回一个包含员工 ID、姓名及其部门名称的表。

5. 修改与删除函数

与存储过程类似，函数创建后也可以进行修改或删除。

修改函数

使用 ALTER FUNCTION 修改函数的定义。

ALTER FUNCTION CalculateAnnualSalary (@MonthlySalary DECIMAL(18, 2))
RETURNS DECIMAL(18, 2)
AS
BEGIN
    RETURN @MonthlySalary * 12 + 500;  -- 修改为年终奖金 500 元
END;

删除函数

使用 DROP FUNCTION 删除函数。

DROP FUNCTION CalculateAnnualSalary;

6. 函数的实际应用场景

函数在 SQL Server 中有广泛的应用，特别是当你需要执行特定的逻辑计算并返回结果时，函数能够提高代码的复用性和简洁性。

场景 1：根据员工的工龄计算年假天数

CREATE FUNCTION CalculateAnnualLeave (@YearsWorked INT)
RETURNS INT
AS
BEGIN
    DECLARE @LeaveDays INT;

    IF @YearsWorked <= 1
        SET @LeaveDays = 5;
    ELSE IF @YearsWorked <= 5
        SET @LeaveDays = 10;
    ELSE
        SET @LeaveDays = 15;

    RETURN @LeaveDays;
END;

调用示例：

-- 查询员工的年假天数
SELECT EmployeeID, Name, dbo.CalculateAnnualLeave(YEAR(GETDATE()) - HireDate) AS AnnualLeave
FROM Employees;

场景 2：获取指定日期范围内的员工考勤记录

CREATE FUNCTION GetAttendanceByDateRange (@StartDate DATE, @EndDate DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Attendance WHERE AttendanceDate BETWEEN @StartDate AND @EndDate
);

调用示例：

-- 查询 2024 年 1 月到 2024 年 3 月的考勤记录
SELECT * FROM dbo.GetAttendanceByDateRange('2024-01-01', '2024-03-31');

7. 函数与存储过程的区别

8. 总结

SQL Server 中的函数是开发者用来执行特定逻辑计算的强大工具。本文详细介绍了两种函数类型：标量函数和表值函数，以及它们的实际应用场景。通过合理地使用函数，能够显著提高数据库查询的性能和代码的复用性。

要点总结：

• 标量函数：用于返回单个值，适合简单的计算操作。
• 表值函数：用于返回表结果，分为内联表值函数和多语句表值函数。
• 函数可以嵌入到 SQL 查询中使用，使代码更加简洁。

在实际项目中，合理使用函数可以使数据库的查询和数据处理更加高效，同时减少冗余代码。