mysql 游标里 使用if_mysql存储过程和游标以及if-else,while典型实例

-- --------------------------------------------------------------------------------

-- 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