Procedimientos almacenados

Procedimientos almacenados y funciones en MySQL

Programación de Bases de Datos (6.- Introducción a SQL/PSM de MySQL)

Base de datos de proyectos:

proyectosDB.sql

Creación de un procedimiento

Usando la base de datos de Proyectos, crear un procedimiento que obtenga un listado con todos los empleados de un determinado departamento que pasamos como parámetro de entrada. Además, este procedimiento también debe obtener en un parámetro de salida la media de los salarios de los empleados que trabajan en ese departamento.

DELIMITER //
CREATE PROCEDURE lista_empleados_departamento(IN dep char(3), OUT mediaSalario INT)
BEGIN
    /* Obtenemos todos los empleados del departamento pasado como parámetro.*/
    SELECT * FROM empleado WHERE cddep = dep;

    /* Calculamos la media de salarios de sus empleados         
        y lo almacenamos en el parámetro de salida. */
    SELECT AVG(salario) INTO mediaSalario FROM empleado WHERE cddep = dep;
END//
DELIMITER ;

Ejecución del procedimiento.

CALL lista_empleados_departamento('02',@media);

SELECT @media AS 'Salario Medio';

Crear un nuevo proyecto con código EEC (Estudio de Enfermedades Contagiosas), cambiar los empleados que trabajan en el proyecto AEE al EEC y borrar el AEE.

DELIMITER $$
 
CREATE PROCEDURE `Cambios`()
BEGIN
    INSERT INTO proyecto (cdpro, nombre, cddep) VALUES ('EEC', 'Estudio de Enf. Contag.', '03');
    UPDATE trabaja SET cdpro = 'EEC' WHERE cdpro = 'AEE';
    DELETE FROM proyecto WHERE cdpro = 'AEE';
    SELECT * FROM proyecto;
END
$$
DELIMITER ;

Ejecución del procedimiento:

CALL Cambios();

Modificar el sueldo de un empleado pasando los parámetros del nuevo sueldo y el nombre del empleado:

DELIMITER $$
 
CREATE PROCEDURE `ActualizarSueldo`(IN `NuevoSueldo` FLOAT(6,2), IN `NombreEmpleado` VARCHAR(30))
BEGIN
    UPDATE empleado SET salario = NuevoSueldo WHERE  nombre = NombreEmpleado;
    SELECT nombre, salario FROM empleado WHERE Nombre = NombreEmpleado;
END
$$
DELIMITER ;

Ejecución:

SET @p0='1000'; 
SET @p1='Pablo Verde';
CALL `ActualizarSueldo`(@p0, @p1);

Funciones

Crear una función que reciba como parámetro un código de proyecto y devuelva el número de empleados que trabaja en ese proyecto

DELIMITER $$
 
CREATE FUNCTION `CuentaEmpleados`(`CodigoProyecto` CHAR(3)) 
RETURNS int
DETERMINISTIC
BEGIN
    DECLARE cuenta INT;
 
    SELECT COUNT(*) INTO cuenta FROM trabaja WHERE cdpro = CodigoProyecto;
    RETURN cuenta;
    
END
$$
DELIMITER ;

Ejecución:

SELECT CuentaEmpleados("DAG");

Obtener los años de antigüedad de los empleados

DELIMITER //
 
CREATE FUNCTION edad (f_nac DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    -- Declaramos una variable
    DECLARE a INT;
    
    -- Asignamos a la variable el cálculo de la edad.
    SET a=truncate(datediff(sysdate(), f_nac)/365,0);
    
    -- Devolvemos la variable.
    RETURN a; 
END
//

DELIMITER ;

Ejecución:

SELECT nombre, edad(fecha_ingreso) AS 'Años de antigüedad' FROM empleado;

Ejemplos de Procedimientos y funciones en MySQL

Base de datos jardineria

Más información:

Introducción a PL/SQL (Universidad de Sevilla)

MySQL Stored Procedures Tutorial

Base de datos usada en los ejemplos de mysqltutorial.org:

classicmodels.sql