-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `P_ReleaseIp`(
In reip varchar(20),
in remac varchar(20),
in ipmode varchar(20),
out out_result int)
BEGIN
#释放的IP
DECLARE t_error INTEGER DEFAULT 0;
declare t_pid INTEGER DEFAULT 0;
DECLARE vlannum INTEGER;
declare m_switch varchar(20);
declare m_port varchar(20);
declare cur_release CURSOR for
select Switch,`Port` from VlanBindDefaultSwtich
where VlanId=(select ResourceId from AllResourceIpInfo where IpValue=funGetIPValue(reip));
declare zw_switchport CURSOR for
SELECT switch,`port` FROM IpManage_v2.StaticIpMacBindInfo
where ip= reip and mac=remac;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET t_pid = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
select VlanNumber into vlannum from VlanInfo where funGetIPValue(reip) BETWEEN StartIpValue and EndIpValue;
START TRANSACTION;
if ipmode='禁用' THEN
delete from IpDisabledInfo where IpValue=funGetIPValue(reip);
DELETE from StaticIpMacBindInfo where Ip=reip and Mac='10:00:00:00:00:01';
/*DELETE from Ipswitcher where Ip=reip;*/
update AllResourceIpInfo set State=1 where IpValue=funGetIPValue(reip) and State=2;
open cur_release;
FETCH cur_release into m_switch,m_port;
while t_pid<>1 AND t_error<>1 DO
insert into StaticIpMacBindTaskInfo(Ip,Mac,Switch,Type,Port,Vlan,VpnInst,CreateTime)
values(reip,'10:00:00:00:00:01',m_switch,'unbind',m_port,vlannum,0,DATE_FORMAT(NOW(),'%Y-%m-%d %T'));
FETCH cur_release into m_switch,m_port;
end while;
close cur_release;
elseif ipmode='保护' then
delete from IpProtectedInfo where Ip=reip;
DELETE from IpProtectedMacList where Ip=reip and Mac=remac;
update AllResourceIpInfo set State=1 where IpValue=funGetIPValue(reip) and State=3;
elseif ipmode='绑定' THEN
delete from IpBindedInfo where Ip=reip and Mac=remac;
delete from StaticIpMacBindInfo where Ip=reip and Mac=remac;
update AllResourceIpInfo set State=1 where IpValue=funGetIPValue(reip) and State=4;
open zw_switchport;
FETCH zw_switchport into m_switch,m_port;
while t_pid<>1 AND t_error<>1 DO
insert into StaticIpMacBindTaskInfo(Ip,Mac,Switch,Type,Port,Vlan,CreateTime)
values(reip,remac,m_switch,'unbind',m_port,vlannum,DATE_FORMAT(NOW(),'%Y-%m-%d %T'));
FETCH zw_switchport into m_switch,m_port;
end while;
close zw_switchport;
end if;
if t_error=1 THEN
ROLLBACK;set out_result=0;
ELSE
COMMIT;set out_result=1;
end if;
END
