Procedimento Armazenado SQL: Automatize e Otimize Consultas

Tutoriais
SQL

Procedimentos armazenados SQL são conjuntos de comandos SQL salvos e armazenados 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ódigo SQL repetitivo para operações comuns. Procedimentos armazenados são úteis na gestão de bancos de dados, promovendo eficiência e reutilização. Além disso, eles suportam uma maior segurança e manutenibilidade do banco de dados. Neste artigo, discutiremos como criar e executar procedimentos armazenados SQL, casos de uso comuns e melhores práticas.

Ao começar, eu recomendo fortemente fazer os cursos de Introdução ao SQL e Aprenda SQL da DataCamp para aprender os conhecimentos fundamentais de extração e análise de dados usando SQL. Além disso, o SQL Basics Cheat Sheet, que você pode baixar, é uma referência útil porque possui todas as funções SQL mais comuns.

O que é Procedimento Armazenado em SQL?

Um procedimento armazenado em SQL é uma coleção de instruções SQL salvas e armazenadas dentro do banco de dados. O objetivo do procedimento armazenado SQL é realizar uma sequência de operações em um banco de dados, como consultar, inserir, atualizar ou excluir dados.

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

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

  • Reutilização de código: Uma vez que um procedimento armazenado é criado, ele pode ser chamado quantas vezes forem necessárias, eliminando redundâncias no código SQL.
  • Desempenho aprimorado:Os procedimentos armazenados geralmente executam 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:Procedimentos armazenados podem melhorar a segurança de dados e o controle sobre o acesso a dados sensíveis, concedendo permissão aos usuários para executar um procedimento armazenado sem acesso direto às tabelas.

Sintaxe básica e estrutura

A sintaxe para criar um procedimento armazenado pode variar ligeiramente dependendo do sistema de banco de dados (por exemplo, MySQL, SQL Server, Oracle). Abaixo está um exemplo geral usando a sintaxe do SQL Server:

-- Criar um procedimento armazenado chamado NomeDoProcedimento
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- Declarações SQL vão aqui
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

No exemplo acima;

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

  • Nome do Procedimento: O nome atribuído ao procedimento armazenado. Deve ser único dentro do banco de dados.

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

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

Parâmetros de entrada e saída

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

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

-- Criar um procedimento para recuperar dados de um usuário específico pelo UserID
CREATE PROCEDURE GetUserData 
 -- Parâmetro de entrada: ID do usuário a ser recuperado
    @UserID INT 
AS
BEGIN
    -- Selecionar todas as colunas de Usuários onde o UserID corresponde ao parâmetro de entrada
    SELECT * FROM Users WHERE UserID = @UserID;
END;

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 calcular um desconto, ele poderia usar um parâmetro de saída para enviá-lo de volta ao programa chamador.

-- Criar um procedimento para calcular o preço com desconto
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Saída: preço final após o desconto
AS
BEGIN
    -- Calcular o preço final aplicando a taxa de desconto ao preço original
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Para chamar este procedimento, você usaria;

-- Declare uma variável para armazenar o preço final após o desconto
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute o procedimento CalculateDiscount com um preço de 100 e um desconto de 10%
-- Armazene a saída na variável @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Selecione e exiba o preço final com desconto
SELECT @FinalPrice AS FinalPrice;

Procedimentos Armazenados no MySQL

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

Criando procedimentos armazenados no MySQL

Criar um procedimento armazenado no MySQL envolve definir o nome do procedimento, os parâmetros e as instruções SQL que compõem seu corpo. O seguinte exemplo cria um procedimento chamado GetEmployeeDetails que recebe EmployeeID como parâmetro de entrada e recupera detalhes para esse funcionário específico.

DELIMITER $
-- Criar um procedimento para recuperar detalhes de um funcionário específico por EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Selecionar todas as colunas de Employees onde EmployeeID corresponde ao parâmetro de entrada
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Executando stored procedures no MySQL

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

CALL ProcedureName();

Usando o procedimento definido GetEmployeeDetails, a query de execução ficaria assim:

-- Executar o stored procedure para recuperar detalhes do EmployeeID 101
CALL GetEmployeeDetails(101);

Stored Procedures no SQL Server

O SQL Server fornece uma sintaxe e comandos específicos para criar, executar e gerenciar stored procedures. Esse enfoque facilita a construção de rotinas SQL eficientes e reutilizáveis que podem lidar com tarefas complexas com repetição mínima.

Criando stored procedures no SQL Server

Criar um procedimento armazenado no SQL Server envolve definir o nome do procedimento, parâmetros e as instruções SQL que compõem seu corpo. O seguinte exemplo cria um procedimento chamado GetEmployeeDetails que recebe @EmployeeID como um parâmetro de entrada e recupera detalhes para esse funcionário específico.

-- Criar um procedimento para recuperar detalhes de um funcionário específico pelo EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Parâmetro de entrada: ID do funcionário a ser recuperado
AS
BEGIN
    -- Selecionar todas as colunas de Employees onde EmployeeID corresponde ao parâmetro de entrada
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Executando procedimentos armazenados no SQL Server

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

-- Executar o procedimento GetEmployeeDetails com o EmployeeID definido como 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 é declarado como uma variável para receber a saída de CalculateTotalSales.

-- Declare uma variável para armazenar o valor total das vendas
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales para SalespersonID 5, armazene o resultado em @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Exiba o valor total das vendas
SELECT @TotalSales AS TotalSales;

Eu recomendo fazer nosso Introdução ao SQL Server curso para entender as diferentes funcionalidades do SQL Server para consultar dados. Além disso, considere nossa trilha completa de carreira em Desenvolvedor SQL Server, que não apenas o equipará com as habilidades para criar, atualizar e executar procedimentos armazenados, mas também o ajudará com funções agregadas, junções, inserções e exclusões de tabelas, e muito mais.

Usos Comuns para Procedimentos Armazenados

Procedimentos armazenados SQL são úteis em cenários onde tarefas complexas e repetitivas são necessárias. As seguintes são aplicações do mundo real de procedimentos armazenados em gerenciamento de dados e operações comerciais.

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

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 é único antes de inserir um novo registro na tabela Clientes, 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 aplicações.

-- Criar um procedimento para adicionar um novo cliente, verificando e-mail duplicado
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Verificar se o e-mail já existe na tabela Clientes
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Lançar um erro se o e-mail já estiver em uso
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Inserir os detalhes do novo cliente se o e-mail for único
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Processamento de dados automatizado e geração de relatórios

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 precisar executar consultas complexas.

-- Crie um procedimento para gerar um relatório de vendas diário
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insira a data de hoje e o total de vendas na tabela SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Selecione a data atual e a soma das vendas de hoje da tabela Sales
    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 tanto com ações de débito quanto de crédito em uma transferência de fundos, garantindo que ambas as ações tenham sucesso ou falhem juntas.

-- Criar um procedimento para transferir fundos entre contas
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Iniciar uma transação para garantir atomicidade
    -- Deduzir o valor especificado do saldo da conta do remetente
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Adicionar o valor especificado ao saldo da conta do destinatário
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Verificar erros e reverter se houver algum; caso contrário, confirmar a transação
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Desfazer todas as alterações se ocorrer um erro
    ELSE
        COMMIT TRANSACTION;    -- Confirmar as alterações se não houver erros
END;

Controle de acesso e segurança de dados

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

-- Criar um procedimento para recuperar o saldo da conta, com verificação de autorização
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Verificar se a conta existe e pertence ao usuário especificado
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Se autorizado, selecionar e retornar o saldo da conta
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Se não autorizado, lançar um erro
        THROW 50000, 'Unauthorized access.', 1;
END;

Migração de dados e processos ETL

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álise.

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

Melhores Práticas para Procedimentos Armazenados

Escrever procedimentos armazenados eficientes e de fácil manutenção garante que seu banco de dados funcione de forma otimizada. A seguir estão as dicas para escrever procedimentos armazenados para seus bancos de dados SQL.

  • Utilize Convenções de Nomenclatura Consistentes: Para tornar os procedimentos armazenados fáceis de identificar e entender, utilize 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 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 frequentemente usadas e evite junções complexas em tabelas grandes para reduzir a sobrecarga de memória e melhorar a eficiência.

  • Parametrize Procedimentos Armazenados: Utilize parâmetros em vez de valores codificados diretamente para permitir que você passe valores dinâmicos para o seu procedimento, tornando-o mais flexível e reutilizável.

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

Conclusão

Os procedimentos armazenados em SQL melhoram a reutilização de código e a otimização de desempenho na gestão de bancos de dados. Os procedimentos armazenados também aumentam a segurança do banco de dados por meio de acesso controlado e garantem a integridade dos dados. Como profissional de dados, recomendo que você pratique a criação e execução de procedimentos armazenados para dominar as melhores práticas de gestão de banco de dados.

Se você tem interesse em se tornar um analista de dados proficiente, confira nossa trilha de carreira Associate Data Analyst in SQL para aprender as habilidades necessárias. O curso Reporting in SQL também é apropriado se você deseja aprender a construir painéis profissionais usando SQL. Por fim, eu recomendo obter a SQL Associate Certification para demonstrar sua maestria em utilizar SQL para análise de dados e se destacar entre outros profissionais de dados.

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