oracle过程循环,oracle存储过程中循环插入数据

//oracle 循环插入数据procedure Insert_WData( p_CODE1 ao_model.code1%type,

p_BRANDID ao_model.brandid%type,

p_CODEvarchar2,

p_CONF_VALvarchar2,

p_DESCvarchar2,

p_CODE2varchar2,

p_DESCvarchar2,

ErrOutin out varchar2) is

begin

--参数

declarestartposition1number(10);

len1number(10);

startposition2number(10);

len2number(10);

startposition3number(10);

len3number(10);

output1varchar2(1024);

output2varchar2(1024);

output3varchar2(1024);

numnumber(1);beginstartposition1 := 1;

startposition2 := 1;

startposition3 := 1;

loopselect instr(p_CODE, ‘|‘, startposition1)intolen1fromdual;select instr(p_CONF_VAL, ‘|‘, startposition2)intolen2fromdual;select instr(p_DESC, ‘|‘, startposition3)intolen3fromdual;if len1 != 0 then

begin

selectsubstr(p_CODE,

startposition1,

len1-startposition1)intooutput1fromdual;selectsubstr(p_CONF_VAL,

startposition2,

len2-startposition2)intooutput2fromdual;selectsubstr(p_DESC,

startposition3,

len3-startposition3)intooutput3fromdual;

num := 0;select count(*)intonumfromao_model awhere a.c0084_brandid =p_BRANDIDand a.c0001_code1 =p_CODE1and a.c0001_code =output1;if num >= 1 then

updateao_model bset b.conf_val =output2where b.c0084_brandid =p_BRANDIDand b.c0001_code1 =p_CODE1and b.c0001_code =output1;else

insert intoao_model

(C0001_CODE1,

C0084_BRANDID,

C0001_CODE,

CONF_VAL,DESC)values(p_CODE1,

p_BRANDID,

output1|| ‘‘,

output2|| ‘‘,

output3|| ‘‘);end if;commit;end;else

begin

selectsubstr(p_CODE, startposition1)intooutput1fromdual;selectsubstr(p_CONF_VAL, startposition2)intooutput2fromdual;selectsubstr(p_DESC, startposition3)intooutput3fromdual;

num := 0;select count(*)intonumfromao_model awhere a.c0084_brandid =p_BRANDIDand a.c0001_code1 =p_CODE1and a.c0001_code =output1;if num >= 1 then

updateao_model bset b.conf_val =output2where b.c0084_brandid =p_BRANDIDand b.c0001_code1 =p_CODE1and b.c0001_code =output1;else

insert intoao_model

(C0001_CODE1,

C0084_BRANDID,

C0001_CODE,

CONF_VAL,DESC)values(p_CODE1,

p_BRANDID,

output1|| ‘‘,

output2|| ‘‘,

output3|| ‘‘);end if;commit;end;exit;end if;

startposition1 := len1 + 1;

startposition2 := len2 + 1;

startposition3 := len3 + 1;endloop;end;

EXCEPTIONWHEN OTHERS THENErrOut :=SQLERRM;rollback;end Insert_WParamStatusData;