Oracle SQL-我可以返回"before"吗?列值的状态 [英] Oracle SQL - can I return the "before" state of a column value

问题描述

假设myTable中的以下行:

Assume the following row in myTable:

id     =  1
letter = 'a'

在Oracle中，可以轻松地执行以下操作:

In Oracle, one can easily do the following:

update myTable set
  letter = 'b'
where id   = 1
returning letter 
into myVariable;

然后myVariable将保留值'b'.

and myVariable will then hold the value 'b'.

我要寻找的是一种返回字母"before"值的方法

What I am looking for is some way of returning the "before" value of letter

即.将先前的更新替换为:

ie. replace the previous update with:

update myTable set
  letter = 'b'
where id   = 1
returning letter "before the update"
into myVariable;

，然后myVariable应该保留值'a';

我了解T-SQL可以通过OUTPUT子句实现这一目标.

I understand that T-SQL can achieve this via the OUTPUT clause.

是否有Oracle等效的方法来实现这一目标，所以我不必先进行选择"就可以获取之前的值吗?

Is there an Oracle equivalent way of achieving this so I don't have to first do a "select" just to get the before value?

推荐答案

update
  (
   select T.*, (select letter from DUAL) old_letter
     from myTable T
    where id=1
  )
   set letter = 'b'
returning old_letter into myVariable;

在Oracle 11.2上测试

Tested on Oracle 11.2

这篇关于Oracle SQL-我可以返回"before"吗?列值的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！