Procedimientos almacenados en SQL: guía

Los procedimientos almacenados en SQL son rutinas guardadas en la base de datos que encapsulan una secuencia de operaciones. Pueden recibir parámetros, aplicar reglas, modificar datos y devolver resultados. Su sintaxis y capacidades cambian entre PostgreSQL, SQL Server, MySQL y Oracle, así que el diseño conceptual es más portable que el código.

Cuándo aportan valor

Son útiles para operaciones transaccionales cercanas a los datos, procesos por lotes, interfaces estables para sistemas heredados y tareas que deben ejecutarse con permisos controlados. También reducen viajes de red cuando una operación exige varios pasos coordinados.

No conviertas cada consulta en un procedimiento. La lógica distribuida entre aplicación y base de datos puede dificultar pruebas, despliegues y observabilidad. Define responsabilidades: integridad y operaciones centradas en datos pueden residir en la base; presentación y flujos de usuario suelen pertenecer a la aplicación.

Parámetros de entrada y salida

Un procedimiento debe exponer un contrato claro: tipos, valores permitidos, efectos y resultado. Un ejemplo conceptual:

CREATE PROCEDURE registrar_pago(
    IN p_factura_id INTEGER,
    IN p_importe DECIMAL(12, 2)
)
BEGIN
    -- validar, actualizar y registrar dentro de una transacción
END;

Los detalles varían según el motor. Evita parámetros ambiguos y no uses valores mágicos para representar estados.

Procedimiento frente a función

Una función suele devolver un valor o una tabla y puede participar en expresiones, mientras un procedimiento se invoca como una operación y puede gestionar múltiples efectos. Algunos motores permiten transacciones dentro de procedimientos y otros imponen restricciones.

Elige según la semántica y la plataforma, no solo por estilo. Si una rutina debe ser pura y reutilizable en una consulta, una función puede encajar mejor.

Transacciones y manejo de errores

Define qué debe ocurrir si falla el tercer paso de cinco. Una operación atómica debe confirmar todo o revertir todo. Captura excepciones solo cuando puedas añadir contexto o aplicar una recuperación segura; no ocultes errores y continúes con datos parciales.

Evita transacciones innecesariamente largas, porque retienen bloqueos y pueden aumentar la contención. Accede a objetos en un orden consistente y analiza niveles de aislamiento.

Seguridad

Los parámetros deben tratarse como datos. El SQL dinámico dentro del procedimiento puede ser vulnerable a inyección si concatena entradas. Parametriza valores y restringe identificadores dinámicos mediante listas permitidas.

Concede EXECUTE sobre el procedimiento sin otorgar automáticamente permisos amplios sobre todas las tablas. Revisa el contexto de ejecución —invocador o propietario— que ofrece el motor. Un procedimiento privilegiado necesita validaciones estrictas y una superficie mínima.

Rendimiento

Los planes pueden reutilizarse, pero también surgir problemas cuando diferentes valores necesitan estrategias distintas. Mide duración, lecturas, bloqueos y planes reales. Añade índices por la carga observada y evita cursores fila a fila si una operación por conjuntos resuelve lo mismo.

No asumas que mover lógica a un procedimiento la hace rápida. Consultas no sargables, conversiones implícitas y selecciones excesivas siguen costando.

Versionado y despliegue

Guarda las definiciones en el repositorio, revísalas como código y despliega mediante migraciones repetibles. Nunca edites manualmente producción sin registrar el cambio. Coordina versiones compatibles si la aplicación y el procedimiento no se actualizan al mismo tiempo.

Una técnica segura es introducir una versión nueva, migrar consumidores y retirar la anterior después de observar el uso. Documenta dependencias, permisos y estrategia de rollback.

Pruebas y observabilidad

Prueba valores normales, límites, nulos, concurrencia, fallos intermedios y permisos insuficientes. Ejecuta pruebas en una base aislada y verifica tanto el resultado como los efectos secundarios.

Registra una identidad de ejecución, duración, filas afectadas y código de resultado sin exponer datos sensibles. Para procesos largos, añade métricas y evita imprimir mensajes como único mecanismo de diagnóstico.

Buen diseño

Mantén cada rutina enfocada, nombres consistentes y contratos explícitos. Evita que un único procedimiento tenga decenas de parámetros y múltiples modos. Si el comportamiento cambia según muchas banderas, probablemente contiene varias operaciones que merecen interfaces separadas.

Continúa aprendiendo

Amplía este tema con nuestras guías sobre prevención de SQL injection, triggers en SQL, transacciones ACID.

Fuentes oficiales

• Documentación oficial de postgresql.org

Preguntas frecuentes

¿Qué es un procedimiento almacenado?
Es una rutina guardada en la base de datos que recibe parámetros y ejecuta una operación definida cerca de los datos.

¿En qué se diferencia de una función SQL?
La función suele devolver un valor y participar en consultas; el procedimiento representa una operación con posibles efectos. Depende del motor.

¿Mejora siempre el rendimiento?
No. Puede reducir viajes de red, pero las consultas internas, bloqueos y planes deben medirse igual que cualquier otro código.

¿Puede tener SQL injection?
Sí, si construye SQL dinámico concatenando entrada. Debe parametrizar valores y restringir identificadores.

¿Cómo se versiona?
Guardando su definición en el repositorio y desplegándola mediante migraciones revisables y repetibles.

¿Qué se debe probar?
Resultados, efectos secundarios, transacciones, errores, concurrencia, límites y permisos del contexto de ejecución.