ECNU数据库作业——Lab6

Lab 6

EX1：Create a stored procedure

Create a stored procedure that takes the name of a continent as a parameter and returns the average acceleration of all cars on that continent.

DELIMITER //
CREATE PROCEDURE ex1(IN name TEXT, OUT avg_acceleration FLOAT, OUT continent TEXT)
BEGIN
	SELECT avg(cars_data.Accelerate), continents.Continent INTO avg_acceleration, continent
	FROM cars_data
	JOIN car_names ON car_names.MakeId = cars_data.Id
	JOIN model_list ON model_list.Model = car_names.Model
	JOIN car_makers ON car_makers.Id = model_list.Maker
	JOIN countries ON countries.CountryId = car_makers.Country
	JOIN continents ON continents.ContId = countries.Continent
	GROUP BY continents.Continent
	HAVING continents.Continent = name;
END //

DELIMITER ;
CALL ex1('asia', @avg_acceleration, @continent);
SELECT @avg_acceleration, @continent