oracle存储过程中循环插入数据
时间：2021-07-01 10:21:17
帮助过：6人阅读
oracle 循环插入数据
procedure Insert_WData( p_CODE1 ao_model.code1
%type,
p_BRANDID ao_model.brandid%type,
p_CODE varchar2,
p_CONF_VAL varchar2,
p_DESC varchar2,
p_CODE2 varchar2,
p_DESC varchar2,
ErrOut in out
varchar2)
is
begin
--参数
declare
startposition1 number(
10);
len1 number(
10);
startposition2 number(
10);
len2 number(
10);
startposition3 number(
10);
len3 number(
10);
output1 varchar2(
1024);
output2 varchar2(
1024);
output3 varchar2(
1024);
num number(
1);
begin
startposition1 := 1;
startposition2 := 1;
startposition3 := 1;
loop
select instr(p_CODE,
‘|‘, startposition1)
into len1
from dual;
select instr(p_CONF_VAL,
‘|‘, startposition2)
into len2
from dual;
select instr(p_DESC,
‘|‘, startposition3)
into len3
from dual;
if len1
!= 0 then
begin
select substr(p_CODE,
startposition1,
len1 - startposition1)
into output1
from dual;
select substr(p_CONF_VAL,
startposition2,
len2 - startposition2)
into output2
from dual;
select substr(p_DESC,
startposition3,
len3 - startposition3)
into output3
from dual;
num := 0;
select count(
*)
into num
from ao_model a
where a.c0084_brandid
= p_BRANDID
and a.c0001_code1
= p_CODE1
and a.c0001_code
= output1;
if num
>= 1 then
update ao_model b
set b.conf_val
= output2
where b.c0084_brandid
= p_BRANDID
and b.c0001_code1
= p_CODE1
and b.c0001_code
= output1;
else
insert into ao_model
(C0001_CODE1,
C0084_BRANDID,
C0001_CODE,
CONF_VAL,
DESC)
values
(p_CODE1,
p_BRANDID,
output1 || ‘‘,
output2 || ‘‘,
output3 || ‘‘);
end if;
commit;
end;
else
begin
select substr(p_CODE, startposition1)
into output1
from dual;
select substr(p_CONF_VAL, startposition2)
into output2
from dual;
select substr(p_DESC, startposition3)
into output3
from dual;
num := 0;
select count(
*)
into num
from ao_model a
where a.c0084_brandid
= p_BRANDID
and a.c0001_code1
= p_CODE1
and a.c0001_code
= output1;
if num
>= 1 then
update ao_model b
set b.conf_val
= output2
where b.c0084_brandid
= p_BRANDID
and b.c0001_code1
= p_CODE1
and b.c0001_code
= output1;
else
insert into ao_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;
end loop;
end;
EXCEPTION
WHEN OTHERS
THEN
ErrOut := SQLERRM;
rollback;
end Insert_WParamStatusData;
oracle存储过程中循环插入数据
标签：int rom else desc sel col ack llb div
