Use cursor instances for MySQL stored procedures

 

MySQL stored procedures, temporary tables, and cursors are used.

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. *,. 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; # end ID
Drop table if exists r_table; create temporary table r_table select D. *,. 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 # Add a column of records from bi_driver d left join bi_gisdata Gon g.int _ mubiao = d.int _ Driver and g.int _ type = 1 left 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, numeric;
Open cur1; values: loopfetch cur1 into temp_id, temp_x, temp_y; if done then leave values; 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