比较没有硬编码的oracle中的预发布表
比较没有硬编码的oracle中的预发布表
提问于 2015-06-04 15:24:08
在我完成一次批次任务之前和之后，我对这张桌子做了一个图像前/后图像。
PRE_TABLE
id|Name |Phone |Score
1 |John |145678 |10
2 |Ptr |23456789 |20
3 |Sarah |34567890 |30
4 |Mary |45678901 |40POST_TABLE
id|Name |Phone |Score
1 |John |12345678 |10
2 |Peter |23456789 |22
3 |Sarah |34567890 |33
4 |Mary |45678901 |40如何比较和提取修改后的字段，并将它们呈现如下：
期望输出
id|modifiedColumn|modifiedVal|prevVal
1 |Phone |12345678 |145678
2 |Name |Peter |Ptr
2 |Score |22 |20
3 |Score |33 |30到目前为止，我可以使用
select * from POST_TABLE
minus select * from PRE_TABLE这给了我
id|Name |Phone |Score
1 |John |12345678 |10
2 |Peter |23456789 |22
3 |Sarah |34567890 |33我正在考虑使用All_tab_columns来迭代表中的字段
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME ='PRE_TABLE';我已经编写了一个部分pl-sql过程，但是当我想比较列名时却陷入了困境。
procedure diff
as
cursor POST_CUR is
(select * from Post_table minus select * from Pre_table);
cursor pre_CUR is
(select * from Pre_table);
cursor COLUMNS_CUR is
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME ='Post_table';
begin
for R in post_CUR LOOP
for p in pre_CUR LOOP
if (R.id=P.id) then
for F in COLUMNS_CUR LOOP
DBMS_OUTPUT.PUT_LINE('id:'||R.ID||'_'||F.COLUMN_NAME);
--compare f.column_name.. STUCK Here!!
--if (R.[f.column_name] <>P.[f.column_name] )
--DBMS_OUTPUT.PUT_LINE('id:'||R.ID||'_'||F.COLUMN_NAME||'_'||R.[f.column_name]||'_'||P.[f.column_name]);
--end if;
end LOOP;
end if;
end LOOP;
end LOOP;
end DIFF;如何在不对表列名进行硬编码的情况下动态地获得表结果？我愿意使用pl-sql或任何其他有效的方法。蒂娅。
回答 1
Stack Overflow用户
发布于 2015-06-04 15:31:42
我认为您可以使用以下SQL获得所需的结果：
--phone
SELECT A.ID, 'Phone' As ModifiedColumn,
B.Phone As ModifiedVal,
A.Phone As PrevVal
FROM PRE_TABLE A
INNER JOIN POST_TABLE B ON A.ID = B.ID AND COALESCE(A.PHONE,'-') <> COALESCE(B.PHONE,'-')
--name
UNION ALL
SELECT A.ID, 'Name' As ModifiedColumn,
B.Phone As ModifiedVal,
A.Phone As PrevVal
FROM PRE_TABLE A
INNER JOIN POST_TABLE B ON A.ID = B.ID AND COALESCE(A.Name,'-') <> COALESCE(B.Name,'-')
--score
UNION ALL
SELECT A.ID, 'Score' As ModifiedColumn,
B.Phone As ModifiedVal,
A.Phone As PrevVal
FROM PRE_TABLE A
INNER JOIN POST_TABLE B ON A.ID = B.ID AND COALESCE(A.Score,'0') <> COALESCE(B.Score,'0')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30647939
复制相关文章
相似问题
腾讯云开发者
