Различия между хранимыми функциями и процедурами

Хранимые функции и процедуры — это два различных механизма для выполнения повторяющихся операций в базах данных. Они имеют сходства, но также и ключевые отличия. Вот основные различия между хранимыми функциями и процедурами:

Возвращаемое значение

Хранимые функции:

• Обязательно возвращают значение (или набор значений). Это значение может быть простым, например, число или строка, или сложным, например, таблица (в случае табличных функций).

• Функции могут быть использованы в SQL-запросах, как часть выражения.

Пример:

CREATE FUNCTION GetEmployeeFullName (@EmployeeID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @FullName NVARCHAR(100);
    SELECT @FullName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
    RETURN @FullName;
END;

Хранимые процедуры:

• Не возвращают значение напрямую. Вместо этого они могут возвращать несколько результатов через выходные параметры или через наборы данных, которые можно получить с помощью SELECT.

• Процедуры используются для выполнения операций, таких как вставка, обновление или удаление данных, а также для выполнения сложной бизнес-логики.

Пример:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT,
    @FullName NVARCHAR(100) OUTPUT,
    @Salary DECIMAL(18, 2) OUTPUT
AS
BEGIN
    SELECT @FullName = FirstName + ' ' + LastName,
           @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Использование в запросах

Хранимые функции:

• Могут быть использованы в SELECT, WHERE, JOIN и других SQL-контекстах, как часть выражений.

• Позволяют использовать их в виде выражения в запросах, что делает их гибкими для вычислений и фильтрации данных.

Пример:

SELECT dbo.GetEmployeeFullName(1) AS EmployeeFullName;

Хранимые процедуры:

• Не могут быть использованы в SQL-запросах. Процедуры вызываются отдельно через команду EXEC или CALL, и не могут быть интегрированы в запросы как часть выражений.

Пример:

EXEC GetEmployeeDetails @EmployeeID = 1, @FullName = @FullName OUTPUT, @Salary = @Salary OUTPUT;
SELECT @FullName AS EmployeeName, @Salary AS EmployeeSalary;

Влияние на базу данных

Хранимые функции:

• Обычно не изменяют состояние базы данных, хотя существуют функции, которые могут иметь побочные эффекты.

• Они предназначены для выполнения вычислений и получения данных.

Хранимые процедуры:

• Могут изменять состояние базы данных, включая вставку, обновление и удаление данных.

• Используются для выполнения операций, которые могут влиять на данные в базе.

Возможности обработки ошибок

Хранимые функции:

• Могут использовать обработку ошибок, но не поддерживают такие возможности, как транзакции или TRY...CATCH в SQL Server.

Хранимые процедуры:

• Поддерживают обработку ошибок и транзакции, что позволяет им выполнять более сложные операции и управлять ошибками.

Пример обработки ошибок в процедуре:

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(18, 2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE Employees
        SET Salary = @NewSalary
        WHERE EmployeeID = @EmployeeID;

        IF @@ROWCOUNT = 0
            THROW 50000, 'Employee not found.', 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Параметры

Хранимые функции:

• Принимают параметры и возвращают результат, но не могут иметь выходные параметры.

• Параметры функции могут быть только входными.

Хранимые процедуры:

• Могут иметь входные, выходные и входные/выходные параметры.

• Позволяют передавать данные в процедуру и получать результаты через выходные параметры.

Пример процедуры с несколькими типами параметров:

CREATE PROCEDURE UpdateEmployee
    @EmployeeID INT,
    @NewSalary DECIMAL(18, 2),
    @OldSalary DECIMAL(18, 2) OUTPUT
AS
BEGIN
    SELECT @OldSalary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END;

Эти различия помогут вам выбрать подходящий инструмент для конкретных задач в вашей базе данных.