从c#调用oracle更新存储过程

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

我在一个包里有两个oracle过程，像这样:

PROCEDURE INSERT_LOG (
    message_id      in VARCHAR2,
    mq_request      in VARCHAR2,
    req_timestamp   in VARCHAR2
)
IS
BEGIN
    INSERT INTO TESTSCHEMA.MY_MESSAGE_LOG (MESSAGE_ID,MQ_REQUEST,REQ_TIMESTAMP)
    VALUES(message_id,mq_request,TO_DATE(req_timestamp,'DD-MM-YYYY HH24:MI:SS'));
    commit;
EXCEPTION
  WHEN OTHERS
  THEN
     ROLLBACK;
     RAISE_APPLICATION_ERROR (-20000, 'Error: INSERT_LOG() '||SQLERRM);
END;
PROCEDURE UPDATE_LOG (
        message_id      IN VARCHAR2,
        mq_response     IN VARCHAR2,
        resp_identifier IN VARCHAR2,
        resp_timestamp  IN VARCHAR2,
        req_timestamp   IN VARCHAR2 
        )
IS
BEGIN
        UPDATE TESTSCHEMA.MY_MESSAGE_LOG A
        SET
            A.MQ_RESPONSE = mq_response,
            A.RESP_IDENTIFIER =resp_identifier,
            A.RESP_TIMESTAMP = TO_DATE(resp_timestamp,'DD-MM-YYYY HH24:MI:SS')
        WHERE    
            A.MESSAGE_ID = message_id
            and A.REQ_TIMESTAMP = TO_DATE(req_timestamp,'DD-MM-YYYY HH24:MI:SS');
        commit;
EXCEPTION
    WHEN OTHERS
    THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20000, 'Error: UPDATE_LOG() '||SQLERRM);
END;

我试着从我的c#代码调用这些过程。问题是插入过程工作正常，但更新过程不更新任何行。在调查中，我注意到，尽管我从c#代码中为存储过程设置了变量，但这些变量的值在数据库端反映为null或空。下面是更新过程的c#代码。

try
        {
            using (DbConnection connection = new DbConnection())
            {
                var comm = new OracleCommand
                {
                    Connection = connection.OpenUsbAppsSchema(),
                    CommandText = <My Procedure Name>,
                    CommandType = CommandType.StoredProcedure
                };
                var param = new OracleParameter[5];
                param[0] = new OracleParameter("message_id", OracleDbType.Varchar2, 500, ParameterDirection.Input) { Value = messageId };
                param[1] = new OracleParameter("mq_response", OracleDbType.Varchar2, 2000, ParameterDirection.Input) { Value = mqResponse };
                param[2] = new OracleParameter("resp_identifier", OracleDbType.Varchar2, 200, ParameterDirection.Input) { Value = identifier };
                param[3] = new OracleParameter("resp_timestamp", OracleDbType.Varchar2,500, ParameterDirection.Input) { Value = resposeTimeStamp };
                param[4] = new OracleParameter("req_timestamp", OracleDbType.Varchar2, 500, ParameterDirection.Input) {Value = requestTimeStamp};
                comm.Parameters.AddRange(param);
                comm.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

任何指针我做错了什么?

从c#调用oracle更新存储过程

一般来说，您不希望为过程命名可能与表中列的名称冲突的参数。这使得它太容易犯错误，你似乎在这里做的，你期望标识符引用参数，而Oracle实际上是将它们解析为表中的列。

查看您的UPDATE语句，

UPDATE TESTSCHEMA.MY_MESSAGE_LOG A
   SET A.MQ_RESPONSE = mq_response,
       A.RESP_IDENTIFIER =resp_identifier,
       A.RESP_TIMESTAMP = TO_DATE(resp_timestamp,'DD-MM-YYYY HH24:MI:SS')
 WHERE A.MESSAGE_ID = message_id
   and A.REQ_TIMESTAMP = TO_DATE(req_timestamp,'DD-MM-YYYY HH24:MI:SS');

当Oracle看到独立标识符mq_response时，它首先使用表中的列来解决这个问题。由于表中有一个mq_response列，这就是Oracle使用的。除非检查表中的列失败，否则它不会查看是否存在名为mq_response的局部变量或名为mq_response的参数。这发生在查询中的每个非限定标识符上，因此您的查询最终(逻辑上)此查询更新表中的每一行，但不更改任何值

UPDATE TESTSCHEMA.MY_MESSAGE_LOG A
   SET A.MQ_RESPONSE = a.mq_response,
       A.RESP_IDENTIFIER =a.resp_identifier,
       A.RESP_TIMESTAMP = TO_DATE(a.resp_timestamp,'DD-MM-YYYY HH24:MI:SS')
 WHERE A.MESSAGE_ID = a.message_id
   and A.REQ_TIMESTAMP = TO_DATE(a.req_timestamp,'DD-MM-YYYY HH24:MI:SS');

解决这个问题最常见的方法是对参数和局部变量采用标准的命名约定，以将它们与表中的列区分开来。就我个人而言，我使用p_作为参数的前缀，使用l_作为局部变量的前缀，这是很常见的。如果你这样做，你会得到像

这样的东西
PROCEDURE UPDATE_LOG (
        p_message_id      IN VARCHAR2,
        p_mq_response     IN VARCHAR2,
        p_resp_identifier IN VARCHAR2,
        p_resp_timestamp  IN VARCHAR2,
        p_req_timestamp   IN VARCHAR2 
        )
IS
BEGIN
        UPDATE TESTSCHEMA.MY_MESSAGE_LOG A
           SET A.MQ_RESPONSE     = p_mq_response,
               A.RESP_IDENTIFIER = p_resp_identifier,
               A.RESP_TIMESTAMP  = TO_DATE(p_resp_timestamp,'DD-MM-YYYY HH24:MI:SS')
         WHERE A.MESSAGE_ID    = p_message_id
           and A.REQ_TIMESTAMP = TO_DATE(p_req_timestamp,'DD-MM-YYYY HH24:MI:SS');
        commit;
EXCEPTION
    WHEN OTHERS
    THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20000, 'Error: UPDATE_LOG() '||SQLERRM);
END;

另一种方法是显式地限定查询中的所有参数名。可以使用函数名作为限定符，例如

UPDATE TESTSCHEMA.MY_MESSAGE_LOG A
   SET A.MQ_RESPONSE = update_log.mq_response,
       A.RESP_IDENTIFIER =update_log.resp_identifier,
       A.RESP_TIMESTAMP = TO_DATE(update_log.resp_timestamp,'DD-MM-YYYY HH24:MI:SS')
 WHERE A.MESSAGE_ID = update_log.message_id
   and A.REQ_TIMESTAMP = TO_DATE(update_log.req_timestamp,'DD-MM-YYYY HH24:MI:SS');

就我个人而言，我发现前缀更容易读，更不容易出错。

看看你的代码，我强烈建议拿出commit语句并删除异常处理程序。当您在过程中使用commit语句时，这些过程的可重用性会大大降低，因为如果一段代码位于事务中间，则无法使用它们。否则，您的commit将提交调用者可能正在执行的所有工作。异常处理程序应该只在代码可以对异常做一些有用的事情或者自定义异常将向调用者提供更多信息的情况下捕获异常。但是，在这种情况下，异常处理程序所做的只是丢弃错误堆栈，并阻止调用者轻松检查错误代码以确定出错的地方。使用这么简单的代码，您可能不会丢失太多调试信息，但如果您编写这样的异常处理程序，那么您将为调试代码设置一个麻烦的世界。

相关文章：