Ejemplos procedimientos almacenados

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Los procedimientos almacenados se utilizan normalmente para realizar
-- operaciones que van más allá de devolver un resultado
-- Tienen una sintaxis un poco más compleja que las funciones
-- https://proyectoa.com/anadir-y-usar-procedimiento-almacenado-stored-procedure-en-mysql-server/
-- https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/

-- Para empezar tenemos parámetros de entrada (IN) y parámetros de salida (OUT)
-- que se definen en el inicio
-- Ejemplo: (IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-- Cuando llamo al procedimiento almacenado (call) pasaré 2 parámetros y recibiré uno

call film_in_stock(1,1,@total);
select @total;

-- Luego dentro del procedimiento almacenado tengo las mismas opciones
-- que en las funciones. Podemos programar de verdad
-- Tenemos variables, estructuras de control (if) bucles (while)...

CREATE PROCEDURE fibonacci(n int, out answer int)
BEGIN 
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    WHILE i < n DO
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END WHILE;
END;

call fibonacci(10,@numero);
select @numero;

-- Ejemplo con utilidad
-- Imaginemos que queremos de dar de alta un cliente
-- Antes del cliente tenemos que tener una dirección
-- Esa dirección tiene que tener una ciudad
-- Esa ciudad tiene que tener un país
-- Spain, logroño, carmen medrano 11, pepito perez
-- Para hacer esto tengo que hacer una serie de comprobaciones
-- Y si no de inserciones
-- Esto con un procedimiento almacenado va muy bien

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente`(
in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int
)
BEGIN
declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

-- Busco el pais que se llame como el parámetro
select country_id into v_country_id from country
where country=p_country;
-- O existe o no existe. Si no existe, lo inserto
IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;
-- Al llegar aquí en v_country_id tengo el id del país que me han
-- pasado como parámetro. Bien porque existía y he recuperado ese valor
-- Bien porque no existía, lo he insertado, y he recuperado el id
-- del registro insertado

-- Con la ciudad hacemos algo parecido

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;
 
IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

-- Igual que antes, al llegar aquí en v_city-id tengo el id de la ciudad

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;
 
IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;
 
IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;
 
select v_customer_id into p_customer_id;
END

-- ejemplo de uso

call alta_cliente('Spain','Barcelona','Agla 6','Pepito','Perez',@customer_id);
select @customer_id;

call alta_cliente('Spain','Barcelona','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

call alta_cliente('Trubulandia','Trubulú','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;