MSSQL的函数调用实战

1. MSSQL函数简介

MSSQL函数是对语句的封装，用于参数化操作，提高代码复用率。函数用于与数据库交互的程序中，我们可以直接使用SQL命令，但是如果在多处使用同样的代码，这种方式将变得非常麻烦，而函数就可以解决这个问题。

MSSQL函数可以用于两种不同的操作：查询和更新。查询操作的函数可以返回一个值，而更新操作的函数可以将数据修改为新值。

2. MSSQL函数分类

在MSSQL中，函数可以分为以下几类：

2.1 内置函数

内置函数是MSSQL自带的一些函数，可以直接使用。这些函数通常用于处理数据或执行常规操作，例如格式化数字、字符串转换等。下面是一些常用的内置函数：

SELECT FORMAT(12345.6789, 'C', 'en-US') AS Result;
--结果为: $12,345.68
SELECT LEFT('Hello World', 5) AS Result;
--结果为: 'Hello'
SELECT LOWER('AbCdEfGhIjK') AS Result;
--结果为: 'abcdefghijk'

2.2 用户定义函数

用户定义函数（User-defined functions，简称UDF）是自定义的函数，可以使用T-SQL编写。函数定义后可以在程序的任何地方调用。

根据返回值类型的不同，用户定义函数又可以分为以下三种类型：

2.2.1 标量函数

标量函数用于返回单个值，可以用于任何地方需要一个单一值的地方。例如：

CREATE FUNCTION fnIsNumber(@str AS NVARCHAR(255)) RETURNS BIT
AS
BEGIN
RETURN CASE
         WHEN @str NOT LIKE '%[^0-9]%' THEN 1
         ELSE 0
       END
END
SELECT dbo.fnIsNumber('123');
--结果为: 1
SELECT dbo.fnIsNumber('123a');
--结果为: 0

2.2.2 表值函数

表值函数用于返回一个表格，可以执行查询操作并返回结果集。例如：

CREATE FUNCTION fnNameMailingList(@lastName AS NVARCHAR(100)) RETURNS TABLE
AS
RETURN
SELECT FirstName, LastName, EmailAddress
FROM Customers
WHERE LastName = @lastName
SELECT * FROM dbo.fnNameMailingList('Doe');
--返回所有姓为'Doe'的客户信息

2.2.3 存储过程

存储过程是一组T-SQL语句，也可以接受参数并返回结果。与标量函数和表值函数不同，存储过程可以执行更复杂的操作，并且可以包含控制流、条件语句、循环等结构。

CREATE PROCEDURE spInsertCustomer
    @firstName NVARCHAR(50),
    @lastName NVARCHAR(50),
    @email NVARCHAR(50)
AS
BEGIN
    INSERT INTO Customers (FirstName, LastName, EmailAddress)
    VALUES (@firstName, @lastName, @email)
END
EXEC dbo.spInsertCustomer 'Jane', 'Doe', 'jane.doe@example.com';
--插入一个客户信息

3. MSSQL函数调用实战

下面我们以一个简单的例子来演示如何使用MSSQL函数进行操作。

3.1 准备数据

我们有一个包含员工基本信息的表格：

CREATE TABLE Employees
(
   EmployeeID INT PRIMARY KEY,
   FirstName NVARCHAR(50) NOT NULL,
   LastName NVARCHAR(50) NOT NULL,
   BirthDate DATE NOT NULL,
   HireDate DATE NOT NULL,
   Gender CHAR(1) NOT NULL,
   Salary DECIMAL(9,2)
);
INSERT INTO Employees VALUES
(1, 'John', 'Doe', '01/01/1970', '01/01/2000', 'M', 50000.00),
(2, 'Jane', 'Doe', '02/02/1972', '01/01/2001', 'F', 60000.00),
(3, 'Bob', 'Smith', '03/03/1974', '01/01/2002', 'M', 55000.00),
(4, 'Sue', 'Jones', '04/04/1976', '01/01/2003', 'F', 65000.00),
(5, 'Tom', 'Brown', '05/05/1978', '01/01/2004', 'M', 70000.00);

3.2 查询数据

现在，我们要查询出所有员工的信息和薪水，但薪水值要保留两位小数并添加货币符号。

我们可以使用内置函数来完成这个任务：

SELECT 
   CONCAT(E.FirstName,' ',E.LastName) AS FullName,
   CONCAT('$',FORMAT(E.Salary, 'N2')) AS Salary
FROM Employees E;

结果如下：

然而，我们发现这些员工的薪水低于平均薪水。我们希望能够根据这个标准查询出符合条件的员工，可以使用标量函数实现查询：

CREATE FUNCTION fnGetAvgSalary() RETURNS DECIMAL(9,2) AS 
BEGIN
 RETURN ( SELECT AVG(Salary) FROM Employees )
END
SELECT 
   CONCAT(E.FirstName,' ',E.LastName) AS FullName,
   CONCAT('$',FORMAT(E.Salary, 'N2')) AS Salary
FROM Employees E 
WHERE E.Salary > dbo.fnGetAvgSalary();

该查询将返回薪水高于平均薪水的员工信息：

3.3 更新数据

现在假设我们要将所有男性员工的薪水提高5%。我们可以使用存储过程来完成这个任务：

CREATE PROCEDURE spIncreaseSalaryForMaleEmployees AS 
BEGIN
 UPDATE Employees 
 SET Salary = Salary * 1.05
 WHERE Gender='M';
END
EXEC spIncreaseSalaryForMaleEmployees;
--执行存储过程，更新数据

执行后，我们可以查询到薪水已经增加了5%：

SELECT 
   CONCAT(E.FirstName,' ',E.LastName) AS FullName,
   CONCAT('$',FORMAT(E.Salary, 'N2')) AS Salary
FROM Employees E

4. 总结

MSSQL函数是非常有用的工具，可以简化代码、提高效率。内置函数可以实现基本的操作，用户定义函数则可以根据需要定义各种复杂的逻辑，而存储过程则可以实现多种操作并且支持控制流、条件语句、循环等结构。

在使用MSSQL函数时，我们需要注意函数的参数和返回值类型，以及函数的作用范围。如果选择正确的函数，可以大大提高我们的工作效率。