使用MERGE语句在ORACLE中进行DELETE和INSERT操作。

huangapple 未分类评论67阅读模式
标题翻译

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)

dbfiddle

问题是你的第二个表没有我可以操作以执行删除的虚拟列,因为 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.

huangapple
  • 本文由 发表于 2020年1月30日 19:11:40
  • 转载请务必保留本文链接:https://java.coder-hub.com/59984685.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定