ASIX/M10/UF1/A05/E1

ASIX M10 UF1

Pt5. Codificació de funcions

Guillem Solà i Boeck (host: PC05)

Enunciat

Crear un procediment/funció, de tal forma que si li passem el nom d'un llenguatge, ens mostri els paisos que el tenen com a oficial.

Solució

CREATE DEFINER=`root`@`localhost` PROCEDURE `paisos_llengua`(in llenguatge varchar(30))
BEGIN
DECLARE pais varchar(30);
  SELECT Country.Name
  FROM CountryLanguage JOIN Country ON Country.Code = CountryLanguage.CountryCode
  WHERE CountryLanguage.Language=llenguatge AND CountryLanguage.IsOfficial="T";
END

Execució

MariaDB [world]> call paisos_llengua("English");

+--------------------------------------+
| Name                                 |
+--------------------------------------+
| Anguilla                             |
| American Samoa                       |
| Antigua and Barbuda                  |
| Australia                            |
| Belize                               |
| Bermuda                              |
| Barbados                             |
| Canada                               |
| Cocos (Keeling) Islands              |
| Christmas Island                     |
| Cayman Islands                       |
| Falkland Islands                     |
| United Kingdom                       |
| Gibraltar                            |
| Guam                                 |
| Hong Kong                            |
| Ireland                              |
| Saint Kitts and Nevis                |
| Saint Lucia                          |
| Lesotho                              |
| Marshall Islands                     |
| Malta                                |
| Northern Mariana Islands             |
| Montserrat                           |
| Norfolk Island                       |
| Niue                                 |
| Nauru                                |
| New Zealand                          |
| Palau                                |
| Saint Helena                         |
| Seychelles                           |
| Turks and Caicos Islands             |
| Tokelau                              |
| Tonga                                |
| Tuvalu                               |
| United States Minor Outlying Islands |
| United States                        |
| Saint Vincent and the Grenadines     |
| Virgin Islands, British              |
| Virgin Islands, U.S.                 |
| Vanuatu                              |
| Samoa                                |
| South Africa                         |
| Zimbabwe                             |
+--------------------------------------+
44 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)