SQL Server:与Oracle中的RowVersion等效 [英] SQL Server : RowVersion equivalent in Oracle

问题描述

Oracle是否具有与 SQL Server的RowVersion 相似的数据类型?

Does Oracle has similar datatype to SQL Server's RowVersion?

插入或更新一行时，相应的版本"列(类型为RowVersion)将自动更新.

When you insert or update a row, the corresponding Version column(which is of type RowVersion) gets updated automatically.

MSDN关于RowVersion的说法 :

• 是一种数据类型，可公开自动生成的唯一二进制文件 数据库中的数字.行版本通常用作一种机制 用于版本标记表行.存储大小为8个字节.这 rowversion数据类型只是一个递增的数字，而不是 保存日期或时间.

• Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.

每个数据库都有一个计数器，该计数器针对每次插入或 对包含以下内容的表执行的更新操作 数据库中的rowversion列.这个计数器是数据库 行版本.这会跟踪数据库中的相对时间，而不是 可以与时钟相关联的实际时间.一个表只能有 一个rowversion列.每次带有rowversion列的行 修改或插入后，增量的数据库rowversion值为 插入rowversion列中.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

您可以使用一行的rowversion列轻松确定是否 自上次读取以来，该行中的任何值均已更改.如果 对行进行任何更改，都会更新rowversion值.如果不 对行进行更改，rowversion的值与更改时的值相同 以前曾阅读过.

You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read.

您可以在表中添加rowversion列，以帮助维护 当多个用户正在更新数据库中的行时，数据库的完整性 同时.您可能还想知道多少行以及哪些行 更新而无需重新查询表.

You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.

我们正在使用oracle设计数据模型，并希望使用Version列来管理并发.

We are designing a data model with oracle and would like to use Version column to manage concurrency.

我还想知道Oracle世界中是否有更好的方法.

I would also like to know if there is better way in Oracle world.

推荐答案

简单的答案是:否-但是使用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.

这篇关于SQL Server:与Oracle中的RowVersion等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！