SQL sever中函数(2)

目录

一、函数分类及应用

1.1标量函数（Scalar Functions）：

1.1.1格式

1.1.2示例

1.1.3作用

1.2表值函数（Table-Valued Functions）：

1.2.1内联表值函数（Inline Table-Valued Functions）：

1.2.2多语句表值函数（Multi-Statement Table-Valued Functions）：

1.2.3CLR表值函数（CLR Table-Valued Functions）：

1.3系统函数（System Functions）：

1.4聚合函数（Aggregate Functions）：

二、函数作用

三、函数总结

3.1创建函数通用格式

3.2注意事项：

 3.3调用函数的方式：

3.4函数可用于以下对象：

3.5函数与存储过程的区别：

3.6修改已创建函数里面内容的方法： 

3.7验证函数的正确性：

3.8删除已创建的函数：

---

一、函数分类及应用

在SQL Server 2008中的函数可以根据其功能和用途进行分类。以下是SQL Server 2008中常见的函数分类：

1.1标量函数（Scalar Functions）：

这种类型的函数接受零个或多个输入参数，并返回一个单一的值作为结果。标量函数可以用于计算、转换数据以及执行其他需要返回单个值的操作。

要创建标量函数，可以使用CREATE FUNCTION语句，并指定函数名、参数、返回值类型和函数体。

1.1.1格式

创建标量函数的具体格式如下：

CREATE FUNCTION [schema_name.]function_name
([@parameter1 data_type][@parameter2 data_type]...
)
RETURNS return_data_type
[WITH { ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }]
[AS]
BEGIN-- Function body with SQL statementsRETURN expression
END

• [schema_name.]function_name：函数名称，可以指定模式名称（可选）。
• @parameter1, @parameter2, ...：输入参数的名称和数据类型。
• return_data_type：返回值的数据类型。
• WITH { ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }：可选项，用于指定函数的特性。
• AS：关键字，表示开始函数定义。
• -- Function body with SQL statements：SQL语句的函数体部分。
• RETURN expression：函数返回表达式。

注意：

标量函数是一种返回单个值的函数。因此，返回的数据类型必须与声明的return_data_type匹配，且函数体内的SQL语句应该生成一个结果，并将其用作表达式返回。例如，可以执行计算、处理字符串等操作，并将最终结果返回。

1.1.2示例

下面是一些示例：

例1：GetFullName：将名字和姓氏合并成一个完整的姓名。

CREATE FUNCTION [dbo].[GetFullName]
(@FirstName VARCHAR(50),@LastName VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGINDECLARE @FullName VARCHAR(100)SET @FullName = @FirstName + ' ' + @LastNameRETURN @FullName
END

示例创建了一个名为GetFullName的标量函数，该函数接受两个字符串参数（@FirstName和@LastName），并返回这两个参数的拼接结果。函数体内部使用了变量和字符串操作来生成结果。

要调用标量函数，可以像调用任何其他函数一样使用SELECT语句或WHERE子句中的函数调用。下面是一个使用示例：

SELECT [dbo].[GetFullName]('John', 'Doe') AS FullName

示例调用了GetFullName函数，并将其返回的结果赋值给别名FullName。查询结果将显示"John Doe"。

例2：GetTotalSalesAmount：获取指定日期范围内的销售总额。

CREATE FUNCTION [dbo].[GetTotalSalesAmount]
(@StartDate DATE,@EndDate DATE
)
RETURNS MONEY
AS
BEGINDECLARE @TotalSales MONEYSELECT @TotalSales = SUM(SalesAmount)FROM SalesWHERE SaleDate >= @StartDate AND SaleDate <= @EndDateRETURN @TotalSales
END--创建表 Sales
CREATE TABLE Sales
(SaleID INT PRIMARY KEY,SaleDate DATE,SalesAmount MONEY
)--插入数据：
INSERT INTO Sales (SaleID, SaleDate, SalesAmount)
VALUES(1, '2022-01-05', 1000),(2, '2022-02-10', 1500),(3, '2022-03-15', 2000),(4, '2022-04-20', 1200)

用例：获取2022年第一季度（1月1日至3月31日）的销售总额。

SELECT dbo.GetTotalSalesAmount('2022-01-01', '2022-03-31') AS TotalSalesAmount

验证：输出结果为销售总额的金额，表示2022年第一季度的销售总额。 

例3：CalculateDiscountedPrice：根据折扣率计算打折后的价格。

CREATE FUNCTION [dbo].[CalculateDiscountedPrice]
(@OriginalPrice MONEY,@DiscountRate FLOAT
)
RETURNS MONEY
AS
BEGINDECLARE @DiscountedPrice MONEYSET @DiscountedPrice = @OriginalPrice * @DiscountRateRETURN @DiscountedPrice
END

用例：计算原价1000元以85%的折扣率打折后的价格。

SELECT dbo.CalculateDiscountedPrice(1000, 0.85) AS DiscountedPrice

 验证：输出结果为850，表示原价1000元以85%的折扣率打折后的价格为850元。

例4：IsAdult：判断一个人是否成年。

CREATE FUNCTION [dbo].[IsAdult]
(@BirthDate DATE
)
RETURNS BIT
AS
BEGINDECLARE @IsAdult BITIF DATEDIFF(YEAR, @BirthDate, GETDATE()) >= 18SET @IsAdult = 1ELSESET @IsAdult = 0RETURN @IsAdult
END

用例：判断1995年1月1日出生的人是否成年。

SELECT dbo.IsAdult('1995-01-01') AS IsAdult

 验证：输出结果为1，表示1995年1月1日出生的人已经成年。

例5：GetOrderStatus：根据订单状态码返回对应的描述信息。

CREATE FUNCTION [dbo].[GetOrderStatus]
(@StatusCode INT
)
RETURNS VARCHAR(50)
AS
BEGINDECLARE @StatusDescription VARCHAR(50)SELECT @StatusDescription = DescriptionFROM OrderStatusWHERE StatusCode = @StatusCodeRETURN @StatusDescription
END--创建OrderStatus表：
CREATE TABLE OrderStatus
(StatusCode INT PRIMARY KEY,Description VARCHAR(50)
)--插入数据：
INSERT INTO OrderStatus (StatusCode, Description)
VALUES(1, 'Pending'),(2, 'Processing'),(3, 'Shipped'),(4, 'Delivered')

用例：根据订单状态码1获取对应的描述信息。

SELECT dbo.GetOrderStatus(1) AS OrderStatus

 验证：输出结果为"Pending"，表示订单状态码1对应的描述为"Pending"。

例6：CalculateSum的标量函数：输入两个整数参数，并返回它们的总和

CREATE FUNCTION dbo.CalculateSum
(@num1 INT,@num2 INT
)
RETURNS INT
AS
BEGINDECLARE @sum INTSET @sum = @num1 + @num2RETURN @sum
END

调用标量函数并检索其返回值，使用SELECT语句，如下所示： 

SELECT dbo.CalculateSum(10, 5)

 调用CalculateSum函数，传递参数10和5，并返回它们的总和作为结果：15

1.1.3作用

标量函数的作用主要包括：

• 执行简单的数学运算，如计算平方根、绝对值等。
• 将数据进行格式化，如日期时间转换、货币格式化等。
• 执行字符串操作，如拼接、分割等。
• 实现自定义逻辑，如业务规则验证、条件判断等。
• 对查询结果进行处理和转换，以满足特定的需求。
• 在查询中获取单个值，例如计算总和、计数等聚合操作。

1.2表值函数（Table-Valued Functions）：

这种类型的函数返回一个表作为结果，可以被查询语句直接引用。表值函数可以返回单行、多行或者嵌套表。

有三种类型的表值函数可用于SQL Server 2008：

1.2.1内联表值函数（Inline Table-Valued Functions）：

内联表值函数是嵌入在SELECT语句中的函数，它返回一个基于输入参数的表。内联表值函数在查询中以表达式的形式使用。

SQL Server 2008中创建和使用内联表值函数的格式：

①创建内联表值函数：

CREATE FUNCTION FunctionName
(@Parameter1 DataType,@Parameter2 DataType,...
)
RETURNS TABLE
AS
RETURN
(SELECT Column1, Column2, ...FROM TableNameWHERE Condition
)

• FunctionName：自定义的函数名称。
• @Parameter1, @Parameter2, ...：输入参数的名称和数据类型。
• DataType：参数的数据类型。
• RETURNS TABLE：指定函数返回一个表。
• AS RETURN：指定返回的表结构和数据。

②使用内联表值函数：

SELECT *
FROM dbo.FunctionName(Parameter1, Parameter2, ...)

• dbo.FunctionName：内联表值函数的名称和所属的模式。
• Parameter1, Parameter2, ...：传递给函数的参数值。

下面是一个示例，演示如何创建和使用内联表值函数：

--创建Sales1
CREATE TABLE Sales1
(ProductName VARCHAR(100),CategoryID INT,UnitPrice DECIMAL(10, 2),Quantity INT
)--插入数据
INSERT INTO Sales1 (ProductName, CategoryID, UnitPrice, Quantity)
VALUES('Product A', 1, 10.00, 5),('Product B', 1, 15.50, 8),('Product C', 2, 20.25, 3),('Product D', 3, 5.75, 10)SELECT * FROM Sales1--创建内联表值函数：
CREATE FUNCTION dbo.GetSalesByCategory
(@CategoryID INT
)
RETURNS TABLE
AS
RETURN
(SELECT ProductName, UnitPrice, QuantityFROM Sales1WHERE CategoryID = @CategoryID
)

 使用内联表值函数查询指定类别的销售数据：

SELECT *
FROM dbo.GetSalesByCategory(1)

创建了名为dbo.GetSalesByCategory的内联表值函数，它接收一个@CategoryID参数，并返回满足指定类别ID的销售数据。然后，使用该函数来查询类别ID为1的销售数据。 

1.2.2多语句表值函数（Multi-Statement Table-Valued Functions）：

多语句表值函数包含多个T-SQL语句，并在最后使用INSERT语句将结果插入到一个表变量中。然后，该表变量作为函数的返回值。

以下是SQL Server 2008中创建和使用多语句表值函数的格式：

①创建多语句表值函数：

CREATE FUNCTION FunctionName
(@Parameter1 DataType,@Parameter2 DataType,...
)
RETURNS @TableVariable TABLE
(Column1 DataType,Column2 DataType,...
)
AS
BEGIN-- SQL statements to populate the table variableINSERT INTO @TableVariable (Column1, Column2, ...)SELECT Column1, Column2, ...FROM TableNameWHERE ConditionRETURN
END

• FunctionName：自定义的函数名称。
• @Parameter1, @Parameter2, ...：输入参数的名称和数据类型。
• DataType：参数的数据类型。
• @TableVariable：表变量的名称，用于存储函数返回的结果集。
• Column1, Column2, ...：表变量中的列名和对应的数据类型。
• TableName：要查询的表名。
• Condition：筛选条件。

②使用多语句表值函数：

SELECT *
FROM dbo.FunctionName(Parameter1, Parameter2, ...)

• dbo.FunctionName：多语句表值函数的名称和所属的模式。
• Parameter1, Parameter2, ...：传递给函数的参数值。

下面是一个示例，演示如何创建和使用多语句表值函数：

--创建Customers表：
CREATE TABLE Customers
(CustomerID INT PRIMARY KEY,CustomerName VARCHAR(100),OrderID INT,OrderAmount DECIMAL(10, 2)
)--插入数据：
INSERT INTO Customers (CustomerID, CustomerName, OrderID, OrderAmount)
VALUES(1, 'Customer A', 101, 1500.00),(2, 'Customer B', 102, 2000.00),(3, 'Customer C', 103, 1200.00),(4, 'Customer D', 104, 1800.00)SELECT * FROM Customers--创建多语句表值函数
CREATE FUNCTION dbo.GetHighValueCustomers
()
RETURNS @Customers TABLE
(CustomerID INT,CustomerName VARCHAR(100),TotalOrders INT
)
AS
BEGININSERT INTO @Customers (CustomerID, CustomerName, TotalOrders)SELECT CustomerID, CustomerName, COUNT(OrderID) AS TotalOrdersFROM CustomersWHERE OrderAmount > 1000GROUP BY CustomerID, CustomerNameRETURN
END

使用多语句表值函数查询订单金额大于1000的高价值客户： 

SELECT *
FROM dbo.GetHighValueCustomers()

创建了名为dbo.GetHighValueCustomers的多语句表值函数，它不接收任何参数，并返回满足订单金额大于1000的高价值客户信息。函数内部使用INSERT语句将结果插入到名为@Customers的表变量中，并通过RETURN语句将表变量作为函数的返回值。

1.2.3CLR表值函数（CLR Table-Valued Functions）：

CLR（Common Language Runtime）表值函数是一种特殊的函数类型，是使用公共语言运行时 (CLR) 编写的自定义函数，它允许开发人员使用.NET编程语言（如C#或VB.NET）编写自定义函数，并将其部署到SQL Server中作为表值函数使用。CLR表值函数提供更大的灵活性和复杂性，可以进行更高级的计算和数据处理操作。

以下是SQL Server 2008中创建和使用CLR表值函数的基本步骤：

①创建CLR表值函数的CLR集成程序集：

• 在Visual Studio中创建一个新的类库项目。
• 编写自定义函数的代码，并确保实现返回IEnumerable<SqlDataRecord>接口以生成表值结果集。
• 将项目编译生成CLR集成程序集（.dll文件）。

②将CLR集成程序集部署到SQL Server中：

• 使用SQL Server Management Studio（SSMS）或其他方法，将CLR集成程序集安装到SQL Server中。
• 使用CREATE ASSEMBLY语句创建程序集，例如：

CREATE ASSEMBLY AssemblyName
FROM 'Path\To\Assembly.dll'
WITH PERMISSION_SET = SAFE;

 ③创建CLR表值函数：

• 使用CREATE FUNCTION语句创建CLR表值函数，指定函数名称、输入参数和返回表的结构，例如：

CREATE FUNCTION FunctionName
(@Parameter1 DataType,@Parameter2 DataType,...
)
RETURNS TABLE
AS EXTERNAL NAME AssemblyName.ClassName.MethodName;

• FunctionName：自定义的函数名称。
• @Parameter1, @Parameter2, ...：输入参数的名称和数据类型。
• DataType：参数的数据类型。
• RETURNS TABLE：指定函数返回一个表。
• AS EXTERNAL NAME AssemblyName.ClassName.MethodName：指定CLR集成程序集、类名和方法名作为函数的实现。

④使用CLR表值函数：

• 使用SELECT语句调用CLR表值函数并从其返回的结果集中检索数据，例如：

SELECT *
FROM dbo.FunctionName(Parameter1, Parameter2, ...)

注意：

CLR表值函数需要具有CLR集成权限，并且对于安全性考虑，应该选择适当的权限级别（如SAFE）。

下面的一个示例是在SQL Server 2008中展示详细的CLR表值函数创建和使用的过程： 

①创建CLR集成程序集：

• 在Visual Studio中创建一个新的类库项目。

• 编写自定义函数的代码，确保实现返回IEnumerable<SqlDataRecord>接口以生成表值结果集。以下是一个简单的示例：

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public class MyFunctions
{[SqlFunction(FillRowMethodName = "FillRow")]public static IEnumerable<SqlDataRecord> MyTableValuedFunction(){// Create metadata for the output tablevar recordMetadata = new SqlMetaData[]{new SqlMetaData("Column1", SqlDbType.Int),new SqlMetaData("Column2", SqlDbType.VarChar, 100)};// Create a list to hold the rows of the output tablevar rows = new List<SqlDataRecord>();// Create a new row and set its valuesvar row = new SqlDataRecord(recordMetadata);row.SetInt32(0, 1);row.SetString(1, "Value 1");rows.Add(row);// Add more rows if needed// Return the list of rowsreturn rows;}public static void FillRow(object obj, out SqlInt32 column1, out SqlString column2){// Implementation if neededthrow new NotImplementedException();}
}

• 编译项目以生成CLR集成程序集（.dll文件）。

②将CLR集成程序集部署到SQL Server中：

• 打开SQL Server Management Studio（SSMS）并连接到目标数据库。

• 使用CREATE ASSEMBLY语句创建CLR集成程序集，例如：

CREATE ASSEMBLY MyAssembly
FROM 'Path\To\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;

③创建CLR表值函数：

• 使用CREATE FUNCTION语句创建CLR表值函数，指定函数名称、输入参数和返回表的结构，例如：

CREATE FUNCTION dbo.MyTableValuedFunction()
RETURNS TABLE
(Column1 INT,Column2 VARCHAR(100)
)
EXTERNAL NAME MyAssembly.[Namespace.ClassName].MyTableValuedFunction;

• dbo.MyTableValuedFunction：自定义的函数名称和所属的模式。
• RETURNS TABLE：指定函数返回一个表。
• Column1, Column2：表的列名和对应的数据类型。
• EXTERNAL NAME MyAssembly.[Namespace.ClassName].MyTableValuedFunction：指定CLR集成程序集、类名和方法名作为函数的实现。

④使用CLR表值函数：

• 使用SELECT语句调用CLR表值函数，并从其返回的结果集中检索数据，例如：

SELECT *
FROM dbo.MyTableValuedFunction()

提示：

请确保在使用CLR表值函数时，已经将CLR集成程序集安装到了正确的数据库，并且具有适当的权限设置（如SAFE）。 

1.3系统函数（System Functions）：

SQL Server提供了许多内置的系统函数，用于执行各种任务，例如日期和时间处理、字符串操作、数学运算等。下面是一些常用的系统函数，并按照功能进行分类：

①日期和时间函数：

• GETDATE：返回当前的日期和时间。
• DATEPART：从日期或时间值中提取指定的部分，如年、月、日、小时、分钟等。
• DATEADD：向指定的日期或时间添加一个时间间隔。
• DATEDIFF：计算两个日期或时间之间的差异。

②字符串函数：

• LEN：返回一个字符串的长度。
• SUBSTRING：从字符串中获取子字符串。
• CONCAT：将多个字符串连接成一个字符串。
• REPLACE：替换字符串中的子字符串。

③数学函数：

• ROUND：将数值四舍五入到指定的位数。
• ABS：返回一个数的绝对值。
• POWER：计算一个数的指定次幂。
• SQRT：计算一个数的平方根。

④聚合函数：

• SUM：计算某一列的总和。
• AVG：计算某一列的平均值。
• COUNT：计算某一列的行数。
• MAX：计算某一列的最大值。
• MIN：计算某一列的最小值。

⑤类型转换函数：

• CAST：将一个表达式转换为指定的数据类型。
• CONVERT：将一个表达式转换为指定的数据类型。
• ISNULL：如果表达式为空，则返回替代值。

以下是SQL Server 2008中常见的系统函数：

①GETDATE：返回当前的日期和时间。

 用例：获取当前日期和时间。

SELECT GETDATE() AS CurrentDateTime

验证：输出结果为当前的日期和时间。

 

②DATEPART：从日期或时间值中提取指定的部分，如年、月、日、小时、分钟等。

用例：从日期中提取年份。

SELECT DATEPART(YEAR, '2022-09-15') AS Year

验证：输出结果为2022，即从日期'2022-09-15'中提取的年份。 

 

③LEN：返回一个字符串的长度。

用例：获取一个字符串的长度。

SELECT LEN('Hello World') AS StringLength

 验证：输出结果为11，表示字符串'Hello World'的长度为11。

④SUBSTRING：从字符串中获取子字符串。

用例：获取指定位置处的字符子串。

SELECT SUBSTRING('Hello World', 7, 5) AS Substring

 验证：输出结果为World，即从字符串'Hello World'的第7个字符开始提取5个字符。

1.4聚合函数（Aggregate Functions）：

聚合函数对一组值进行计算，并返回单个值作为结果。常见的聚合函数包括SUM、AVG、COUNT、MIN和MAX等。

二、函数作用

函数的作用可以有很多，例如：

• 封装复杂的业务逻辑，使其可以在不同的查询中重复使用。
• 提高查询性能，通过将一些计算逻辑放到函数中，减少主查询的复杂度。
• 简化查询语句，通过函数调用实现更简洁、可读性更好的代码。
• 实现自定义数据转换和处理逻辑，满足特定需求。
• 执行数据验证和格式化等任务，确保数据的正确性和一致性。

三、函数总结

3.1创建函数通用格式

CREATE FUNCTION [schema_name.]function_name
([@parameter1 data_type][@parameter2 data_type]...
)
RETURNS return_data_type
[WITH { ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }]
[AS]
BEGIN-- Function body with SQL statementsRETURN expression
END

• [schema_name.]function_name：函数名称，可以指定模式名称（可选）。
• @parameter1, @parameter2, ...：输入参数的名称和数据类型。
• return_data_type：返回值的数据类型。
• WITH { ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }：可选项，用于指定函数的特性。
• AS：关键字，表示开始函数定义。
• -- Function body with SQL statements：SQL语句的函数体部分。
• RETURN expression：函数返回表达式。

3.2注意事项：

• 函数名称必须是唯一的，并且符合标识符的规则。
• 输入参数可以有多个，但每个参数必须具有唯一的名称。
• 返回值的数据类型必须与声明的return_data_type匹配。
• 函数体内可以包含各种SQL语句，例如SELECT、INSERT、UPDATE等。
• 函数可以包含控制流程语句（如IF、WHILE）和变量声明。

 3.3调用函数的方式：

• 使用SELECT语句调用函数并检索其返回的值，例如：

SELECT dbo.MyFunction(parameter1, parameter2, ...)

3.4函数可用于以下对象：

• SELECT语句的列列表。
• WHERE、HAVING和JOIN子句的条件。
• COMPUTED列的公式。
• 触发器的操作。

3.5函数与存储过程的区别：

• 函数返回一个单个值，而存储过程可以执行多个操作并不返回值。
• 函数可以嵌套在其他查询或表达式中，而存储过程通常作为独立的代码块执行。
• 存储过程可以修改数据库中的数据，而函数通常只用于查询和计算。

3.6修改已创建函数里面内容的方法： 

• 使用ALTER FUNCTION语句来修改已创建函数的定义，例如：

ALTER FUNCTION [schema_name.]function_name
([@parameter1 data_type][@parameter2 data_type]...
)
RETURNS return_data_type
[WITH { ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }]
[AS]
BEGIN-- Updated function body with SQL statementsRETURN expression
END

3.7验证函数的正确性：

• 可以使用SELECT语句调用函数，并检查返回的结果是否符合预期。

3.8删除已创建的函数：

• 使用DROP FUNCTION语句删除已创建的函数，例如：

DROP FUNCTION [schema_name.]function_name

 注意：

在删除函数之前，请确保您不再需要该函数，并且没有任何依赖该函数的对象。