学步园

LABEL_PROC:
BEGIN
    
    -- 任务表关联时使用的key值
    DECLARE v_faxkey VARCHAR(50) default '';
    -- 任务表中的传真状态
    DECLARE v_taskstate int default 0;
    DECLARE v_ID int default 0;
    DECLARE v_userName VARCHAR(50) default '';
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT ID FROM childfax where ParentId=p_id;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    -- 如果id不在合法范围，直接返回-1
    if( p_id<=0 ) then
        select -1 into result;
        leave LABEL_PROC; 
    end if;
    -- id 指定记录不存在,直接返回 -2
    if(not exists(select * from parentfax where id=p_id) ) then
        select -2 into result;
        leave LABEL_PROC;
    end if;
  if(select count(*) from faxtask where ParentId = p_id and status=1 > 0) then
        select -3 into result;
        select '正在发送中,不能删除';
                leave LABEL_PROC;
  end if;
  
    -- 设置子传真会删除状态 
    UPDATE childfax set faxstate=-1 where ParentId=p_id;
        
    -- 设置份传真为删除状态
    UPDATE parentfax set FaxState=-1 where id=p_id;
  
  -- 删除任务表中数据
  delete from faxtask where parentid = p_id;
  OPEN cur1;
    
    REPEAT
            FETCH cur1 INTO v_ID;
        
            IF NOT done THEN
                    select username into v_userName from user_childfax_view where id=v_ID;
                    call pro_add_log(v_ID,2,concat(v_userName,' 删除') );
            END IF;
        UNTIL done END REPEAT;
      CLOSE cur1;
  
    -- 返回执行成功结果
    select 1 into result;
END LABEL_PROC