MySQL. Funciones y Procedimientos almacenados

Las funciones y procedimientos almacenados en MySQL permiten almacenar código SQL que permite realizar ciertas acciones directamente desde el motor de la base de datos.

Pueden mejorar el rendimiento ya que se necesita enviar menos información entre servidor y cliente.

Ventajas

  • Seguridad: el usuario puede ejecutar funciones/procedimientos sin necesidad de conocer la estructura de la base de datos.
  • Es más fácil ejecutar un programa ya definido que reescribir de nuevo las instrucciones.
  • Estándares de código: En un equipo de desarrollo usar la misma función/procedimiento permite crear una sinergia en las fases de construcción.

Funciones

El usuario puede definir funciones propias, éstas deben retornar algún tipo de valor (cadena, entero, decimal…)

Pueden recibir parámetros de entrada y se define el tipo de datos a devolver (RETURNS…).

La principal diferencia entre un procedimiento es que una función devuelve un tipo de valor definido mientras que un procedimiento puede realizar una tarea en la base de datos sin devolver ningún valor.

Sintaxis

CREATE FUNCTION nombre_función([parámetro1 tipo_de_dato, parámetro2 tipo_de_dato,...])
RETURNS tipo_de_dato
<Bloque de instrucciones>

Ejemplo:

DELIMITER //
CREATE FUNCTION multiplicar(num1 INT, num2 INT)
RETURNS INT
BEGIN
    DECLARE solucion INT;
    SET solucion = num1 * mum2;
    RETURN solucion;
END;

Es obligatorio establecer un limitador diferente a punto y coma [ ; ] para poder definir la líneas de código. Usualmente se cambia el limitador a //. DELIMITER //

Para llamar la función utilizamos un SELECT:

SELECT multiplicar(5, 10)

Para eliminar una función:

DROP FUNCTION nombre_funcion;

Declaración de variables

Es conveniente utilizar guión bajo [ _ ] al principio del nombre de las variables para que no den problemas con las palabras reservadas para el sistema (como status)

Podemos definir variables con DECLARE

DECLARE _anyo INT DEFAULT 1995;

DEFAULT indicia el valor por defecto para esa variable.

Asignar valores:

SET _anyo = 2000;

También se puede asignar el resultado de una consulta:

SELECT YEAR(fechaNacimiento) INTO _anyo FROM Alumnos WHERE dni = '12345678X'

Estructuras de control

IF, THEN, ELSE, END IF

Ejemplo, comprueba si es mayor que 0 y devuelve TRUE o FALSE.

DELIMITER //
CREATE FUNCTION esPositivo(_num INT) RETURNS BOOLEAN
BEGIN
    DECLARE _esPositivo BOOLEAN;
    IF _num > 0 THEN
        SET _esPositivo = TRUE;
    ELSE
        SET _esPositivo = FALSE;
    END IF;
    RETURN _esPositivo;
END

CASE

Ejemplo, según la nota devuelve una calificación u otra.

DELIMITER //
CREATE FUNCTION calificacion(_nota DOUBLE) RETURNS VARCHAR(10)
BEGIN
    CASE _nota
        WHEN 10 THEN RETURN 'Sobresaliente';
        WHEN 9 THEN RETURN 'Sobresaliente';
        WHEN 8 THEN RETURN 'Notable';
        WHEN 7 THEN RETURN 'Notable';
        WHEN 6 THEN RETURN 'Bien';
        WHEN 5 THEN RETURN 'Suficiente';
        ELSE RETURN 'Insuficiente';
    END CASE;
END //

Procedimientos almacenados

Conjuntos de instrucciones que se guardan en el servidor para su posterior uso, ya que se ejecutarán frecuentemente.

Sintaxis

CREATE PROCEDURE nombre_procedimiento([parámetro1 tipo_de_dato, parámetro2 tipo_de_dato,...])
<Bloque de instrucciones>

Ejemplo, aumenta la existencias de todos los productos según el parámetro de entrada:

DELIMITER //
CREATE PROCEDURE aumentarExistencias(_cantidad INT)
BEGIN
    UPDATE Productos
    SET Existencias = Existencias + _cantidad;
END //

Podemos ejecutar un procedimiento con:

CALL miProcedimiento()

Eliminar un procedimiento:

DROP PROCEDURE nombre_procedimiento;

Parámetros de entrada y salida

Los parámetros pueden ser de entrada (IN), salida (OUT) o entrada/salida (INOUT).

El parámetro de salida es un espacio en memoria en el cual devolverá almacenado su resultado. Y un parámetro de entrada/salida permite introducir el dato y mostrar el resultado en la misma variable. Por defecto, si no indicas el tipo de parámetro se asigna IN.

Ejemplo:

CREATE PROCEDURE miProcedimiento(OUT _solucion INT)

Los parametros utilizados en en OUT como INOUT pueden ser accedidos más adelante, aun cuando la ejecución del procedimiento haya terminado.

Ejemplo:

CREATE PROCEDURE ejemplo (OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM usuarios;
END //

Llamamos al procedimiento:

CALL ejemplo(@mi_variable);

Ahora podemos acceder a la variable cuando lo deseemos:

SELECT @mi_variable;

Deja una respuesta