Ejemplos:
CREATE DEFINER=`root`@`localhost`
FUNCTION `inventory_in_stock`(p_inventory_id INT)
RETURNS tinyint(1)
READS SQL DATA
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
select inventory_in_stock(30)
CREATE DEFINER=`root`@`localhost`
FUNCTION `clientes_por_pais`
(p_country varchar(50)) RETURNS int(11)
BEGIN
declare total int;
SELECT count(customer_id) into total
FROM sakila.country
left join city using (country_id)
left join address using (city_id)
left join customer using (address_id)
where country=p_country
group by country;
RETURN total;
END
select clientes_por_pais('Algeria')
CREATE DEFINER=`root`@`localhost` FUNCTION `peliculas_por_actor`(p_actor_id int) RETURNS int(11)
BEGIN
declare total int;
select count(film_id) into total from film join
film_actor using (film_id)
join actor using(actor_id)
where actor.actor_id=p_actor_id;
RETURN total;
END
select peliculas_por_actor(1)
CREATE DEFINER=`root`@`localhost`
FUNCTION `total_ventas`() RETURNS decimal(10,2)
BEGIN
declare total decimal(10,2);
select sum(amount) into total from payment;
RETURN total;
END
