列转换为Oracle(pl/sql,sql)

我这样做(如果我必须这样做的话)的方法是找出表b中需要比较的列,然后您可以找出表a中的值可以用来比较那些列。

一旦(通过查询user_tables(或者根据需要查询所有的_tables/dba_tables)来检索相关列,就可以生成一个join子句。

join子句需要检查两列是否都为空,或者两列是否具有相同的非空值。

一旦有了它,就可以在merge语句中使用它来删除与join条件匹配的行。我们首先更新匹配的行(为了让这些行在下一步被delete看到,我们需要这样做),然后删除它们。

下面是一个工作测试用例:

设置:

create table a (id integer, col varchar2(30), val number, constraint a_pk primary key (id, col));

create table b (id integer, abc number, test number, xyz number, constraint b_pk primary key (id));

insert into a (id, col, val)
select 10, 'test', null from dual union all
select 11, 'test', null from dual union all
select 12, 'test', 99 from dual union all
select 13, 'test', 5 from dual union all
select 14, 'test', 4 from dual union all
select 10, 'abc', 1 from dual union all
select 10, 'xyz', 7 from dual union all
select 11, 'abc', 4 from dual union all
select 11, 'xyz', 6 from dual union all
select 12, 'abc', 12 from dual union all
select 12, 'efg', 30 from dual union all
select 13, 'abc', 3 from dual union all
select 13, 'xyz', 5 from dual union all
select 14, 'abc', 8 from dual union all
select 14, 'xyz', 9 from dual;

insert into b (id, abc, test, xyz)
select 10, 1, null, 7 from dual union all
select 11, 4, 99, 8 from dual union all
select 12, 11, 99, 30 from dual union all
select 13, 1, 5, 5 from dual union all
select 14, 1, null, 7 from dual;

commit;

我们希望保留在表a中的行

select a.*
from   a tgt
        full outer join b src on (tgt.id = src.id
      and (1 = 0
           or (upper(tgt.col) = 'ABC' and (tgt.val = src.ABC or (tgt.val is null and src.ABC is null)))
           or (upper(tgt.col) = 'TEST' and (tgt.val = src.TEST or (tgt.val is null and src.TEST is null)))
           or (upper(tgt.col) = 'XYZ' and (tgt.val = src.XYZ or (tgt.val is null and src.XYZ is null)))))
where tgt.id is not null and src.id is NULL
ORDER BY a.id, a.col;

ID COL  VAL
-- ---- ---
11 test    
11 xyz    6
12 abc   12
12 efg   30
13 abc    3
14 abc    8
14 test   4
14 xyz    9

运行代码

set serveroutput on

declare
  v_sql clob;
begin
  v_sql := 'merge into a tgt' || chr(10) ||
           '  using  b src' || chr(10) ||
           '  on (tgt.id = src.id' || chr(10) ||
           '      and (1 = 0';

  -- Generate the join conditions
  for rec in (select '           or (upper(tgt.col) = '''||column_name||''' and (tgt.val = src.'||column_name||' or (tgt.val is null and src.'||column_name||' is null)))' join_condition
              from   user_tab_columns
              where  table_name = 'B'
              and    column_name != 'ID')
  loop
    v_sql := v_sql || chr(10) || rec.join_condition;
  end loop;

  v_sql := v_sql || '))' || chr(10) ||
                    'when matched then' || chr(10) || -- we only care about rows that match on the join clause
                    '  update set tgt.col = tgt.col' || chr(10) || -- we need to physically update those rows, or the delete clause won't see them.
                    '  delete where 1=1'; -- we need to have the where clause here, but we're deleting all rows that were updated in the previous step, hence 1=1 which is always true.

  dbms_output.put_line (v_sql||';');

  execute immediate v_sql;
end;
/

语句的dbms_输出

merge into a tgt
  using  b src
  on (tgt.id = src.id
      and (1 = 0
           or (upper(tgt.col) = 'ABC' and (tgt.val = src.ABC or (tgt.val is null and src.ABC is null)))
           or (upper(tgt.col) = 'TEST' and (tgt.val = src.TEST or (tgt.val is null and src.TEST is null)))
           or (upper(tgt.col) = 'XYZ' and (tgt.val = src.XYZ or (tgt.val is null and src.XYZ is null)))))
when matched then
  update set tgt.col = tgt.col
  delete where 1=1;

检查我们是否有我们希望保留的列

select *
from   a
order by id, col;

ID COL  VAL
-- ---- ---
11 test    
11 xyz    6
12 abc   12
12 efg   30
13 abc    3
14 abc    8
14 test   4
14 xyz    9

不过,如果你对这件事有任何选择,我会认真地,认真地请你重新考虑这个设计。

在表a中使用键值更新表b是一种非常奇怪的方法,正如您所发现的,这使得做最简单的事情变得非常棘手。