mysql 存储过程示例 mysql 存储过程

drop procedure if EXISTS shige_proce;
create procedure shige_proce(in p_app_id varchar(32),in p_cpu_info varchar(32),out result varchar(20),out resultCode int)
label:begin -- 加上label: 之后可以在退出的地方 LEAVE label;直接退出
    declare expiredTime datetime; -- 过期时间
    declare codeType char(3); -- 卡类型
    declare opendays tinyint(3) default 7; -- 当前卡状态，0 未启用，1 分发 2 启用
    set result = '卡号错误！';
    set resultCode = 500;
    select p_app_id;
    select p_cpu_info;
    select  expired_time into expiredTime from yys_open_card where cpu_info = p_cpu_info and appid like CONCAT(p_app_id,'%');

    select expiredTime;
    IF expiredTime is null THEN
     -- 这里是为空的情况，表示这个卡还没有开通或者是假的
     -- 查询是否存在卡号
--   select 1;
     select code_type into codeType from yys_appcode where id = p_app_id and `status` = 1;
--   select codeType;
     IF codeType is null THEN
            set result =  '卡号错误，或者该卡已经绑定其他电脑！';
            set resultCode =  500;
        ELSE
--          select '卡号正确的！';
--          select codeType;
            CASE codeType
                WHEN 'WZH' THEN
                    set opendays = 7;
                when 'MYU' then
                    set opendays = 31;
                when 'YNI' then
                    set opendays = 365;
                when 'VER' then
                    set opendays = 36500;
                when 'BNI' then
                    set opendays = 183;
                ELSE
                    select 100;
                    set result =  '卡类型错误';
                    set resultCode =  500;
                    LEAVE label;
            END CASE;
            -- 这里说明卡号正确，需要添加开卡信息，并记录过期时间
--              begin;
                START TRANSACTION;
                    insert into yys_open_card(cpu_info,appid,card_type,create_time,expired_time) values (p_cpu_info,p_app_id,codeType,NOW() ,date_add(now(), interval opendays day));

                    update yys_appcode set `status` = 2 where id = p_app_id and `status` = 1;
--                  select 1/0;
                    set result =  '创建卡号成功！';
                    set resultCode =  200;
                commit;
        END IF;
    ELSE
--      SELECT '卡号正确，正在验证是否过期!';
        IF expiredTime > NOW() THEN
            set result =  '未过期';
            set resultCode =  200;
        ELSE
            set result =  '已过期';
            set resultCode =  500;
        END IF;
    END IF;

end;