如何写一个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) ;
但是我测试到它是表中有多少行就插入多少次的
解决方案 »
- OCI连接池报weblogic.rjvm.PeerGoneException: ; nested exception is: 错误
- 如何利用bea监视或跟踪oracle数据操作,以便调试!
- 关于Oracle Net Manager 连接测试失败--Oracle连接问题
- oracle 10g ORA-01555
- 大家来帮我找个分页的BUG,找了一上午没找出来....谢谢哟.... 妈妈说标题要长..
- 如何快速检查表是否为空
- Oracle中能写这样的Sql吗? 在线等待
- 用PRO*C 做Oracle 数据库应用开发本地必须装 oracle 数据库服务器吗?
- Oracle&BDE乱码问题
- 求助一个oracle取值问题
- oracle 日期格式问题
- 存储过程,如何传入一个数组并进行处理,有样例吗?
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