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