USE test;
DELIMITER $$
DROP PROCEDURE IF EXISTS example3$$
CREATE PROCEDURE example3()
BEGIN

DECLARE dname VARCHAR(255);
DECLARE hasnext INT DEFAULT TRUE;
DECLARE nation VARCHAR(255);

DECLARE c CURSOR FOR
SELECT displayname
FROM moma.artists
WHERE nationality = nation
LIMIT 20;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET hasnext = FALSE;

SET nation = 'Belgian';
SELECT concat(nation,' artists:');
OPEN c;
FETCH c INTO dname;
WHILE hasnext DO
  SELECT dname;
  FETCH c INTO dname;
END WHILE;
CLOSE c;

SET nation = 'Finnish';
SELECT concat('\n',nation,' artists:');
SET hasnext = TRUE;
OPEN c;
FETCH c INTO dname;
WHILE hasnext DO
  SELECT dname;
  FETCH c INTO dname;
END WHILE;

END $$
DELIMITER ;
call example3();
