Procedimento Armazenado SQL: Automatizar e Otimizar Consultas

Tutoriais
SQL

Procedimentos armazenados SQL são conjuntos de declarações 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 oferecem suporte à segurança aprimorada do banco de dados e à manutenibilidade. Neste artigo, discutiremos como criar e executar procedimentos armazenados SQL, casos de uso comuns e melhores práticas.

Ao começarmos, recomendo fortemente fazer os cursos Introdução ao SQL e Aprenda SQL da DataCamp para aprender o conhecimento básico de extração e análise de dados usando SQL. Além disso, o Guia Rápido de Conceitos Básicos de SQL, que você pode baixar, é uma referência útil porque contém todas as funções SQL mais comuns.

O que é uma Stored Procedure em SQL?

Uma stored procedure em SQL é uma coleção de instruções SQL salvas e armazenadas dentro do banco de dados. O propósito da stored procedure SQL é realizar uma sequência de operações em um banco de dados, como consultar, inserir, atualizar ou excluir dados.

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

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

  • Reutilização de código: Uma vez que um procedimento armazenado é criado, ele pode ser chamado tantas vezes quanto necessário, eliminando a redundância no código SQL.
  • Desempenho aprimorado: Os procedimentos armazenados frequentemente executam mais rapidamente porque são precompilados e armazenados no servidor de banco de dados, reduzindo a latência de rede e o tempo de compilação.
  • Segurança: Procedimentos armazenados podem melhorar a segurança dos dados e o controle sobre o acesso a dados sensíveis, concedendo aos usuários permissão para executar um procedimento armazenado sem acesso direto às tabelas.

Sintaxe e estrutura básica

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 ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- As instruções SQL vão aqui
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

Na sintaxe acima;

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

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

  • @Parâmetro1, @Parâmetro2: 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)).

  • COMO COMEÇAR…FIM: 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 passar valores para e de um procedimento armazenado.

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

-- Criar uma procedure 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 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 uma procedure armazenada envie um valor de volta ao ambiente que a chamou. Por exemplo, se uma procedure calcula um desconto, ela poderia usar um parâmetro de saída para passá-lo de volta ao programa chamador.

-- Criar uma procedure 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 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 em MySQL

Como mencionei, executar um procedimento armazenado em SQL pode ser feito de diferentes maneiras, dependendo do sistema de banco de dados e das ferramentas utilizadas.

Criando procedimentos armazenados em MySQL

Criar um procedimento armazenado em MySQL 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.

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

Executando 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 GetEmployeeDetails, a consulta de execução seria assim:

-- Executar o procedimento armazenado para obter detalhes do EmployeeID 101
CALL GetEmployeeDetails(101);

Procedimentos Armazenados no SQL Server

O SQL Server fornece uma sintaxe e comandos específicos para criar, executar e gerenciar procedimentos armazenados. Essa abordagem torna mais fácil construir rotinas SQL eficientes e reutilizáveis que podem lidar com tarefas complexas com repetição mínima.

Criando procedimentos armazenados no SQL Server

Criar um procedimento armazenado no SQL Server envolve definir o nome do procedimento, os parâmetros e as instruções SQL que compõem seu corpo. O exemplo a seguir 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 exemplo a seguir 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 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;
-- Exibir o valor total das vendas
SELECT @TotalSales AS TotalSales;

Recomendo fazer nosso curso Introdução ao SQL Server para entender as diferentes funcionalidades do SQL Server para consultas de dados. Além disso, considere nossa trilha de carreira completa de Desenvolvedor SQL Server, que não só irá equipá-lo com as habilidades para criar, atualizar e executar procedimentos armazenados, mas também ajudará com funções de agregação, junção, inserção e exclusão de tabelas, e muito mais.

Usos Comuns para Procedimentos Armazenados

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

Validação de dados e imposiçã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 Customers, garantindo a consistência dos dados. Isso centraliza a lógica de validação dentro do banco de dados, reduzindo a redundância e assegurando 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 Customers
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Gerar 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 e relatórios de dados automatizados

Você também pode usar procedimentos armazenados para gerar relatórios regulares ou processar grandes conjuntos de dados. Por exemplo, um procedimento armazenado pode agregar dados de vendas diárias de uma plataforma de e-commerce 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.

-- 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 gerenciar tanto ações de débito quanto de crédito em uma transferência de fundos, garantindo que ambas as ações sejam bem-sucedidas ou falhem juntas.

-- Crie um procedimento para transferir fundos entre contas
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Inicie uma transação para garantir a 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;
    -- Verifique se há erros e reverta se ocorreram; caso contrário, confirme a transação
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Desfaça todas as alterações se um erro ocorrer
    ELSE
        COMMIT TRANSACTION;    -- Confirme as alterações se não houver erros
END;

Controle de acesso e segurança de dados

Você também pode usar procedimentos armazenados SQL para controlar o acesso a dados 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 da transação.

-- Crie um procedimento para recuperar o saldo da conta, com verificação de autorização
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Verifique se a conta existe e é de propriedade do usuário especificado
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Se autorizado, selecione e retorne o saldo da conta
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Se não autorizado, lance 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á-lo conforme necessário e inseri-lo em uma tabela de destino, simplificando a integração de dados para relatórios ou análises.

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

Práticas recomendadas para Procedimentos Armazenados

Escrever procedimentos armazenados eficientes e fáceis de manter garante que seu banco de dados tenha um desempenho ótimo. Abaixo estão algumas dicas para escrever procedimentos armazenados para seus bancos de dados SQL.

  • Usar 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 lidar com erros e manter a integridade dos dados. 

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

  • Parametrize 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 aprender mais sobre o uso de funções de agregação e junções para filtrar dados. Experimente também nossas trilhas de habilidades Fundamentos do SQL Server e SQL Fundamentals para melhorar suas habilidades de junção de tabelas e análise de dados.

Conclusão

Procedimentos armazenados em SQL aprimoram a reutilização de código e a otimização de desempenho em gestão de banco de dados. Os procedimentos armazenados também aprimoram a segurança do banco de dados por meio de acesso controlado e garantia da integridade dos dados. Como praticante de dados, eu o encorajo a praticar a criação e execução de procedimentos armazenados para dominar as melhores práticas de gestão de banco de dados.

Se você está interessado em se tornar um analista de dados proficiente, confira nossa trilha de carreira Analista de Dados Associado em SQL para aprender as habilidades necessárias. O curso Relatórios em SQL também é apropriado se você deseja aprender como construir painéis profissionais usando SQL. Por fim, recomendo obter a Certificação Associada em SQL para demonstrar seu domínio no uso de SQL para análise de dados e se destacar entre outros profissionais de dados.

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