Procedimientos almacenados (stored procedures) en MySQL (1)

De acuerdo a la Wikipedia, un procedimiento almacenado es:

Un procedimiento almacenado (stored procedure) es un programa (o procedimiento) el cual es almacenado físicamente en una base de datos. Generalmente son escritos en un lenguaje de bases de datos propietario como PL/SQL para Oracle database o PL/PgSQL para PostgreSQL. La ventaja de un procedimiento almacenado es que al ser ejecutado, en respuesta a una petición de usuario, es ejecutado directamente en el motor de bases de datos, el cual usualmente corre en un servidor separado. Como tal, posee acceso directo a los datos que necesita manipular y solo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes.

Sus ventajas son varias, entre las que destacamos que permiten ejecutar sentencias de SQL una tras otra, ya que algunas veces los programas de cómputo requieren antes de la ejecución (inserción, borrado ó modificación) de un query determinar o desencadenar otra serie de condiciones, piensa por ejemplo, que tienes una tabla donde manejas el inventario de productos y otra donde manejas las ventas, si pretendes registrar la venta de un producto tendrías que descontarlo del inventario, para tal efecto desde la aplicación (sin procedimientos almacenados) ejecutarías un par de queries, el primero para registrar la venta y el segundo para descontar en el inventario las unidades salientes, aunque en el mundo real es mucho más complejo ya que primero tendrías que determinar si existen unidades de ese producto, si hay descuentos aplicables, etc, etc, etc. Toda esa parte se puede encapsular en un procedimiento almacenado.

Para entender cómo funcionan primero tendremos que crear una base y sus respectivas tablas a fin de seguir los conceptos con ejemplos, para ello crearemos un script de SQL que llamaremos schema.sql

 

Delimiter ;

Create database prueba;

 

Create table inventario(

Id_producto int,

Producto varchar(255),

Precio double,

piezas int,

Primary key(id_producto)

);

 

Create table movimiento(

Fecha_venta datetime,

id_producto int,

cantidad_vendida int

);

Insert into inventario values (1, 'Paquete cervezas 6 pzas.', 10.50, 5);

Insert into inventario values (2, 'Refresco de lata', 1.50, 10);

Insert into inventario values (1, 'Agua embotellada 1 litro', 1.30, 3);

 

Supongamos que tenemos que crear un stored procedure que por cada producto vendido registre la venta efectuada en la tabla movimiento y también descuente la cantidad existente en almacén de dicho producto utilizando el campo piezas de la tabla inventario.