如何写一个sql语句可以根据table的主键去判断是否insert还是update,即如果主键已经存在了就update,否则insert,最好是通用的sql。我想用它来写一个程序,程序中用到sql,而值是未定的,例如insert into A valuse(?,?);update A set id=?,name=?.这个应该怎么写呢?
刚才问到有朋友推荐用下边这个
merge into a
using (select ? user_no, ? name from dual) b
on (a.USER_NO=b.USER_NO)
when matched then
update set a.NAME=b.NAME
when not matched then
insert values (b.USER_NO,b.NAME) ;
但是我测试到它是表中有多少行就插入多少次的
刚才问到有朋友推荐用下边这个
merge into a
using (select ? user_no, ? name from dual) b
on (a.USER_NO=b.USER_NO)
when matched then
update set a.NAME=b.NAME
when not matched then
insert values (b.USER_NO,b.NAME) ;
但是我测试到它是表中有多少行就插入多少次的
using (select * from test1) b
on (a.empno = b.empno)
when matched then
update set a.comm = b.comm
when not matched then
insert
values
(b.empno,
b.ename,
b.job,
b.mgr,
b.hiredate,
b.sal,
b.comm,
b.deptno);
select * from test2;
, v_name in varchar2(20)
, v_status out number
)
is
-- declare v_count to check if the record exists.
v_count number := 0;begin select count(user_no) into v_count from a where user_no = v_user_no; -- if not exist then insert
if v_count = 0 then
insert into a values(v_user_no,v_name);
-- if exist then update
else
update a set name = v_name where user_no = v_user_no;
end if; v_status := 0;exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
v_status := 1;
rollback;end REC_UPDATE;写了个procedure,v_status传出执行状态。
2 using (select 4 id, 'd' name from dual) b
3 on (a.id = b.id)
4 when matched then
5 update set a.name = b.name
6 when not matched then
7 insert values (b.id , b.name);DoneSQL> select * from test; ID NAME
---------- --------------------
1 a
2 b
4 dSQL> update test set id = 2 where id = 4;1 row updatedSQL> merge into test a
2 using (select 2 id, 'e' name from dual) b
3 on (a.id = b.id)
4 when matched then
5 update set a.name = b.name
6 when not matched then
7 insert values (b.id , b.name);DoneSQL> select * from test; ID NAME
---------- --------------------
1 a
2 e
2 e
我测试的结果是正常的oracle 11g