MySQL 存储过程：根据水表信息填充 month_freeze_amount 表的 meter_type 字段

{"title":"MySQL 存储过程：根据水表信息填充 month_freeze_amount 表的 meter_type 字段","description":"本文提供一个 MySQL 存储过程，用于根据 water_meter 表和 meter_model 表中的信息，填充 month_freeze_amount 表的 meter_type 字段。","keywords":"MySQL, 存储过程, 水表, month_freeze_amount, meter_type, water_meter, meter_model, 数据库操作","content":""DELIMITER //\n\nCREATE PROCEDURE fill_meter_type()\nBEGIN\n -- 声明变量\n DECLARE done INT DEFAULT FALSE;\n DECLARE meter_code VARCHAR(255);\n DECLARE meter_type VARCHAR(255);\n\n -- 游标\n DECLARE cur CURSOR FOR SELECT meter_code FROM month_freeze_amount;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\n\n -- 打开游标\n OPEN cur;\n\n -- 遍历游标\n read_loop: LOOP\n -- 从游标中获取meter_code\n FETCH cur INTO meter_code;\n IF done THEN\n LEAVE read_loop;\n END IF;\n\n -- 根据meter_code查询meter_model表获取meter_type\n SELECT meter_type INTO meter_type FROM meter_model WHERE meter_model_id = (SELECT meter_model_id FROM water_meter WHERE meter_code = meter_code);\n\n -- 更新month_freeze_amount表中的meter_type字段\n UPDATE month_freeze_amount SET meter_type = meter_type WHERE meter_code = meter_code;\n END LOOP;\n\n -- 关闭游标\n CLOSE cur;\nEND //\n\nDELIMITER ;\n\n使用方法：\n\nsql\nCALL fill_meter_type();\n\n\n该存储过程会遍历month_freeze_amount表中的每一行记录，根据meter_code字段查询water_meter表和meter_model表，获取对应的meter_type，然后更新month_freeze_amount表中的meter_type字段。"}