在MySQL中使用多个选择时出错存储过程

问题描述：

我试图在一个过程中使用多个选择，但我不断收到第18行所述的错误，我对存储过程有所了解，所以我可以使用一些帮助至于我做错了什么。在MySQL中使用多个选择时出错存储过程

DROP PROCEDURE IF EXISTS `WeatherRecord`; 

DELIMITER // 
CREATE PROCEDURE WeatherRecord(IN ID INT) 
BEGIN 
    DECLARE Rid INT UNSIGNED; 
    DECLARE Rdate DATE; 
    DECLARE Record DECIMAL(5,2); 


DECLARE c CURSOR FOR select Date, City_id, High_temp 
    FROM Past_weather WHERE High_temp = (
SELECT MAX(High_temp) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '1', 'HH_Temp'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, High_temp 
    FROM Past_weather WHERE High_temp = (
SELECT MIN(High_temp) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '2', 'LH_Temp'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_temp 
    FROM Past_weather WHERE Low_temp = (
SELECT MAX(Low_temp) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '3', 'HL_Temp'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_temp 
    FROM Past_weather WHERE Low_temp = (
SELECT MIN(Low_temp) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '4', 'LL_Temp'); 
    close c; 


DECLARE c CURSOR FOR select Date, City_id, High_hum 
    FROM Past_weather WHERE High_hum = (
SELECT MIN(High_hum) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '5', 'LH_Hum'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_hum 
    FROM Past_weather WHERE Low_hum = (
SELECT MIN(Low_hum) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '6', 'LL_Hum'); 
    close c; 


DECLARE c CURSOR FOR select Date, City_id, High_dew 
    FROM Past_weather WHERE High_dew = (
SELECT MAX(High_dew) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '7', 'HH_dew'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, High_dew 
    FROM Past_weather WHERE High_dew = (
SELECT MIN(High_dew) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '8', 'LH_dew'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_dew 
    FROM Past_weather WHERE Low_dew = (
SELECT MAX(Low_dew) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '9', 'HL_dew'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_dew 
    FROM Past_weather WHERE Low_dew = (
SELECT MIN(Low_dew) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '10', 'LL_dew'); 
    close c; 


DECLARE c CURSOR FOR select Date, City_id, High_ pressure 
    FROM Past_weather WHERE High_ pressure = (
SELECT MAX(High_pressure) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '11', 'HH_pressure'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, High_ pressure 
    FROM Past_weather WHERE High_ pressure = (
SELECT MIN(High_pressure) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '12', 'LH_pressure'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_ pressure 
    FROM Past_weather WHERE Low_ pressure = (
SELECT MAX(Low_pressure) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '13', 'HL_pressure'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Low_ pressure 
    FROM Past_weather WHERE Low_ pressure = (
SELECT MIN(Low_pressure) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '14', 'LL_pressure'); 
    close c; 


DECLARE c CURSOR FOR select Date, City_id, Wind_max 
    FROM Past_weather WHERE Wind_max = (
SELECT MAX(Wind_max) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '15', 'H_wind'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Wind_gust 
    FROM Past_weather WHERE Wind_gust = (
SELECT MAX(Wind_gust) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '16', 'H_gust'); 
    close c; 


DECLARE c CURSOR FOR select Date, City_id, Rainfall 
    FROM Past_weather WHERE Rainfall = (
SELECT MAX(Rainfall) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '17', 'H_rainfall'); 
    close c; 

DECLARE c CURSOR FOR select Date, City_id, Snowfall 
    FROM Past_weather WHERE Snowfall = (
SELECT MAX(Snowfall) FROM Past_weather WHERE City_id = ID) && City_id = ID; 
open c; 
fetch c INTO Rdate, Rid, Record; 

Insert into Weather_records(Date, City_id, Record, Record_type, Record_name) 
    values(Rdate, Rid, Record, '18', 'H_snowfall'); 


    close c; 
END // 
DELIMITER ;

做基本调试，删除东西，直到开始工作，然后再开始添加东西。 –

是的，这将是一个好主意 –

答

从official docs：

所存储的程序可以包含多个光标的声明，但在一个给定的块中声明的每个光标必须有一个唯一的名称。

作为一个附注;与可能使用的INSERT-SELECT查询相比，游标的效率相对较低。例如....

INSERT INTO `Weather_records`(`Date`, `City_id`, `Record`, `Record_type`, `Record_name`) 
SELECT `Date`, `City_id`, `High_temp`, '1', 'HH_Temp' 
FROM Past_weather 
WHERE High_temp = (
    SELECT MAX(High_temp) 
    FROM Past_weather 
    WHERE City_id = ID 
    ) 
    && City_id = ID 
;

...这将替换第光标插入您的SQL处理... 和/但如果多行出来选择的（多个不会忽略具有相同最高温度的行）。

好的谢谢，我想我可以重复使用相同的游标名称，如果我关闭它，然后重新分配它。但也感谢给我一个更好的写作方法。 –

@CaseyBridges我总是在procs中的任何“代码”之前声明我的所有游标，总是使用处理程序，并且始终遍历游标结果;我不知道为什么，但是我甚至从未想过你可以稍后（根据需要）宣布它们。 – Uueerdo

使用'vs'有什么区别？ –

在MySQL中使用多个选择时出错存储过程

相关推荐