Conceptos Básicos de Procedimientos Almacenados en MySQL

Introducción

En este laboratorio, aprenderá los conceptos básicos de los procedimientos almacenados de MySQL. El objetivo es comprender cómo crear, llamar y modificar procedimientos almacenados para administrar datos dentro de una base de datos MySQL.

Comenzará creando una base de datos y una tabla llamada employees. Luego, escribirá un procedimiento almacenado llamado insert_employee para insertar datos en la tabla employees. Aprenderá a llamar a este procedimiento utilizando la sentencia CALL y cómo agregar parámetros de entrada al procedimiento. Finalmente, aprenderá a eliminar el procedimiento utilizando la sentencia DROP PROCEDURE.

Nota: Para este laboratorio, solo necesita ingresar al shell de MySQL una vez al principio y salir al final. Todos los comandos SQL en los siguientes pasos deben ejecutarse dentro de la misma sesión de MySQL. No es necesario conectarse o desconectarse repetidamente de MySQL entre pasos.

Crear un Procedimiento para Insertar Datos

En este paso, aprenderá cómo crear un procedimiento almacenado en MySQL que inserta datos en una tabla. Los procedimientos almacenados son sentencias SQL precompiladas almacenadas dentro de la base de datos. Pueden ejecutarse por nombre, lo que puede mejorar el rendimiento y la seguridad.

Primero, abra su terminal y conéctese al servidor MySQL usando el siguiente comando:

sudo mysql -u root

Este comando se conecta al servidor MySQL como el usuario root. Mantenga esta sesión de MySQL abierta para todos los pasos subsiguientes.

Una vez conectado, estará en el shell de MySQL. Ahora, cambie a la base de datos testdb que se creó durante la configuración:

USE testdb;

Ahora que estamos en la base de datos correcta, creemos un procedimiento almacenado para insertar datos en la tabla employees. Un procedimiento almacenado se crea utilizando la sentencia CREATE PROCEDURE. Definiremos un procedimiento llamado insert_employee que inserta un nuevo registro de empleado.

Aquí está el código SQL para el procedimiento almacenado:

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 ;

Analicemos este código:

• DELIMITER //: Esto cambia el delimitador de sentencia de ; a //. Esto es necesario porque el procedimiento en sí contiene puntos y comas, y necesitamos indicarle a MySQL que trate toda la definición del procedimiento como una sola sentencia.
• CREATE PROCEDURE insert_employee: Esto declara la creación de un procedimiento almacenado llamado insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): Esto define los parámetros de entrada para el procedimiento. employee_name y employee_department son los nombres de los parámetros, y VARCHAR(255) es su tipo de dato. La palabra clave IN indica que estos son parámetros de entrada.
• BEGIN ... END: Este bloque contiene las sentencias SQL que se ejecutarán cuando se llame al procedimiento.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: Esta es la sentencia SQL que inserta una nueva fila en la tabla employees, utilizando los valores pasados como parámetros de entrada.
• DELIMITER ;: Esto restablece el delimitador de sentencia de nuevo a ;.

Para ejecutar este código, cópielo y péguelo directamente en su shell de MySQL.

Después de ejecutar el código, puede verificar que el procedimiento se ha creado ejecutando el siguiente comando en el shell de MySQL:

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

Este comando mostrará información sobre el procedimiento insert_employee, incluyendo su nombre, base de datos y fecha de creación.

Ahora ha creado con éxito un procedimiento almacenado para insertar datos en la tabla employees. En el siguiente paso, aprenderá cómo llamar a este procedimiento.

Llamar al Procedimiento con la Sentencia CALL

En el paso anterior, creó un procedimiento almacenado llamado insert_employee. En este paso, aprenderá cómo llamar a este procedimiento utilizando la sentencia CALL.

Recordatorio: Todavía debería estar en el shell de MySQL y utilizando la base de datos testdb. Si no es así, cámbiela con:

USE testdb;

La sentencia CALL se utiliza para ejecutar un procedimiento almacenado. La sintaxis es la siguiente:

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

En nuestro caso, el nombre del procedimiento es insert_employee, y toma dos argumentos: el nombre del empleado y el departamento del empleado.

Llamemos al procedimiento insert_employee para insertar un nuevo empleado llamado "Alice Smith" en el departamento "Engineering":

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

Esta sentencia ejecutará el procedimiento insert_employee con los argumentos especificados.

Para verificar que los datos se han insertado correctamente, puede consultar la tabla employees en el shell de MySQL:

SELECT * FROM employees;

Debería ver una nueva fila en la tabla con el nombre "Alice Smith" y el departamento "Engineering". El id se asignará automáticamente.

Insertemos otro empleado, "Bob Johnson" en el departamento "Marketing":

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

Nuevamente, verifique la inserción consultando la tabla employees:

SELECT * FROM employees;

Ahora debería ver dos filas en la tabla, una para "Alice Smith" y otra para "Bob Johnson".

Ahora ha llamado con éxito al procedimiento almacenado insert_employee utilizando la sentencia CALL y ha verificado que los datos se han insertado correctamente. Esto demuestra cómo se pueden utilizar los procedimientos almacenados para encapsular y reutilizar la lógica SQL.

Añadir un Parámetro de Entrada al Procedimiento

En los pasos anteriores, creó y llamó a un procedimiento almacenado llamado insert_employee que toma dos parámetros de entrada: employee_name y employee_department. En este paso, aprenderá cómo añadir otro parámetro de entrada al procedimiento.

Añadamos un parámetro employee_salary al procedimiento insert_employee. Esto nos permitirá especificar el salario del empleado al insertar un nuevo registro.

Primero, necesita eliminar el procedimiento existente. Si no lo elimina, obtendrá un error al intentar crear un procedimiento con el mismo nombre. En su shell de MySQL, ejecute:

DROP PROCEDURE IF EXISTS insert_employee;

Ahora, creemos el procedimiento almacenado modificado con el nuevo 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 ;

Analicemos los cambios:

• Añadimos un nuevo parámetro de entrada IN employee_salary DECIMAL(10, 2) a la definición del procedimiento. DECIMAL(10, 2) es el tipo de dato para el salario, que permite hasta 10 dígitos con 2 decimales.
• Modificamos la sentencia INSERT para incluir la columna salary y el parámetro employee_salary.

Ahora, llamemos al procedimiento modificado insert_employee para insertar un nuevo empleado llamado "Charlie Brown" en el departamento "Finance" con un salario de 60000.00:

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

Para verificar que los datos se han insertado correctamente, puede consultar la tabla employees en el shell de MySQL:

SELECT * FROM employees;

Debería ver una nueva fila en la tabla con el nombre "Charlie Brown", el departamento "Finance" y el salario 60000.00.

Ahora ha añadido con éxito un parámetro de entrada al procedimiento almacenado insert_employee y ha verificado que los datos se han insertado correctamente. Esto demuestra cómo se pueden modificar los procedimientos almacenados para adaptarse a nuevos requisitos.

Eliminar el Procedimiento

En este paso final, aprenderá cómo eliminar (borrar) un procedimiento almacenado de la base de datos. Eliminar un procedimiento lo remueve de la base de datos, haciéndolo ya no disponible para su ejecución.

Recordatorio: Todavía debería estar en el shell de MySQL y utilizando la base de datos testdb.

La sentencia DROP PROCEDURE se utiliza para remover un procedimiento almacenado. La sintaxis es la siguiente:

DROP PROCEDURE [IF EXISTS] nombre_procedimiento;

La cláusula IF EXISTS es opcional pero recomendada. Evita que ocurra un error si el procedimiento no existe.

En nuestro caso, el nombre del procedimiento es insert_employee. Eliminemos el procedimiento:

DROP PROCEDURE IF EXISTS insert_employee;

Esta sentencia removerá el procedimiento insert_employee de la base de datos testdb.

Para verificar que el procedimiento ha sido eliminado, puede intentar mostrar el estado del procedimiento nuevamente en el shell de MySQL:

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

Este comando debería devolver un conjunto de resultados vacío, indicando que el procedimiento ya no existe.

Alternativamente, si intenta llamar al procedimiento, obtendrá un error:

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

Esto resultará en un mensaje de error similar a: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Ahora ha eliminado con éxito el procedimiento almacenado insert_employee.

Ahora puede salir del shell de MySQL escribiendo:

exit

Esto concluye el ejercicio de laboratorio sobre la creación, llamada, modificación y eliminación de procedimientos almacenados en MySQL.

Resumen

En este laboratorio, aprendió los conceptos básicos de los procedimientos almacenados de MySQL, comenzando con la creación de una base de datos y una tabla llamada employees. Luego definió un procedimiento almacenado llamado insert_employee utilizando la sentencia CREATE PROCEDURE, que inserta datos en la tabla employees. Se utilizó el comando DELIMITER para manejar los puntos y comas dentro de la definición del procedimiento.

El laboratorio también cubrió cómo definir parámetros de entrada para el procedimiento almacenado, especificando sus nombres y tipos de datos. Esto le permite pasar valores al procedimiento cuando se llama, haciéndolo más flexible y reutilizable. Practicó la llamada al procedimiento almacenado utilizando la sentencia CALL y verificó la inserción de datos. Finalmente, aprendió cómo eliminar un procedimiento almacenado utilizando la sentencia DROP PROCEDURE.