Funciones y Procedimientos Almacenados en MySQL

En el ámbito de la gestión de bases de datos, la eficiencia y la organización son clave. MySQL, uno de los sistemas de gestión de bases de datos relacionales más populares, ofrece herramientas poderosas para lograrlo: las rutinas almacenadas. Estas rutinas son, esencialmente, bloques de código SQL que se almacenan en el servidor de la base de datos y pueden ser ejecutados bajo demanda. Se dividen principalmente en dos tipos: procedimientos almacenados y funciones.

El uso de rutinas almacenadas va más allá de la simple ejecución de comandos. Permiten encapsular lógica de negocio, mejorar la seguridad al abstraer el acceso directo a las tablas, incrementar el rendimiento gracias a la pre-compilación de las sentencias y facilitar la reutilización de código. En este artículo, exploraremos en detalle qué son, cómo crearlos y las diferencias fundamentales entre procedimientos y funciones en MySQL, basándonos en la información proporcionada para administradores de sistemas.

Procedimientos Almacenados vs. Funciones: Comprendiendo las Diferencias

Tanto los procedimientos almacenados (stored procedures) como las funciones (functions) son tipos de rutinas almacenadas en MySQL. Agrupan un conjunto de órdenes SQL bajo un nombre para su posterior ejecución. Sin embargo, presentan diferencias cruciales que determinan su uso:

• Valor de Retorno: La diferencia más significativa. Una función está diseñada para devolver un único valor escalar (un número, una cadena, una fecha, etc.). Un procedimiento almacenado no tiene esta restricción; puede devolver múltiples conjuntos de resultados (via SELECT statements) o modificar parámetros de salida, pero no devuelve un único valor como resultado directo de su llamada.
• Llamada: Una función puede ser invocada desde dentro de una sentencia SQL, como parte de una expresión (por ejemplo, en un SELECT, WHERE, o SET). Un procedimiento almacenado se invoca mediante la sentencia CALL.
• Parámetros: Los procedimientos almacenados soportan parámetros de entrada (IN), salida (OUT) y entrada/salida (INOUT). Las funciones solo soportan parámetros de entrada (IN), ya que su resultado se devuelve a través de la cláusula RETURNS y la sentencia RETURN.
• Uso en SQL: Las funciones son ideales para cálculos o transformaciones de datos dentro de consultas SQL. Los procedimientos son más adecuados para realizar acciones que implican múltiples pasos, lógica compleja, o manipulación de datos (INSERT, UPDATE, DELETE).

En resumen, piensa en una función como una operación que calcula y devuelve un valor, y en un procedimiento como una secuencia de acciones a ejecutar.

Procedimientos Almacenados: Creación y Gestión

Un procedimiento almacenado es una colección de sentencias SQL que se ejecutan secuencialmente. Son útiles para tareas administrativas, control de acceso y encapsulación de lógica compleja.

Creación de Procedimientos

La creación se realiza con la sentencia CREATE PROCEDURE. Debes especificar un nombre y, opcionalmente, parámetros. Las sentencias SQL que forman el cuerpo del procedimiento se colocan entre BEGIN y END.

Un aspecto importante al crear procedimientos (o cualquier rutina compuesta por múltiples sentencias separadas por punto y coma) es el uso del DELIMITER. MySQL interpreta el punto y coma (;) como el fin de una sentencia SQL. Si intentas crear un procedimiento con múltiples sentencias dentro, MySQL intentará ejecutar cada sentencia interna por separado en lugar de tratar todo el bloque como una sola instrucción CREATE PROCEDURE.

Para evitar esto, cambias temporalmente el delimitador por defecto a otro símbolo (comúnmente $$ o //) antes de la sentencia CREATE PROCEDURE, y lo restauras a punto y coma después del END del procedimiento.

USE employees; DELIMITER $$ CREATE PROCEDURE department_getList () BEGIN SELECT dept_no, dept_name FROM departments; END$$ DELIMITER ;

Este ejemplo simple crea un procedimiento que lista todos los departamentos.

Listar y Visualizar Procedimientos

Para ver los procedimientos almacenados en una base de datos específica, utilizas SHOW PROCEDURE STATUS con una cláusula WHERE Db = 'nombre_bd':

SHOW PROCEDURE STATUS WHERE Db = 'employees';

Para ver el código fuente de un procedimiento ya creado, se usa SHOW CREATE PROCEDURE:

SHOW CREATE PROCEDURE department_getList;

Llamar a Procedimientos

La ejecución de un procedimiento almacenado se realiza con la sentencia CALL:

CALL employees.department_getList(); -- Indicando la base de datos CALL department_getList(); -- Si la base de datos 'employees' está activa

Modificar y Borrar Procedimientos

MySQL no permite modificar directamente el cuerpo o los parámetros de un procedimiento almacenado ya creado. Para realizar cambios significativos, debes borrar el procedimiento existente y volver a crearlo con la nueva definición. Puedes usar DROP PROCEDURE para eliminarlo:

DROP PROCEDURE IF EXISTS department_getList;

La cláusula IF EXISTS es útil para evitar errores si intentas borrar un procedimiento que no existe. Aunque no puedes modificar el cuerpo, sí puedes alterar ciertas características del procedimiento (como comentarios o configuraciones) usando ALTER PROCEDURE.

Variables en MySQL Routines

Dentro de los procedimientos y funciones, puedes utilizar variables para almacenar y manipular datos temporalmente.

Variables de Usuario

Se identifican con un prefijo @ (ej: @nombre). Tienen un alcance a nivel de sesión, lo que significa que su valor persiste durante la conexión del usuario y es visible en diferentes bloques de código o llamadas a rutinas dentro de esa sesión. No requieren declaración explícita con un tipo de dato antes de su uso y se les asigna valor con SET @variable = valor; o SELECT columna INTO @variable FROM ....

SET @salarioMinimo = 10000; SELECT distinct emp_no FROM salaries WHERE salary > @salarioMinimo;

Son útiles para pasar valores entre diferentes sentencias o para ser usadas como parámetros en llamadas a procedimientos desde fuera de otra rutina.

Variables Locales

Se declaran dentro del cuerpo de un procedimiento o función usando la sentencia DECLARE. Tienen un alcance limitado al bloque BEGIN...END donde se declaran. Deben tener un tipo de dato asociado y, opcionalmente, un valor por defecto. Se asignan valores usando SET variable = valor; o SELECT ... INTO variable ....

DELIMITER \ CREATE PROCEDURE department_getCount () BEGIN DECLARE numDepts INT DEFAULT 0; SELECT count(*) INTO numDepts FROM departments; SELECT numDepts; END \ DELIMITER ;

A diferencia de las variables de usuario, no llevan el prefijo @. Es importante elegir nombres que no colisionen con los nombres de columnas de las tablas usadas en las sentencias, ya que la variable local tendrá precedencia.

Variables del Sistema

MySQL también tiene variables de sistema (globales y de sesión) que controlan el comportamiento del servidor. Ya fueron vistas durante la instalación y su gestión. Pueden ser leídas dentro de las rutinas.

Estructuras de Control de Flujo

Para implementar lógica compleja dentro de las rutinas, MySQL proporciona estructuras de control similares a las de lenguajes de programación.

Instrucción IF-ELSE

Permite ejecutar bloques de código condicionalmente. Su sintaxis es flexible, permitiendo múltiples ramas ELSEIF y una rama ELSE final opcional.

IF condicion THEN -- Bloque de código si la condición es verdadera ELSEIF otra_condicion THEN -- Bloque de código si la otra_condición es verdadera ... ELSE -- Bloque de código si ninguna condición anterior es verdadera END IF;

Instrucción CASE

Similar a IF, pero más legible para múltiples comparaciones de un mismo valor o expresión.

CASE expresion WHEN valor1 THEN -- Código para valor1 WHEN valor2 THEN -- Código para valor2 ... ELSE -- Código para otros valores END CASE; -- O la forma sin expresion CASE WHEN condicion1 THEN -- Código para condicion1 WHEN condicion2 THEN -- Código para condicion2 ... ELSE -- Código para otras condiciones END CASE;

Instrucciones Repetitivas (Bucles)

Permiten ejecutar un bloque de código repetidamente.

• WHILE: El bucle se repite *mientras* la condición sea verdadera. La condición se evalúa al principio.
• REPEAT: El bucle se repite *hasta que* la condición sea verdadera. La condición se evalúa al final (garantiza al menos una ejecución).
• LOOP: Un bucle infinito que requiere sentencias LEAVE o ITERATE para salir o pasar a la siguiente iteración. Se pueden usar etiquetas para controlar qué bucle abandonar en estructuras anidadas.

-- Ejemplo WHILE WHILE condicion DO -- Código a repetir END WHILE; -- Ejemplo REPEAT REPEAT -- Código a repetir UNTIL condicion END REPEAT; -- Ejemplo LOOP con etiqueta salida: LOOP IF condicion_salida THEN LEAVE salida; END IF; -- Código a repetir END LOOP;

Cursores: Procesando Filas Individualmente

Aunque las operaciones basadas en conjuntos (como SELECT, UPDATE, DELETE sin cursores) suelen ser más eficientes en bases de datos relacionales, hay situaciones en las que necesitas procesar cada fila de un resultado de consulta de forma individual. Para esto, MySQL ofrece cursores.

Un cursor es una estructura que te permite iterar sobre las filas de un resultado de consulta (un SELECT statement) una por una. Su ciclo de vida típico es:

1. Declarar el cursor: Defines el nombre del cursor y la sentencia SELECT que generará el conjunto de resultados sobre el que iterarás.
2. Declarar un manejador de fin de cursor: Necesitas una forma de saber cuándo has llegado a la última fila. Esto se hace declarando un CONTINUE HANDLER para la condición NOT FOUND (o SQLSTATE '02000'), que establece una variable a verdadero cuando no hay más filas para obtener.
3. Abrir el cursor: Ejecuta la sentencia SELECT asociada y prepara el cursor para la lectura.
4. Recorrer el cursor (FETCH): Dentro de un bucle, usas la sentencia FETCH para obtener la siguiente fila del cursor y guardar sus valores en variables locales previamente declaradas. Compruebas la variable del manejador para saber si has llegado al final.
5. Cerrar el cursor: Libera los recursos asociados al cursor. Es una buena práctica cerrar el cursor tan pronto como hayas terminado de procesar las filas.

DECLARE done INT DEFAULT FALSE; DECLARE varA tipoA; DECLARE varB tipoB; DECLARE nombreCursor CURSOR FOR SELECT columnaA, columnaB FROM Tabla; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN nombreCursor; read_loop: LOOP FETCH nombreCursor INTO varA, varB; IF done THEN LEAVE read_loop; END IF; -- Lógica para procesar varA y varB END LOOP; CLOSE nombreCursor;

Gestión de Errores en Rutinas

Es crucial manejar los errores que puedan ocurrir durante la ejecución de una rutina para evitar que falle abruptamente y para proporcionar retroalimentación útil. MySQL permite definir manejadores de errores (handlers).

Un manejador de errores especifica una acción a tomar cuando ocurre una condición particular (un error SQL, una advertencia o el fin de un cursor). Los manejadores pueden ser CONTINUE (la ejecución continúa después de manejar el error) o EXIT (la ejecución del bloque actual termina después de manejar el error).

Se declaran usando DECLARE handler_type HANDLER FOR condition statement. La condición puede ser un código de error SQL (SQLSTATE), un código de error específico de MySQL, o condiciones nombradas como NOT FOUND o SQLEXCEPTION.

DELIMITER $$ CREATE PROCEDURE employee_add ( numEmp INT, fechaNac DATE, nombre VARCHAR(14), apellidos VARCHAR(16), mujer BOOLEAN ) BEGIN DECLARE error BOOLEAN DEFAULT FALSE; DECLARE genero CHAR(1); DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' -- Error de clave duplicada BEGIN SET error = TRUE; END; IF (mujer) THEN SET genero = 'F'; ELSE SET genero = 'M'; END IF; INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (numEmp, fechaNac, nombre, apellidos, genero, '1999-01-01'); IF (error) THEN SELECT -1, 'Clave primaria duplicada'; ELSE SELECT 0, 'Fila añadida'; END IF; END$$ DELIMITER ;

En este ejemplo, si ocurre un error de clave duplicada (SQLSTATE '23000') durante el INSERT, el manejador captura el error, establece la variable error a verdadero y la ejecución continúa después de la sentencia INSERT, permitiendo al procedimiento reportar el problema en lugar de fallar.

Funciones: Sintaxis y Uso Avanzado

Como mencionamos, las funciones devuelven un único valor y pueden usarse dentro de sentencias SQL. Su sintaxis es similar a la de los procedimientos, pero incluyen la cláusula RETURNS tipo_dato y la sentencia RETURN valor;.

DELIMITER // CREATE FUNCTION department_getName ( numero CHAR(4) ) RETURNS VARCHAR(40) BEGIN DECLARE nombre VARCHAR(40) DEFAULT 'DESCONOCIDO'; SELECT dept_name INTO nombre FROM departments WHERE dept_no = numero; RETURN nombre; END // DELIMITER ;

Este ejemplo crea una función que, dado un número de departamento, devuelve su nombre. Si el departamento no existe, devuelve 'DESCONOCIDO'.

La gran ventaja de las funciones es su capacidad para ser utilizadas directamente en consultas. Por ejemplo, para obtener el nombre del departamento asociado a cada empleado en la tabla dept_emp (asumiendo que tuviéramos el dept_no en esa tabla o vía JOIN), podrías usar la función:

SELECT emp_no, department_getName(dept_no) AS nombre_departamento FROM dept_emp LIMIT 10; -- Ejemplo con LIMIT

Aquí, la función department_getName se ejecuta una vez por cada fila devuelta por el SELECT, usando el valor de la columna dept_no como parámetro de entrada.

Un aspecto importante para el registro binario (binary logging) es el concepto de determinismo. Una función es determinista si, dadas las mismas entradas, siempre produce el mismo resultado. Si una función no es determinista (por ejemplo, usa NOW(), RAND(), o accede a datos que pueden cambiar independientemente de sus parámetros), deberías declararla explícitamente como NOT DETERMINISTIC al crearla. Si es determinista, puedes declararla como DETERMINISTIC. Si no especificas nada, MySQL puede asumir que es determinista, lo cual puede ser problemático durante la replicación o restauración basada en logs binarios si la función realmente no lo es.

Beneficios de Utilizar Rutinas Almacenadas

El empleo de procedimientos almacenados y funciones en MySQL ofrece múltiples ventajas:

• Seguridad: Puedes otorgar permisos de ejecución sobre rutinas sin dar acceso directo a las tablas subyacentes. Esto limita las operaciones que los usuarios o aplicaciones pueden realizar y ayuda a prevenir ataques como la inyección SQL.
• Rendimiento: Las rutinas almacenadas se compilan (o al menos se analizan y optimizan) una vez y se almacenan en un formato ejecutable en el servidor. Esto reduce la sobrecarga de análisis y compilación en cada ejecución, a diferencia de las sentencias SQL enviadas individualmente por el cliente.
• Reutilización de Código: Define una tarea o cálculo una vez y llámalo desde múltiples aplicaciones o puntos dentro de la base de datos.
• Encapsulación: La lógica de negocio compleja puede residir en la base de datos, facilitando su mantenimiento y asegurando la consistencia en cómo se realizan las operaciones.
• Reducción del Tráfico de Red: En lugar de enviar múltiples sentencias SQL individuales, se envía una sola llamada a la rutina almacenada.

Preguntas Frecuentes sobre Rutinas Almacenadas en MySQL

Aquí respondemos algunas dudas comunes.

¿Cuál es la diferencia principal entre un procedimiento y una función?

La diferencia clave es que una función devuelve un único valor escalar y puede ser llamada desde dentro de una sentencia SQL, mientras que un procedimiento no devuelve un valor de esta forma directa y se llama usando la sentencia CALL.

¿Puedo usar variables de usuario y locales en la misma rutina?

Sí, puedes utilizar ambos tipos de variables dentro de la misma rutina. Las variables locales se declaran con DECLARE y tienen alcance dentro del bloque BEGIN...END de la rutina. Las variables de usuario (prefijo @) tienen alcance de sesión.

¿Cómo manejo errores como la clave duplicada en un procedimiento?

Puedes declarar un CONTINUE HANDLER o EXIT HANDLER para manejar condiciones específicas como SQLSTATE '23000' (para clave duplicada) o SQLEXCEPTION. Esto te permite ejecutar código de manejo de errores cuando ocurre la condición.

¿Por qué necesito DELIMITER al crear procedimientos o funciones?

Necesitas cambiar el delimitador por defecto (punto y coma) porque el cuerpo de la rutina puede contener múltiples sentencias SQL, cada una terminando en punto y coma. Cambiando el delimitador, le indicas a MySQL que no ejecute el bloque CREATE PROCEDURE o CREATE FUNCTION hasta que encuentre el nuevo delimitador, tratando así todo el bloque como una única sentencia de creación.

¿Puedo modificar un procedimiento o función después de crearlo?

No directamente el cuerpo o los parámetros. Debes borrar la rutina con DROP PROCEDURE/FUNCTION y volver a crearla con la nueva definición. Puedes usar ALTER PROCEDURE/FUNCTION para modificar ciertas características, pero no el código principal.

¿Qué significa que una función sea DETERMINISTIC?

Significa que la función siempre devolverá el mismo resultado si se le pasan los mismos argumentos de entrada. Es importante declararlo correctamente para el buen funcionamiento de características como el registro binario (binary logging) usado en replicación y recuperación.

Conclusión

Los procedimientos almacenados y las funciones son componentes fundamentales para desarrollar aplicaciones robustas y eficientes con MySQL. Permiten centralizar la lógica de la base de datos, mejorar la seguridad y el rendimiento, y fomentar la reutilización del código. Dominar su uso, incluyendo el manejo de variables, estructuras de control, cursores y gestión de errores, es esencial para cualquier administrador o desarrollador que trabaje con MySQL.

Si quieres conocer otros artículos parecidos a Funciones y Procedimientos Almacenados en MySQL puedes visitar la categoría Bases de datos.