mysql 存储过程使用游标实例

 

使用了mysql存储过程、临时表、游标

CREATE DEFINER=`root`@`%` PROCEDURE `get_driver_nearconsumer`(
    in_groupid int,
    in_serviceid int
)
BEGIN
declare 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 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) ;

#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:Loop
FETCH 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