Mediante procedimientos almacenados (Stored Procedures)
Es cierto que, cuando pensamos en automatizar un flujo de trabajo, sea cual sea este, lo primero en lo que pensamos es en utilizar lenguajes orientados a objetos como Python, o flujos de trabajo mediante Power Automate, sin embargo, esta no siempre es la mejor opción. En algunos casos, como cuando el proceso implica un gran volumen de datos que ya residen en nuestra BD, la mejor alternativa puede ser el uso de Stored Procedures en SQL. Estos permiten ejecutar la lógica directamente en el motor de la base de datos, reduciendo la latencia, optimizando el rendimiento y garantizando mayor seguridad al limitar el acceso a los datos, al no tener que ser importados directamente a nuestra computadora, pero ¿Qué es un procedimientos almacenado? No es más que un conjunto de instrucciones predefinidas que se almacenan y ejecutan en la base de datos, es decir, prácticamente es el equivalente a generar un pipeline directamente en la base de datos, ya que nos permite encapsular procesos dentro del propio motor de la base de datos.
La lógica para crear un procedimiento almacenado es muy similar a la que utilizaríamos para crear una función en R o Python, por ejemplo, supongamos que queremos insertar de forma periódica datos en una tabla de registro de usuarios como la siguiente:
Pero puede convertirse en algo tedioso el tener que usar constantemente la función INSERT INTO …, por lo que podemos crear un procedimiento almacenado que ya englobe de manera directa esta función, siendo:
create procedure registrar_venta(
in p_cliente_id int,
in p_monto decimal(10,2)
)
begin
insert into registros (usuario_id, transaccion)
values (p_cliente_id, p_monto);
end Claro que también podemos hacer uso de otras funciones como now(), para añadir de forma automática la fecha en la que estamos registrando esos valores:
create procedure registrar_venta(
in p_cliente_id int,
in p_monto decimal(10,2)
)
begin
insert into registros (usuario_id, transaccion, fecha)
values (p_cliente_id, p_monto, now());
end Teniendo este proceso establecido, ahora el insertar datos a la tabla se convierte en una tarea mucho más sencilla, ya que solo tendremos que llamar a la función registrar_venta(), con los valores a insertar:
call registrar_venta(1, 300.99);Trabajar con procesos para simplificar ciertas tareas no nos exime de cometer errores al momento de introducir datos, por lo que, tal como lo haríamos con una estructura de control tradicional (IF ELSE, FOR, etc), podemos establecer ciertas condiciones para que, en caso de introducir valores incorrectos (o no permitidos), podamos parar la función. Volvamos al caso anterior, supongamos que queremos insertar nuevos registros, pero solamente aquellos que tengan un valor en la transacción positivo. Podemos establecer una condición en la que, cuando el valor esperado no sea mayor a 0, simplemente devuelva un mensaje de error (sin insertar ningún valor), tal que:
create trigger valores_invalidos
before insert on registros
for each row
begin
if new.transaccion < 0 then
signal sqlstate '45000'
set message_text = 'el monto de la transacción no puede ser negativo';
end if;
endComo puedes darte cuenta, esta condición se encuentra fuera del procedimiento almacenado, por lo que, independientemente de que utilicemos dicha función o simplemente hagamos uso de una sentencia INSERT INTO, no podremos insertar valores negativos en la tabla.
Obviamente, al no insertar valores negativos debido a la condición anterior, directamente se pierde el registro de esa información. Si quisiéramos guardar esos datos en una tabla separada, podríamos establecer una condición de error en el PA (similar a tryCatch() en R o Try en Python). Comencemos por crear una tabla que guarde valores erroneos:
create table log_errores(
mensaje varchar(50),
transaccion decimal(10,2),
fecha date
)Ahora, ya que tenemos la tabla para guardar los valores erroneos, podemos establecer un PA que, en caso de devolver un error, guarde los datos en esta última:
create procedure registrar_transaccion_segura(
in p_usuario_id int,
in p_transaccion decimal(10,2)
)
begin
declare exit handler for sqlexception
begin
insert into log_errores (mensaje, transaccion, fecha)
values ('error al registrar transacción', p_transaccion, now());
end;
insert into registros (usuario_id, transaccion, fecha)
values (p_usuario_id, p_transaccion, now());
endPor lo que, al llamar a la función con un valor incorrecto, por ejemplo:
call registrar_transaccion_segura(1,-200)La información será guardada en log_errores.
Finalmente, llegamos al tema que probablemente te interesará más: Programación de eventos para automatizar procesos en intervalos de tiempo. En muchos casos, es necesario ejecutar ciertas tareas de manera recurrente, como la limpieza de registros antiguos, el procesamiento de datos diarios o la actualización de estadísticas. Para ello, en SQL podemos hacer uso de los eventos programados (JOBS).
Para crear un evento programado en MySQL, primero debemos asegurarnos de que el scheduler está habilitado. Esto lo podemos verificar con la siguiente consulta:
show variables like 'event_scheduler'Esto nos devolverá un valor ON u OFF. Si el valor resultante es OFF, entonces tendremos que activar el scheduler mediante la siguiente sentencia:
set global event_scheduler = ON;Ahora, ya que tenemos activado el scheduler podemos comenzar. Regresemos al ejemplo anterior, con la función registrar_transaccion_segura. Supongamos que queremos insertar valores cada hora, sencillamente podemos automatizar este proceso mediante:
create event job_registrar_transaccion
on schedule every 1 hour
do
begin
call registrar_transaccion_segura(1, 100.50);
endDonde únicamente englobamos el PA en un JOB que se ejecutará cada hora. En una situación real, no vamos a utilizar constantes que se actualicen cada hora. Generalmente haremos uso de variables basadas en registros nuevos que, usualmente, vendrán de otras tablas. Veamos un ejemplo más detallado, donde el JOB tomará información de, una tabla secundaria llamada transacciones_pendientes, fila por fila:
create event job_registrar_transaccion
on schedule every 1 hour
do
begin
declare done int default false;
declare v_usuario_id int;
declare v_transaccion decimal(10,2);
declare cur cursor for
select usuario_id, transaccion from transacciones_pendientes;
declare continue handler for not found set done = true;
open cur;
read_loop: loop
fetch cur into v_usuario_id, v_transaccion;
if done then
leave read_loop;
end if;
call registrar_transaccion_segura(v_usuario_id, v_transaccion);
end loop;
close cur;
delete from transacciones_pendientes;
endNotese que le agregamos un par de elementos a este JOB: Un cursor declare cur cursor, ya que este elemento nos permitirá avanzar de fila en fila de forma similar a como trabaja una estructura WHILE, para la inserción de datos provenientes de otra tabla, así como una pequeña condición declare continue handler for not found set done = true para que, una vez que se terminen de insertar los registros, cierre el cursor.
[1] Melton, J., & Simon, A. R. (1993). Understanding the new SQL: a complete guide. Morgan Kaufmann.
[2] Celko, J. (2010). Joe Celko’s SQL for smarties: advanced SQL programming. Elsevier.
[3] Eisenberg, A. (1996). New standard for stored procedures in SQL. ACM SIGMOD Record, 25(4), 81-88.
[4] Panwar, V. (2024). Optimizing Big Data Processing in SQL Server through Advanced Utilization of Stored Procedures. Journal Homepage: http://www. ijmra. us, 14(02).