Question

oracle存储过程怎样批量插入新数据

Answer 1

需要生成的SQL
insert into TMP_UPSTATE_CASEKEY values('TMP0000001', 1, sysdate);

存储过程实现
create or replace procedure proc_casekey_upstate
as
casekey char(14);
begin
for i in 1..10000000 loop
casekey := 'TMP'||lpad(i,7,0); -- TMP0000001
insert into TMP_UPSTATE_CASEKEY values(casekey, 1, sysdate);
end loop;
commit;
end;

begin
proc_casekey_upstate();
end;

测试发现生成一千万条数据用了14分钟左右，性能还是可以了，如果先去掉TMP_NUM_STATUS_ID的外键估计更快。
或者：
insert into TMP_UPSTATE_CASEKEY select 'TMP'||LPAD(rownum,7,0),1,sysdate from dual connect by level <= 1000000;