Question

存储过程插入sql数据会重复问题

create or replace procedure prc_v8_busy_revisit(begintime in varchar2,
endtime in varchar2
)
--begintime 开始时间
--endtime 结束时间
as
BEGIN
insert into TBL_CUST_DATA_TABLE_0000000298
(FIELD_50028161,FIELD_50055037,FIELD_50179728,mobile_tele_no,FIELD_50252045,FIELD_50368392,FIELD_50569934,FIELD_50585876)
select CONTACT_ID, INQUIRE_CODE,CALLEE, CALLER, CUST_BAND,CUST_LEVEL,START_TIME,CREATE_TIME
from test_v8_busy_revisit where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1' ;
update test_v8_busy_revisit set OUTBOUND_STAT='10' where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1' ;
commit;
end;
前台根据时间按钮就可以导入了，但是这样会有重复数据，怎样可以加一个判断让他不存在重复数据？

Answer 1

你可以根据关键字段判断是否重复
if not exists （select * from TBL_CUST_DATA_TABLE_0000000298 where 关键字段=）
BEGIN
insert into TBL_CUST_DATA_TABLE_0000000298
(FIELD_50028161,FIELD_50055037,FIELD_50179728,mobile_tele_no,FIELD_50252045,FIELD_50368392,FIELD_50569934,FIELD_50585876)
select CONTACT_ID, INQUIRE_CODE,CALLEE, CALLER, CUST_BAND,CUST_LEVEL,START_TIME,CREATE_TIME
from test_v8_busy_revisit where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1' ;
update test_v8_busy_revisit set OUTBOUND_STAT='10' where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1' ;
commit;
end;

追问

没有什么关键字段呀，就是空表往里面插数据，他要是选择了两次相同的时间的话就会重复。

追答

if not exists （select * from TBL_CUST_DATA_TABLE_0000000298 where existis （select CONTACT_ID, INQUIRE_CODE,CALLEE, CALLER, CUST_BAND,CUST_LEVEL,START_TIME,CREATE_TIME
from test_v8_busy_revisit where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1'））

Answer 2

CONTACT_ID是唯一的吗？你的记录里有唯一主键吗？如 CONTACT_ID 是唯一的，语句改成：
from test_v8_busy_revisit where start_time >=begintime and start_time <=endtime and OUTBOUND_STAT ='1' and CONTACT_ID not in (select FIELD_50028161 from TBL_CUST_DATA_TABLE_0000000298 )
没有唯一主键的话就在原表加一个自增长的ID字段