Oracle 批量插入数据

• 问题描述

insert into T_CC_L3_CALL_SCRIPT_WARN VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1');
insert intoT_CC_L3_CALL_SCRIPT_WARN  VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1');
insert intoT_CC_L3_CALL_SCRIPT_WARN  VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1');

当需要大批量插入数据时，形如这样单条插入的语句效率是很低的，为了优化，我们改用如下写法：

insert all into table_name(col_1,col_2) values (value_1,value_2)
           into table_name(col_1,col_2) values (value_1,value_2)
           into table_name(col_1,col_2) values (value_1,value_2)
           .........................................................................
           into table_name(col_1,col_2) values (value_1,value_2)
select 1 from dual

insert all into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
           into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
select 1 from dual;

问题一：这样写抛出一个异常，说【违反唯一性约束条件】，意味着SEQ_CALL_SCRIPT_WARN.NEXTVAL取到的是同一个值

    通过触发器自动给insert语句设置id值

create or replace trigger TR_CALL_SCRIPT_WARN_INSERT
before insert on T_CC_L3_CALL_SCRIPT_WARN
for each row
begin
  select SEQ_CALL_SCRIPT_WARN.NEXTVAL into :new.CALL_SCRIPT_ID from dual;
end;

    改进脚本

create or replace trigger TR_CALL_SCRIPT_WARN_INSERT
before insert on T_CC_L3_CALL_SCRIPT_WARN
for each row
begin
  select SEQ_CALL_SCRIPT_WARN.NEXTVAL into :new.CALL_SCRIPT_ID from dual;
end;

insert all into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
           into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
select 1 from dual;

COMMIT;

DROP TRIGGER TR_CALL_SCRIPT_WARN_INSERT;

问题二：执行后发现创建触发器成功了，但是后面插入和删除触发器的语句均未执行。

    使用"/"结束执行一下创建触发器的begin-end语句块

create or replace trigger TR_CALL_SCRIPT_WARN_INSERT
before insert on T_CC_L3_CALL_SCRIPT_WARN
for each row
begin
  select SEQ_CALL_SCRIPT_WARN.NEXTVAL into :new.CALL_SCRIPT_ID from dual;
end;
--执行命令
/

• 最终方案

select count(1)  from user_triggers where trigger_name = 'TR_CALL_SCRIPT_WARN_INSERT';
--创建ID自增触发器
create or replace trigger TR_CALL_SCRIPT_WARN_INSERT
before insert on T_CC_L3_CALL_SCRIPT_WARN
for each row
begin
  select SEQ_CALL_SCRIPT_WARN.NEXTVAL into :new.CALL_SCRIPT_ID from dual;
end;
--执行命令
/
--批量插入数据
insert all into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
           into T_CC_L3_CALL_SCRIPT_WARN 
           VALUES(SEQ_CALL_SCRIPT_WARN.NEXTVAL,50001,'513','51302','单方事故','发生交通事故建议您报交警并开具警方证明。','','','','',-1,SYSDATE,-1,SYSDATE,3,'1','1')
select 1 from dual;

COMMIT;
--删除触发器
DROP TRIGGER TR_CALL_SCRIPT_WARN_INSERT;