每次写存储过程都是个挑战，不过每次写完，写好了，感觉都蛮不错的。

嘿嘿，看看，感觉良好。

其实有时候，我感觉不一定非得用存储过程，不过既然老大要求，那也没办法呢，做呢。

当然，这个确实是可以使用存储过程的呢。

DROP PROCEDURE IF EXISTS `clean_off_route`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `clean_off_route`(IN psvrid varchar(30),IN savedata varchar(30),out routeids varchar(30)) COMMENT '清除超时或者使用完成的路由，并下线'
begin 

  declare maxnum INTEGER DEFAULT 6; -- 路由公司最大使用次数，超过该次数，下线
declare maxnousehour INTEGER DEFAULT 3; -- 路由最大大无使用时间，大于该时间、下线

declare v_finished INTEGER DEFAULT 0;
declare pid varchar(30) default "";
 declare rid varchar(30) default "";
 declare rtype varchar(30) default "";




 DECLARE _Cur CURSOR for
select proxyserver_id,routeid,type from (
-- 过滤中转服务器、在线状态
select * from (
-- 全量1条件过滤
select * from (
-- 全量1--路由公司使用次数---
select r.proxyserver_id,r.flag,r.free,rcnums.*,'maxuse' type from route_info r left join
(
select routeid,companyid,count(*) num from (

-- 关联路由使用记录-公司
select rus.*, pcpsvr.companyid from route_use_log  rus
left JOIN
(
-- 手机账号-公司 中转服务器-，，对应关系
select cphone.phoneip,cphone.accountid,cpsvr.* from 
(
select 
substring_index(p.ip , '.',3) phone_ippre,p.accountid
,cast(substring_index(p.ip , '.',-1)  as SIGNED INTEGER) phone_iplast,
c.accountid companyid,c.company_name,p.accountid phoneaccount,p.city phonecity,p.ip phoneip from phoneaccount_info p LEFT JOIN company_info c on c.accountid=p.company_userid
order by c.accountid
) cphone
left join
(
select substring_index(substring_index(cp.ipdesc, '-',1), '.',3) ippre
, cast( substring_index(substring_index(cp.ipdesc, '-',1), '.',-1) as SIGNED INTEGER) ipstart
,cast( substring_index(substring_index(cp.ipdesc, '-',-1), '.',-1)  as SIGNED INTEGER) ipend
, psvr.id proxyserver_id ,psvr.assignphones,psvr.maxphones,cp.companyid,cp.ipdesc from proxyserver_info psvr left join company_proxyserver cp on psvr.id=cp.proxyserver_id
) cpsvr 
 on (cpsvr.companyid=cphone.companyid
 and cphone.phone_ippre=cpsvr.ippre 
 and cphone.phone_iplast>=cpsvr.ipstart
 and cphone.phone_iplast<=cpsvr.ipend 
) 
 -- 手机账号-公司 中转服务器-，，对应关系--end
) pcpsvr on rus.phoneip=pcpsvr.phoneip

) rtcmp  where time>DATE_SUB(now(),INTERVAL 24 HOUR)  group by companyid ,routeid order by count(*) desc
) rcnums on r.routeid=rcnums.routeid
-- 全量1--路由公司使用次数--- end
) tmax where tmax.num>maxnum 

union
(
-- 查询
select * from (
-- 全量2，xx小时内没有时间记录
select r.proxyserver_id,r.flag,r.free,r.routeid,rcnums.num,'' companyid,'nouse' type from route_info r left join
(

select routeid,count(*) num  from route_use_log where
 time>DATE_SUB(now(),INTERVAL maxnousehour HOUR)  group by routeid 
) rcnums on r.routeid=rcnums.routeid
-- 全量2，xx小时内没有时间记录--end
) tuse where tuse.num is null
)

) tall where proxyserver_id=psvrid and flag=2
) tall2;

DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET v_finished = 1;


set routeids = "xxxx";


set v_finished = 0;
-- 循环处理
OPEN _Cur;
dowork: LOOP
 FETCH _Cur INTO  pid,rid,rtype;

 IF v_finished = 1 THEN 
 LEAVE dowork;
 END IF;


-- insert into  mysql_pro_log   values(concat(pid,rid,rtype,date_format(now(),'%Y-%m-%d %H'),' '),now());

  -- 操作
  -- 处理返回
   set routeids= concat(routeids,",",rid) ;

if(savedata='true') then

  -- 表状态
 select routeid into rid from route_info where proxyserver_id=psvrid and routeid=rid and flag=2;
 if(rid is not null) then    -- 过滤
   --  路由状态
   update route_info set flag=0,free=0,offlinetime=now() ,proxyserver_id="" where proxyserver_id=psvrid and routeid=rid;
    -- 下线时间
	 update route_log_Info set offlinetime=now(),offlinetype="0" where offlinetime is null and routeid=rid and proxyserver_id=pid;

    
    if(rtype = 'maxuse') then
     --  服务完成
    insert into server_complete_route values(pid,rid,now());
    end if;
 end if;
end if;


 
END LOOP dowork;
CLOSE _Cur;


end
;;
DELIMITER ;