delimiter | create procedure test_cursor() begin DECLARE plat char(30); DECLARE e_type, e_id, no_more_departments INT; DECLARE tmp_cur cursor for (select uu.platform_gamesvr_id , uu.event_type, uu.event_id from t_user_event_info as uu, (select u.platform_gamesvr_id , u.event_type, u.event_id, tmp_new.t_platform, tmp_new.t_event_type, tmp_new.t_count from t_user_event_info as u, (select tmp.t_platform, tmp.t_event_type, tmp.t_count from (select platform_gamesvr_id as t_platform, event_type as t_event_type, count(*) as t_count from t_user_event_info where event_type >= 400055 and event_type <=400057 group by platform_gamesvr_id,event_type) tmp ) as tmp_new where u.platform_gamesvr_id = tmp_new.t_platform and u.event_type=400054 and ((u.event_id = 1 and tmp_new.t_event_type = 400055 and tmp_new.t_count = 7) or (u.event_id = 2 and tmp_new.t_event_type = 400056 and tmp_new.t_count = 10) or (u.event_id = 3 and tmp_new.t_event_type = 400057 and tmp_new.t_count = 14))) tmp_new_1 where (uu.platform_gamesvr_id = tmp_new_1.platform_gamesvr_id and uu.event_type = tmp_new_1.event_type and uu.event_id = tmp_new_1.event_id) or (uu.platform_gamesvr_id = tmp_new_1.t_platform and uu.event_type = tmp_new_1.t_event_type)); DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; SET no_more_departments=0; open tmp_cur; REPEAT fetch tmp_cur into plat, e_type, e_id; select plat, e_type, e_id; delete from t_user_event_info where platform_gamesvr_id = plat and event_type = e_type and event_id = e_id; UNTIL no_more_departments END REPEAT; close tmp_cur; end;| delimiter ; call test_cursor(); drop procedure test_cursor;
mysql实验程序设计与游标_初次实验mysql游标
最新推荐文章于 2021-04-21 19:32:30 发布