declare num int; begin select count(*) into num from tb where col1 = '**' and col2 = '**' and col3 = '**' and col4 = '**'; if num = 0 then insert into tb values('**','**','**','**'); end if; end; /
可以开游标,然后处理; 但是如果数据量太大的话,每条数据都commit也很慢。
写个存储过程...把异常忽略不就行了?? With Others Exception Null;end ......
insert into table1 select distinct * from table2 where id not in (select id from table1)
应该弄成联合主键,有重复数据就通过plsql抛个异常,不进行insert ,这种效率会高些吧
4列弄成联合主键,当插入的时候用save exception,这样不会断开job,可以继续插入下一行。 插入语法:declare type t_name is table of table_name.name%type index by binary_integer; v_name t_name; type t_cur is ref cursor; v_cur t_cur; v_sql varchar2(100); begin v_sql ='select name from table_name'; open v_cur for v_sql; loop fetch v_cur bulk into v_name; forall i in v_name.first..v_name.last save exceptions
insert into table_name (name) values(v_name(i)) exception when ex_dml_errors then l_error_count := sql%bulk_exceptions.count; end loop; end ; 错误地方请指正。
网上找的一个MERGE的例子,修改一下应该可以符合你的要求.MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.category = 'DVD' WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category != 'BOOKS'
num int;
begin
select count(*) into num from tb where col1 = '**' and col2 = '**' and col3 = '**' and col4 = '**';
if num = 0 then
insert into tb values('**','**','**','**');
end if;
end;
/
但是如果数据量太大的话,每条数据都commit也很慢。
With Others Exception Null;end ......
插入语法:declare
type t_name is table of
table_name.name%type index by binary_integer;
v_name t_name;
type t_cur is ref cursor;
v_cur t_cur;
v_sql varchar2(100);
begin
v_sql ='select name from table_name';
open v_cur for v_sql;
loop
fetch v_cur bulk into v_name;
forall i in v_name.first..v_name.last save exceptions
insert
into table_name
(name)
values(v_name(i))
exception
when ex_dml_errors then
l_error_count := sql%bulk_exceptions.count;
end loop;
end ;
错误地方请指正。
USING newproducts np ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != 'BOOKS'