DB2中怎么循环插入语句
insert into "T_RP_GY_DMBXX"("BBDM","TBNAME","DM_BH","SJDM_BH","MC","MS","CPBH","XY_BJ","LR_SJ","XG_SJ") values ('RP003','SYNSYHZBCZLQKB','@DB_MH',null,null,null,null,'1',null,null);
如上语句要求是DB_MH从1查到45 其他不需要改 如何循环插入45次且每次加1
begin atomic declare i integer; set i=1; while i<=45 do insert into "T_RP_GY_DMBXX"("BBDM","TBNAME","DM_BH","SJDM_BH","MC","MS","CPBH","XY_BJ","LR_SJ","XG_SJ") values ('RP003','SYNSYHZBCZLQKB',trim(char(i)),null,null,null,null,'1',null,null); set i=i+1; end while; end@
写到一个文件中，比如tmp.sql，然后用 db2 -td -f tmp.sql 执行。
掉了一个@号，用下面这句执行
db2 -td@ -f tmp.sql
create procedure db_sjpt.sp_insert_T_RP_GY_DMBXX(in p_count int ,in p_tbname varchar(32) ,in p_BBDM varchar(32) ,out p_result varchar(1000)) begin declare v_sql varchar(1000); declare v_row int default 1; while v_row<=p_count do set v_sql='insert into T_RP_GY_DMBXX(BBDM,TBNAME,DM_BH,SJDM_BH,MC,MS,CPBH,XY_BJ,LR_SJ,XG_SJ) values ('''||p_BBDM||''','''||p_tbname||''','''||v_row;--||''',null,null,null,null,1,null,null)'; set v_row=v_row+1; end while; set p_result=v_sql; end;