-- 如果不用merge into 的话,可以用存储过程: 有一张表ABC,有字段 a,b,c;主键是a,现在我是在插入一条记录前先判断是否已经有相同的主键,如果主键一样则不插入,主键不一样则插入,sql语句怎么写,我写的-- insert into ABC values('1','2','3') where (select count(*) from ABC where a= ?) < 1create or replace procedure abc_inc_proc( i_a abc.a%type, i_b abc.b%type, i_c abc.c%type ) is begin insert into abc(a,b,c) select i_a as a, i_b as b, i_c as c from dual a where not exists (select 1 from abc b where b.a=i_a ); commit; end; / /
--使用merge into merge into ABC L using (select '1' a from dual) N on (L.a= N.a) WHEN NOT MATCHED THEN INSERT (a) values ('1')
insert into ... values();-- 只要有 values() 后面就不能加 where条件!
现在我是不想update,只做insert,该怎么做?
-- 你还是不明白merge into 的真正用法,不然:你就不会这么问:该怎么做!-- 有些事情,偏偏要人家把完整的答案给你,你才能做吗?自己就不会用脑子想想?-- 建议先去看下面链接的文档:http://wenku.baidu.com/view/1308e0563c1ec5da50e27006.html
有一张表ABC,有字段 a,b,c;主键是a,现在我是在插入一条记录前先判断是否已经有相同的主键,如果主键一样则不插入,主键不一样则插入,sql语句怎么写,我写的-- insert into ABC
values('1','2','3') where (select count(*) from ABC where a= ?) < 1create or replace procedure abc_inc_proc(
i_a abc.a%type,
i_b abc.b%type,
i_c abc.c%type
)
is
begin
insert into abc(a,b,c)
select i_a as a, i_b as b, i_c as c
from dual a
where not exists (select 1 from abc b where b.a=i_a );
commit;
end;
/
/
merge into ABC L
using (select '1' a from dual) N
on (L.a= N.a)
WHEN NOT MATCHED THEN
INSERT (a) values ('1')