oracle 使用java在oracle数据库中自动增加RowID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6046895/
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
Auto Increment RowID in oracle database using java
提问by auwall
Ok say I have a table with two columns. Entry_id and name. Entry_id is a ROWID NOT NULL. Essentially I just want it to increment every time something new is put in. How do i do this in the PreparedStatement. I won't know the correct Entry_id bc it doesn't matter. It should just increment everytime. So it would be nice if i could just insert the name into the table and entry_id increments automatically. Any idea how to do this?
好的说我有一个有两列的表格。条目 ID 和名称。Entry_id 是一个非空的 ROWID。本质上，我只是希望它在每次放入新东西时递增。我如何在 PreparedStatement 中执行此操作。我不知道正确的 Entry_id bc 没关系。它应该每次都增加。因此，如果我可以将名称插入表中并且 entry_id 自动递增，那就太好了。知道如何做到这一点吗？
回答by Justin Cave
A ROWID is a physical address for a row in a table. It does not make sense to use a ROWID as a key-- a ROWID will change over time if a table is moved from one tablespace to another, if you do an export and import, if row movement occurs, etc. And it does not make sense to increment a ROWID since the result would quite likely be invalid either in the sense that it would no longer be the physical address of an actual row or that it would not longer be a valid physical address.
ROWID 是表中一行的物理地址。使用 ROWID 作为键是没有意义的——如果一个表从一个表空间移动到另一个表空间，如果你进行导出和导入，如果发生行移动等等，ROWID 会随着时间的推移而改变。增加 ROWID 是有意义的，因为结果很可能是无效的，因为它不再是实际行的物理地址，或者它不再是有效的物理地址。
If you want an auto-incrementing primary key in Oracle, you would declare the column as a NUMBER, not a ROWID. You would then create a sequence object
如果您想在 Oracle 中使用自动递增的主键，您可以将该列声明为 NUMBER，而不是 ROWID。然后，您将创建一个序列对象
CREATE SEQUENCE entry_id_seq
START WITH 1
INCREMENT BY 1
CACHE 100;
and reference the NEXTVALof that sequence in your INSERTstatement
并NEXTVAL在您的INSERT语句中引用该序列的
INSERT INTO entry( entry_id, name )
VALUES( entry_id_seq.nextval, :1 );
Of course, you could create a before-insert trigger to populate the primary key from the sequence
当然，您可以创建一个 before-insert 触发器来填充序列中的主键
CREATE OR REPLACE TRIGGER get_entry_id
BEFORE INSERT ON entry
FOR EACH ROW
IS
BEGIN
SELECT entry_id_seq.nextval
INTO :new.entry_id
FROM dual;
END;
Your INSERTstatement could then omit the ENTRY_IDcolumn, letting the trigger automatically populate it.
INSERT然后您的语句可以省略该ENTRY_ID列，让触发器自动填充它。
INSERT INTO entry( name )
VALUES( :1 );
回答by DaveH
If you are happy with a database dependent way of doing this, then the usual approach is to use an oracle sequence.
如果您对执行此操作的数据库相关方式感到满意，那么通常的方法是使用oracle 序列。
After you create the sequence, which lives in the database your code would be along the lines of
创建位于数据库中的序列后，您的代码将沿着
p = conn.prepareStatement("insert into mytable (entry_id, name) values (mysequence.next_val,?)");
p.setString(1,"My Name");
p.executeUpdate();
回答by Dilum Ranatunga
I used the keywords "oracle" and "autoincrement". Found this: http://situsnya.wordpress.com/2008/09/02/how-to-create-auto-increment-columns-in-oracle/
我使用了关键字“oracle”和“autoincrement”。发现这个：http: //situsnya.wordpress.com/2008/09/02/how-to-create-auto-increment-columns-in-oracle/
By using the trigger in conjunction with the sequence, you don't need to include oracle-specific constructs into your insert, you simply leave the entry_id's value out of the explicit list of values in the insert.
通过将触发器与序列结合使用，您无需在插入中包含特定于 oracle 的构造，您只需将 entry_id 的值保留在插入中的显式值列表之外。
