65.9K
CodeProject 正在变化。 阅读更多。
Home

在Oracle中模拟SQL Server标识列

Wendelius
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.94/5 (5投票s)

2012年1月9日

CPOL

6分钟阅读

viewsIcon

54150

downloadIcon

339

本文介绍如何在 Oracle 中使用序列来模仿 SQL Server 的标识列。

引言

经常有人问，如何在 Oracle 中定义一个像 SQL Server 那样的标识（自动增量）列？Oracle 没有直接的概念，但可以轻松创建类似的行为。

Oracle 中的唯一编号

SQL Server 中的标识定义绑定到单个列，该列在插入时自动编号。通常，该列定义为从 1 开始，以 1 递增的 identity。在列定义之后，SQL Server 会负责为该行在标识列中赋予一个“唯一”值。

在 Oracle 中，在表创建时无法定义自动生成的数值。相反，Oracle 有一种称为 sequence 的特殊对象类型来生成唯一数字。一个主要区别是序列不绑定到任何表或列，因此可以在任何需要的地方使用。所以，要实现与 SQL Server 类似的行为，必须使用代码将序列生成的值插入到行的列中。

让我们创建一个小的测试表来使用

-----------------------------------------------
-- Create the table for testing
-----------------------------------------------
CREATE TABLE IdentityTest (
   Id   NUMBER        NOT NULL PRIMARY KEY,
   Text VARCHAR2(100) NOT NULL
);

这个想法是通过只为文本列指定值来向此表添加行。ID 列应该“自动”获得一个值。

下一步是创建一个序列

-----------------------------------------------
-- Create a sequence
-----------------------------------------------
CREATE SEQUENCE SeqIdentityTest
   START WITH 1
   INCREMENT BY 1
   NOCYCLE;

上述语句创建了一个新的序列对象，编号从 1 开始，每次递增 1。需要注意的一个重要点是 NOCYCLE 定义。这意味着当序列达到上限时，它不会从头开始生成数字。相反，尝试从序列获取新值将生成一个错误。这是序列的默认行为，如果序列用于生成键值，则不应修改。

现在来组合表列和序列。一种非常简单且不可见的方法是为表创建一个触发器

-----------------------------------------------
-- Create a trigger to add a unique value to the id
-----------------------------------------------
CREATE OR REPLACE TRIGGER IdentityTest_Insert
BEFORE INSERT ON IdentityTest
FOR EACH ROW
BEGIN
   SELECT SeqIdentityTest.NEXTVAL
   INTO :NEW.Id
   FROM Dual;
END;
/

对于插入到 IdentityTest 的每一行，都会单独触发该触发器。触发器的主体只需从序列中获取一个新值，并将结果放入 :NEW.Id。:NEW 指的是触发器执行后将插入到表中的行。

由于 Oracle 不支持不带表的 SELECT 语句，因此使用了 dual。dual 是一个内置表，包含一行，因此从 dual 进行选择总是会返回一行。

让我们测试一下 Insert 会发生什么

-----------------------------------------------
-- Test insert
-----------------------------------------------
INSERT INTO IdentityTest (Text) VALUES ('Test2');
SELECT * FROM IdentityTest;

结果是：

   ID TEXT
----- ----------
    1 Test2

与 SQL Server 一样，序列不保证序列生成的所有值都能在数据库中找到。它只保证不会两次提供相同的值。例如，如果一行被插入到 IdentityTest 表，但事务被回滚，则序列生成的值将丢失。

如何知道生成的值

ID 列自动获得值很好，但如果应用程序需要知道给该行赋予了什么值，该怎么办？例如，如果该表是父表，然后应用程序需要插入到子表，并在子表的 [外键] 列中使用父表的主键值。有必要将该值返回给调用应用程序。

包含的小演示程序显示了如何使用 RETURNING 子句将值返回给客户端。非常值得注意的是，对于使用 msdaora 驱动程序的 ADO.NET OleDbCommand，不支持 RETURNING 子句。因此，演示应用程序中使用了 ODP.NET。ODP.NET 可以在 Oracle Data Provider for .NET 中找到。

为了使用该应用程序，请为 Oracle 连接定义一个合适的连接字符串。默认连接字符串包含默认端口 1521，例如 XE。如果实例正确，只需添加正确的用户 ID 和密码。

代码如下

Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();
Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
Oracle.DataAccess.Client.OracleParameter parameter;
int rowsAffected;

connection.ConnectionString = this.ConnectionString.Text;
command.CommandText = "INSERT INTO IdentityTest (Text) VALUES (:text) RETURNING Id INTO :id";

parameter = new Oracle.DataAccess.Client.OracleParameter(":text", "Test 1");
command.Parameters.Add(parameter);

parameter = new Oracle.DataAccess.Client.OracleParameter();
parameter.ParameterName = ":id";
parameter.DbType = System.Data.DbType.Int64;
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(parameter);

command.Connection = connection;

try {
   connection.Open();
   rowsAffected = command.ExecuteNonQuery();
   System.Windows.MessageBox.Show(string.Format("Added a row with id: {0}",
      command.Parameters[":id"].Value.ToString()),
      "Operation succeeded");
} catch (System.Exception exception) {
   System.Windows.MessageBox.Show(exception.Message,
      "Exception occurred",
      MessageBoxButton.OK,
      MessageBoxImage.Error);
} finally {
   connection.Dispose();
   command.Dispose();
}

代码中的关键是 Insert 语句。与第一个测试一样，它只包含第二个列的值。该值使用参数传递到数据库。Insert 语句被定义为将插入的 Id 值返回给第二个参数 :id。这是通过添加

RETURNING Id INTO :id

到 insert 语句的末尾来实现的。第一个参数是一个正常的输入参数，从客户端到服务器。但第二个参数的方向被定义为 System.Data.ParameterDirection.ReturnValue，这意味着参数值在调用期间填充。

执行 Insert 时，程序会显示所插入行的 ID 字段的值。现在，程序可以存储此值并在后续数据库调用中使用它。

复制

当涉及复制时，自动生成数字始终是一个问题。SQL Server 在复制带有标识列的表时非常严格。即使在参与 Oracle 复制的两个数据库中使用单独的序列，主要问题仍然存在。如何防止数据库之间的重复值？

一种方法是为每个数据库中的序列分配完全不同的数字范围。这与 SQL Server 的解决方案相同。根据情况，更合适的解决方案可能是创建复合键。这意味着键包含多个列。例如，第一列可以是单个数据库范围内的静态值，如站点或数据库 ID，第二列是实际生成的数字。使用此解决方案，序列可以完美地生成相同的值，但即使行从一个数据库传输到另一个数据库，主键的完整性也不会受到损害。

由于序列是独立的[对象]，复制数据库实际上也可以从位于单个数据库中的公共序列获取新值。但是，这也意味着数据库不再独立，因此此解决方案通常不用于复制环境，而用于分布式数据库。

其他机制

使用序列不是唯一的选择。SQL Server 有一个 NEWID() 函数来为 uniqueidentifier 列创建值。Oracle 有一个类似（但不完全相同）的函数，称为 SYS_GUID()。在 Oracle 中，此函数生成一个新的全局唯一的 RAW 值。此函数可以与示例中的序列类似地使用。SYS_GUID() 的值可以通过触发器插入，或将其设置为主键列的默认值。使用默认值时，不需要创建额外的触发器。但是，使用默认值时，总有客户端实际提供值的风险，因此为了安全起见，可能需要创建触发器。将 RAW 值返回给客户端遵循与示例应用程序相同的规则。

在复制环境中使用 SYS_GUID() 时，不再需要复合键，因为该值是全局唯一的。每个数据库都可以生成一个新值，它们不会重叠。

参考文献

本文的一些参考资料

历史

  • 2012 年 1 月 8 日：文章创建。
© . All rights reserved.