欢迎来到雲闪世界。SQL 用户定义函数 (UDF) 是一项重要但经常被忽视的功能。尽管有许多在线资源解释了 SQL UDF 的语法，但大多数都无法指导用户如何在实际场景中有效地应用此工具。因此，我撰写了这篇文章，通过讨论何时以及如何使用 SQL UDF、探索其语法背后的底层逻辑以及提供实际用例来缩小差距。此外，本文将阐明各种类型的 SQL UDF，并解决 UDF 与存储过程之间的混淆，存储过程是 SQL 中的另一项重要技术。鉴于 UDF 的语法在不同的数据库系统中可能有所不同，我将重点介绍 SQL Server UDF 以进行演示，尽管我通常更喜欢在工作中使用 MySQL。这种选择的原因将在本文的后面部分中揭示。
什么是 UDF？何时应使用 UDF？
SQL 中的用户定义函数 (UDF) 是开发人员创建的对象，用于执行操作并返回结果。除了 UDF，SQL 还提供内置函数（或本机函数）。内置函数由 SQL 预定义，包括字符串函数、数字函数、数据和时间函数、聚合函数、逻辑函数等。这些内置函数可以高效地执行常见任务。但是，当开发人员必须进行一些复杂的计算或执行内置 SQL 函数不支持的自定义数据转换时，他们必须创建自己的函数并将其存储在数据库中以备将来使用。除了缩小内置函数留下的空白之外，UDF 还带来以下好处。
·简化查询： UDF 通过抽象复杂的计算、业务逻辑和重复操作进行模块化编程。这降低了主查询的复杂性，并提高了代码的可读性和可维护性。
·实现可重用性： UDF 将逻辑、计算和表达式打包成一个函数，从而减少代码重复，这些函数可以在不同的查询、视图或存储过程之间重复使用。
·增强安全性： UDF 被视为查询的独立部分，因此开发人员可以通过将某些数据或逻辑打包到 UDF 中来强制限制直接访问它们。通过这种方式可以保护敏感数据或操作。
·优化性能： UDF 通过在数据库中编译并存储来提高 SQL 查询的执行速度。此外，UDF 可以防止数据库和应用程序之间的往返，从而优化编程的性能。
·提高灵活性： UDF 允许开发人员创建带有参数的动态且适应性强的 SQL 语法。它们可以使代码在各种数据库之间更具可移植性。
SQL UDF 的通用结构
在 SQL Server 中，有三种类型的 UDF：Scaler UDF、Aggregate UDF 和 Table UDF。由于 MySQL 本身不支持 Table UDF，因此在本文中我使用 SQL Server 而不是我偏爱的工具 MySQL。一些在线教程提供了 MySQL 中 Table UDF 的示例，这可能会产生误导。虽然 MySQL 不支持 Table UDF，但可以通过存储过程或视图实现类似的功能。
·缩放器 UDF： 这些函数作用于单行并返回单个值。
·聚合 UDF： 这些函数处理多行并返回单个聚合值。但是，无法在纯 SQL 环境中创建聚合 UDF；它通常需要使用 .NET 语言（例如 C# 或 VB.NET），然后将其作为程序集注册到 SQL Server。由于创建聚合 UDF 需要了解其他编程语言，因此在本文中我不会使用示例来深入探讨此 UDF 类型。
·表 UDF： 这些函数返回一个表，可以在查询中像常规表一样使用。
如何正确编写 SQL UDF 有时会让初学者感到困惑，因此我总结了一个通用的结构，开发人员可以使用这个结构轻松创建 90％ 的 SQL UDF。
CREATE FUNCTION [schema_name.]function_name
(
@parameter _name1 datatype [ = default_value],
@parameter _name2 datatype [ = default_value],
...
)
RETURNS return_type
[ WITH < function_options > ]
AS
BEGIN
-- 函数体：函数的逻辑
RETURN [ value or table_expression];
END ;
GO
SQL UDF 结构中关键元素的解释：
· [schema_name.]function_name： 定义 ****稍后将调用的函数名称。函数名称是必需的，但如果使用默认架构，则架构是可选的。
· @parameter_name1 datatype [= default_value]： 指定传递给函数的参数的名称和数据类型。此部分是可选的。
· RETURNS return_type： 表示函数返回的结果，必填项，对于 Scalar UDF，return_type 为返回值的数据类型，对于 Table UDF，return_type 为TABLE。
· [WITH <function_options>]： 指定函数特性的可选属性。
· BEGIN…END： 包含定义逻辑或查询的函数体。对于标量 UDF 和多语句表 UDF，这是必需的。对于直接返回查询结果的内联表函数，则BEGIN...END不是必需的。
· RETURN [value or table_expression]： 对于 Scalar UDF，返回函数中的单个值；对于 Inline Table UDF，返回查询结果；对于 Multistatement Table UDF，返回执行复杂 SQL 语句后的最终表。此项为必填项。
在以下章节中，我将使用示例来解释如何有效地创建和调用这些不同类型的 UDF。
标量 UDF 示例
标量 UDF 用于从单行数据生成单个值。例如，如果我们想创建一个将两个数字相乘的函数，我们可以直接应用前面提到的通用结构，通过替换以下组件并获得语法。
[schema_name.]function_name -> dbo.func_multiply
参数 -> @num_1,@num_2(均为 FLOAT)
返回类型 ->FLOAT
函数主体 -> 将两个数字相乘并返回结果
-- Creating the Scalar UDF
CREATE FUNCTION dbo.func_multiply
(
@num_1 FLOAT,
@num_2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
RETURN @num_1 * @num_2;
END;
GO
-- Calling the UDF
SELECT dbo.func_multiply(10.5, 2);
编辑
图片来自作者
表 UDF 示例
在我的另一篇文章《在科技行业取得成功最有用的高级 SQL 技术》中，我使用了星光百货促销活动的模拟销售数据来介绍一些高级 SQL 技术。
最有用的高级 SQL 技术助您在科技行业取得成功
掌握高级 SQL 的语法、用例和专家提示
今天我将详细说明如何创建和调用具有相同数据集的 Table UDF。
CREATE TABLE promo_sales(
Sale_Person_ID VARCHAR(40) PRIMARY KEY,
Department VARCHAR(40),
Sales_Amount INT
);
INSERT INTO promo_sales VALUES ('001', 'Cosmetics', 500);
INSERT INTO promo_sales VALUES ('002', 'Cosmetics', 700);
INSERT INTO promo_sales VALUES ('003', 'Fashion', 1000);
INSERT INTO promo_sales VALUES ('004', 'Jewellery', 800);
INSERT INTO promo_sales VALUES ('005', 'Fashion', 850);
INSERT INTO promo_sales VALUES ('006', 'Kid', 500);
INSERT INTO promo_sales VALUES ('007', 'Cosmetics', 900);
INSERT INTO promo_sales VALUES ('008', 'Fashion', 600);
INSERT INTO promo_sales VALUES ('009', 'Fashion', 1200);
INSERT INTO promo_sales VALUES ('010', 'Jewellery', 900);
INSERT INTO promo_sales VALUES ('011', 'Kid', 700);
INSERT INTO promo_sales VALUES ('012', 'Fashion', 1500);
INSERT INTO promo_sales VALUES ('013', 'Cosmetics', 850);
INSERT INTO promo_sales VALUES ('014', 'Kid', 750);
INSERT INTO promo_sales VALUES ('015', 'Jewellery', 950);
编辑
promo_sales（图片来自作者）
第一个任务是获取所有部门的摘要，包括销售人员数量和每个部门的总销售额。与标量 UDF 类似，我们可以通过替换以下部分来使用通用结构并获取语法。
[schema_name.]function_name -> dbo.GetDepartmentSummary
参数 -> 无
返回类型 ->TABLE
函数主体 -> 返回显示部门摘要的表格
-- Creating the Table UDF
CREATE FUNCTION dbo.GetDepartmentSummary()
RETURNS TABLE
AS
RETURN
(
SELECT
Department,
SUM(Sales_Amount) AS Total_Sales,
COUNT(DISTINCT Sale_Person_ID) AS Number_of_Sales_Persons
FROM promo_sales
GROUP BY Department
);
GO
-- Calling the UDF
-- Getting the department summary
SELECT * FROM dbo.GetDepartmentSummary();
-- Getting the summary for cosmetics department
SELECT * FROM dbo.GetDepartmentSummary() WHERE Department = 'Cosmetics';
-- Getting the summary for departments whose total sales over 2000K USD
SELECT * FROM dbo.GetDepartmentSummary() WHERE Total_Sales > 2000;
-- Getting the summary for department with highest total sales amount
SELECT TOP 1 * FROM dbo.GetDepartmentSummary()
ORDER BY Total_Sales DESC;
-- Adding the Avg_Sales_Per_Person to the summary
SELECT
Department,
Total_Sales,
Number_of_Sales_Persons,
Total_Sales / CAST(Number_of_Sales_Persons AS FLOAT) AS Avg_Sales_Per_Person
FROM dbo.GetDepartmentSummary();
可以调用表 UDF 来检索部门摘要、按特定标准进行过滤或执行其他操作。
编辑
图片来自作者
第二项任务也是获取部门汇总，但这次汇总涉及每人平均销售额和每个部门的奖金。后来，公司管理层决定改变奖金规则——销售额超过 2000K 美元的部门奖金率将增加 10%。与使用内联表值 UDF 完成的第一项任务不同，此任务将使用 多语句表值 UDF进行。
内联表值 UDF和多语句表值 UDF之间的主要区别可以从两个主要方面观察到：
·逻辑的复杂性： 内联表值 UDF 包含单个查询并且不需要BEGIN…END块，这对于简单逻辑来说是理想的，而多语句表值 UDF 使用BEGIN…END块来容纳多个 SQL 语句和更复杂的逻辑。
·性能： SQL Server 的查询优化器将内联表值 UDF 视为调用查询的一部分，这通常会提高性能。另一方面，多语句表值 UDF 必须在内存中构建整个表，然后才能返回，这可能会影响性能。
对于第二个任务，可以将以下元素填充到通用结构中以创建多语句表值 UDF：
[schema_name.]function_name -> dbo.MultiStmt_GetDepartmentSummary
参数 -> 无
返回类型 ->TABLE
函数主体 -> 返回显示部门摘要的最终表
CREATE FUNCTION dbo.MultiStmt_GetDepartmentSummary()
RETURNS @DeptSummary TABLE
(
Department VARCHAR(40),
Total_Sales INT,
Number_of_Sales_Persons INT,
Avg_Sales_Per_Person DECIMAL(10, 2),
Bonus DECIMAL(10, 2)
)
AS
BEGIN
-- First Statement: Initialize the table variable with department sales summary
INSERT INTO @DeptSummary (Department, Total_Sales, Number_of_Sales_Persons, Avg_Sales_Per_Person, Bonus)
SELECT
Department,
SUM(Sales_Amount) AS Total_Sales,
COUNT(DISTINCT Sale_Person_ID) AS Number_of_Sales_Persons,
AVG(Sales_Amount) AS Avg_Sales_Per_Person,
SUM(Sales_Amount) * 0.2 AS Bonus
FROM promo_sales
GROUP BY Department;
-- Second Statement: Update rows in the table variable
UPDATE @DeptSummary
SET Bonus = Bonus * 1.1
WHERE Total_Sales > 2000;
-- Return the final table
RETURN;
END;
GO
-- Usage:
SELECT * FROM dbo.MultiStmt_GetDepartmentSummary();
编辑
图片来自作者
表 UDF 与存储过程之间的差异
刚接触 SQL UDF 的人经常会将表 UDF与存储过程混淆，因为它们通常看起来很相似，有时可以执行相同的功能。存储过程是 SQL 语句的预编译集合，可以作为单个单元执行并生成结果集。
表函数和存储过程之间的主要区别是：
·返回类型： 表 UDF 始终返回表，但存储过程返回结果集。
·灵活性： 存储过程比表 UDF 更灵活，因为它们允许更广泛的 SQL 语句和操作。
·参数： 表 UDF 仅允许输入参数，但存储过程同时接受输入和输出参数。
·用法： 表 UDF 通常由 SQL 查询使用，而存储过程则有更多场景——它们可以从其他过程、脚本或应用程序中调用。
·性能： 存储过程通常比表 UDF 具有更好的性能，因为它们经过预编译并针对执行进行了优化。
尽管存在差异，存储过程可以执行与 SQL Server 中的表 UDF 类似的任务。以下是使用存储过程作为多语句表值 UDF 完成第二项任务以获取部门摘要的语法。
-- Creating the stored procedure to achieve the same functionality
CREATE PROCEDURE dbo.GetDepartmentSummary_Proc
AS
BEGIN
-- Create a temporary table to store the department summary
CREATE TABLE #DeptSummary
(
Department VARCHAR(40),
Total_Sales INT,
Number_of_Sales_Persons INT,
Avg_Sales_Per_Person DECIMAL(10, 2),
Bonus DECIMAL(10, 2)
);
-- First Statement: Insert department summary into the temporary table
INSERT INTO #DeptSummary (Department, Total_Sales, Number_of_Sales_Persons, Avg_Sales_Per_Person, Bonus)
SELECT
Department,
SUM(Sales_Amount) AS Total_Sales,
COUNT(DISTINCT Sale_Person_ID) AS Number_of_Sales_Persons,
AVG(Sales_Amount) AS Avg_Sales_Per_Person,
SUM(Sales_Amount) * 0.2 AS Bonus
FROM promo_sales
GROUP BY Department;
-- Second Statement: Update rows in the temporary table
SET Bonus = Bonus * 1.1
WHERE Total_Sales > 2000;
-- Return the final table
SELECT * FROM #DeptSummary;
-- Clean up: Drop the temporary table
DROP TABLE #DeptSummary;
END;
GO
-- Usage:
EXEC dbo.GetDepartmentSummary_Proc;
它产生的结果与表 UDF 完全相同MultiStmt_GetDepartmentSummary。
结论
本文详细介绍了 SQL UDF。通过关注 SQL Server 环境，本教程不仅介绍了从头开始创建 UDF 的通用结构，还讨论了 Scale UDF、内联表值函数和多语句表值函数之间的异同，并附上了用例和语法。此外，本文还解决了几个常见的误解，并详细说明了表 UDF 和存储过程之间的区别。希望本文能帮助您在 SQL 开发中有效地应用 UDF。
感谢关注雲闪世界。（Aws解决方案架构师vs开发人员&GCP解决方案架构师vs开发人员）
订阅频道(t.me/awsgoogvps_…)
TG交流群(t.me/awsgoogvpsHost)
