MySQL stored procedures and cursors and if-else,while typical instances

-- --------------------------------------------------------------------------------
--Routine DDL
--note:comments before and after the routine body is not being 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= ' Disable ' 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= ' protection ' 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= ' bind ' 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

MySQL stored procedures and cursors and if-else,while typical instances