存储过程示例

CREATE PROCEDURE weather_day_precipitation (IN DAY INT(10)) COMMENT '一天的降雨量处理'
BEGIN
DECLARE total_precipitation DOUBLE (10, 2) DEFAULT 0.00;
DECLARE precipitation DOUBLE (10, 2) DEFAULT 0.00;
DECLARE weather_id INT DEFAULT 0;
DECLARE done BOOLEAN DEFAULT 0;
-- 当天未处理数据列表
DECLARE precipitation_list CURSOR FOR SELECT
    weather_id,
    precipitation
FROM
    cf_weather_data
WHERE
    statice_done = 0
AND TO_DAY (monitor_time) = DAY
ORDER BY
    monitor_time ASC;
-- 当SQLSTATE为02000时设置done值为1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1;
-- 当天最后的处理数据
SELECT
    add_up_precipitation
FROM
    cf_weather_data
WHERE
    statice_done = 1
AND TO_DAY (monitor_time) = DAY
ORDER BY
    monitor_time DESC
LIMIT 1 INTO total_precipitation;
-- 打开游标
OPEN precipitation_list;
-- 开始循环
REPEAT
    -- 把当前行的值赋给声明的局部变量o中
    FETCH precipitation_list INTO weather_id,
    precipitation;
SET total_precipitation = total_precipitation + precipitation;
UPDATE cf_weather_data
SET add_up_precipitation = total_precipitation
WHERE
    weather_id = weather_id;
-- 当done为真时停止循环
UNTIL done
END
REPEAT
;
-- 关闭游标
CLOSE precipitation_list;
-- 更新当天未处理的全天降雨值
UPDATE cf_weather_data
SET all_day_precipitation = total_precipitation
WHERE
    TO_DAY (monitor_time) = DAY;
END;