批量导入数据（insert）

由于导入数据是多条insert会大量占用cpu，并且慢。下面是把多条插入数据写入一条insert语句的方法。仿照insert into select语句，把多条记录通过select from dual和union把数据合并到一个记录集中。sql拼写程序如下：

   String sql = "insert into training_moduleright_info(name,MODELGROUP_ID,status) " ;
   boolean first = true ;
   for(int cur = 0 ; cur < rows ; cur ++) {
    String model = fixnull(sheet.getCell(0,cur).getContents()).trim();
    String module = fixnull(sheet.getCell(1,cur).getContents()).trim();
    if(first) {
     sql += "select '"+module+"',(select a.id from training_modelgroup_info a,training_office_info b where a.office_id = b.id and a.name = '"+model+"' and b.type = '" + type+ "'),'1' from dual" ;
     first = false ;
    }
    else {
     sql += " union select '"+module+"',(select a.id from training_modelgroup_info a,training_office_info b where a.office_id = b.id and a.name = '"+model+"' and b.type = '" + type+ "'),'1' from dual" ;
    }
   }
   out.print("execute sql:"+sql+"<br>");

id由于一般都用序列，所以在拼sql的时候不能直接用.nextval，这样我们需要可以写一个id自增长的前触发器。代码如下：

create or replace trigger autoincrease_moduleright_id
  before insert on training_moduleright_info
  for each row
declare
  v_id varchar2(20) ;
  v_num number ;
begin
  select '000'||to_char(s_moduleright_id.nextval) into v_id from dual;
  v_num := length(v_id);
  select :new.MODELGROUP_ID||substr(v_id,v_num-2,v_num) into :new.id  from dual;
end autoincrease_moduleright_id;

写完后在我的机器上用此方法插入了1000+条数据，只用了0.6秒。速度比1000条insert要快很多。