更新时间:2023-02-05 10:38:10
简单的答案是:否-但是使用NUMBER列和设置/更新它的触发器来创建自己很容易.
The simple answer is No - but it's easy to create one yourself with a NUMBER column and a trigger to set/update it.
Oracle 11gR2的简单示例:
A simple example for Oracle 11gR2:
CREATE SEQUENCE global_rowversion_seq;
ALTER TABLE mytable1 ADD rowversion NUMBER;
ALTER TABLE mytable2 ADD rowversion NUMBER;
CREATE TRIGGER mytable1_biu
BEFORE INSERT OR UPDATE
ON mytable1
FOR EACH ROW
BEGIN
:NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable1_biu;
CREATE TRIGGER mytable2_biu
BEFORE INSERT OR UPDATE
ON mytable2
FOR EACH ROW
BEGIN
:NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable2_biu;
(如果您使用的是较早的Oracle版本,则触发器中的分配必须通过查询完成,例如:
(If you're on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:
SELECT global_rowversion_seq.NEXTVAL
INTO :NEW.rowversion
FROM dual;
现在,请记住,在某些情况下,由于所有使用相同序列进行数据库插入/更新的争用,该设计在极端情况下(例如,具有极高插入/更新活动的数据库)可能会对性能产生影响.当然,在这种情况下,您可能首先还是会避免触发.
Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.
取决于您如何使用rowversion列,***对每个表使用单独的序列.当然,这意味着行版本将不再是全局唯一的-但是,如果您仅对比较表中行的更改感兴趣,那么就可以了.
Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique - but if you are only interested in comparing changes to rows within a table, then this would be fine.
另一种方法是分别为每一行增加计数器-不需要序列,可以让您检测到行的更改(但不允许将任何行与另一行进行比较):
Another approach is to advance the counter for each row individually - this doesn't need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):
ALTER TABLE mytable ADD rowversion NUMBER;
CREATE TRIGGER mytable_biu
BEFORE INSERT OR UPDATE
ON mytable
FOR EACH ROW
BEGIN
:NEW.rowversion := NVL(:OLD.rowversion, 0) + 1;
END mytable_biu;
每行将以rowversion = 1插入,然后对该行的后续更新会将其递增到2、3等.
Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.