利用DB2存储过程高效批量插入数据：实践与优化技巧

要求&＃xff1a;插入一张表(TESTMV)中三个字段(ID, GROUPID【组id】, USERID【用户id】)&＃xff0c;为了产生多个组&＃xff0c;每个组多个人的数据&＃xff0c;进行统计每个组多少人数据用

代码如下&＃xff1a;

delete testmv;

create procdedure insertdb()

language SQL

begin

declare v_num int;

declare v_num1 int;

declare v_num2 varchar(12);

set v_num&＃61;0;

set v_num1&＃61;1;

loop1:

LOOP

if(v_num<100) then

set v_num &＃61; v_num &＃43;1;

set v_num1&＃61;1;

loop2:

LOOP

if(v_num1<&＃61;100) then

insert into testmv(ID, GROUPID, USERID) VALUES(&＃39;id&＃39;||v_num||v_num1, &＃39;groupid&＃39;||v_num, &＃39;userid&＃39;||v_num||v_num1);

set v_num1&＃61;v_num1&＃43;1;

else

leave loop2;

end if;

end loop loop2;

else

leave loop1;

end if;

end loop loop1;

end;

call insertdb();

drop procedure insertdb();