mysql 游标使用实例_mysql 存储过程使用游标实例

CREATE DEFINER=`root`@`%` PROCEDURE`get_driver_nearconsumer`(

in_groupidint,

in_serviceidint)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 defaultFALSE;DECLARE cur1 CURSOR FOR SELECT int_driver,dec_x,dec_y FROM(selectd.*, a.vc_name asgrouparea_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_statusfrombi_driver dleft joinbi_gisdata gon g.int_mubiao = d.int_driver and g.int_type = 1

left joinbi_grouparea aon a.int_grouparea =d.int_arealeft join(select count(1) asdcount,int_driverfrombi_serviceinfowhere int_status >-1 and int_status < 100 group byint_driver

) con c.int_driver =d.int_driverleft join(selectint_service,consumer_name,vc_fadd,vc_tadd,int_driver,int_statusfromv_serviceinfowhere int_service in(select int_service frombi_serviceinfowhere int_status >-1 and int_status < 100 and ifnull(int_driver,0)>0

group byint_driver)

) ddon dd.int_driver =d.int_driverwhere int_area = in_groupid and g.dt_sc>DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -1HOUR)

) cur_table;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =true;#结束标识DROP TABLE IF EXISTSr_table ;CREATE TEMPORARY TABLEr_tableselectd.*, a.vc_name asgrouparea_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_xasdistance#添加一列记录距离frombi_driver dleft joinbi_gisdata gon g.int_mubiao = d.int_driver and g.int_type = 1

left joinbi_grouparea aon a.int_grouparea =d.int_arealeft join(select count(1) asdcount,int_driverfrombi_serviceinfowhere int_status >-1 and int_status < 100 group byint_driver

) con c.int_driver =d.int_driverleft join(selectint_service,consumer_name,vc_fadd,vc_tadd,int_driver,int_statusfromv_serviceinfowhere int_service in(select int_service frombi_serviceinfowhere int_status >-1 and int_status < 100 and ifnull(int_driver,0)>0

group byint_driver)

) ddon dd.int_driver =d.int_driverwhere int_area = in_groupid and g.dt_sc>DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -1Hour) ;

#delete fromr_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);

#selecttemp_c_x,temp_c_y;OPENcur1;

fetchSeqLoop:LoopFETCH cur1 INTOtemp_id,temp_x,temp_y;if done thenleave fetchSeqLoop;else#selecttemp_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;endLoop;CLOSEcur1;select * from r_table where distance < 2000 order bydistance;drop tabler_table;END