Noções Básicas de Stored Procedures no MySQL

Introdução

Neste laboratório, você aprenderá os fundamentos dos stored procedures (procedimentos armazenados) do MySQL. O objetivo é entender como criar, chamar e modificar stored procedures para gerenciar dados dentro de um banco de dados MySQL.

Você começará criando um banco de dados e uma tabela chamada employees. Em seguida, escreverá um stored procedure chamado insert_employee para inserir dados na tabela employees. Você aprenderá como chamar este procedimento usando a instrução CALL e como adicionar parâmetros de entrada ao procedimento. Finalmente, você aprenderá como remover o procedimento usando a instrução DROP PROCEDURE.

Observação: Para este laboratório, você só precisa entrar no shell do MySQL uma vez no início e sair no final. Todos os comandos SQL nas etapas seguintes devem ser executados dentro da mesma sessão do MySQL. Não há necessidade de conectar ou desconectar repetidamente do MySQL entre as etapas.

Criar um Procedimento para Inserir Dados

Nesta etapa, você aprenderá como criar um stored procedure no MySQL que insere dados em uma tabela. Stored procedures são instruções SQL pré-compiladas armazenadas dentro do banco de dados. Elas podem ser executadas por nome, o que pode melhorar o desempenho e a segurança.

Primeiro, abra seu terminal e conecte-se ao servidor MySQL usando o seguinte comando:

sudo mysql -u root

Este comando conecta-se ao servidor MySQL como o usuário root. Mantenha esta sessão do MySQL aberta para todas as etapas subsequentes.

Uma vez conectado, você estará no shell do MySQL. Agora, mude para o banco de dados testdb que foi criado durante a configuração:

USE testdb;

Agora que estamos no banco de dados correto, vamos criar um stored procedure para inserir dados na tabela employees. Um stored procedure é criado usando a instrução CREATE PROCEDURE. Definiremos um procedimento chamado insert_employee que insere um novo registro de funcionário.

Aqui está o código SQL para o stored procedure:

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

Vamos analisar este código:

• DELIMITER //: Isso muda o delimitador de instrução de ; para //. Isso é necessário porque o próprio procedimento contém pontos e vírgulas, e precisamos dizer ao MySQL para tratar toda a definição do procedimento como uma única instrução.
• CREATE PROCEDURE insert_employee: Isso declara a criação de um stored procedure chamado insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): Isso define os parâmetros de entrada para o procedimento. employee_name e employee_department são os nomes dos parâmetros, e VARCHAR(255) é o tipo de dado deles. A palavra-chave IN indica que estes são parâmetros de entrada.
• BEGIN ... END: Este bloco contém as instruções SQL que serão executadas quando o procedimento for chamado.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: Esta é a instrução SQL que insere uma nova linha na tabela employees, usando os valores passados como parâmetros de entrada.
• DELIMITER ;: Isso redefine o delimitador de instrução de volta para ;.

Para executar este código, copie e cole-o diretamente no seu shell MySQL.

Após executar o código, você pode verificar se o procedimento foi criado executando o seguinte comando no shell MySQL:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Este comando exibirá informações sobre o procedimento insert_employee, incluindo seu nome, banco de dados e data de criação.

Você agora criou com sucesso um stored procedure para inserir dados na tabela employees. Na próxima etapa, você aprenderá como chamar este procedimento.

Chamar o Procedimento com a Instrução CALL

Na etapa anterior, você criou um stored procedure chamado insert_employee. Nesta etapa, você aprenderá como chamar este procedimento usando a instrução CALL.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados testdb. Se não estiver, mude para ele com:

USE testdb;

A instrução CALL é usada para executar um stored procedure. A sintaxe é a seguinte:

CALL nome_do_procedimento(argumento1, argumento2, ...);

No nosso caso, o nome do procedimento é insert_employee, e ele recebe dois argumentos: o nome do funcionário e o departamento do funcionário.

Vamos chamar o procedimento insert_employee para inserir um novo funcionário chamado "Alice Smith" no departamento "Engineering":

CALL insert_employee('Alice Smith', 'Engineering');

Esta instrução executará o procedimento insert_employee com os argumentos especificados.

Para verificar se os dados foram inseridos corretamente, você pode consultar a tabela employees no shell MySQL:

SELECT * FROM employees;

Você deverá ver uma nova linha na tabela com o nome "Alice Smith" e o departamento "Engineering". O id será atribuído automaticamente.

Vamos inserir outro funcionário, "Bob Johnson" no departamento "Marketing":

CALL insert_employee('Bob Johnson', 'Marketing');

Novamente, verifique a inserção consultando a tabela employees:

SELECT * FROM employees;

Você deverá agora ver duas linhas na tabela, uma para "Alice Smith" e outra para "Bob Johnson".

Você agora chamou com sucesso o stored procedure insert_employee usando a instrução CALL e verificou que os dados foram inseridos corretamente. Isso demonstra como os stored procedures podem ser usados para encapsular e reutilizar lógica SQL.

Adicionar um Parâmetro de Entrada ao Procedimento

Nas etapas anteriores, você criou e chamou um stored procedure chamado insert_employee que aceita dois parâmetros de entrada: employee_name e employee_department. Nesta etapa, você aprenderá como adicionar outro parâmetro de entrada ao procedimento.

Vamos adicionar um parâmetro employee_salary ao procedimento insert_employee. Isso nos permitirá especificar o salário do funcionário ao inserir um novo registro.

Primeiro, você precisa remover o procedimento existente. Se você não o remover, receberá um erro ao tentar criar um procedimento com o mesmo nome. No seu shell MySQL, execute:

DROP PROCEDURE IF EXISTS insert_employee;

Agora, vamos criar o stored procedure modificado com o novo parâmetro de entrada.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

Vamos analisar as alterações:

• Adicionamos um novo parâmetro de entrada IN employee_salary DECIMAL(10, 2) à definição do procedimento. DECIMAL(10, 2) é o tipo de dado para o salário, que permite até 10 dígitos com 2 casas decimais.
• Modificamos a instrução INSERT para incluir a coluna salary e o parâmetro employee_salary.

Agora, vamos chamar o procedimento modificado insert_employee para inserir um novo funcionário chamado "Charlie Brown" no departamento "Finance" com um salário de 60000.00:

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

Para verificar se os dados foram inseridos corretamente, você pode consultar a tabela employees no shell MySQL:

SELECT * FROM employees;

Você deverá ver uma nova linha na tabela com o nome "Charlie Brown", o departamento "Finance" e o salário 60000.00.

Você agora adicionou com sucesso um parâmetro de entrada ao stored procedure insert_employee e verificou que os dados foram inseridos corretamente. Isso demonstra como os stored procedures podem ser modificados para acomodar novos requisitos.

Remover o Procedimento

Nesta etapa final, você aprenderá como remover (excluir) um stored procedure do banco de dados. Remover um procedimento o retira do banco de dados, tornando-o indisponível para execução.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados testdb.

A instrução DROP PROCEDURE é usada para remover um stored procedure. A sintaxe é a seguinte:

DROP PROCEDURE [IF EXISTS] nome_do_procedimento;

A cláusula IF EXISTS é opcional, mas recomendada. Ela evita que um erro ocorra caso o procedimento não exista.

No nosso caso, o nome do procedimento é insert_employee. Vamos remover o procedimento:

DROP PROCEDURE IF EXISTS insert_employee;

Esta instrução removerá o procedimento insert_employee do banco de dados testdb.

Para verificar se o procedimento foi removido, você pode tentar mostrar o status do procedimento novamente no shell MySQL:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Este comando deverá retornar um conjunto de resultados vazio, indicando que o procedimento não existe mais.

Alternativamente, se você tentar chamar o procedimento, receberá um erro:

CALL insert_employee('Test', 'Test', 1000);

Isso resultará em uma mensagem de erro semelhante a: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Você agora removeu com sucesso o stored procedure insert_employee.

Agora você pode sair do shell MySQL digitando:

exit

Isso conclui o exercício prático sobre criação, chamada, modificação e remoção de stored procedures no MySQL.

Resumo

Neste laboratório, você aprendeu os conceitos básicos de stored procedures do MySQL, começando pela criação de um banco de dados e uma tabela chamada employees. Em seguida, você definiu um stored procedure chamado insert_employee usando a instrução CREATE PROCEDURE, que insere dados na tabela employees. O comando DELIMITER foi usado para lidar com pontos e vírgulas dentro da definição do procedimento.

O laboratório também abordou como definir parâmetros de entrada para o stored procedure, especificando seus nomes e tipos de dados. Isso permite passar valores para o procedimento quando ele é chamado, tornando-o mais flexível e reutilizável. Você praticou a chamada do stored procedure usando a instrução CALL e verificou a inserção dos dados. Finalmente, você aprendeu como remover um stored procedure usando a instrução DROP PROCEDURE.