比较表结构未知的两个表的plsql过程 [英] plsql procedure to compare two tables where structure of table is not known
问题描述
所以问题是这样的:
我有一个包含两列的表，即源查询和目标查询. 每行包含一个SQL查询，用于查询informatica 的映射源端和目标端，我们需要建立一个对帐过程，对每一行取这两个值，并在其中生成输出，并将其存储在temp1和temp2的临时表中，然后比较这两个临时表的结果.我这样做是通过创建两个表，然后通过两个游标批量获取，并使用减号运算符对两个表进行比较，然后从那里得到不同的行.
I have a table with two columns namely source query and target query. each row contains a sql query for a informatica mapping from source side and target side and we needed to build a reconciliation procedure which take those two values for each row and generate there output and store it in temp table say temp1 and temp2 and then compare the result of these two temp tables. I Did this by creating two tables and then bulk fetching through two cursors and comparison was done using minus set operator for both the tables and then we got the different rows from there.
现在这是棘手的部分，我们要做的是检查那些具有不同值的行，并输出发生更改的列的名称，并输出源端值(temp1)和目标端值( temp2).
Now here comes the tricky part , what we have to do is check those rows which have different value and output the name of the column where there is a change and also output the source side values(temp1) and target side values(temp2).
如果我之前已经知道表的结构，那么可以通过硬编码来实现，但是由于表temp1和temp2是动态创建的，因此我无法理解在这种情况下，我的意思是如何使用一个过程动态获取行名并检查值的更改位置，然后输出这两个值和列名，来获取列名和这两个值.
If I had known the structure of the tables before then yes hard-coding was a way to get there but since the tables temp1 and temp2 are getting dynamically created therefore I'm not able to get my head around this situation, I mean how to get the column name and those two values using a procedure which dynamically loop through row and check where the value is changing and then output those two values and the column name.
救救我！如果您为此提供了代码，将非常有帮助.
Help me ! if you provide me a code for this , it will be really helpful.
样本数据集
SOURCE
PK COLUMN1 COLUMN2 COLUMN3 COLUMN4
2 NAME2 VALUE2 3 4
1 NAME1 VALUE1 2 3
3 NAME3 VALUE3 4 5
TARGET
PK COLUMN1 COLUMN2 COLUMN3 COLUMN4
1 NAME1 VALUE1 2 3
2 NAME2 VALUE2 4 4
3 NAME3 VALUE3 4 5
现在
SELECT * FROM SOURCE MINUS SELECT * FROM TARGET
给予
PK COLUMN1 COLUMN2 COLUMN3 COLUMN4
2 NAME2 VALUE2 3 4
和
SELECT * FROM TARGET MINUS SELECT * FROM SOURCE
给予
PK COLUMN1 COLUMN2 COLUMN3 COLUMN4
2 NAME2 VALUE2 4 4
我们可以看到column3的值从3更改为4.
we can see that column3 value got changed from 3 to 4.
所以我们需要的是这样的
So what we need is something like this
COLUMN_NAME OLD_VALUE NEW_VALUE
COLUMN3 3 4
表源和目标是通过一个过程创建的，该过程将sql用作另一表的源表和目标表的sql，该表有两列，一个是源查询，另一个是目标查询，并且该表中的每一行都有一个不同的查询查询下次创建这些表时，列数和名称可以更改.
Tables source and target are created from a procedure which take the sql for source and target table for another table that has two columns one is source query and other is target query and each row in this table has a different query for recon also the number of column and there name can change next time these table are created.
推荐答案
假设您的temp1和temp2表具有相同的列，使用EXECUTE IMMEDIATE时很容易做到，并且知道如何浏览Oracle系统表ALL_TABLES和ALL_TAB_COLUMNS.
Assuming your temp1 and temp2 tables have same columns, its easy to do when you use EXECUTE IMMEDIATE, and know how to browse into Oracle system tables ALL_TABLES and ALL_TAB_COLUMNS.
由于我不知道temp表有多少列，所以我们的想法是比较(与您原来的MINUS想法)列串联的结果.请注意，您不能以相同的方式(例如日期)来连接所有内容，因此我向您展示了如何获取DATA_TYPE.
Since I don't know how many columns temp tables have, the idea is to compare (with your original MINUS idea) the results of a concatenation of the columns. Beware you can't concatenate everything the same way (e.g. dates), so I showed how you could get DATA_TYPE.
获得上述结果后，您可以手动查看发生更改的列.如果有时间，我将添加有关已更改的列的部分:
Once you have the above result, you can manually see the column that changes. If I have time, I will add the part about the column that changed:
- 如果您有一个PK，那么我们可以使用它来了解发生变化的行，并在各列上再次循环；
- 如果没有PK，它可能会变得更加棘手...
这样做很有趣，因此，假设您的PK是称为PK的单列，我将尝试编写一个小代码:
I have much fun doing this, so I will try making a small code for it, assuming you PK is single column called PK:
create or replace procedure compare_tables(t1 in varchar2, t2 in varchar2)
is
v_qry varchar2(10000);
TYPE T_MY_LIST IS TABLE OF VARCHAR2(32000);
v_cols T_MY_LIST; -- list of columns
v_types T_MY_LIST; -- list of columns' type
v_cmp_cols T_MY_LIST; -- list of distinct
v_col_t1_t2 T_MY_LIST; -- t1 minus t2 - value of lines
v_pk_t1_t2 T_MY_LIST; -- associated PKs in t1 minus t2
v_col_t2_t1 T_MY_LIST; -- t2 minus t1 - value of lines
v_pk_t2_t1 T_MY_LIST; -- associated PKs in t2 minus t1
TYPE T_Y_ IS TABLE OF VARCHAR2(32000) index by varchar2(1000);
v_s varchar2(1000); -- for indexing
v_t1_t2 T_Y_; -- list of distinct lines from t1 - t2 /indexed by PK
v_t2_t1 T_Y_; -- list of distinct lines from t2 - t1 /indexed by PK
begin
-- the below assumes all tables have a PK called simply "PK".
v_qry:='PK, ';
execute immediate ' select COLUMN_NAME, DATA_TYPE '
||' from ALL_TAB_COLUMNS where TABLE_NAME=upper('''||t1||''')'
bulk collect into v_cols, v_types;
-- building query with list of columns:
FOR I in 1..v_cols.count loop -- dbms_output.put_line(v_cols(i)||'.'||v_types(i));
v_qry := v_qry||v_cols(i)||'||';
end loop;
v_qry := v_qry||'''''';
execute immediate ' select '||v_qry||' from '||t1||' minus select '||v_qry||' from '||t2
bulk collect into v_pk_t1_t2, v_col_t1_t2;
execute immediate ' select '||v_qry||' from '||t2||' minus select '||v_qry||' from '||t1
bulk collect into v_pk_t2_t1, v_col_t2_t1;
-- build indexed structures that will help compare lines brought by "minus" queries
FOR I in 1..v_pk_t1_t2.count loop
v_t1_t2(v_pk_t1_t2(i)):=v_col_t1_t2(i);
end loop;
FOR I in 1..v_pk_t2_t1.count loop
v_t2_t1(v_pk_t2_t1(i)):=v_col_t2_t1(i);
end loop;
v_s := v_t1_t2.FIRST; -- Get first element of array
WHILE v_s IS NOT NULL LOOP
if (v_t2_t1.exists(v_s)) then
-- distinct rows on same PK
DBMS_Output.PUT_LINE (v_s || ' -> ' || v_t1_t2(v_s));
-- loop on each column joined on PK:
FOR i in 1..v_cols.count
loop
v_qry:= 'select '''||v_cols(i)||':''||'||t1||'.'||v_cols(i)||'||''<>''||'||t2||'.'||v_cols(i)
||' from '||t1||','||t2
||' where '||t1||'.PK='||t2||'.PK'
||' and '||t1||'.PK='||v_s
||' and '||t1||'.'||v_cols(i)||'<>'||t2||'.'||v_cols(i)
;
--DBMS_Output.PUT_LINE (v_qry);
execute immediate v_qry bulk collect into v_cmp_cols;
FOR j in 1..v_cmp_cols.count loop
DBMS_Output.PUT_LINE (v_cmp_cols(j));
end loop;
end loop;
else
DBMS_Output.PUT_LINE (v_s || ' not in ' || t2);
end if;
v_s := v_t1_t2.NEXT(v_s); -- Get next element of array
END LOOP;
v_s := v_t2_t1.FIRST; -- Get first
WHILE v_s IS NOT NULL LOOP
if (not v_t1_t2.exists(v_s)) then
DBMS_Output.PUT_LINE (v_s || ' not in ' || t1);
end if;
v_s := v_t2_t1.NEXT(v_s); -- Get next
END LOOP;
end compare_tables;
/
测试数据:
create table temp1 (PK number,
COLUMN1 varchar2(10),
COLUMN2 varchar2(10),
COLUMN3 varchar2(10),
COLUMN4 varchar2(10)
);
create table temp2 (PK number,
COLUMN1 varchar2(10),
COLUMN2 varchar2(10),
COLUMN3 varchar2(10),
COLUMN4 varchar2(10)
);
delete temp1;
insert into temp1
select 1, 'a', 'a', 'bb', 'cc' from dual
union all select 2, 'a', 'a', 'bb', 'cc' from dual
union all select 3, 'a', 'a', 'bb', 'cc' from dual
union all select 4, 'a', 'a', 'bb', 'cc' from dual
;
insert into temp2
select 1, 'a', 'a', 'bb', 'cc' from dual
union all select 2, 'a', 'a', 'b', 'cc' from dual
union all select 3, 'a', 'a', 'bb', 'cc' from dual
;
begin
compare_tables('temp1','temp2');
end;
/
结果:
2 -> 2aabbcc
COLUMN3:bb<>b
4 not in temp2
这是受搜索所有字段"的启发在所有表中都有特定值(Oracle)，其中解释了基本技术.
This was inspired by Search All Fields In All Tables For A Specific Value (Oracle) where the basic techinque is explained.
这篇关于比较表结构未知的两个表的plsql过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
