mysql 預存程序使用遊標執行個體

 

使用了mysql預存程序、暫存資料表、遊標

CREATE DEFINER=`root`@`%` PROCEDURE `get_driver_nearconsumer`(    in_groupid int,    in_serviceid int)BEGINdeclare temp_id int;declare temp_x decimal(10,6);declare temp_y decimal(10,6);declare temp_c_x decimal(10,6);declare temp_c_y decimal(10,6);
DECLARE done bool default FALSE;  
DECLARE cur1 CURSOR FOR SELECT int_driver,dec_x,dec_y FROM (    select         d.*, a.vc_name as grouparea_name , g.dec_x,g.dec_y,g.dt_sc        ,c.dcount,dd.consumer_name,dd.vc_fadd,dd.vc_tadd,dd.int_service,dd.int_status    from bi_driver d      left join bi_gisdata g    on g.int_mubiao = d.int_driver and g.int_type = 1    left join  bi_grouparea a     on a.int_grouparea = d.int_area    left join     (select count(1) as dcount,int_driver      from bi_serviceinfo     where int_status >-1  and int_status < 100 group by int_driver    ) c    on c.int_driver = d.int_driver     left join     (select int_service,consumer_name,vc_fadd,vc_tadd,int_driver,int_status     from v_serviceinfo     where int_service in         (select int_service from bi_serviceinfo         where  int_status >-1  and int_status < 100 and ifnull(int_driver,0)>0         group by int_driver)    ) dd    on dd.int_driver = d.int_driver     where int_area = in_groupid  and g.dt_sc>DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -1 HOUR) ) cur_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;#結束標識  
DROP TABLE IF EXISTS r_table ; CREATE TEMPORARY TABLE r_table select     d.*, a.vc_name as grouparea_name , g.dec_x,g.dec_y,g.dt_sc    ,c.dcount,dd.consumer_name,dd.vc_fadd,dd.vc_tadd,dd.int_service,dd.int_status    ,g.dec_x as distance#添加一列記錄距離from bi_driver d  left join bi_gisdata gon g.int_mubiao = d.int_driver and g.int_type = 1left join  bi_grouparea a on a.int_grouparea = d.int_arealeft join (select count(1) as dcount,int_driver  from bi_serviceinfo where int_status >-1  and int_status < 100 group by int_driver) con c.int_driver = d.int_driver left join (select int_service,consumer_name,vc_fadd,vc_tadd,int_driver,int_status from v_serviceinfo where int_service in     (select int_service from bi_serviceinfo     where  int_status >-1  and int_status < 100 and ifnull(int_driver,0)>0     group by int_driver)) ddon dd.int_driver = d.int_driver where int_area = in_groupid  and g.dt_sc>DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -1 Hour) ;
#delete from r_table;set temp_c_x =(select dec_fx from bi_serviceinfo where int_service = in_serviceid );set temp_c_y =(select dec_fy from bi_serviceinfo where int_service = in_serviceid);#select temp_c_x,temp_c_y;
OPEN cur1;fetchSeqLoop:LoopFETCH cur1 INTO temp_id,temp_x,temp_y;if done then      leave fetchSeqLoop;  else        #select temp_id,temp_x,temp_y;    update r_table set distance = f_getdistance(temp_c_x,temp_c_y,temp_x,temp_y) where int_driver = temp_id;end if;  end Loop;  CLOSE cur1;
select * from r_table where distance < 2000 order by distance;drop table r_table;END