Etiqueta: stored procedure

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente2` (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;

select country_id into v_country_id from country
where country=p_country;

IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;

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;

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$$
delimiter ;

Uso:

call alta_cliente('Spain','Logroño','Laurel, 4','Juan','Pi',@id);
select @id;