oracle 比较两条记录并仅显示差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6829212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two records and show only the difference
提问by Revious
I'd like a Oracle Pipelined Function which compares two records from a query result and shows only the column which have changed between the query result?
我想要一个 Oracle 流水线函数，它比较查询结果中的两条记录，并只显示查询结果之间发生变化的列？
回答by APC
Here is a blunt instrument approach to the problem.
这是解决问题的生硬工具方法。
create or replace function col_diff
( p_empno_1 in emp.empno%type
, p_empno_2 in emp.empno%type )
return col_nt pipelined
is
out_val col_t := new col_t(null, null, null);
emp_rec1 emp%rowtype;
emp_rec2 emp%rowtype;
begin
select *
into emp_rec1
from emp
where empno = p_empno_1;
select *
into emp_rec2
from emp
where empno = p_empno_2;
if emp_rec1.ename != emp_rec2.ename
then
out_val.col_name := 'ENAME';
out_val.old_val := emp_rec1.ename;
out_val.new_val := emp_rec2.ename;
pipe row (out_val);
end if;
if emp_rec1.hiredate != emp_rec2.hiredate
then
out_val.col_name := 'HIREDATE';
out_val.old_val := to_char(emp_rec1.hiredate, 'DD-MON-YYYY');
out_val.new_val := to_char(emp_rec2.hiredate, 'DD-MON-YYYY');
pipe row (out_val);
end if;
return;
end;
/
So, given this test data...
所以，鉴于这个测试数据......
SQL> select empno, ename, hiredate
2 from emp
3 where empno > 8100
4 /
EMPNO ENAME HIREDATE
---------- ---------- ---------
8101 PSMITH 03-DEC-10
8102 PSMITH 02-JAN-11
SQL>
... we get this output:
...我们得到这个输出：
SQL> select * from table (col_diff(8101,8102))
2 /
COL_NAME
------------------------------
OLD_VAL
-------------------------------------------------------------------
NEW_VAL
-------------------------------------------------------------------
HIREDATE
03-DEC-2010
02-JAN-2011
SQL>
Now, doubtlessly you would like something which is less verbose. I think it may be possible to do something using the enhanced Method 4 dynamic SQL which was introduced in 11g. Alas, you say you are using 10g.
现在，毫无疑问你会想要一些不那么冗长的东西。我认为可以使用 11g 中引入的增强的 Method 4 动态 SQL 做一些事情。唉，你说你用的是10g。
回答by StevieG
Its not quite what you want, but Kevin Meade's blog on OracleFAQ has a solution which works for me:
这不是您想要的，但 Kevin Meade 在 OracleFAQ 上的博客有一个对我有用的解决方案：
http://www.orafaq.com/node/1826
http://www.orafaq.com/node/1826
