Procedimento armazenado em SQL: Automatize e otimize as consultas

Os procedimentos armazenados SQL são conjuntos de instruções SQL salvas e armazenadas em um banco de dados. Eles podem ser executados sob demanda para realizar tarefas de manipulação e validação de dados, reduzindo a necessidade de escrever códigos SQL repetitivos para operações comuns. Os procedimentos armazenados são úteis no gerenciamento de bancos de dados, pois promovem a eficiência e a reutilização. Além disso, eles oferecem suporte à segurança e à capacidade de manutenção aprimoradas do banco de dados. Neste artigo, discutiremos como criar e executar procedimentos armazenados em SQL, casos de uso comuns e práticas recomendadas.

Para começar, recomendo que você faça os cursos Introduction to SQL e Learn SQL da DataCamp para aprender os conhecimentos básicos de extração e análise de dados usando SQL. Além disso, a Folha de dicas básicas de SQL, que você pode baixar, é uma referência útil porque contém todas as funções SQL mais comuns.

O que é Stored Procedure no SQL?

Um procedimento armazenado em SQL é uma coleção de instruções SQL salvas e armazenadas no banco de dados. O objetivo do procedimento armazenado SQL é executar uma sequência de operações em um banco de dados, como consulta, inserção, atualização ou exclusão de dados.

Diferentemente das consultas SQL comuns, executadas como comandos separados, os procedimentos armazenados encapsulam um conjunto de instruções SQL, o que facilita a reutilização do código sem a necessidade de escrever comandos SQL repetidamente.

Os benefícios dos procedimentos armazenados do SQL incluem o seguinte:

• Reutilização de código: Depois que um procedimento armazenado é criado, ele pode ser chamado quantas vezes forem necessárias, eliminando a redundância no código SQL.
• Desempenho aprimorado: Os procedimentos armazenados geralmente são executados mais rapidamente porque são pré-compilados e armazenados no servidor de banco de dados, reduzindo a latência da rede e o tempo de compilação.
• Segurança: Os procedimentos armazenados podem aumentar a segurança dos dados e o controle sobre o acesso a dados confidenciais, concedendo aos usuários permissão para executar um procedimento armazenado sem acesso direto às tabelas.

Sintaxe e estrutura básicas

A sintaxe para criar um procedimento armazenado pode variar um pouco, dependendo do sistema de banco de dados (por exemplo, MySQL, SQL Server, Oracle). A seguir, você verá um exemplo geral usando a sintaxe do SQL Server:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

Na sintaxe acima;

• CRIAR PROCEDIMENTO: Esse comando é usado para definir um novo procedimento armazenado.

• ProcedureName: O nome dado ao procedimento armazenado. Ele deve ser exclusivo no banco de dados.

• @Parameter1, @Parameter2: Os parâmetros são opcionais; eles permitem que o procedimento receba entradas de dados. Cada parâmetro é definido com um símbolo @ e um tipo de dados (por exemplo, INT, VARCHAR(50)).

• AS BEGIN...END: As instruções SQL em BEGIN e END formam o corpo do procedimento, onde a lógica principal é executada. Neste exemplo, o procedimento recupera registros de uma tabela com base em condições específicas.

Parâmetros de entrada e saída

Os parâmetros de entrada e saída permitem que você passe valores de e para um procedimento armazenado.

Por exemplo, se @Parameter1 for definido como um parâmetro de entrada, você poderá atribuir a ele qualquer valor quando o procedimento for chamado, afetando a lógica ou a saída do SQL. No exemplo abaixo, o parâmetro @UserID recupera dados específicos para o UserID fornecido.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Os parâmetros de saída, definidos pela palavra-chave OUTPUT, permitem que um procedimento armazenado envie um valor de volta ao ambiente de chamada. Por exemplo, se um procedimento calcula um desconto, ele pode usar um parâmetro de saída para passá-lo de volta ao programa de chamada.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Para chamar esse procedimento, você usaria;

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;

Procedimentos armazenados no MySQL

Como mencionei, a execução de um procedimento armazenado no SQL pode ser feita de diferentes maneiras, dependendo do sistema de banco de dados e das ferramentas usadas.

Criando procedimentos armazenados no MySQL

A criação de um procedimento armazenado no MySQL envolve a definição do nome do procedimento, dos parâmetros e das instruções SQL que compõem seu corpo. O exemplo a seguir cria um procedimento chamado GetEmployeeDetails que recebe EmployeeID como parâmetro de entrada e recupera os detalhes desse funcionário específico.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Execução de procedimentos armazenados no MySQL

A maneira mais comum de executar um procedimento armazenado é usando comandos SQL. No MySQL, usamos o comando CALL para executar o procedimento armazenado.

CALL ProcedureName();

Usando o procedimento definido em GetEmployeeDetails, a consulta de execução teria a seguinte aparência:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);

Procedimentos armazenados no SQL Server

O SQL Server fornece sintaxe e comandos específicos para criar, executar e gerenciar procedimentos armazenados. Essa abordagem facilita a criação de rotinas SQL eficientes e reutilizáveis que podem lidar com tarefas complexas com o mínimo de repetição.

Criação de procedimentos armazenados no SQL Server

A criação de um procedimento armazenado no SQL Server envolve a definição do nome do procedimento, dos parâmetros e das instruções SQL que compõem seu corpo. O exemplo a seguir cria um procedimento chamado GetEmployeeDetails que recebe @EmployeeID como parâmetro de entrada e recupera os detalhes desse funcionário específico.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Execução de procedimentos armazenados no SQL Server

No SQL Server, o comando EXEC ou EXECUTE chama um procedimento armazenado. O exemplo a seguir mostra como você pode executar o procedimento armazenado GetEmployeeDetails usando parâmetros de entrada específicos. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;

Você também pode executar os parâmetros de saída declarando a variável no comando. No exemplo a seguir, @TotalSales é declarada uma variável para receber a saída de CalculateTotalSales. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;

Recomendo que você faça o curso Introdução ao SQL Server para entender as diferentes funcionalidades do SQL Server para consulta de dados. Além disso, considere nosso plano de carreira completo de Desenvolvedor do SQL Server, que não apenas equipará você com as habilidades para criar, atualizar e executar procedimentos armazenados, mas também o ajudará com funções agregadas, junção, inserção e exclusão de tabelas e muito mais.

Usos comuns de procedimentos armazenados

Os procedimentos armazenados SQL são úteis em cenários em que são necessárias tarefas complexas e repetitivas. A seguir, você encontrará aplicativos reais de procedimentos armazenados no gerenciamento de dados e nas operações comerciais. 

Validação de dados e aplicação de integridade

Os procedimentos armazenados podem ser usados para validar dados antes da atualização ou inserção. No exemplo abaixo, um procedimento armazenado verifica se o e-mail de um cliente é exclusivo antes de inserir um novo registro na tabela Customers, garantindo a consistência dos dados. Isso centraliza a lógica de validação no banco de dados, reduzindo a redundância e garantindo a aplicação uniforme em diferentes aplicativos.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Processamento de dados e relatórios automatizados

Você também pode usar procedimentos armazenados para gerar relatórios regulares ou processar grandes conjuntos de dados. Por exemplo, um procedimento armazenado poderia agregar dados de vendas diárias de uma plataforma de comércio eletrônico e armazená-los em uma tabela de relatórios, facilitando o acesso das equipes a insights de vendas sem a necessidade de executar consultas complexas.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Gerenciamento de transações

Ao usar procedimentos armazenados, você pode garantir que várias operações sejam executadas como uma única transação. Por exemplo, em um sistema bancário, um procedimento armazenado pode lidar com ações de débito e crédito em uma transferência de fundos, garantindo que ambas as ações sejam bem-sucedidas ou falhem juntas.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;

Controle de acesso e segurança de dados

Você também pode usar o SQL armazenado para controlar o acesso de dados a informações confidenciais. Por exemplo, um procedimento armazenado pode limitar o acesso direto à tabela, permitindo que os usuários chamem um procedimento que recupere apenas campos relevantes, como saldos de contas, sem detalhes de transações.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;

Migração de dados e processos de ETL

Os procedimentos armazenados também são usados para carregar, transformar e migrar dados entre sistemas. Um procedimento armazenado pode automatizar a extração de dados de um banco de dados de origem, transformá-los conforme necessário e inseri-los em uma tabela de destino, simplificando a integração de dados para relatórios ou análises.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Práticas recomendadas para procedimentos armazenados

Ao escrever procedimentos armazenados eficientes e de fácil manutenção, você garante que seu banco de dados tenha um desempenho ideal. A seguir, você encontrará dicas para escrever procedimentos armazenados para seus bancos de dados SQL.

• Use convenções de nomenclatura consistentes: Para que os procedimentos armazenados sejam fáceis de identificar e entender, use um formato de nomenclatura consistente e descritivo. Além disso, evite o prefixo sp_ no SQL Server reservado para procedimentos do sistema para evitar possíveis conflitos e problemas de desempenho.

• Implementar o tratamento de erros: Envolva as instruções SQL em blocos TRY...CATCH para capturar e tratar erros e manter a integridade dos dados. 

• Otimizar para desempenho: Minimize o uso de cursores, pois eles podem ser lentos e consumir muitos recursos. Em vez disso, tente usar operações baseadas em conjuntos, que geralmente são mais eficientes. Além disso, indexe as colunas usadas com frequência e evite uniões complexas em tabelas grandes para reduzir a sobrecarga de memória e aumentar a eficiência.

• Parametrizar procedimentos armazenados: Use parâmetros em vez de valores codificados para permitir que você passe valores dinâmicos para o seu procedimento, tornando-o mais flexível e reutilizável.

Confira nosso curso SQL Intermediário para saber mais sobre o uso de funções agregadas e junções para filtrar dados. Além disso, experimente nossas trilhas de habilidades SQL Server Fundamentals e SQL Fundamentals para aprimorar suas habilidades em tabelas de junção e análise de dados.

Conclusão

Os procedimentos armazenados SQL aumentam a reutilização do código e a otimização do desempenho no gerenciamento de bancos de dados. Os procedimentos armazenados também aumentam a segurança do banco de dados por meio do acesso controlado e da garantia da integridade dos dados. Como profissional de dados, incentivo você a praticar a criação e a execução de procedimentos armazenados para dominar as melhores práticas de gerenciamento de banco de dados.

Se você estiver interessado em se tornar um analista de dados proficiente, confira nosso curso de carreira Associate Data Analyst in SQL para aprender as habilidades necessárias. O curso Reporting in SQL também é adequado se você quiser aprender a criar painéis profissionais usando SQL. Por fim, recomendo que você obtenha a certificação SQL Associate para demonstrar que domina o uso do SQL para análise de dados e se destacar entre outros profissionais de dados.