以下是业务处理部分,insert部分为啥不执行,需要修改哪里while sGroupA is not null loop
              nPosA:=instr(sGroupA,',');
              if JOINTRELDESIGNIDB is null then
                  if nPosA>0 then
                     sIndexA:=to_number(substr(sGroupA,1,instr(sGroupA,'-')-1));
                     eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1,nPosA-1-instr(sGroupA,'-')));
                     sGroupA:=substr(sGroupA,nPosA+1);
                  else
                     sIndexA:=to_number(substr(sGroupA,1,instr(sGroupA,'-')-1));
                     eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1));
                     sGroupA:=null;
                  end if;                  ------生成新的熔接关系                  insert into TGX_JOINT_REL_DESIGN(CONTAINER_TYPE, CONTAINER_ID, FLDBOXTAG, BOX_ID, A_OPTCABLE_ID, A_OC_SEG_ID,
                 A_OC_SEG_NO, A_S_FIBER_NUM, A_E_FIBER_NUM, A_COOR, B_OPTCABLE_ID, B_OC_SEG_ID, B_OC_SEG_NO, B_S_FIBER_NUM,
                 B_E_FIBER_NUM, B_COOR)
                  select container_type, container_id, fldboxtag,box_id, a_optcable_id, a_oc_seg_id,
                         a_oc_seg_no, sIndexA, eIndexA, '', null, null, null, null, null, ''
                         from TGX_JOINT_REL_DESIGN
                         where FLDID = JOINTRELDESIGNIDA;
               else
                   sGroupB:=trim(FIBERGROUPB);
                   nPosA:=instr(sGroupA,',');
                   nPosB:=instr(sGroupB,',');
                   if nPosA>0 then
                      sIndexA:=to_number(substr(sGroupA,1,instr(sGroupA,'-')-1));
                      eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1,nPosA-1-instr(sGroupA,'-')));
                      sGroupA:=substr(sGroupA,nPosA+1);
                      sIndexB:=to_number(substr(sGroupB,1,instr(sGroupB,'-')-1));
                      eIndexB:=to_number(substr(sGroupB,instr(sGroupB,'-')+1,nPosB-1-instr(sGroupB,'-')));
                      sGroupB:=substr(sGroupB,nPosB+1);
                   else
                      sIndexA:=to_number(substr(sGroupA,1,instr(sGroupA,'-')-1));
                      eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1));
                      sGroupA:=null;
                      sIndexB:=to_number(substr(sGroupB,1,instr(sGroupB,'-')-1));
                      eIndexB:=to_number(substr(sGroupB,instr(sGroupB,'-')+1));                   end if;                   ------分组并建立熔接关系                   insert into TGX_JOINT_REL_DESIGN(CONTAINER_TYPE, CONTAINER_ID, FLDBOXTAG, BOX_ID, A_OPTCABLE_ID, A_OC_SEG_ID,
                 A_OC_SEG_NO, A_S_FIBER_NUM, A_E_FIBER_NUM, A_COOR, B_OPTCABLE_ID, B_OC_SEG_ID, B_OC_SEG_NO, B_S_FIBER_NUM,
                 B_E_FIBER_NUM, B_COOR )
                         select a.container_type, a.container_id, a.fldboxtag, a.box_id, a.a_optcable_id, a.a_oc_seg_id,
                         a.a_oc_seg_no, sIndexA, eIndexA, '', b.a_optcable_id, b.a_oc_seg_id, b.a_oc_seg_no, sIndexB, eIndexB, ''
                         from TGX_JOINT_REL_DESIGN a , TGX_JOINT_REL_DESIGN b
                         where a.FLDID = JOINTRELDESIGNIDA and b.fldid=JOINTRELDESIGNIDB;                   insert into TGX_JOINT_REL_DESIGN(CONTAINER_TYPE, CONTAINER_ID, FLDBOXTAG, BOX_ID, A_OPTCABLE_ID, A_OC_SEG_ID,
                 A_OC_SEG_NO, A_S_FIBER_NUM, A_E_FIBER_NUM, A_COOR, B_OPTCABLE_ID, B_OC_SEG_ID, B_OC_SEG_NO, B_S_FIBER_NUM,
                 B_E_FIBER_NUM, B_COOR )
                         select a.container_type, a.container_id, a.fldboxtag, a.box_id, a.a_optcable_id, a.a_oc_seg_id,
                         a.a_oc_seg_no, sIndexB, eIndexB, '', b.a_optcable_id, b.a_oc_seg_id, b.a_oc_seg_no, sIndexA, eIndexA, ''
                         from TGX_JOINT_REL_DESIGN a, TGX_JOINT_REL_DESIGN b
                         where a.FLDID = JOINTRELDESIGNIDB and b.fldid = JOINTRELDESIGNIDA;
                 end if;
              end loop;

解决方案 »

  1.   

    刚刚测了一下,就是这行的问题
    eIndexB:=to_number(substr(sGroupB,instr(sGroupB,'-')+1));
    说是不能转换为数字,
    但是类似的用法
    eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1)); 
    是能够正确执行的,传入的sGroupB和sGroupA一样
      

  2.   

    应该还是传入的值有问题。
    可以放在简单的呃pl/sql语句块里做下测试
      

  3.   


    找到问题了,我把这块sGroupB:=trim(FIBERGROUPB);放到了循环里  ,使得每次取到的B端的值总是最初传进来的