Создание хранимых процедур
Хранимые процедуры являются одним из самых мощных средств в SQL Server для инкапсулирования бизнес-логики и повышения производительности приложений. При создании хранимых процедур вы создаете многоразовые блоки кода, которые выполняются на сервере, уменьшая сетевой трафик и централизованную логику доступа к данным.
Общие сведения о хранимых процедурах
Хранимая процедура представляет собой скомпилированную коллекцию инструкций T-SQL, которые SQL Server хранит и выполняет как единую единицу. В отличие от незапланированных запросов, которые вы отправляете на сервер каждый раз, хранимые процедуры предварительно компилируются и оптимизированы, что означает, что они выполняются быстрее при последующих выполнениях.
Хранимые процедуры используются для инкапсулирования сложной бизнес-логики, применения правил проверки данных и управления взаимодействием приложений с базой данных. Например, вместо прямого доступа к таблицам, можно создать процедуры хранения, которые проверяют входные данные, применяют бизнес-правила и журналируют изменения перед изменением этих данных.
Преимущества производительности зависят от кэширования плана запросов. При использовании незапланированных запросов SQL Server должен анализировать и оптимизировать каждый запрос каждый раз. При использовании хранимых процедур план выполнения кэшируется после первого запуска, уменьшая затраты на повторяющиеся операции.
Создание основных хранимых процедур
Создание хранимой процедуры начинается с инструкции, CREATE PROCEDURE за которой следует логика T-SQL. Вы указываете имя процедуры с помощью идентификатора с указанием схемы, что повышает четкость и производительность.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
Инструкция SET NOCOUNT ON предотвращает отправку клиенту сообщения о количестве затронутых строк. Это уменьшает сетевой трафик и повышает производительность, особенно когда процедура выполняет несколько заявлений.
При создании процедур используйте ключевые слова BEGIN и END для четкого определения тела процедуры. Это делает код более читаемым и помогает предотвратить ошибки при добавлении или изменении логики позже.
Работа с параметрами
Параметры делают хранимые процедуры гибкими и многократно используемыми. Вы определяете входные параметры для приема значений из вызывающего приложения и выходных параметров для возврата значений обратно вызывающей стороне.
Входные параметры используют символ @, за которым следует имя параметра и тип данных. Можно указать значения по умолчанию, чтобы сделать параметры необязательными:
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Выходные параметры позволяют возвращать значения вызывающему приложению. Вы определяете их с помощью ключевого OUTPUT слова:
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
При вызове процедуры с выходными параметрами необходимо объявить переменную для получения значения и использовать OUTPUT ключевое слово в инструкции EXECUTE .
Реализация обработки ошибок
Надежные хранимые процедуры включают обработку ошибок для управления непредвиденными условиями и поддержания целостности данных. Вы реализуете обработку ошибок с помощью TRY...CATCH блоков, которые работают аналогично обработке исключений на других языках программирования.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
Блок TRY содержит основную логику, а CATCH блок обрабатывает все ошибки, возникающие. Вы можете использовать такие системные функции, как ERROR_MESSAGE(), ERROR_SEVERITY()и ERROR_STATE() для записи сведений об ошибках и передачи их в вызывающее приложение.
Всегда проверяйте @@TRANCOUNT, прежде чем откатывать транзакции в блоке CATCH. Это предотвращает ошибки, если транзакция уже завершена или никогда не была запущена.
Применение лучших практик
Следуя установленным лучшим практикам при создании хранимых процедур, вы обеспечиваете их поддерживаемость, безопасность и производительность.
Использование имен с указанием схемы
Используйте имена с указанием схемы для всех объектов. Это устраняет неоднозначность и повышает производительность, избегая затрат на разрешение схем:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Реализация проверки параметров
Реализуйте проверку параметров в начале процедуры. Быстро завершайте работу, если входные данные недопустимы, вместо обработки плохих данных.
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Избегать SELECT *
Избегайте использования в рабочем коде SELECT * . Явным образом перечисляет столбцы для предотвращения проблем при изменении структур таблиц и повышении производительности запросов:
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Использование значимых имен
Используйте значимые имена, описывающие действия процедуры. Включите глагол, описывающий операцию (Get, Insert, Update, Delete, Calculate):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Избегайте префикса sp_
Не используйте sp_ префикс для хранимых процедур. SQL Server резервирует этот префикс для системных процедур, хранящихся в master базе данных. При создании процедуры с sp_помощью SQL Server сначала выполняется поиск master перед проверкой текущей базы данных, добавляя ненужные затраты:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
На основе этих методик вы можете создавать хранимые процедуры, которые ваша команда может понять, поддерживать и доверять, чтобы обеспечить надежную работу в рабочих средах.