3.7 Ejecución de procedimientos y funciones almacenadas

Introducción a procedimientos y funciones almacenadas

Los procedimientos almacenados y las funciones almacenadas son bloques de código SQL que se guardan directamente en el servidor de bases de datos. Permiten encapsular operaciones repetitivas y complejas en la base de datos, mejorando la eficiencia y reutilización del código.

  • Procedimientos almacenados: Son similares a las funciones, pero no devuelven un valor directamente. Se utilizan para realizar una serie de operaciones o modificaciones en la base de datos, como inserciones, actualizaciones o eliminaciones.

  • Funciones almacenadas: Son similares a las funciones en la programación tradicional. Devuelven un valor, lo que las hace útiles para realizar cálculos y devolver resultados dentro de una consulta SQL.

Los procedimientos almacenados y las funciones almacenadas pueden ser ejecutados desde PHP, y pueden aceptar parámetros para hacer más flexible su uso.

3.5 Ejecución de procedimientos y funciones almacenadas

3.5.1 Procedimientos almacenados

Ejemplo de un procedimiento almacenado para actualizar el salario de un empleado:

Como se ha explicado anteriormente, los procedimientos almacenados son útiles para realizar operaciones complejas en la base de datos. En este caso, vamos a crear un procedimiento almacenado que actualiza el salario de un empleado en la tabla salaries.

En este ejemplo, creamos un procedimiento almacenado que actualiza el salario del empleado en la tabla salaries y agrega un nuevo registro con la nueva fecha de inicio para ese salario.

Creación del procedimiento almacenado:

Creación del procedimiento almacenado
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE PROCEDURE actualizar_salario(IN emp_id INT, IN nuevo_salario DECIMAL(10,2))
BEGIN
    -- Actualizamos el salario del empleado en la tabla de salarios
    UPDATE salaries 
    SET to_date = CURDATE() 
    WHERE emp_no = emp_id AND to_date = '9999-01-01';  -- Marcar el salario actual como inactivo (termina)

    -- Insertamos el nuevo salario con la fecha de inicio
    INSERT INTO salaries (emp_no, salary, from_date, to_date) 
    VALUES (emp_id, nuevo_salario, CURDATE(), '9999-01-01'); -- El salario actual se marcará con una fecha máxima
END;

Este procedimiento actualiza el salario del empleado y mantiene el historial de salarios, marcando el salario anterior con la fecha actual y agregando un nuevo salario con fecha de inicio CURDATE().

Para ser más correcto, el procedimiento debería hacer algunas cosas más:

  • Comprobar si el empleado existe en la tabla salaries.
  • Crear una transacción para asegurar que ambas operaciones (actualización e inserción) se realicen correctamente. O se realizan ambas o no se realiza ninguna. Sino corremos el peligro de dejar la tabla salaries en un estado inconsistente.
Ejemplo de procedimiento almacenado con transacción

Procedimiento almacenado con transacción
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE PROCEDURE actualizar_salario(IN emp_id INT, IN nuevo_salario DECIMAL(10,2))
BEGIN

    -- Comprobamos si el empleado existe, si no existe producimos un error

    declare emp_count INT; -- Variable para contar el número de empleados

    -- Manejo de errores
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Manejo de errores
        ROLLBACK;
    END;

    -- Contamos el número de empleados con el id pasado como parámetro
    SELECT COUNT(*) INTO emp_count
        FROM employees
        WHERE emp_no = emp_id;
    -- Si no existe el empleado, lanzamos un error
    IF emp_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El empleado no existe';
    END IF;

    START TRANSACTION;

    -- Actualizamos el salario del empleado en la tabla de salarios
    UPDATE salaries 
    SET to_date = CURDATE() 
    WHERE emp_no = emp_id AND to_date = '9999-01-01';  -- Marcar el salario actual como inactivo (termina)

    -- Insertamos el nuevo salario con la fecha de inicio
    INSERT INTO salaries (emp_no, salary, from_date, to_date) 
    VALUES (emp_id, nuevo_salario, CURDATE(), '9999-01-01'); -- El salario actual se marcará con una fecha máxima

    COMMIT;
END;
Este procedimiento almacenado utiliza una transacción para asegurar que ambas operaciones (actualización e inserción) se realicen correctamente. Si ocurre un error en cualquiera de las operaciones, se realiza un ROLLBACK para deshacer los cambios.

Ejemplo de llamada al procedimiento desde PHP con MySQLi

Ejemplo de llamada a procedimiento almacenado

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetros de ejemplo
$emp_id = 1001;
$salario = 55000;

// Llamada al procedimiento almacenado
$query = "CALL actualizar_salario(?, ?)";

$stmt = $conexion->prepare($query);
$stmt->bind_param("id", $emp_id, $salario); // "i" para entero, "d" para decimal
$stmt->execute();

echo "Salario actualizado correctamente";

// Cerrar la conexión
$stmt->close();
$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetros de ejemplo
$emp_id = 1001;
$salario = 55000;
// Llamada al procedimiento almacenado
$query = "CALL actualizar_salario($emp_id, $salario)";
if (mysqli_query($conexion, $query)) {
    echo "Salario actualizado correctamente";
} else {
    echo "Error: " . mysqli_error($conexion);
}
// Cerrar la conexión
mysqli_close($conexion);
?>

Ejemplo de llamada al procedimiento desde PHP con PDO:

Ejemplo de llamada a procedimiento almacenado con PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetros de ejemplo
    $emp_id = 1001;
    $salario = 55000;

    // Llamada al procedimiento almacenado
    $query = "CALL actualizar_salario(:emp_id, :nuevo_salario)";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':emp_id', $emp_id, PDO::PARAM_INT);
    $stmt->bindParam(':nuevo_salario', $salario, PDO::PARAM_STR);
    $stmt->execute();

    echo "Salario actualizado correctamente";

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

Se recomienda en ambos casos (MySQLi y PDO) usar sentencias preparadas para evitar inyecciones SQL. En el caso de MySQLi, se utiliza bind_param para enlazar los parámetros, mientras que en PDO se utiliza bindParam.

Hacer pruebas con diferentes valores de salario y empleados para ver que funciona correctamente, debemos utilizar también valores que produzcan errores, como un salario negativo o un id de empleado que no existe, y ver qué sucede y como controlar esos errores.

3.5.2 Funciones almacenadas

Ahora vamos a crear una función almacenada para calcular el salario promedio de todos los empleados de un departamento, usando los datos de la tabla salaries.

Creación de una función almacenada para calcular el salario promedio de un departamento:

Creación de la función almacenada
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE FUNCTION salario_promedio(department_id INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE promedio DECIMAL(10,2);

    SELECT AVG(salary) INTO promedio
    FROM salaries
    JOIN dept_emp ON salaries.emp_no = dept_emp.emp_no
    WHERE dept_emp.dept_no = department_id AND salaries.to_date = '9999-01-01';  -- Solo salarios actuales

    RETURN promedio;
END;

Este procedimiento almacenado calcula el salario promedio de un departamento, basándose en los salarios actuales de los empleados en ese departamento.

Ejemplo de llamada a la función almacenada desde PHP con MySQLi (orientado a objetos):

Ejemplo de llamada a función almacenada

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetro de ejemplo
$department_id = 5;

// Llamar a la función almacenada
$query = "SELECT salario_promedio(?) AS promedio";

$stmt = $conexion->prepare($query);
$stmt->bind_param("i", $department_id); // "i" para entero
$stmt->execute();
$stmt->bind_result($promedio);
$stmt->fetch();

echo "El salario promedio del departamento $department_id es: $promedio";

// Cerrar la conexión
$stmt->close();
$conexion->close();
?>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetro de ejemplo
$department_id = 5;
// Llamar a la función almacenada
$query = "SELECT salario_promedio($department_id) AS promedio";
$resultado = mysqli_query($conexion, $query);
if ($resultado) {
    $fila = mysqli_fetch_assoc($resultado);
    echo "El salario promedio del departamento $department_id es: " . $fila['promedio'];
} else {
    echo "Error: " . mysqli_error($conexion);
}
// Cerrar la conexión
mysqli_close($conexion);
?>

Ejemplo de llamada a la función almacenada desde PHP con PDO:

Ejemplo de llamada a función almacenada con PDO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetro de ejemplo
    $department_id = 5;

    // Llamar a la función almacenada
    $query = "SELECT salario_promedio(:dept_id) AS promedio";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':dept_id', $department_id, PDO::PARAM_INT);
    $stmt->execute();

    $resultado = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "El salario promedio del departamento $department_id es: " . $resultado['promedio'];

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

Resumen de la sección 3.5

En esta sección hemos cubierto los siguientes temas:

  • Procedimientos almacenados: Aprendimos a crear procedimientos almacenados para realizar operaciones como la actualización de salarios, y cómo invocarlos desde PHP.
  • Funciones almacenadas: Aprendimos a crear funciones almacenadas que realizan cálculos como el salario promedio de un departamento, y cómo invocarlas desde PHP.

Ejercicios prácticos:

  1. Ejercicio 1: Crea un procedimiento almacenado para cambiar un empleado de departamento. El procedimiento debe recibir el ID del empleado y el nuevo ID del departamento, y actualizar la tabla dept_emp para reflejar el cambio. Recuerda que debes cerrar el período anterior UPDATE y abrir uno nuevo INSERT. Crea una prueba en PHP para invocar este procedimiento.
  2. Ejercicio 2: Crea una función almacenada que devuelva el número de empleados de un departamento en la base de datos employees. Crea una prueba en PHP para invocar esta función y mostrar el resultado.
  3. Ejercicio 3: Crea una función que devuelva el ID del jefe actual de un empleado. Para ello primero debes averigual el departamento actual del empleado y luego averiguar el jefe de ese departamento. Crea una prueba en PHP para invocar esta función y mostrar el resultado.