标题翻译
DELETE and INSERT using MERGE statement in ORACLE
问题
我有一个实体员工(Employee),它有一个字段 List<String> accountIds.
因此表结构如下:
CREATE TABLE EMPLOYEE (
ID varchar2(255) not null,
OBJ_ID varchar2(36),
NAME varchar2(255),
VER_NBR number(19,0),
CREATEID varchar2(255) not null,
CREATETIME timestamp (6) not null,
UPDATEID varchar2(255),
UPDATETIME timestamp (6),
primary key (ID));
而要存储 AccountIds,我有另一张表
CREATE TABLE EMPLOYEE_ACCOUNT_IDS(
EMP_ID varchar2(255),
ACC_ID varchar2(255),
primary key (EMP_ID, ACC_ID)
);
更新操作:员工表中的 ACCOUNT_IDS
目前,在应用程序中,我正在删除与员工相关的所有 accountids,并重新插入所有数据。
为了提高性能并减少数据库查询次数,是否可以使用“MERGE”语句来完成呢?
英文翻译
I have entity Employee, and it has a field List<String> accountIds.
so table structure looks like this:
CREATE TABLE EMPLOYEE (
ID varchar2(255) not null,
OBJ_ID varchar2(36),
NAME varchar2(255),
VER_NBR number(19,0),
CREATEID varchar2(255) not null,
CREATETIME timestamp (6) not null,
UPDATEID varchar2(255),
UPDATETIME timestamp (6),
primary key (ID));
and to store AccountIds I've another table
CREATE TABLE EMPLOYEE_ACCOUNT_IDS(
EMP_ID varchar2(255),
ACC_ID varchar2(255),
primary key (EMP_ID, ACC_ID)
);
Update operation: ACCOUNT_IDS in EMPLOYEE table
Right now, in the application I'm deleting all the accountids related to the employee and re-insert all.
To improve performance and reduce the number of db queries. Is this possible to do with "MERGE" STATEMENT.
答案1
得分: 0
是的。这是可能的,不确定是否比删除和插入更快。我不知道你的输入是什么样子的,所以如果你能告诉我你是如何执行插入的,那就很好了。无论如何,你可以将现有数据和新值进行连接,并将这个查询用作源表。
merge into employee_account_ids tgt
using (
with new_data(acc_id) as (select * from table(sys.odcivarchar2list('NEW01', 'NEW02', 'ACC13')))
select '001' emp_id, nvl(a.acc_id, n.acc_id) acc_id, rwd,
case when n.acc_id is null then 'del' end dsc
from new_data n
full join (select e.*, rowid rwd from employee_account_ids e where emp_id = '001') a
on a.acc_id = n.acc_id ) src
on (src.emp_id = tgt.emp_id and src.rwd = tgt.rowid)
when matched then update set tgt.acc_id = tgt.acc_id delete where dsc = 'del'
when not matched then insert values (src.emp_id, src.acc_id)
问题是你的第二个表没有我可以操作以执行删除的虚拟列,因为 Oracle 要求先进行更新,然后再删除行。所以我使用了 rowid,欺骗了更新(tgt.acc_id = tgt.acc_id
),这样就能够工作了。
英文翻译
Yep. It is possible, not sure if faster then delete and insert. I don't know how your input looks like, so it would be nice how do you perform insert. Anyway you can make join of existing data and new values and use this query as source table.
merge into employee_account_ids tgt
using (
with new_data(acc_id) as (select * from table(sys.odcivarchar2list('NEW01', 'NEW02', 'ACC13')))
select '001' emp_id, nvl(a.acc_id, n.acc_id) acc_id, rwd,
case when n.acc_id is null then 'del' end dsc
from new_data n
full join (select e.*, rowid rwd from employee_account_ids e where emp_id = '001') a
on a.acc_id = n.acc_id ) src
on (src.emp_id = tgt.emp_id and src.rwd = tgt.rowid)
when matched then update set tgt.acc_id = tgt.acc_id delete where dsc = 'del'
when not matched then insert values (src.emp_id, src.acc_id)
<sup>dbfiddle</sup>
The problem is your second table has no dummy column which I could touch to perform delete, because Oracle requires update first to delete row. So I used rowid, cheated update (tgt.acc_id = tgt.acc_id
) and it works.
专注分享java语言的经验与见解,让所有开发者获益!
评论