将列转换为Oracle(pl/sql，sql)

Question

我正在进行换位，并在其他表中查找某个列是否存在完全相同的值。示例：

tableA: id = 10, col = abc , value = 10
tableB: id = 10, abc = 10

我有以下代码：

declare
TYPE  t_my_list is record(id VARCHAR2(4000),col VARCHAR2(4000),val VARCHAR2(4000));
Type list_3STR is table of t_my_list;
v_stmt VARCHAR2(32000) := 'SELECT id, col, val FROM userA.tableA';
v_lstmt VARCHAR2(32000);
v_ret list_3STR := list_3STR();      
cDel   number; 
begin
EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_ret;  
for i in v_ret.first..v_ret.last loop
--DBMS_OUTPUT.PUT_LINE('ID: '||v_ret (i).id||', COL: '||v_ret (i).col||', VAL: '||v_ret (i).val);
v_lstmt := 'SELECT count(*) FROM userB.tableB WHERE NVL(cast('||v_ret (i).col||' as Varchar2(100)), ''<null>'') in ('''||v_ret (i).val||''', ''<null>'') and ID = '''||v_ret (i).id||''' ';
DBMS_OUTPUT.PUT_LINE(v_lstmt);
EXECUTE IMMEDIATE v_lstmt INTO cDel;
If cDel > 0 Then
DBMS_OUTPUT.PUT_LINE('delete row from userA.tableA')
End if;
end loop;
DBMS_OUTPUT.PUT_LINE('v_ret = '||v_ret.count);   
end;

我必须考虑5个案例：

userA.tableA到userB.tableB

1. NULL到NULL=1--DELETE值
2. NULL到DATA=0--不删除
3. 相同的DATA到DATA=1——删除
4. diffrent DATA到diffrent DATA=0--不删除
5. DATA变为NULL=0=--不删除

我的代码适用于情况1到4。如何解决第五个问题？

更新：CASE示例：

1. 
id = 10, col = test, val = null
id = 10, test = null
2.
id = 10, col = test, val = null
id = 10, test = 99
3.
id = 10, col = test, val = 99
id = 10, test = 99
4.
id = 10, col = test, val = 5
id = 10, test = 99
5.
id = 10, col = test, val = 4
id = 10, test = null

Answer 1

我这样做的方法(如果我必须这样做的话(是找出表b中需要比较的列，然后你就可以找出表a中的值可以用来与这些列进行比较。

一旦有了这些(通过查询user_tables(或all_tables/dba_tables，根据需要(来检索相关列(，就可以生成一个联接子句。

联接子句需要检查两列是否为null，或者两列是否具有相同的非null值。

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

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

设置：

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_output

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的一种非常奇怪的方式，正如您所发现的，这使得做最简单的事情变得非常棘手。