SQL 进阶 使用存储过程和函数

1、存储过程

存储过程是一组为了完成特定功能的SQL声明，它可以执行包括插入、更新、删除等在内的SQL语句。存储过程可以接受参数、具有执行权限控制，并能返回状态值，但不直接返回值（如数据集）。

可以减少网络流量，多个操作封装在一次调用中，减少客户端与数据库服务器之间的通信。

可以提高性能，数据库可以优化存储过程的执行，常见的优化包括缓存计划和预编译。

通过控制对存储过程的访问，可以隐藏数据访问的细节，提高安全性。

使用场景比较适合， 复杂的业务逻辑，需要在数据库层面快速处理。 需要操作多个表或进行大量计算的任务。 安全性和权限控制要求高的环境。

1）创建存储过程

下面是一个创建存储过程的基本示例，此过程用于插入新的员工记录到employees表中：

CREATE PROCEDURE AddEmployee
    @Name NVARCHAR(50),
    @Position NVARCHAR(50),
    @DepartmentId INT
AS
BEGIN
    INSERT INTO employees (Name, Position, DepartmentId)
    VALUES (@Name, @Position, @DepartmentId);
END;

2）调用存储过程

一旦存储过程被创建，可以从应用程序或其他SQL脚本中调用它：

EXEC AddEmployee @Name = 'John Doe', @Position = 'Analyst', @DepartmentId = 1;

3）存储过程中的事务处理

存储过程经常用于处理需要事务控制的业务逻辑。下面的示例展示了如何在存储过程中使用事务：

CREATE PROCEDURE UpdateEmployeeDetails
    @EmployeeId INT,
    @NewPosition NVARCHAR(50),
    @NewDepartmentId INT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        UPDATE employees
        SET Position = @NewPosition
        WHERE EmployeeId = @EmployeeId;

        UPDATE employees
        SET DepartmentId = @NewDepartmentId
        WHERE EmployeeId = @EmployeeId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- Re-throw caught exception to handle it on the application side
    END CATCH
END;

2、函数

函数（特别是用户定义函数UDF）通常用于计算并返回单一的值，或者返回表类型的对象。函数不能执行诸如INSERT、UPDATE、DELETE等修改数据的操作。 函数可以在查询中被重复调用，有助于代码的简洁和维护。 可以在查询中直接使用函数来进行计算，方便数据的处理和转换。 使用场景较适合，需要在查询中进行数据转换或复杂计算。 需要在多个查询和存储过程中复用逻辑。

CREATE FUNCTION GetTotal(@Price DECIMAL, @Quantity INT)
RETURNS DECIMAL
AS
BEGIN
    RETURN @Price * @Quantity;
END;

创建一个表值函数来返回特定条件的员工：

-- SQL Server示例
CREATE FUNCTION GetHighEarningEmployees(@SalaryThreshold DECIMAL(10,2))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > @SalaryThreshold
);

2）使用这个函数

SELECT *
FROM dbo.GetHighEarningEmployees(50000);