SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE tbl_pokemongames ( poke_game_id CHAR(10) PRIMARY KEY NOT NULL, poke_game_name VARCHAR(150), poke_game_region VARCHAR(50), poke_game_platform VARCHAR(50) ); -- Table CREATE TABLE tbl_nationaldex ( pokemon_generation INT NOT NULL, pokemon_id INT PRIMARY KEY NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL ); CREATE TABLE tbl_fireredleafgreen ( poke_game_id CHAR(10) DEFAULT 'frld' NOT NULL, pokemon_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, pokemon_national_id INT UNIQUE NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL, pokemon_location VARCHAR(150) NOT NULL, CONSTRAINT pk_poke_game_id_frld FOREIGN KEY (poke_game_id) REFERENCES tbl_pokemongames(poke_game_id), CONSTRAINT pk_poke_naional_id_frld FOREIGN KEY (pokemon_national_id) REFERENCES tbl_nationaldex(pokemon_id) ); CREATE TABLE tbl_rubysapphireemerald ( pokemon_game_id CHAR(10) DEFAULT 'rse' NOT NULL, pokemon_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, pokemon_national_id INT UNIQUE NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL, pokemon_location VARCHAR(150) NOT NULL, CONSTRAINT pk_poke_game_id_rse FOREIGN KEY (pokemon_game_id) REFERENCES tbl_pokemongames(poke_game_id), CONSTRAINT pk_poke_naional_id_rse FOREIGN KEY (pokemon_national_id) REFERENCES tbl_nationaldex(pokemon_id) ); INSERT INTO tbl_pokemongames (poke_game_id, poke_game_name, poke_game_region, poke_game_platform) VALUES ('rby', 'Red, Blue, and Yellow', 'Kanto', 'Game Boy'), ('frld', 'FireRed and LeafGreen', 'Kanto', 'Game Boy Advance'), ('rse', 'Ruby Sapphire and Emerald', 'Hoenn', 'Game Boy Advance'), ('hgss', 'HeartGold and SoulSilver', 'Johto', 'Nintendo DS'), ('p', 'Platinum', 'Sinnoh', 'Nintendo DS'), ('oras', 'Omega Ruby and Alpha Sapphire', 'Hoenn', '3DS'), ('sm', 'Sun and Moon', 'Alola', '3DS'); INSERT INTO tbl_nationaldex (pokemon_generation, pokemon_id, pokemon_name, pokemon_type, pokemon_base_stats) VALUES (1, 1, 'Bulbasaur', 'Grass, Poison', 318), (1, 2, 'Ivysaur', 'Grass, Poison', 405), (1, 3, 'Venusaur', 'Grass, Poison', 525), (3, 252, 'Treecko', 'Grass', 318), (3, 253, 'Grovyle', 'Grass', 405), (3, 254, 'Sceptile', 'Grass', 530); INSERT INTO tbl_fireredleafgreen (pokemon_national_id, pokemon_name, pokemon_type, pokemon_base_stats, pokemon_location) VALUES (1, 'Bulbasaur', 'Grass, Poison', 318, 'Pallet Town'), (2, 'Ivysaur', 'Grass, Poison', 405, 'Evolve Bulbasaur'), (3, 'Venusaur', 'Grass, Poison', 525, 'Evolve Bulbasaur/Ivysaur'); INSERT INTO tbl_rubysapphireemerald (pokemon_national_id, pokemon_name, pokemon_type, pokemon_base_stats, pokemon_location) VALUES (252, 'Treecko', 'Grass', 318, 'Route 101'), (253, 'Grovyle', 'Grass', 405, 'Evolve Treecko'), (254, 'Sceptile', 'Grass', 530, 'Evolve Treecko/Grovyle'); DELIMITER // CREATE FUNCTION fn_search_pokemon_game_or_region (_game_or_region VARCHAR(150)) RETURNS VARCHAR(500) BEGIN DECLARE result VARCHAR(500); -- Declare variables to store column values DECLARE game_name VARCHAR(150); DECLARE game_region VARCHAR(50); DECLARE game_platform VARCHAR(50); -- Initialize result SET result = ''; -- Declare a cursor to retrieve data from tbl_pokemongames DECLARE cur CURSOR FOR SELECT poke_game_name, poke_game_region, poke_game_platform FROM tbl_pokemongames WHERE LOWER(poke_game_name) LIKE CONCAT('%', LOWER(_game_or_region), '%') OR LOWER(poke_game_region) LIKE CONCAT('%', LOWER(_game_or_region), '%'); -- Declare CONTINUE handler to exit the loop DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- Open the cursor OPEN cur; -- Start loop to fetch data from cursor read_loop: LOOP -- Fetch data into variables FETCH cur INTO game_name, game_region, game_platform; -- Check if no more rows found IF done THEN LEAVE read_loop; END IF; -- Concatenate data into result SET result = CONCAT(result, 'Game Name: ', game_name, ', Region: ', game_region, ', Platform: ', game_platform, '\n'); END LOOP; -- Close cursor CLOSE cur; -- Return result RETURN result; END// DELIMITER ; select fn_search_pokemon_game_or_region ('Kanto');