Si está buscando una forma más eficiente de administrar las tareas de su base de datos, tenga la seguridad de que no está solo. Pero bueno, ¡has aterrizado en el lugar perfecto! Liberar el poder de los procedimientos almacenados de MySQL es la puerta de entrada para remodelar la gestión de su base de datos.

En este tutorial, aprenderá cómo crear y utilizar procedimientos almacenados MySQL para administrar y ejecutar operaciones de bases de datos de manera eficiente. No estás simplemente abordando un problema; te estás transformando en un pez gordo de la base de datos.

¡Sumérgete en las complejidades, experimenta y observa cómo las tareas de tu base de datos se alinean perfectamente!

Requisitos previos

Asegúrese de tener lo siguiente implementado antes de profundizar en los detalles del uso de procedimientos almacenados de MySQL:

• Un servidor que admita MySQL: este tutorial utiliza Ubuntu 20.04 para demostraciones.

Preparar una base de datos de muestra

Los procedimientos almacenados son potentes objetos de base de datos que encapsulan una serie de declaraciones SQL en una unidad reutilizable y manejable. Se almacenan en un servidor de base de datos y pueden ser invocados por aplicaciones o scripts.

Pero antes de sumergirse en las complejidades de los procedimientos almacenados de MySQL, comience sentando las bases con un toque práctico: su base de datos de muestra. En este tutorial, creará una base de datos de muestra que servirá como base para varios ejemplos.

Para preparar una base de datos de muestra, siga estos pasos:

1. Conéctese a su servidor MySQL.

2. Una vez conectado, ejecute la siguiente consulta para CREATE un nuevo DATABASE llamado inventory (arbitrario). Esta base de datos almacena información sobre productos electrónicos y su respectivo inventario.

CREATE DATABASE inventory;

3. A continuación, ejecute el siguiente comando para cambiar (USE) a la base de datos recién creada (inventory).

4. Con la base de datos en su lugar, ejecute la siguiente consulta para CREATE un solo TABLE llamado electronics (arbitrario) para contener información sobre productos electrónicos.

CREATE TABLE electronics (
    brand varchar(100),
    type varchar(100),
    release_year int,
    price decimal(10, 2)
);

5. Por último, ejecute las consultas siguientes para INSERT Data de muestra INTO su electronics mesa.

INSERT INTO electronics
VALUES
('Sony', 'PlayStation 5', 2020, 499.99),
('Sony', 'Bravia OLED', 2021, 1899.99),
('Apple', 'iPhone 12', 2020, 799.00),
('Apple', 'iPad Pro', 2021, 999.00),
('Samsung', 'Galaxy S21', 2021, 699.99),
('Samsung', 'Galaxy Tab S7', 2020, 649.99),
('LG', 'Gram Laptop', 2021, 1199.99),
('LG', 'UltraGear Monitor', 2020, 399.99),
('Dell', 'XPS 13', 2020, 999.99),
('Dell', 'Alienware M15', 2021, 1499.99);

Crear una forma básica de procedimientos almacenados MySQL

Ahora que su base de datos de muestra está lista y esperando acción, cambie sin problemas al corazón de la magia de MySQL: la creación de procedimientos almacenados de MySQL.

Los procedimientos almacenados ofrecen numerosas ventajas en la gestión de bases de datos y el desarrollo de aplicaciones. Un ejemplo es la racionalización de tareas complejas de bases de datos en componentes eficientes y manejables.

Para crear un procedimiento almacenado MySQL, realice lo siguiente:

1. Ejecute la siguiente consulta para definir una forma básica de un procedimiento almacenado llamado get_all_electronics (arbitrario).

Este procedimiento no tiene parámetros (aprenderá sobre los parámetros más adelante) y recupera todos los registros del electronics mesa. Los registros recibidos están en orden ascendente por brand y descendente (DESC) ordenar por price.

-- Set a custom delimiter to avoid conflicts in the stored procedure definition
DELIMITER //

-- Create a stored procedure named get_all_electronics
CREATE PROCEDURE get_all_electronics()
BEGIN
    -- Select all columns from the 'electronics' table
    -- Order the result by 'brand' in ascending order and 'price' in descending order
    SELECT * FROM electronics ORDER BY brand, price DESC;
END //

-- Reset the delimiter to the default value
DELIMITER ;

💡 Dado que los procedimientos almacenados están precompilados, ofrecen un mejor rendimiento que las consultas SQL normales que deben analizarse cada vez que se ejecutan.

2. A continuación, ejecute el siguiente comando para ejecutar (call) su procedimiento almacenado (get_all_electronics()).

CALL get_all_electronics();

💡 Un procedimiento almacenado es un fragmento de código que se escribe una vez y que se puede invocar repetidamente según sea necesario. Esta funcionalidad es una joya que ahorra tiempo y le ahorra el esfuerzo de reescribir consultas complejas cada vez que requiere su ejecución.

Si tiene éxito, verá un conjunto de resultados similar al siguiente. Los registros se ordenan por marca en orden ascendente y descendente por precio, según lo especificado en el procedimiento almacenado.

¡Felicidades! Ha creado y ejecutado con éxito su primer procedimiento almacenado MySQL. Ya no está limitado a escribir código repetitivo para las mismas operaciones de base de datos. En su lugar, puede llamar a su procedimiento almacenado cuando sea necesario para realizar la tarea por usted.

Aprovechando el IN y OUT Parámetros en procedimientos almacenados de MySQL

Ha creado su primer procedimiento almacenado MySQL básico, que funciona de maravilla. Pero en muchos escenarios, necesitará pasar parámetros a sus procedimientos, especialmente cuando se trata de operaciones de bases de datos más complejas.

Un procedimiento almacenado MySQL puede tener diferentes tipos de parámetros, incluidos los siguientes:

Para aprovechar el IN y OUT parámetros, proceda con lo siguiente:

1. Ejecute las siguientes consultas para definir un procedimiento almacenado MySQL llamado get_electronics_by_yearque realiza lo siguiente:

• Toma un parámetro de entrada (release_year_filter) como un número entero (int) para el año de lanzamiento.
• Recupera productos electrónicos del electronics tabla que coincida con el año de lanzamiento especificado.
• Ordena el resultado por brand en orden ascendente (az) y price en descendente (DESC)orden.

-- Set a custom delimiter to avoid conflicts in the stored procedure definition
DELIMITER //

-- Create a stored procedure named get_electronics_by_year
-- This procedure takes an IN parameter named release_year_filter of type int
CREATE PROCEDURE get_electronics_by_year(IN release_year_filter int)
BEGIN
    -- Select all columns from the 'electronics' table
    -- Filter the result to include only rows where the 'release_year' matches the input parameter
    -- Order the result by 'brand' in ascending order and 'price' in descending order
    SELECT * FROM electronics WHERE release_year = release_year_filter ORDER BY brand, price DESC;
END //

-- Reset the delimiter to the default value
DELIMITER ;

2. A continuación, CALL el get_electronics_by_year procedimiento almacenado pasando un release_year_filter valor (es decir, 2021) para filtrar los resultados por el año de lanzamiento especificado.

CALL get_electronics_by_year(2021);

El conjunto de resultados muestra todos los productos electrónicos lanzados en 2021, como se muestra a continuación.

Observe que el conjunto de resultados está ordenado por marca en orden ascendente (az) y por precio en orden descendente como se describe en el procedimiento.

Si no pasa los parámetros requeridos (es decir, release_year_filter), encontrará un error como el siguiente. Este comportamiento muestra la ventaja de seguridad de utilizar procedimientos almacenados, ya que solo permiten pasar parámetros específicos.

💡 Los procedimientos almacenados ayudan a prevenir ataques de inyección SQL al permitir que solo se pasen parámetros específicos al procedimiento.

3. Ejecute las siguientes consultas para definir un procedimiento almacenado MySQL llamado get_electronics_stats_by_year.

Este procedimiento almacenado toma un IN parámetro (release_year_filter) y devuelve varias estadísticas relacionadas con productos electrónicos lanzados en el año especificado como un OUT parámetro.

Tenga en cuenta que puede combinar varios tipos de parámetros en un único procedimiento almacenado. Esta característica ofrece mayor flexibilidad y funcionalidad al diseñar y utilizar el procedimiento.

DELIMITER //

-- This stored procedure retrieves statistics for electronic products released in a specific year.
CREATE PROCEDURE get_electronics_stats_by_year(
    IN release_year_filter INT,    -- Input parameter: The year to filter electronic products.
    OUT electronics_count INT,     -- Output parameter: Count of electronic products.
    OUT min_price DECIMAL(10, 2),  -- Output parameter: Minimum price.
    OUT avg_price DECIMAL(10, 2),  -- Output parameter: Average price.
    OUT max_price DECIMAL(10, 2)   -- Output parameter: Maximum price.
)
BEGIN
    -- Calculate statistics and store them in output parameters.
    SELECT COUNT(*), MIN(price), AVG(price), MAX(price)
    INTO electronics_count, min_price, avg_price, max_price
    FROM electronics
    WHERE release_year = release_year_filter;
END //

DELIMITER ;

4. Ahora, CALL el get_electronics_stats_by_year procedimiento almacenado para realizar lo siguiente:

• pasar un release_year_filter valor (es decir, 2021) para filtrar los resultados por el año de lanzamiento especificado
• Defina las variables definidas por el usuario (@count, @min, @avgy @max) para recuperar los valores de los parámetros devueltos por el procedimiento.

CALL get_electronics_stats_by_year(2021, @count, @min, @avg, @max);

Como puede ver en el resultado a continuación, el conjunto de resultados no se muestra en la terminal, lo cual es de esperar ya que este procedimiento no devuelve datos directamente.

En cambio, el procedimiento almacena las estadísticas calculadas en las variables definidas por el usuario.

5. Por último, ejecute la siguiente consulta para SELECT y ver los valores almacenados en las variables definidas por el usuario (@count, @min, @avg, @max).

SELECT @count, @min, @avg, @max;

Verá los valores de las variables definidas por el usuario calculadas mediante el procedimiento, como se muestra a continuación. Este resultado confirma que el procedimiento almacenado funciona como se esperaba.

Aprovechando el INOUT Parámetro en procedimientos almacenados de MySQL

En la mayoría de los casos, el IN y OUT Los parámetros de parámetros son suficientes. Pero cuando necesita la capacidad de recibir y devolver valores sin problemas, el INOUT El parámetro funcionará.

Un INOUT El parámetro combina las características de ambos. IN y OUT parámetros. Puede pasar un valor al procedimiento, y el procedimiento puede modificar y devolver el valor actualizado.

Para ilustrar la funcionalidad del INOUT parámetro en los procedimientos almacenados de MySQL, complete los pasos a continuación:

1. Ejecute la siguiente consulta para ADD un id columna a la electronics TABLE para identificar cada elemento de forma única.

ALTER TABLE electronics ADD id INT AUTO_INCREMENT PRIMARY KEY;

2. A continuación, CALL el get_all_electronics procedimiento almacenado para recuperar los datos en el electronics mesa.

CALL get_all_electronics();

desde un id Se ha agregado una columna a la tabla, anote el ID de un elemento específico (es decir, identificación 1 para sony PlayStation 5) y su precio (es decir, 499,99).

Esta información verificará la actualización de precios realizada por el apply_discount_to_price procedimiento almacenado en el siguiente paso.

3. Ejecute la siguiente consulta para definir un procedimiento almacenado MySQL llamado apply_discount_to_price

Este procedimiento almacenado aplica un descuento al precio de un artículo electrónico según el ID del artículo proporcionado y el porcentaje de descuento.

El nuevo precio se calcula tomando el precio anterior y restándole el porcentaje de descuento. El precio actualizado se almacena entonces en el new_price INOUT parámetro, que se utilizará para actualizar el registro del artículo electrónico en la tabla.

CREATE PROCEDURE apply_discount_to_price(
    IN item_id INT,
    IN discount_percentage DECIMAL(5,2),
    OUT old_price DECIMAL(10,2),
    INOUT new_price DECIMAL(10,2)
)
BEGIN
    -- Select the current price of the electronic item
    SELECT price INTO old_price FROM electronics WHERE id = item_id;
    
    -- Calculate the new price by applying the discount
    SET new_price = old_price - (old_price * (discount_percentage / 100));
    
    -- Update the electronics table with the new price
    UPDATE electronics SET price = new_price WHERE id = item_id;
END //

DELIMITER ;

4. Ahora, ejecute cada consulta a continuación para SET un porcentaje de descuento, el ID del artículo cuyo precio desea actualizar y una variable para mantener el nuevo precio.

Asegúrese de reemplazar el @item_id valor con el ID del artículo que anotó anteriormente en el paso dos y un porcentaje de descuento de su elección (es decir, 10.0que es el 10%).

-- Example discount percentage
SET @discount_rate = 10.0; 
-- The ID of the item to update
SET @item_id = 1; 
-- Variable to store the new price after applying the discount
SET @new_price = 0; 

5. CALL el apply_discount_to_price procedimiento almacenado, pasando el @item_id y @discount_rate como parámetros.

CALL apply_discount_to_price(@item_id, @discount_rate, @old_price, @new_price);

6. SELECT y ver los datos almacenados en el @old_price y @new_price parámetros.

SELECT @old_price, @new_price;

Asumiendo el apply_discount_to_price El procedimiento almacenado funciona, verá el valor de los precios antiguos y nuevos con descuento, como se muestra a continuación.

7. Por último, CALL el get_all_electronics() procedimiento almacenado para confirmar los cambios en los datos.

CALL get_all_electronics();

CALL get_all_electronics();

Si todo fue exitoso, ahora verá el precio actualizado (con descuento) del artículo de destino.

Conclusión

A lo largo de este tutorial, viajó para desentrañar el poder de los procedimientos almacenados de MySQL. Comenzó preparando una base de datos de muestra, creando una forma básica de procedimientos almacenados de MySQL y aprovechando y aprovechando los parámetros.

Ahora, aproveche esta nueva experiencia y sumérjase en aplicaciones prácticas. ¿Por qué no explorar casos de uso dinámicos como lógica condicional, buclesy manejo de errores¿anticipar y gestionar con gracia los problemas? O considere sumergirse en desencadenantespermitiendo que su base de datos responda a eventos automáticamente?

¡Aventúrese en estas áreas para ampliar su conjunto de habilidades y descubrir la profundidad y versatilidad de los procedimientos almacenados de MySQL!