Oracle 将常量合并到单个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/167471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle merge constants into single table
提问by David Citron
In Oracle, given a simple data table:
在Oracle中，给定一个简单的数据表：
create table data (
id VARCHAR2(255),
key VARCHAR2(255),
value VARCHAR2(511));
suppose I want to "insert or update" a value. I have something like:
假设我想“插入或更新”一个值。我有类似的东西：
merge into data using dual on
(id='someid' and key='testKey')
when matched then
update set value = 'someValue'
when not matched then
insert (id, key, value) values ('someid', 'testKey', 'someValue');
Is there a better way than this? This command seems to have the following drawbacks:
还有比这更好的方法吗？这个命令似乎有以下缺点：
- Every literal needs to be typed twice (or added twice via parameter setting)
- The "using dual" syntax seems hacky
- 每个文字都需要输入两次（或通过参数设置添加两次）
- “使用双重”语法似乎很hacky
If this is the best way, is there any way around having to set each parameter twice in JDBC?
如果这是最好的方法，有没有办法在 JDBC 中将每个参数设置两次？
回答by Craig
I don't consider using dual to be a hack. To get rid of binding/typing twice, I would do something like:
我不认为使用 Dual 是一种黑客行为。为了摆脱两次绑定/打字，我会做类似的事情：
merge into data
using (
select
'someid' id,
'testKey' key,
'someValue' value
from
dual
) val on (
data.id=val.id
and data.key=val.key
)
when matched then
update set data.value = val.value
when not matched then
insert (id, key, value) values (val.id, val.key, val.value);
回答by Tony Andrews
I would hide the MERGE inside a PL/SQL API and then call that via JDBC:
我会将 MERGE 隐藏在 PL/SQL API 中，然后通过 JDBC 调用它：
data_pkg.merge_data ('someid', 'testKey', 'someValue');
As an alternative to MERGE, the API could do:
作为 MERGE 的替代方案，API 可以执行以下操作：
begin
insert into data (...) values (...);
exception
when dup_val_on_index then
update data
set ...
where ...;
end;
回答by Nick Pierpoint
I prefer to try the update before the insert to save having to check for an exception.
我更喜欢在插入之前尝试更新以节省检查异常的时间。
update data set ...=... where ...=...;
if sql%notfound then
insert into data (...) values (...);
end if;
Even now we have the merge statement, I still tend to do single-row updates this way - just seems more a more natural syntax. Of course, mergereally comes into its own when dealing with larger data sets.
即使现在我们有了合并语句，我仍然倾向于以这种方式进行单行更新 - 似乎更自然的语法。当然，在处理更大的数据集时，merge真正发挥作用。
回答by shyam
Use a stored procedure
使用存储过程
