pM7A17aU

1use acrs;
2drop procedure getUA;
3delimiter ;;
4CREATE PROCEDURE getUA()
5begin
6DECLARE min INTEGER;
7DECLARE max INTEGER;
8
9select min(id) into min from user_info;
10set max = min + 10000;
11-- select max(id) into max from user_info;
12CREATE TABLE IF NOT EXISTS user_activity_load(INDEX netactivity (id)) select * from user_info where id between min and max;
13insert into user_activity_load (id, src_ip, dst_ip, src_port, dst_port, data, Created, smalicious, sgeoLoc, seq, app_id, tcp_flag, dgeoLoc, dmalicious)
14  select id, src_ip, dst_ip, src_port, dst_port, data, Created, smalicious, sgeoLoc, seq, app_id, tcp_flag, dgeoLoc, dmalicious from user_info where id between min and max;
15DELETE from user_info where id >= min and id <=max;
16  WHILE (min <= max) DO
17    BEGIN
18      DECLARE src INTEGER unsigned;
19      DECLARE dst INTEGER unsigned;
20      DECLARE s_id INTEGER;
21      DECLARE d_id INTEGER;
22      select src_ip, dst_ip into src, dst from user_activity_load where id = min;
23      select id into s_id from ip2location_db3 where (src between ip_from and ip_to);
24      select id into d_id from ip2location_db3 where (dst between ip_from and ip_to);
25      update user_activity_load set sgeoLoc = s_id, dgeoLoc = d_id where id = min;
26    END;
27    set min = min + 1;
28  END WHILE;
29end ;;
30delimiter ;