mysql 过程

CREATE DEFINER=`root`@`%` PROCEDURE `tt2`(
    out  o_ret      int
)
task:begin

        set @v_r = '0_2_123';
        if(@v_r is not null and '' <> @v_r) then
                set o_ret = 1;
        else
                set o_ret = 2;
        end if;
end

call tt2(@ret);
select @ret;

CREATE DEFINER=`root`@`%` PROCEDURE `p_olps_fetch_portrait_task`(
    in   i_task_type    varchar(128),
    in   i_task_id      varchar(64),
    in   i_proc_seq     int,
    out  o_task_id      varchar(64)
)
label_olps_fetch_portrait_task:begin
    declare v_sql         varchar(1024);
    declare v_name        varchar(32);
    declare v_task_id     varchar(64);
    declare v_cnt         int default 0;
    declare v_ret         int default -1;
    
    set v_name = 'olps_fetch_portrait_task';
    
    set o_task_id = '';

    call p_add_log(7, v_name, concat('params i_task_type:', i_task_type, ', i_proc_seq:', i_proc_seq, ', i_task_id:', i_task_id));
    
    
    if(i_task_id is not null and '' <> i_task_id) then
        call p_imp_olps_fetch_portrait_task(i_task_type, i_task_id, i_proc_seq, v_ret);
    end if;
    if v_ret > 0 then
            set o_task_id = i_task_id;
        leave label_olps_fetch_portrait_task;
    end if;
    
    select task_id into v_task_id from t_object_export_task where status='START' order by create_time asc limit 1;
    set v_cnt = FOUND_ROWS();
    if v_cnt > 0 then
        call p_imp_olps_fetch_portrait_task(i_task_type, v_task_id, i_proc_seq, v_ret);
    end if;
    
    if v_ret > 0 then
        set o_task_id = v_task_id;
        leave label_olps_fetch_portrait_task;
    end if;
    
end

CREATE DEFINER=`root`@`%` PROCEDURE `p_imp_olps_fetch_portrait_task`(
    in   i_task_type    varchar(64),
    in   i_task_id      varchar(64),
    in   i_proc_seq     int,
    out  o_ret          int
)
label_imp_olps_fetch_portrait_task:begin
    declare v_sql       varchar(1024);
    declare v_name      varchar(32);
    declare v_task_type varchar(64) default '1';
    declare v_cnt       int default 0;
    declare v_seq       int default 0;
    declare v_now       bigint default 0;
    
    set v_task_type = i_task_type;
    set v_name = 'imp_olps_fetch_portrait_task';
    set o_ret = 0;
    
    call p_add_log(7, v_name, concat('params i_task_id:', i_task_id));
    
    
    if(i_task_id is null or '' = i_task_id) then
        call p_add_log(3, v_name, 'i_task_id error.');
        leave label_imp_olps_fetch_portrait_task;
    end if;
    
    
    set v_cnt = 0;
    select proc_seq into v_seq from `t_offlinetask_apply` where task_id = i_task_id and task_type= v_task_type and lock_status=1;
    set v_cnt = FOUND_ROWS();
    if v_cnt > 0 then
        call p_add_log(3, v_name, concat('task_id:', i_task_id, ' is locked by seq:', v_seq));
        leave label_imp_olps_fetch_portrait_task;
    end if;
    
    start transaction;
    
        update `t_object_export_task` set status='WORK' where task_id = i_task_id and status='START';
    
        set v_now = UNIX_TIMESTAMP() * 1000;
        insert into `t_offlinetask_apply` values(v_task_type, i_task_id, i_proc_seq, v_now, 0, 1, '');
    commit;
    
    
    set v_cnt = 0;
    select count(1) into v_cnt from `t_offlinetask_apply` where task_type = v_task_type and task_id = i_task_id and lock_status=1 and proc_seq = i_proc_seq;
    if v_cnt = 0 then
        call p_add_log(3, v_name, concat('task_id:', i_task_id, ' locked failed.'));
        leave label_imp_olps_fetch_portrait_task;
    end if;
    
    set v_cnt = 0;
    select count(1) into v_cnt from `t_object_export_task` where task_id = i_task_id and status='WORK';
    if v_cnt = 0 then
        call p_add_log(3, v_name, concat('task_id:', i_task_id, ' updated failed.'));
        leave label_imp_olps_fetch_portrait_task;
    end if;
    
    
    set o_ret = 1;
    
end

CREATE DEFINER=`root`@`%` PROCEDURE `p_add_log`(
    in   log_level      int, 
    in   proc_name      varchar(32),
    in   log_info       varchar(2048)
)
lable_p_add_log:
begin
    declare v_err int default 0;
    declare v_logname varchar(64);   
    declare v_sql varchar(2048);
    
    declare continue handler for sqlexception set v_err=1;
    
      
    set v_logname = concat('t_log_',DATE_FORMAT(CURDATE(), '%Y%m')); 
    set v_sql = concat(' insert into ',v_logname,'(log_level,proc_name,log_info) values(',log_level,',''',proc_name,''',''',log_info,''')');
    set @v_sql=v_sql;
    prepare stmt from @v_sql;
    EXECUTE stmt;
    deallocate prepare stmt; 
    if v_err =1 then
        set v_sql =concat('create table ',v_logname,     
        '(log_id    BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        log_time  TIMESTAMP  NOT NULL default now(),
        log_level int not null, 
        proc_name varchar(32) not null,
        log_info  varchar(2048) null)');
        set @v_sql=v_sql;
        prepare stmt from @v_sql;
        EXECUTE stmt;
         
        
        set v_sql = concat(' insert into ',v_logname,'(log_level,proc_name,log_info) values(',log_level,',''',proc_name,''',''',log_info,''')');
        set @v_sql=v_sql;
        prepare stmt from @v_sql;
        EXECUTE stmt; 
        deallocate prepare stmt;  
    end if;
end lable_p_add_log