get_film_actos.sql

USE sakila;
DROP PROCEDURE IF EXISTS get_film_actors;
DELIMITER &&
CREATE PROCEDURE get_film_actors(IN film_title VARCHAR(128))
BEGIN
    DECLARE done INTEGER DEFAULT 0;
    DECLARE current_film_id INT;
    DECLARE current_title VARCHAR(128) DEFAULT '';
    DECLARE film_cursor CURSOR FOR
        SELECT film_id, title
            FROM film
            WHERE title LIKE CONCAT('%', film_title, '%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN film_cursor;
    get_actors: LOOP
        FETCH film_cursor INTO current_film_id, current_title;
        IF done = 1 THEN
            LEAVE get_actors;
        END IF;
        SELECT current_title;
        SELECT first_name, last_name
            FROM actor INNER JOIN film_actor USING(actor_id)
            INNER JOIN film USING(film_id)
            WHERE film_id = current_film_id;
    END LOOP;
    CLOSE film_cursor;
END &&
DELIMITER ;