SQL Хранимая Процедура: Автоматизация и Оптимизация Запросов

Учебники
SQL

Хранимые процедуры SQL – это наборы SQL-заявлений, сохраненных и хранящихся в базе данных. Их можно выполнять по мере необходимости для выполнения задач манипуляции и валидации данных, что снижает необходимость в написании повторяющегося SQL-кода для общих операций. Хранимые процедуры полезны в управлении базами данных, так как способствуют эффективности и повторному использованию. Кроме того, они поддерживают повышенную безопасность базы данных и удобство обслуживания. В этой статье мы обсудим, как создавать и выполнять хранимые процедуры SQL, распространенные случаи их использования и лучшие практики.

Когда вы начинаете, я настоятельно рекомендую пройти курсы DataCamp Введение в SQL и Изучение SQL, чтобы освоить основные знания по извлечению и анализу данных с использованием SQL. Также Чек-лист по основам SQL, который вы можете скачать, является полезным справочным материалом, так как содержит все самые распространенные функции SQL.

Что такое хранимая процедура в SQL?

Хранимая процедура в SQL — это коллекция операторов SQL, сохраненных и хранящихся в базе данных. Цель хранимой процедуры SQL состоит в том, чтобы выполнять последовательность операций с базой данных, таких как запросы, вставка, обновление или удаление данных.

В отличие от обычных SQL-запросов, выполняемых как отдельные команды, хранимые процедуры инкапсулируют набор SQL-операторов, что упрощает повторное использование кода без необходимости многократного написания SQL-команд.

Преимущества хранимых процедур SQL включают следующее:

  • Повторное использование кода: После создания хранимой процедуры ее можно вызывать столько раз, сколько необходимо, устраняя избыточность в SQL-коде.
  • Улучшенная производительность: Хранимые процедуры часто выполняются быстрее, так как они предварительно компилируются и хранятся на сервере базы данных, что снижает задержку сети и время компиляции.
  • Безопасность: Хранимые процедуры могут улучшить безопасность данных и контроль доступа к конфиденциальным данным, предоставляя пользователям разрешение на выполнение хранимой процедуры без прямого доступа к таблицам.

Основной синтаксис и структура

Синтаксис создания хранимой процедуры может немного различаться в зависимости от системы управления базами данных (например, MySQL, SQL Server, Oracle). Ниже приведен общий пример с использованием синтаксиса SQL Server:

-- Создать хранимую процедуру с именем ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL операторы помещаются сюда
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

В указанном выше синтаксисе;

  • СОЗДАТЬ ПРОЦЕДУРУ: Эта команда используется для определения новой сохраненной процедуры.

  • ИмяПроцедуры: Имя, присвоенное сохраненной процедуре. Оно должно быть уникальным в рамках базы данных.

  • @Parameter1, @Parameter2: Параметры являются необязательными; они позволяют процедуре получать входные данные. Каждый параметр определяется с помощью символа @ и типа данных (например, INT, VARCHAR(50)).

  • КАК НАЧАЛО…КОНЕЦ: SQL-выражения между BEGIN и END формируют тело процедуры, где выполняется основная логика. В данном примере процедура извлекает записи из таблицы на основе определенных условий.

Входные и выходные параметры

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

Например, если @Parameter1 определен как входной параметр, ему можно присвоить любое значение при вызове процедуры, что повлияет на SQL-логику или вывод. В приведенном ниже примере параметр @UserID извлекает данные, специфичные для предоставленного UserID.

-- Создайте процедуру для извлечения данных для конкретного пользователя по UserID
CREATE PROCEDURE GetUserData 
 -- Входной параметр: ID пользователя, данные которого нужно извлечь
    @UserID INT 
AS
BEGIN
    -- Выберите все столбцы из Users, где UserID совпадает с входным параметром
    SELECT * FROM Users WHERE UserID = @UserID;
END;

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

-- Создайте процедуру для расчета цены со скидкой
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Выход: окончательная цена после скидки
AS
BEGIN
    -- Рассчитайте окончательную цену, применив ставку скидки к первоначальной цене
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Для вызова этой процедуры используйте:

-- Объявите переменную для хранения конечной цены после скидки
DECLARE @FinalPrice DECIMAL(10, 2);
-- Выполните процедуру CalculateDiscount с ценой 100 и скидкой 10%
-- Сохраните вывод в переменной @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Выберите и отобразите конечную цену со скидкой
SELECT @FinalPrice AS FinalPrice;

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

Как я упоминал, выполнение хранимой процедуры в SQL может осуществляться различными способами в зависимости от используемой системы баз данных и инструментов.

Создание хранимых процедур в MySQL

Создание хранимой процедуры в MySQL включает в себя определение имени процедуры, параметров и SQL-инструкций, составляющих ее тело. В следующем примере создается процедура с именем GetEmployeeDetails, которая принимает EmployeeID в качестве входного параметра и извлекает сведения о конкретном сотруднике.

DELIMITER $
-- Создайте процедуру для извлечения информации о конкретном сотруднике по EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Выберите все столбцы из таблицы Employees, где EmployeeID соответствует входному параметру
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Выполнение хранимых процедур в MySQL

Самый распространенный способ выполнения хранимой процедуры – использование SQL-команд. В MySQL мы используем команду CALL для выполнения хранимой процедуры.

CALL ProcedureName();

При использовании определенной процедуры GetEmployeeDetails запрос на выполнение будет выглядеть следующим образом:

-- Выполните хранимую процедуру для получения информации о сотруднике с EmployeeID 101
CALL GetEmployeeDetails(101);

Хранимые процедуры в SQL Server

SQL Server предоставляет специфический синтаксис и команды для создания, выполнения и управления хранимыми процедурами. Этот подход упрощает создание эффективных и повторно используемых SQL-процедур, способных обрабатывать сложные задачи с минимальным повторением.

Создание хранимых процедур в SQL Server

Создание хранимой процедуры в SQL Server включает в себя определение имени процедуры, параметров и SQL-операторов, составляющих ее тело. В следующем примере создается процедура под названием GetEmployeeDetails, которая принимает @EmployeeID в качестве входного параметра и извлекает детали для этого конкретного сотрудника.

-- Создание процедуры для извлечения деталей по конкретному сотруднику по EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Входной параметр: ID сотрудника для извлечения
AS
BEGIN
    -- Выбор всех столбцов из Employees, где EmployeeID совпадает с входным параметром
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Выполнение хранимых процедур в SQL Server

В SQL Server команда EXEC или EXECUTE вызывает хранимую процедуру. В следующем примере показано, как выполнить хранимую процедуру GetEmployeeDetails, используя конкретные входные параметры.

-- Выполнение процедуры GetEmployeeDetails с установленным EmployeeID равным 102
EXEC GetEmployeeDetails @EmployeeID = 102;

Вы также можете выполнить выходные параметры, объявив переменную в команде. В следующем примере @TotalSales объявляется переменной для получения вывода из CalculateTotalSales. 

-- Объявите переменную для хранения общей суммы продаж
DECLARE @TotalSales DECIMAL(10, 2);
-- Выполните CalculateTotalSales для SalespersonID 5, сохраните результат в @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Отобразите общую сумму продаж
SELECT @TotalSales AS TotalSales;

Я рекомендую пройти наш курс Введение в SQL Server, чтобы понять различные функции SQL Server для запроса данных. Также рассмотрите наш полный карьерный трек Разработчик SQL Server, который не только даст вам навыки для создания, обновления и выполнения хранимых процедур, но и поможет вам с агрегатными функциями, объединением, вставкой и удалением таблиц и многим другим.

Общие способы использования хранимых процедур

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

Проверка данных и обеспечение целостности

Хранимые процедуры могут использоваться для проверки данных перед обновлением или вставкой. В приведенном ниже примере хранимая процедура проверяет, что электронная почта клиента уникальна перед вставкой новой записи в таблицу Customers, обеспечивая согласованность данных. Это централизует логику проверки в базе данных, уменьшая избыточность и обеспечивая единообразное выполнение в различных приложениях.

-- Создать процедуру для добавления нового клиента, проверяя наличие дублирующих адресов электронной почты
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Проверить, существует ли уже электронная почта в таблице Customers
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Выдать ошибку, если адрес электронной почты уже используется
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Вставить новые данные о клиенте, если электронная почта уникальна
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Автоматизированная обработка данных и отчетность

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

-- Создать процедуру для генерации ежедневного отчета о продажах
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Вставить сегодняшнюю дату и общие продажи в таблицу SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Выбрать текущую дату и сумму продаж за сегодня из таблицы Sales
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Управление транзакциями

С помощью хранимых процедур вы можете гарантировать, что несколько операций выполняются как одна транзакция. Например, в банковской системе хранимая процедура может обрабатывать как дебетовые, так и кредитовые действия при переводе средств, обеспечивая успешное или неуспешное выполнение обоих действий вместе.

-- Создать процедуру для перевода средств между счетами
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Начать транзакцию для обеспечения атомарности
    -- Вычесть указанную сумму из баланса счета отправителя
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Добавить указанную сумму к балансу счета получателя
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Проверить наличие ошибок и откатить транзакцию в случае их возникновения; в противном случае подтвердить транзакцию
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Отменить все изменения в случае ошибки
    ELSE
        COMMIT TRANSACTION;    -- Подтвердить изменения в случае отсутствия ошибок
END;

Управление доступом и безопасность данных

Вы также можете использовать хранимые процедуры SQL для контроля доступа к конфиденциальной информации. Например, хранимая процедура может ограничить прямой доступ к таблице, разрешив пользователям вызывать процедуру, которая извлекает только необходимые поля, такие как балансы счетов, без деталей транзакции.

-- Создать процедуру для получения баланса счета с проверкой авторизации
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Проверить, существует ли счет и принадлежит ли он указанному пользователю
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- При наличии авторизации выбрать и вернуть баланс счета
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- При отсутствии авторизации вызвать ошибку
        THROW 50000, 'Unauthorized access.', 1;
END;

Миграция данных и процессы ETL

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

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Извлечение
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Преобразование
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Загрузка
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Лучшие практики для хранимых процедур

Написание эффективных и поддерживаемых хранимых процедур обеспечивает оптимальную работу вашей базы данных. Вот несколько советов по написанию хранимых процедур для ваших SQL баз данных.

  • Используйте последовательные соглашения о наименовании: Чтобы сделать хранимые процедуры легко идентифицируемыми и понятными, используйте последовательный и описательный формат наименования. Также избегайте префикса sp_ в SQL Server, зарезервированного для системных процедур, чтобы избежать потенциальных конфликтов и проблем с производительностью.

  • Реализация обработки ошибок: Оборачивайте операторы SQL в блоки TRY...CATCH для перехвата и обработки ошибок, а также для поддержания целостности данных.

  • Оптимизация производительности: Минимизируйте использование курсоров, так как они могут быть медленными и требовательными к ресурсам. Вместо этого попробуйте использовать операции на основе множеств, которые обычно более эффективны. Также индексируйте часто используемые столбцы и избегайте сложных объединений в больших таблицах для уменьшения накладных расходов памяти и улучшения эффективности.

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

Ознакомьтесь с нашим курсом Продвинутый SQL, чтобы узнать больше о применении агрегатных функций и объединениях для фильтрации данных. Также попробуйте наши треки навыков Основы SQL Server и Основы SQL, чтобы улучшить ваши навыки объединения таблиц и анализа данных.

Заключение

Хранимые процедуры SQL улучшают повторное использование кода и оптимизацию производительности в управлении базами данных. Хранимые процедуры также улучшают безопасность баз данных через контролируемый доступ и обеспечение целостности данных. Как практикующий специалист по данным, я призываю вас практиковаться в создании и выполнении хранимых процедур, чтобы овладеть лучшими практиками управления базами данных.

Если вы заинтересованы в становлении опытным аналитиком данных, ознакомьтесь с нашим карьерным треком Ассоциированный аналитик данных в SQL, чтобы освоить необходимые навыки. Курс Отчётность в SQL также подходит, если вы хотите научиться создавать профессиональные дашборды с использованием SQL. Наконец, я рекомендую получить Сертификацию SQL Associate, чтобы продемонстрировать своё мастерство использования SQL для анализа данных и выделиться среди других специалистов по данным.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure