mysqlカーソル+ストレージ・プロシージャ

1.まず、mysqlでカーソルをストレージ中に確立する必要があることに注意してください.
2.直接sql

--       
SELECT in_flow+out_flow
FROM water_meter_data
WHERE 1=1 and date(sys_read_time) = curdate()-1
ORDER BY (in_flow+out_flow) DESC
LIMIT 0,1


--        
SELECT in_flow+out_flow
FROM water_meter_data
WHERE 1=1 and date(sys_read_time) = curdate()
ORDER BY (in_flow+out_flow) DESC
LIMIT 0,1


--       
SELECT MAX(in_flow+out_flow)
FROM water_meter_data
WHERE 1=1 and date(sys_read_time) = curdate()


--    +      


--   
drop procedure if exists useCursor;
delimiter 
CREATE PROCEDURE useCursor()
  BEGIN
    DECLARE oneAddr varchar(150) default '';
    DECLARE allAddr varchar(150) default '';
    DECLARE done INT DEFAULT 0;
    DECLARE curl CURSOR FOR SELECT addr FROM energy.person;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN curl;
    REPEAT
      FETCH curl INTO oneAddr;
      IF NOT done THEN
        set oneAddr = CONCAT(oneAddr, ';');
        set allAddr = CONCAT(allAddr, oneAddr);
      END IF;
    UNTIL done END REPEAT;
    CLOSE curl;
    select allAddr;
  END;
call useCursor();


--   


--     
drop procedure if exists water_total;--           
delimiter; --    
CREATE PROCEDURE energy.water_total()--       
BEGIN
		--         declare
		DECLARE meter_id INT DEFAULT 0;--   id
    DECLARE flow_now INT DEFAULT 0;--      
    DECLARE flow_before INT DEFAULT 0;--       
		DECLARE build_id INT DEFAULT 0;--   id
		DECLARE room_id INT DEFAULT 0;--   id
		DECLARE done INT DEFAULT 0; --        


    DECLARE cur_meter CURSOR FOR 
						SELECT energy.meter_info.hid ,energy.meter_info.buildid,energy.meter_info.consumer_id 
						FROM energy.meter_info where energy.meter_info.meter_type='5';--     


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;--        


		-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;--                          id   null     
    OPEN cur_meter;--     


    REPEAT -- repeat  


      FETCH cur_meter INTO meter_id,build_id,room_id;--            ,          
	-- SELECT meter_id;
			IF NOT done THEN --         
				
				 --           
         SELECT  max(energy.water_meter_data.in_flow+energy.water_meter_data.out_flow) INTO flow_now
				 FROM  energy.water_meter_data
				 WHERE 1=1 and date(energy.water_meter_data.sys_read_time) = curdate() and energy.water_meter_data.meter_id = meter_id;


				 --           
         SELECT  max(energy.water_meter_data.in_flow+energy.water_meter_data.out_flow) INTO flow_before
				 FROM  energy.water_meter_data
				 WHERE 1=1 and date(energy.water_meter_data.sys_read_time) = curdate()-1 and energy.water_meter_data.meter_id = meter_id;
					--       
					IF (flow_now IS NOT NULL) AND (flow_before IS NOT NULL) THEN
						  --          
						 INSERT INTO energy.water_data_total(energy.water_data_total.meter_id,energy.water_data_total.build_id,
													energy.water_data_total.room_id,energy.water_data_total.water_flow,
													energy.water_data_total.create_date,energy.water_data_total.create_user)
						 VALUES(meter_id,build_id,room_id,flow_now-flow_before,date(NOW()),'admin');
					END IF;
      END IF;
    UNTIL done END REPEAT; -- repeat    
    CLOSE cur_meter;--   ，       
  END;


--       
CALL energy.water_total();