时间戳

首页 > 解决方案 > 即使是好的查询也无法在 Oracle 中捕获重复的行

c# - 即使是好的查询也无法在 Oracle 中捕获重复的行

问题描述

它是关于插入后检查重复的行。我知道你会说为什么在插入之前不先检查，但由于某些原因我们不能这样做。我们正在使用 Oracle 12c。

当发现多个相同的条目（transactionNumber 和 vendor 作为唯一）时，最早时间的第一个条目应作为“成功”状态传递，而其他时间最晚的条目应作为“重复”状态被拒绝。

见下图。

在此处输入图像描述

在此处输入图像描述

此查询似乎很有用，但有时它不会捕获重复的行。当运行控制台应用程序（C#）来加载 1000 个数据时，我最终得到了 1000 个数据中的 2 个重复项。在生产中，我们在 50000 个中得到了不到 15 个重复项。我知道这还不错，但是有办法吗比这个查询更好？根本没有重复！

Oracle DB 似乎失控了，还是我对这个查询做错了什么？我们应该为这个查询创建索引吗？有什么建议吗？

function checkDuplicate(i_vendor varchar2,i_transactionnumber varchar2, i_txId 
raw) return number 
is
    transactionId raw(16);
    o_result number;
BEGIN
    select tx.id into transactionId from (select tx.id,row_number() over (order by tx.trans_time asc) as seqnum
    from test_tx_log tx
    where tx.transactionnumber = i_transactionnumber and lower(tx.vendor) = lower(i_vendor)) tx where seqnum = 1;

    o_result := CASE transactionId = i_txId WHEN true THEN 1 ELSE 0 END;

    return o_result;

 END;

我的桌子是：

  Create TABLE test_tx_log
  (
   id                  RAW(16) not null,
   status              VARCHAR2 (300) NOT NULL,
   trans_time          TIMESTAMP NOT NULL,
   receiptnumber       VARCHAR2 (100) NULL,
   transactionnumber   VARCHAR2 (120) NULL,
   customerreference   VARCHAR2 (100) NULL,
   vendor              VARCHAR2 (100) NULL
   ) ;

C＃

    public bool CheckDuplicate3()
    {
        DbConnection connection = null;
        try
        {
            connection = GetFactory().CreateConnection();
            if (connection != null)
            {
                connection.ConnectionString = "user id=XXXX;password=XXXX;data source=XXXX";
                connection.Open();
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "mca_test_package.checkDuplicate";
                    command.CommandType = CommandType.StoredProcedure;
                    command.AddParameter("o_result", DbType.Decimal, 0, ParameterDirection.ReturnValue);
                    command.AddParameter("i_vendor", DbType.String, tx.Vendor);
                    command.AddParameter("i_transactionnumber", DbType.String, tx.TransactionNumber.Trim());
                    command.AddParameter("i_txId", DbType.Binary, tx.Id.ToByteArray(), ParameterDirection.Input, 16);
                    command.ExecuteNonQuery();
                    var result = Convert.ToInt32(command.Parameters["o_result"].Value);

                    if (result == 1)
                    {
                        tx.status = "Success";
                        Console.WriteLine("No Duplicate {0}", tx);
                    }
                    else
                    {
                        Console.WriteLine("Duplicate {0}", tx);
                        tx.status = "RejectedDuplicate";
                    }
                }

                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = "update test_tx_log tx set tx.status = :status where id = :id";
                    command.AddParameter("status", DbType.String, tx.status);
                    //command.AddParameter("id", DbType.Decimal, tx.Id);
                    command.AddParameter("id", DbType.Binary, tx.Id.ToByteArray());
                    command.ExecuteNonQuery();
                }
            }
            return true;
        }
        finally
        {
            if (connection != null)
                connection.Close();
        }
    }

标签： c#databaseoraclestored-functions

解决方案

改变(select tx.id,row_number() over (order by tx.trans_time asc) as seqnum

至

(select tx.id,row_number() over (order by tx.trans_time, tx.id asc) as seqnum

按 trans_time、tx.id 排序，而不仅仅是 trans_time。

如果没有订单，则无法保证返回订单，因此查询中的 ID 顺序在 trans_time 内是随机的。

在您的示例中，当您使用第一个 ID 调用函数时，第一个 ID 是第一个 seqnum 并且匹配。

当您使用第二个 ID 调用函数时，第二个 ID 是第一个 seqnum 并且也匹配。

将 ID 添加到 order by 将保证 ID 1 是最早 trans_time 内两个调用的 seqnum 1。

推荐阅读