多次调用oracle过程卡住

本文关键字：过程 oracle 调用 | 更新日期: 2023-09-27 18:10:09

我可以加快速度。我必须调用存储过程，每次从oracle和更新它的最新值。这是我的最后一个瓶颈，因为它可以被称为数千次。我将其他插入转换为数组绑定大容量插入。

c# call oracle

for (int i = 0; i < r.receiptkey.Count(); i++)
{       
  ld.receiptlinenumber.Add(AsnGetNextAvailableReceiptLineNumber(r.receiptkey[i]));
}
c#甲骨文

 public string AsnGetNextAvailableReceiptLineNumber(string myReceiptKey)
    {
        using (OracleCommand cmd = new OracleCommand())
        {
            OracleConnection conn;
            conn = new OracleConnection(ConnectionStringOracle);
            cmd.CommandText = "bpPack.GetNextReceiptLineNumber";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.Add(new OracleParameter("iReceiptKey", OracleDbType.Varchar2, 10));
            cmd.Parameters.Add(new OracleParameter("oRetValue", OracleDbType.Varchar2, 5)).Direction = ParameterDirection.Output;
            cmd.Parameters["iReceiptKey"].Value = myReceiptKey;
            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e9)
            {
                return "Error" + e9.Message;
            }
            return cmd.Parameters["oRetValue"].Value.ToString();
        }
    }

plsql 

  PROCEDURE GetNextReceiptLineNumber(
   iReceiptKey IN VARCHAR2,
   oRetValue OUT VARCHAR2)
  IS
    xreceiptlinenumber VARCHAR2 (5);
    xreceiptlinecount  NUMBER (10);
  BEGIN
     xreceiptlinenumber :='';
     xreceiptlinecount  := 0;
     SELECT MAX (receiptlinenumber)
    INTO xreceiptlinenumber
     FROM receiptdetail
    WHERE receiptkey        = iReceiptKey;
   IF (xreceiptlinenumber IS NULL) THEN
    xreceiptlinecount    := 0;
  ELSE
    xreceiptlinecount := TO_NUMBER (LTRIM (xreceiptlinenumber, '0'));
  END IF;
  xreceiptlinecount := xreceiptlinecount + 1;
  oRetValue         := LPAD (xreceiptlinecount, 5, '0');
  END GetNextReceiptLineNumber;

多次调用oracle过程卡住

不要在每个过程调用上连接-将连接代码和过程调用代码分开。

您的过程可能会遇到获得重复的情况(想象一下如果两个会话同时使用相同的键调用它会发生什么)-如果可能的话，使用序列而不是max。

也许您可以在c#代码中详细说明以下查询所检索的结果。

SELECT receiptkey, LPAD (nvl((to_number(MAX(receiptlinenumber))+1),'0'), 5, '0') as NextReceiptLineNumber
FROM receiptdetail
group by receiptkey
相关文章：