截取号段

create or replace procedure p_get_haoduan(v_start_number in number, --传入起始号
                                          v_end_number   in number, --传入终止号
                                          v_CITY_CODE    in varchar2, --传入城市代号
                                          emsg           out varchar2) --接收返回结果
 as
  /*
  操作流程：
  根据起始号，终止号在 SK_DEVICE_SHIPMENTS_NUMBER 查找是否有记录
  1、如果没有，不管了
  2、如果存在，与在SK_DEVICE_SHIPMENTS关联 判断省份（CITY_CODE）是否不正确 且准入状态(SHIPMENTS_STATUS = 3)
       2.1 如果不是，进行提示，不能删除
       2.2 如果是 ，在数据库已有号段中删除起号-止号间的记录。

  */
  v_qsh      number; --接收起始号码
  v_zzh      number; --接收终止号码
  v_citycode varchar2(330); --接收城市代号
  v_status   varchar2(330); --接收操作状态
  v_uuid     varchar2(32); --接收UUID
  v_DQ_GUID  varchar2(32);

  cursor cur(v_start_number number, v_end_number number) is
    select b.start_number,
           b.end_number,
           a.city_code,
           a.shipments_status,
           b.uuid
      from SK_DEVICE_SHIPMENTS_NUMBER b, SK_DEVICE_SHIPMENTS a
     where to_number(end_number) >= v_start_number
       and to_number(start_number) <= v_end_number
       and a.uuid = b.fk_uuid;

begin

  emsg := ' ';

  open cur(v_start_number, v_end_number);
  loop
    fetch cur
      into v_qsh, v_zzh, v_citycode, v_status, v_uuid;
    exit when cur%notfound;

    if length(v_start_number)<>13 or length(v_end_number)<>13 or length(v_CITY_CODE)<>6  then
    emsg :='输入参数有误';
      goto endtras;
      end if;

    if (v_citycode != v_CITY_CODE and v_status = 3) then

    --起始号小于等于数据库起始号，终止号大于等于数据库终止号
      if v_start_number <= v_qsh and v_end_number >= v_zzh then
        delete SK_DEVICE_SHIPMENTS_NUMBER r where r.uuid = v_uuid;
        emsg := emsg || ' delete：' || v_qsh || ' -- ' || v_zzh;
      
      --起始号小于等于数据库起始号，终止号码小于等于数据库终止号码  
      elsif  v_start_number <= v_qsh and v_end_number <= v_zzh then
        update SK_DEVICE_SHIPMENTS_NUMBER r
           set r.start_number  = to_char(v_end_number + 1),
               r.device_number = to_number(r.end_number) -
                                 (v_end_number + 1)+1
         where r.uuid = v_uuid;
         emsg := emsg || ' update:' || to_char(v_end_number + 1) || ' -- ' || v_zzh;
        --起始号大于等于数据库起始号，终止号大于等于数据库终止号
      elsif v_start_number >= v_qsh and v_end_number >= v_zzh then
        update SK_DEVICE_SHIPMENTS_NUMBER r
           set r.end_number    = to_char(v_start_number - 1),
               r.device_number = to_number(v_start_number-1 ) -
                                 to_number(r.start_number)+1
         where r.uuid = v_uuid;
         emsg := emsg || ' update:' || v_qsh || ' -- ' || to_char(v_start_number - 1);
        --起始号大于等于数据库起始号，终止号小于等于数据库终止号
      elsif v_start_number >= v_qsh and v_end_number <= v_zzh then
        v_DQ_GUID := SYS_GUID();
        update SK_DEVICE_SHIPMENTS_NUMBER r
           set r.end_number    = to_char(v_start_number - 1),
               r.device_number = to_number(v_start_number - 1) -
                                 to_number(r.start_number)+1
         where r.uuid = v_uuid;
         emsg := emsg || ' update:' || v_qsh || ' -- ' || to_char(v_start_number - 1);
        insert into SK_DEVICE_SHIPMENTS_NUMBER
          (uuid,
           fk_uuid,
           model_number,
           version_number,
           device_number,
           start_number,
           end_number)
          select v_DQ_GUID uuid,
                 fk_uuid,
                 r.model_number,
                 r.version_number,
                 to_number(v_zzh) - (v_end_number + 1)+1,
                 to_char(v_end_number + 1),
                 v_zzh
            from SK_DEVICE_SHIPMENTS_NUMBER r
           where r.uuid = v_uuid;
           emsg := emsg || ' insert:' || to_char(v_end_number + 1) || ' -- ' || v_zzh;
       
      end if;
    else

      emsg := emsg || ' 不能删除，请确认：' || v_qsh || ' -- ' || v_zzh;
      --记录下来，提示用
    end if;

  end loop;
  close cur;

  <<endtras>>
  null;
exception
  when no_data_found then
    emsg := '失败';

  when others then
    emsg := '失败';

end p_get_haoduan;
/