以下是业务处理部分,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;
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;
eIndexB:=to_number(substr(sGroupB,instr(sGroupB,'-')+1));
说是不能转换为数字,
但是类似的用法
eIndexA:=to_number(substr(sGroupA,instr(sGroupA,'-')+1));
是能够正确执行的,传入的sGroupB和sGroupA一样
可以放在简单的呃pl/sql语句块里做下测试
找到问题了,我把这块sGroupB:=trim(FIBERGROUPB);放到了循环里 ,使得每次取到的B端的值总是最初传进来的