Under MySQL’s current implementation, a stored function can only return a single value. However, there is a not-so-pretty workaround: create a temporary table within the function body to store the values returned, and then access this table outside the function. Here’s an example:
mysql> DELIMITER // mysql> CREATE FUNCTION get_airport_names(min_terminals INT) -> RETURNS INT -> BEGIN -> DECLARE count INT DEFAULT 0; -> CREATE TEMPORARY TABLE -> IF NOT EXISTS -> get_airport_names_out (value VARCHAR(255)); -> DELETE FROM get_airport_names_out; -> INSERT INTO get_airport_names_out (value) -> SELECT AirportName FROM airport -> WHERE NumTerminals >= min_terminals; -> SELECT COUNT(value) INTO count -> FROM get_airport_names_out; -> RETURN count; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> SELECT get_airport_names(3); +----------------------+ | get_airport_names(3) | +----------------------+ | 4 | +----------------------+ 1 row in set, 1 warning (0.03 sec) mysql> SELECT value FROM get_airport_names_out; +---------------------------------+ | value | +---------------------------------+ | Heathrow Airport | | Barcelona International Airport | | Barajas Airport | | Changi Airport | +---------------------------------+ 4 rows in set (0.00 sec)
Leave a Reply