青少年编程与数学 02-002 Sql Server 数据库应用 13课题、函数的编写

本课题介绍了SQL Server中函数的概念、类型和应用。函数是预定义的SQL代码块，用于数据处理并返回结果。它们分为内置函数和用户定义函数（UDFs），后者包括标量函数、内联表值函数和多语句表值函数。

课题摘要:

本课题介绍了SQL Server中函数的概念、类型和应用。函数是预定义的SQL代码块，用于数据处理并返回结果。它们分为内置函数和用户定义函数（UDFs），后者包括标量函数、内联表值函数和多语句表值函数。内置函数涵盖数学、字符串和日期时间处理等，而UDFs允许自定义以满足特定需求。函数的主要特点包括返回值、参数、确定性、用途和重用性。与存储过程相比，函数更适用于计算和数据转换，而存储过程适合执行一系列数据库操作。自定义函数可以简化复杂逻辑，提高代码可读性和可维护性。在使用自定义函数时，需要注意它们受到一系列限制，以确保确定性和性能。最后，介绍了如何修改和删除函数，以及一个计算订单总价的自定义函数示例。

---

一、函数

在 SQL Server 中，函数是一种预定义的 SQL 代码块，用于执行特定的数据处理任务并返回一个结果或一系列结果。SQL Server 提供了多种内置函数来处理各种常见任务，同时它也支持用户自定义函数以满足更具体的需求。

内置函数

内置函数是由 SQL Server 自带的一系列函数，这些函数可以处理数据类型转换、数学计算、字符串操作、日期时间处理等多种任务。例如：

• 数学函数：ABS(x)（返回 x 的绝对值）、FLOOR(x)（返回不大于 x 的最大整数）等。
• 字符串函数：LEN(str)（返回字符串长度）、SUBSTRING(str, start, length)（从字符串 str 的 start 位置开始截取 length 镕度的子字符串）等。
• 日期时间函数：GETDATE()（获取当前日期和时间）、DATEDIFF(part, startdate, enddate)（返回两个日期之间的差异）等。
• 聚合函数：SUM(column)（返回 column 列的总和）、AVG(column)（返回 column 列的平均值）等。

用户定义的函数 (User-Defined Functions, UDFs)

除了内置函数外，SQL Server 还允许用户根据需要创建自己的函数。这些函数可以是标量函数、内联表值函数 (Inline Table-Valued Function) 或者多语句表值函数 (Multi-Statement Table-Valued Function)。

• 标量函数：返回单一的标量值，如 int 或 varchar 类型。
• 内联表值函数：返回表或表表达式的引用，通常在查询中作为从句使用。
• 多语句表值函数：可以包含多个 SQL 语句，返回一个表类型的对象。

函数可以通过调用其名称并传递所需的参数来执行。函数的定义通常包括输入参数列表、函数体（即要执行的 SQL 语句）以及返回类型。

使用示例

下面是一个简单的标量函数的例子，该函数接受一个整数作为输入参数，并返回该整数的平方：

CREATE FUNCTION dbo.GetSquare (@n int)
RETURNS int
AS
BEGINRETURN (@n * @n);
END;

之后可以在查询中调用这个函数：

SELECT dbo.GetSquare(5);

这将返回 25，即 5 * 5 的结果。

通过使用 SQL Server 的内置函数或创建用户定义的函数，可以简化复杂的查询逻辑，提高代码的可读性和可维护性。

主要特点

函数的主要特点包括：

1. 返回值：函数必须返回一个值，可以是标量值（如整数、字符串等）或表类型（表值函数）。

2. 参数：函数可以定义零个或多个输入参数，这些参数用于传递数据给函数进行处理。

3. 确定性：函数的结果应该是确定的，即对于给定的输入参数，函数总是返回相同的结果。

4. 用途：函数可以在SQL语句中像内置函数一样使用，例如在SELECT、WHERE子句中。

5. 类型：SQL Server提供了多种类型的函数，包括标量函数、内联表值函数、多态表值函数和聚合函数。

6. 作用域：函数可以定义在数据库级别，也可以定义在架构级别。

7. 重用性：函数可以被数据库中的多个查询和存储过程重用，提高了代码的可维护性和重用性。

8. 优化：函数可以提高查询性能，因为它们可以被优化和预先编译。

二、内置函数

SQL Server 提供了大量的内置函数来帮助开发者处理数据。这些内置函数可以根据它们的功能进行分类。以下是 SQL Server 中一些常见的内置函数及其分类：

数学函数（Mathematical Functions）

数学函数用于执行数学运算。

• ABS(x): 返回 x 的绝对值。
• ACOS(x): 返回 x 的反余弦值。
• ASIN(x): 返回 x 的反正弦值。
• ATAN(x): 返回 x 的反正切值。
• ATN2(y, x): 返回 y/x 的角的反正切值。
• CEILING(x) / CEIL(x): 返回不小于 x 的最小整数。
• COS(x): 返回 x 的余弦值。
• COT(x): 返回 x 的余切值。
• DEGREES(x): 将弧度转换为度数。
• EXP(x): 返回 e 的 x 次方。
• FLOOR(x): 返回不大于 x 的最大整数。
• LOG(x): 返回 x 的自然对数。
• LOG10(x): 返回 x 的常用对数（基 10）。
• POWER(x, y): 返回 x 的 y 次方。
• RADIANS(x): 将度数转换为弧度。
• RAND([seed]): 返回介于 0 和 1 之间的随机浮点数。
• ROUND(x, length[, function]): 返回 x 四舍五入到 length 位数的结果。
• SIGN(x): 返回 x 的符号（-1, 0, +1）。
• SIN(x): 返回 x 的正弦值。
• SQRT(x): 返回 x 的平方根。
• TAN(x): 返回 x 的正切值。

字符串函数（String Functions）

字符串函数用于处理文本数据。

• CHARINDEX(expression, expression [,start_location]): 返回子字符串在字符串中的起始位置。
• CONCAT(string,...): 连接多个字符串。
• CONCAT_WS(separator,string,...): 带有分隔符的字符串连接。
• DIFFERENCE(expression, expression): 返回两个字符串的相似度级别。
• LEFT(string, length): 返回从字符串左边开始的指定长度的子字符串。
• LEN(string): 返回字符串的长度。
• LOWER(string): 将字符串转换为小写。
• LTRIM(string): 删除字符串左侧的空白。
• PATINDEX('pattern', expression): 返回模式在表达式中的起始位置。
• QUOTENAME(name): 返回带有转义字符的名称。
• REPLACE(string, old_string, new_string): 替换字符串中的旧子串为新子串。
• REPLICATE(string, number): 复制字符串指定次数。
• REVERSE(string): 反转字符串。
• RIGHT(string, length): 返回从字符串右边开始的指定长度的子字符串。
• RTRIM(string): 删除字符串右侧的空白。
• SPACE(number): 返回指定数量的空格组成的字符串。
• STR(number, total_length[, decimal_length]): 返回数字转换成的字符串。
• STUFF(string, start, length, replace_with): 从字符串的指定位置替换一段字符串。
• SUBSTRING(string, start, length): 返回从字符串指定位置开始的子字符串。
• UCASE(string): 将字符串转换为大写。

日期时间函数（Date and Time Functions）

日期时间函数用于处理日期和时间数据。

• DATEADD(interval, number, date): 在指定的日期上加上或减去时间间隔。
• DATEDIFF(interval, date1, date2): 返回两个日期之间的时间间隔。
• DATEPART(interval, date): 返回指定日期的日期部分。
• DATENAME(interval, date): 返回指定日期的日期部分的名称。
• DAY(date): 返回日期中的天数。
• MONTH(date): 返回日期中的月份。
• YEAR(date): 返回日期中的年份。
• GETDATE(): 返回当前的系统日期和时间。
• GETUTCDATE(): 返回当前的系统日期和时间（协调世界时）。

这只是 SQL Server 中可用的一些内置函数的概述。实际上，SQL Server 包含更多的内置函数来支持不同的需求，如位操作、加密、文件 I/O 等。对于具体的函数列表和详细文档，可以查阅 SQL Server 的官方文档。

三、自定义函数

在 SQL Server 中，自定义函数（User-Defined Functions, UDFs）是由用户创建的函数，用于扩展 SQL Server 的内置功能。自定义函数可以用于执行复杂的数据处理任务，并且可以被存储过程、触发器或其他 SQL 查询调用。SQL Server 支持几种不同类型的自定义函数：

标量函数（Scalar Functions）

标量函数返回一个单一的标量值。这类函数通常用于执行简单的计算或者基于某些条件返回一个值。例如，你可以创建一个标量函数来计算折扣价格：

CREATE FUNCTION dbo.GetDiscountedPrice (@OriginalPrice money, @DiscountRate float)
RETURNS money
AS
BEGINDECLARE @DiscountedPrice money;SET @DiscountedPrice = @OriginalPrice * (1 - @DiscountRate);RETURN @DiscountedPrice;
END;

标量函数在 SQL 查询中可以像内置函数一样被调用：

SELECT dbo.GetDiscountedPrice(100.00, 0.20) AS DiscountedPrice;

表值函数（Table-Valued Functions）

表值函数返回一个表。这种类型的函数非常有用，因为它们可以返回一个记录集，而不是单一的值。表值函数有两种主要类型：内联表值函数（Inline Table-Valued Functions, ITVs）和多语句表值函数（Multi-Statement Table-Valued Functions, MSTVs）。

内联表值函数（ITVs）

内联表值函数直接在一个 SELECT 语句中定义，并且该 SELECT 语句必须是单条语句。内联表值函数可以嵌入到其他查询中，就像一个真正的表一样。

CREATE FUNCTION dbo.GetOrdersByCustomer (@CustomerID char(5))
RETURNS TABLE
AS
RETURN (SELECT OrderID, OrderDate, ShipNameFROM Sales.OrdersWHERE CustomerID = @CustomerID
);

然后可以在查询中直接使用这个函数：

SELECT * FROM dbo.GetOrdersByCustomer('ALFKI');

多语句表值函数（MSTVs）

多语句表值函数可以包含多条 SQL 语句，并且通常涉及到创建一个临时表或表变量来保存结果。这类函数可以包含更复杂的逻辑。

CREATE FUNCTION dbo.GetOrdersWithDetails (@CustomerID char(5))
RETURNS @OrderTable TABLE(OrderID int, OrderDate datetime, ShipName nvarchar(40), ProductName nvarchar(40), UnitPrice money)
AS
BEGININSERT INTO @OrderTable(OrderID, OrderDate, ShipName, ProductName, UnitPrice)SELECT o.OrderID, o.OrderDate, o.ShipName, p.ProductName, od.UnitPriceFROM Sales.Orders oINNER JOIN Sales.OrderDetails od ON o.OrderID = od.OrderIDINNER JOIN Production.Products p ON od.ProductID = p.ProductIDWHERE o.CustomerID = @CustomerID;RETURN;
END;

调用方式类似内联表值函数：

SELECT * FROM dbo.GetOrdersWithDetails('ALFKI');

注意事项

• 在设计自定义函数时，应该考虑到性能问题。如果函数涉及复杂的查询或大量数据处理，可能会影响整体系统的性能。
• 函数不应该修改数据库的状态（如插入、更新或删除数据），除非这是函数的设计目的。
• 对于复杂的业务逻辑，考虑使用存储过程而不是函数，因为存储过程可以包含更复杂的事务控制逻辑。

通过使用自定义函数，你可以封装复杂的逻辑，使 SQL 查询更加清晰易读，并且可以重复使用相同的代码段。

四、自定义函数与存储过程的区别

函数（Function）和存储过程（Stored Procedure）都是SQL Server中用于封装SQL逻辑的数据库对象，但它们之间有一些关键的区别：

1. 返回值：

   • 函数必须返回一个单一的值或者一个表值。
   • 存储过程不返回值，或者可以返回零个、一个或多个结果集，以及一个可选的返回值（通过RETURN语句）。

2. 使用方式：

   • 函数可以在SQL语句中直接使用，例如在SELECT子句、WHERE子句或者作为条件表达式的一部分。
   • 存储过程通常需要使用EXEC或EXECUTE语句单独调用，不能直接嵌入到其他SQL语句中。

3. 参数：

   • 函数和存储过程都可以接受输入参数和输出参数，但函数的使用方式限制了它们不能像存储过程那样灵活地处理参数。

4. 结果集：

   • 函数只能返回单一的结果集（对于表值函数）或单一的标量值（对于标量函数）。
   • 存储过程可以返回多个结果集，这使得它们在需要返回复杂或多个数据集时更加有用。

5. 调用上下文：

   • 函数通常用于计算单个值或转换数据。
   • 存储过程更适合执行一系列数据库操作，如数据插入、更新、删除等。

6. 性能：

   • 函数通常会被SQL Server优化，因为它们是确定性的，这意味着对于相同的输入总是返回相同的输出。
   • 存储过程虽然也可以被优化，但它们的执行路径可能不如函数那样可预测，尤其是在涉及多个结果集或复杂的业务逻辑时。

7. 事务处理：

   • 函数不能直接包含BEGIN TRANSACTION、COMMIT或ROLLBACK语句。
   • 存储过程可以在其内部管理事务，提供更复杂的数据一致性保障。

8. 重用性：

   • 函数由于可以在SQL语句中直接使用，因此可以被视为SQL查询的一部分，易于重用。
   • 存储过程由于其独立性，也可以在不同的上下文中被重用。

9. 安全性：

   • 函数和存储过程都可以提高数据库安全性，通过封装复杂的逻辑和限制对底层数据的直接访问。

选择使用函数还是存储过程通常取决于特定的需求、预期的使用方式以及性能考虑。在某些情况下，函数和存储过程可以一起使用，以实现更高效的数据处理和查询优化。

五、自定义函数中的限制

在SQL Server中，函数的操作受到一系列限制，以确保函数的确定性和优化执行。以下是函数中的一些主要限制：

1. 副作用限制：函数不能对数据库进行修改，例如不能更新、插入或删除表中的数据。这意味着所有对数据库的写操作都是不允许的。

2. 游标使用限制：函数中不能使用FETCH语句将数据返回给客户端。只允许使用FETCH子句为局部变量赋值的INTO语句。

3. 控制流语句限制：除了TRY...CATCH语句之外，函数中不能使用控制流语句，如WHILE循环或IF条件语句，这些通常在存储过程中使用。

4. 返回值限制：标量函数只能返回单一的标量值，而不能返回结果集。表值函数可以返回一个表类型的结果集。

5. 并行执行限制：查询中的Transact-SQL用户定义函数（UDF）只能针对单个线程执行（串行执行计划），因此使用UDF会阻止并行查询处理。

6. 确定性限制：如果函数是确定性的，即对于相同的输入总是返回相同的输出，那么它们必须是架构绑定的。这意味着一旦函数被创建，它所引用的所有数据库对象（如表、视图等）都不能被修改或删除，除非先移除函数的架构绑定。

7. 资源副作用限制：函数不能有任何对函数外部有影响的操作，如发送电子邮件、修改目录等。

8. 数据类型限制：函数不能返回某些数据类型，如text、ntext、image、cursor和timestamp。

9. 执行次数限制：在查询优化过程中，函数的执行次数可能不同，这取决于优化器选择的访问路径。例如，在WHERE子句中的子查询调用的函数，其执行次数可能会因优化器选择的访问路径不同而异。

10. 不确定性函数限制：某些不确定性内置函数，如NEWID、NEWSEQUENTIALID、RAND和TEXTPTR，不能在Transact-SQL用户定义函数中使用。

遵守这些限制有助于确保函数的可预测性、性能和安全性。

六、自定义函数的修改和删除

在SQL Server中，修改和删除函数是两个不同的操作，它们都涉及到使用SQL Server的Transact-SQL（T-SQL）语句。

修改函数

如果你需要修改一个已经存在的函数，你可以使用ALTER FUNCTION语句。这允许你更改函数的定义，比如修改函数的参数、逻辑或返回类型。

ALTER FUNCTION function_name
(@param1 datatype,@param2 datatype-- 更多参数
)
RETURNS datatype
AS
BEGIN-- 新的函数体RETURN computed_value
END

请注意，ALTER FUNCTION语句会替换现有的函数定义。在修改函数时，你需要确保新的函数定义不违反任何依赖于该函数的数据库对象，比如视图或存储过程。

删除函数

如果你想要删除一个函数，可以使用DROP FUNCTION语句。这将从数据库中完全移除该函数。

DROP FUNCTION function_name;

在删除函数之前，你应该确保没有数据库对象依赖于这个函数。如果有，你可能需要先修改或删除那些依赖于该函数的对象。

注意事项

• 权限：确保你有足够的权限来修改或删除函数。通常，你需要有对该数据库的ALTER或DROP权限。
• 依赖性：在修改或删除函数之前，检查是否有其他数据库对象（如视图、触发器或其他存储过程）依赖于它。否则，修改或删除函数可能会破坏这些对象的功能。
• 备份：在删除函数之前，考虑是否需要备份函数的代码，特别是如果你不确定未来是否还需要它。
• 测试：在生产环境中修改或删除函数之前，建议在测试环境中进行测试，以确保修改或删除操作不会对数据库的其他部分产生不良影响。

使用ALTER FUNCTION和DROP FUNCTION语句可以有效地管理数据库中的函数，保持数据库的整洁和高效。

七、应用示例

下面通过一个具体的例子来展示如何编写和使用 SQL Server 中的自定义函数。我们将创建一个数据库应用程序，其中包含一个自定义函数，该函数用于计算某个客户的订单总价，并且能够处理订单中可能出现的折扣情况。

假设我们有一个数据库，其中包含以下表格：

• Customers: 包含客户信息。
• Orders: 包含订单信息。
• OrderDetails: 包含订单详情（每个订单项）。
• Products: 包含产品信息。

步骤 1: 创建必要的表结构

首先，我们需要创建这些表格的基本结构（这里仅为示例，实际应用中应包含更多字段）：

CREATE TABLE Customers (CustomerID int IDENTITY PRIMARY KEY,CustomerName nvarchar(100),ContactName nvarchar(50),Country nvarchar(50)
);CREATE TABLE Orders (OrderID int IDENTITY PRIMARY KEY,CustomerID int,OrderDate datetime,FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);CREATE TABLE OrderDetails (OrderDetailID int IDENTITY PRIMARY KEY,OrderID int,ProductID int,Quantity int,UnitPrice money,FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);CREATE TABLE Products (ProductID int IDENTITY PRIMARY KEY,ProductName nvarchar(100),UnitPrice money
);

步骤 2: 插入一些测试数据

接下来，我们需要向这些表中插入一些测试数据：

INSERT INTO Customers (CustomerName, ContactName, Country)
VALUES ('Company A', 'John Doe', 'USA');INSERT INTO Orders (CustomerID, OrderDate)
VALUES (1, '2024-01-15');INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 1, 5, 100.00);INSERT INTO Products (ProductName, UnitPrice)
VALUES ('Product X', 100.00);

步骤 3: 创建自定义函数

现在，我们将创建一个自定义函数来计算订单总价，并考虑到可能存在的折扣。假设我们的折扣规则是订单金额超过 $500 时，可以获得 10% 的折扣。

CREATE FUNCTION dbo.GetTotalOrderAmount (@OrderID int)
RETURNS money
AS
BEGINDECLARE @TotalAmount money;-- 计算订单总价SELECT @TotalAmount = SUM(od.Quantity * od.UnitPrice)FROM OrderDetails odWHERE od.OrderID = @OrderID;-- 应用折扣IF @TotalAmount > 500SET @TotalAmount = @TotalAmount * 0.9; -- 应用 10% 折扣RETURN @TotalAmount;
END;

步骤 4: 使用自定义函数

最后，我们可以编写一个查询来使用上面创建的自定义函数：

SELECT o.OrderID, o.OrderDate, c.CustomerName, dbo.GetTotalOrderAmount(o.OrderID) AS TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

这个查询将返回每个订单的订单号、下单日期、客户名称以及订单的总价（已经应用了折扣）。

以上就是一个完整的自定义函数的应用示例。通过这种方式，我们可以更好地组织和复用代码，并且保持数据库逻辑的清晰。