Cómo crear y usar procedimientos almacenados en MySQL

¡Comparte nuestro contenido!

Un procedimiento almacenado simplemente es una porción de código que se puede guardar y reutilizar, muy útil cuando se tiene que realizar una misma tarea muchas veces. Es muy similar a una función o procedimiento típico de un lenguaje de programación ¿Quieres saber cómo funcionan? ¡Manos a la obra!

Conozcamos primero lo básico de los procedimientos almacenados

Un procedimiento almacenado como mencionamos al principio, ayuda a optimizar las tareas repetitivas que se puedan realizar sobre una base de datos. También el uso correcto de los procedimientos almacenados, puede ayudar a mejorar el rendimiento de las consultas que realicemos.

Para que nuestro usuario pueda hacer uso de los procedimientos almacenados, es necesario que la cuenta de usuario cuente con los permisos INSERT, DELETE, CREATE ROUTINE y ALTER ROUTINE.

De acuerdo al manejo que hacen de los parámetros, existen 3 tipos de procedimientos almacenados:

• IN: Es el tipo de procedimiento por default, el procedimiento utiliza parámetros que pasan el argumento por valor hacia dentro del mismo.
• OUT: El valor del parámetro se regresa hacia afuera de este para utilizarse ya sea en una variable u otra consulta.
• INOUT: Los parámetros definidos funcionan en ambas direcciones.

Las palabras clave que utilizaremos serán las siguientes:

• DELIMITER: Permite establecer un delimitador para que el procedimiento pueda crearse y finalizarse correctamente. Como delimitador, se puede usar casi cualquier carácter, pero los más recomendados son: punto y coma (;), signo monetario doble($$) y barra doble (//)
• CREATE PROCEDURE: Como su nombre lo indica, sirve para crear un procedimiento.
• BEGIN: Marca el inicio del cuerpo del procedimiento.
• END: Marca el final del cuerpo del procedimiento.
• CALL: Lo utilizaremos para llamar al procedimiento y que este haga su trabajo.

Preparemos el camino para ejecutar los procedimientos

Comencemos creando una nueva base de datos.

create database ejercicio;

Abrimos la nueva base de datos.

use ejercicio;

A continuación, creamos una lista de productos.

create table productos(
   id int not null auto_increment,
   producto varchar(40) not null,
   cantidad int not null,
   precio decimal(19,2) not null,
   marca varchar(20) not null,
   mayoreo set('S','N') default 'N',
   primary key(id)
);

Entonces, insertamos 5 productos a nuestra lista.

-- Producto 1
insert into productos values(NULL, "Alpha",20, 250.00, "Biomax", 'N');
-- Producto 2
insert into productos values(NULL, "Alpha Plus", 30, 480.00, "Biomax", 'N');
-- Producto 3
insert into productos values(NULL, "Beta", 50, 270.00, "Caroten", 'S');
-- Producto 4
insert into productos values(NULL, "Gamma", 15, 370.00, "Caroten", 'S');
-- Producto 5
insert into productos values(NULL, "Delta Plus", 55, 170.00, "Dubix", 'N');

Así deben quedar nuestros datos.

Crear procedimientos almacenados de tipo IN

Comencemos con los básicos. Hagamos un procedimiento que regrese todos los datos de un producto, por medio de su id.

Primero, establecemos el delimitador, en este caso usaré //

delimiter //

Ahora, creamos el procedimiento llamandolo productoId, con un parámetro tipo IN que será el id del producto, por supuesto con su correspondiente tipo de datos.

create procedure productoId(IN idProd int)

Iniciamos el cuerpo del procedimiento.

begin

Y escribimos la consulta, asegurándonos que el id del producto coincida con el valor proporcionado a través de la variable idProd.

select producto, cantidad, precio, marca, mayoreo
from productos where id = idProd;

Entonces, finalizamos el procedimiento.

end

Colocamos el delimitador para que se de por finalizada la escritura del procedimiento.

//

Una vez terminado el procedimiento, regresamos el delimitador al clásico punto y coma.

delimiter ;

Entonces, con CALL, llamamos al procedimiento para obtener los resultados. En nuestro ejemplo, obtendremos los datos del producto con id número 4.

call productoId(4);

Este es el resultado obtenido.

Para entender mejor el asunto, vamos a crear un segundo procedimiento almacenado. Hagamos un procedimiento que sume las cantidades de todos los productos en base al campo mayoreo.

delimiter //
create procedure sumaMayoreo(IN tipo char(1))
begin
   select sum(cantidad) as "Existencia al mayoreo"
   from productos where mayoreo = tipo;
end
//

Regresamos el delimitador al típico punto y coma.

delimiter ;

Y llamamos al procedimiento, pidiendo que nos entregue la suma de aquellos que estén marcados con la letra S.

call sumaMayoreo('S');

El resultado es el siguiente:

Crear procedimientos almacenados de tipo OUT

Como mencionamos arriba, los procedimientos de tipo OUT se llaman así, porque su resultado se envía hacia fuera del procedimiento a través de una variable. En este caso, podemos incluso usar un parámetro de tipo IN para filtrar nuestra consulta y luego otro de tipo OUT para obtener el resultado. Esto es muy útil en los típicos casos donde necesitamos regresar un valor único o un conjunto de valores separados.

Hagamos un procedimiento que nos regrese el importe de un producto (precio x cantidad) según el nombre del mismo. Iniciemos como siempre estableciendo el delimitador.

delimiter //

Creamos el procedimiento con dos parámetros, el primero será de tipo IN correspondiente el nombre del producto, el segundo de tipo OUT que nos entregará el importe.

create procedure importeProd(IN nombre varchar(30), OUT importe decimal(10,2))

Iniciamos el cuerpo del procedimiento.

begin

Hacemos la consulta multiplicando precio por cantidad, el resultado lo metemos en la variable importe con la palabra reservada INTO, por supuesto hacemos coincidir el campo producto con nuestra variable nombre.

select (precio * cantidad) into importe from productos where producto = nombre;

Finalizamos el cuerpo del procedimiento y utilizamos el delimitador.

end
//

Primero recuperamos el delimitador clásico.

delimiter ;

Ahora llamamos al procedimiento y metemos su resultado en una variable SQL.

call importeProd('Beta', @importe);

Hacemos un select a esa variable.

select @importe as "Importe del producto";

Y este es el resultado.

Crear procedimientos almacenados de tipo INOUT

Para entender este último tipo de procedimiento, vamos a crear un procedimiento que nos regrese la venta de un producto determinado por su id y la cantidad de producto que queremos vender. Este resultado se guardará en una variable de tipo INOUT para poder hacer acumulación, ya que básicamente ese es el mayor uso de este tipo de procedimientos.

Comencemos definiendo nuestro delimitador.

delimiter //

Creamos nuestro procedimiento con las 3 variables requeridos, siendo la primera la de tipo INOUT y las otras dos de tipo IN.

create procedure vender(INOUT importe decimal(10,2), IN idProd int, IN venta int)

Iniciamos nuestro procedimiento.

begin

Ahora, multiplicamos la variable venta por el precio del producto que coincida con el id que estamos buscando. El resultado lo metemos en una variable llamada @vendido.

select (precio * venta) into @vendido from productos where id = idProd;

Ahora acumulamos lo vendido en nuestra variable importe, y con la palabra reservada SET hacemos que ese sea el valor que saldrá fuera del procedimiento.

SET importe = importe + @vendido;

Finalizamos el procedimiento y aplicamos el delimitador.

end
//

Recuperamos el delimitador clásico.

delimiter ;

Ahora, pongamos a prueba nuestro procedimiento. Primero creamos una variable que comience con cero.

set @total = 0;

Llamamos al procedimiento la primera vez para vender 2 items del producto con id 1, este tiene un precio de $250. Recordemos que el primer parámetro tiene el valor de esta operación, así que lo asignamos a nuestra variable @total.

call vender(@total, 1, 2);

Veamos ahora cuanto ha acumulado la variable.

select @total as "Vendido";

El resultado será:

Llamemos de nuevo nuestro procedimiento para vender 3 items más de ese mismo producto.

call vender(@total, 1, 3);

Mostremos el resultado de nuevo.

select @total as "Vendido";

Recordando que el producto con id 1 tiene un precio de $250, primero vendimos 2 x $250 = 500, ahora vendimos 3 x $250 = $750, lo que nos da un total de $1250.

Finalmente, hemos de mencionar que en caso de cometer un error, es mejor eliminar el procedimiento y comenzar otra vez. Para eliminar el procedimiento utilizamos DROP PROCEDURE seguido del nombre del procedimiento.

¡Y eso es todo por ahora! ¿Te ha resultado? Déjanos saber en los comentarios aquí abajo, en nuestra cuenta de twitter @cablenaranja7 o en nuestra página de facebook.

Xamus Dyson

Docente, IT Manager, Blogger & Developer. Escribo por diversión, educo por pasión. | Grandstanding is not my thing.

www.cablenaranja.com

¡Comparte nuestro contenido!