この記事は約9分1秒で読むことができます。

oracle sql 差分更新について考える話

まえがき

考えてみようかなと。

環境

コード表示

[oracle@centos ~]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                 PORTS                                            NAMES
42b3dd2da618        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   3 hours ago         Up 3 hours (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos ~]$ docker exec -it orcl_19cr3 /bin/bash
[oracle@42b3dd2da618 ~]$ sqlplus aine/ORACLE_PWD@pdb1 

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 25 15:59:05 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat May 25 2019 15:17:56 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

対象データ

コード表示

drop table src purge;
create table src as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
connect by
  level <= 10;

drop table tgt purge;
create table tgt as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
where
 level between 3 and 7
connect by
  level <= 10;

対象データ確認

コード表示

SQL> select * from src;

	RN STR
---------- ----
	 1 A
	 2 B
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G
	 8 H
	 9 I
	10 J

10 rows selected.

SQL> select * from tgt;

	RN STR
---------- ----
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G

chk.sql

これでなにも取れなかったら、差分更新できたことにする。

コード表示

WITH src_minus_tgt AS (
  SELECT
    *
  FROM
    src
  MINUS
  SELECT
    *
  FROM
    tgt
), tgt_minus_src AS (
  SELECT
    *
  FROM
    tgt
  MINUS
  SELECT
    *
  FROM
    src
)
SELECT
  *
FROM
  src_minus_tgt
UNION ALL
SELECT
  *
FROM
  tgt_minus_src;

ptn1

コード表示

insert into tgt
select
  s1.*
from
  src s1
    left outer join tgt s2
      on
        s1.rn=s2.rn
where
  s2.rn is null
;
commit;

ptn2

コード表示

insert into tgt
select
  s1.*
from
  src s1
where
  not exists(
          select
            1
          from
            tgt s2
          where
            s1.rn=s2.rn
          )
;
commit;

ptn3

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      where
        not exists(
                select
                  1
                from
                  tgt s2
                where
                  s1.rn=s2.rn
                )
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

ptn4

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      minus
      select
        s2.*
      from
        tgt s2
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

あとがき

んんん、もうちょい臨場感がほしいい。fin。

Leave a Reply

Your email address will not be published. Required fields are marked *