如何在 Oracle 联接视图中使用 ROWID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8416734/
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
How to use ROWID with an Oracle Join View
提问by user973479
I am trying to create a unique identifier for every row of a view. The view I have joins a lot of tables and therefore no one table's primary key will help me identify the row.
我正在尝试为视图的每一行创建一个唯一标识符。我的视图连接了很多表，因此没有一个表的主键可以帮助我识别行。
Doing a google search it looks like I may be able to achieve this by using rowid? But I'm not sure how to reference the view's rowid. Below is an example of how I envisioned rowid would work, but it obviously fails with an 'ambiguous column' error because I am not specifying a specific table for rowid.
进行谷歌搜索，看起来我可以通过使用 rowid 来实现这一点？但我不确定如何引用视图的 rowid。下面是我设想的 rowid 如何工作的示例，但它显然因“不明确的列”错误而失败，因为我没有为 rowid 指定特定的表。
Ex:
前任：
with v_someTable (select...),
v_anotherTable as (select blah, id from v_someTable where...),
v_yetAnotherTable as (select foo, id from v_someTable where...)
select distinct rowid, rt.key, v1.blah, v2.foo
from realTable rt
left join v_anotherTable v1 on v1.id=rt.id
left join v_yetAnotherTable v2 on v2.id=rt.id
I am trying to do this in a query and not a stored procedure. Any help would be appreciated!
我试图在查询而不是存储过程中执行此操作。任何帮助，将不胜感激！
Thanks!
谢谢！
回答by paulbailey
My understanding is that a rowidrefers to a row in a physical table, rather than a row in a result set (which is effectively what a view is).
我的理解是 arowid指的是物理表中的一行，而不是结果集中的一行（实际上就是视图）。
To get a unique identifier for each row, you'd need to combine the primary keys of the tables that you're joining in some way.
要获得每一行的唯一标识符，您需要以某种方式组合您要加入的表的主键。
回答by Kevin Burton
If you do not have primary primary keys on all your tables you could select the rowids from the individual tables and concatenate them:
如果所有表上都没有主键，则可以从各个表中选择 rowids 并将它们连接起来：
SELECT rt.rowid||v1.rowid||v2.rowid as uniqueid
FROM ......
回答by eckes
Don't use the ROWID pseudo column, it is storage dependend (and might change when the usefull ENABLE ROW MOVEMENTis used). You can also not use it to look up (joined) records on the view in a efficient way.
不要使用 ROWID 伪列，它是存储相关的（并且在使用有用的时候可能会改变ENABLE ROW MOVEMENT）。您也不能使用它以有效的方式在视图上查找（连接）记录。
It is better to use the real PKs on your records in this case (for index lookups to work). And I would not join them, but just use multiple columns - only this way you can re-select them (with index support).
在这种情况下，最好在您的记录中使用真实的 PK（以便索引查找工作）。我不会加入它们，而只是使用多列 - 只有这样你才能重新选择它们（有索引支持）。
回答by Henning
This question is answered for a time now but please be careful when concatinating primary keys.
For example when you have key1 = 23 and key2 = 45and concatinate it to 2345it is not clear if the keys were 23 and 45or if they were 2 and 345.
这个问题现在已经回答了一段时间，但在连接主键时请小心。例如，当您拥有key1 = 23 and key2 = 45并连接它时，2345不清楚这些键是23 and 45还是2 and 345。
Use a delimiter (23,45 -> 23_45)that can not occur in any of the keys (not all keys are numeric). Or fill up keys to max possible length (23,45 -> 00230045 (for key1 and key2 NUMBER(4,0))).
使用(23,45 -> 23_45)不能出现在任何键中的分隔符（并非所有键都是数字）。或者将键填充到最大可能的长度(23,45 -> 00230045 (for key1 and key2 NUMBER(4,0)))。
Also be aware of Oracles feature (not all DBs can handle this) to define primary and foreign keys over multiple columns which might be faster and extend your possibilities for clean joins without having to split your concatinated key.
还要注意 Oracle 功能（并非所有数据库都可以处理此问题）在多个列上定义主键和外键，这可能会更快，并扩展干净连接的可能性，而无需拆分连接的键。
