Procedimientos almacenados

Ejemplos de procedimientos almacenados

CREATE DEFINER=`root`@`localhost`
 PROCEDURE film_in_stock
 (IN p_film_id INT, IN p_store_id INT,
 OUT p_film_count INT)
 READS SQL DATA
BEGIN
 SELECT inventory_id
 FROM inventory
 WHERE film_id = p_film_id
 AND store_id = p_store_id
 AND inventory_in_stock(inventory_id);

 SELECT FOUND_ROWS() INTO p_film_count;
END

call film_in_stock(123,1,@w);
select @w

CREATE DEFINER=`root`@`localhost` PROCEDURE 
`actores_por_categoria`
(in p_categoria varchar(50))
BEGIN
select distinct concat(first_name,' ',last_name) actor 
from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category c using (category_id)
where c.name=p_categoria
order by actor;

END

call actores_por_categoria('Animation')