oracle 选择oracle中最后更新的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6152757/
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
Select the last updated row in oracle
提问by Nitesh
How can I get the last updated row or record in an oracle database?
I want to ensure that a trigger is fired to check whether the last updated row has all uppercase entries, and if not convert them to upper case.
如何在 oracle 数据库中获取最后更新的行或记录？
我想确保触发触发器以检查最后更新的行是否包含所有大写条目，如果没有将它们转换为大写。
回答by Tony Andrews
Just write a trigger to do that:
只需编写一个触发器来做到这一点：
create trigger mytable_trg
before insert or update on mytable
for each row
begin
:new.col1 := upper(:new.col1);
:new.col2 := upper(:new.col2);
end;
The trigger fires for each row inserted or updated, so there is no need to "find" the row.
触发器为插入或更新的每一行触发，因此无需“查找”该行。
回答by Rob van Wijk
SQL is a set based language. If you are going to update several rows, there is no concept of "last updated row".
SQL 是一种基于集合的语言。如果您要更新多行，则没有“最后更新行”的概念。
Making sure your API inserts UPPER(column_name) seems to be what you want.
确保您的 API 插入 UPPER(column_name) 似乎是您想要的。
You can also create a before row update trigger that does:
您还可以创建一个 before 行更新触发器：
:new.column_name := upper(:new.column_name);
But that would be less efficient than offering an upper cased column_name in your API.
但这比在 API 中提供大写的 column_name 效率低。
Regards,
Rob.
问候，
罗伯。
回答by Mike Meyers
Many Oracle developers will try to tell you that triggers are a bad idea and should really only be used as a last resort to solve a problem if everything else has been tried. They often get used to do things that they were never really designed for. Some of the reasons for avoiding triggers are described by Tom Kyte in this Oracle Magazine article.
许多 Oracle 开发人员会试图告诉您触发器是一个坏主意，如果其他所有方法都已尝试过，那么它实际上应该仅用作解决问题的最后手段。他们经常习惯于做他们从未真正设计过的事情。Tom Kyte 在这篇Oracle 杂志文章中描述了避免触发器的一些原因。
In his answer Rob van Wijk is saying that the API used to modify the data should take care of making sure only upper case data is inserted. An alternative to this would be to create a check constraint on the column so that it is only possible to add upper case data.
在他的回答中，Rob van Wijk 说用于修改数据的 API 应该确保只插入大写数据。对此的替代方法是在列上创建检查约束，以便只能添加大写数据。
SQL> create table my_temp_table (
2 col_1 varchar2(50) check (col_1 = upper(col_1))
3 );
Table created.
Create the table. Then try to insert some invalid and an error will be raised by the check constraint.
创建表。然后尝试插入一些无效的，检查约束会引发错误。
SQL> insert into my_temp_table values ('data');
insert into my_temp_table values ('data')
*
ERROR at line 1:
ORA-02290: check constraint (EMIR_MONTHLY_PAL.SYS_C00113139) violated
Valid data will go in without a problem.
有效数据将毫无问题地输入。
SQL> insert into my_temp_table values ('DATA');
1 row created.
回答by schurik
you can use return rowid intoto get the rowid of the last updated row
您可以使用return rowid into获取最后更新行的rowid
in sqlplus:
在 sqlplus 中：
create table t (id number, val varchar2(10));
insert into t (id, val) values(1, 'abc');
var rid varchar2(100);
update t set val = 'xxx' where id = 1 return rowid into :rid;
select * from t where rowid = :rid;
