时间戳

首页 > 解决方案 > 如何通过代码调用存储过程（带有用户参数）？

c# - 如何通过代码调用存储过程（带有用户参数）？

问题描述

从我的 C# 程序中，我需要调用 Oracle 数据库中的存储过程，该数据库具有以下合同：

PKG_ENTITY.ALTER_ENTITY (VARCHAR2 NAME, VARCHAR2 FULLNAME, ATTRS_TYPE ATTRS, VARCHAR2 STATUS, INTEGER OUT RESULT, VARCHAR2 OUT ERRORMSG).

和RESULT参数ERRORMSG是OUT参数。

我知道ATTRS_TYPE它指定的类型：

TYPE ATTRS_TYPE IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(30);

我曾经这样调用这个存储过程：

 private void ExecuteNonQuery(string query, params OracleParameter[] parameters)
 {
      using (var connection = new OracleConnection(_connectionString))
      {
            var command = new OracleCommand(query, connection) { CommandType = CommandType.Text };
            connection.Open();
            command.Parameters.AddRange(parameters);
            command.ExecuteNonQuery();
      }
 }

其中查询 =

DECLARE
    tNAME varchar2(100);
    tATTRS PKG_ENTITY.ATTRS_TYPE;

    tRESULT INTEGER;
    tERRORMSG varchar2(100);


BEGIN

    tNAME := :pEntityId;

     tATTRS(:pPropId) := :pPropValue;

    PKG_ENTITY.ALTER_ENTITY(tUSERNAME,NULL,tATTRS,NULL,tRESULT,tERRORMSG);
END;

参数值：pEntityId、pPropId 和 pPropValue 在代码中定义。

一切都很好，但后来我收到了需要注销 tRESULT 和 tERRORMSG 参数值的要求，因此我遇到了很大的困难。我想在调用存储过程后通过添加 SELECT 来修改查询。像那样：

DECLARE
    tNAME varchar2(100);
    tATTRS PKG_ENTITY.ATTRS_TYPE;

    tRESULT INTEGER;
    tERRORMSG varchar2(100);


BEGIN

    tNAME := :pEntityId;

     tATTRS(:pPropId) := :pPropValue;

    PKG_USER.ALTER_USER(tUSERNAME,NULL,tATTRS,NULL,tRESULT,tERRORMSG);
    SELECT tRESULT, tERRORMSG FROM DUAL;
END;

但是从语言的角度来看，这样的查询是不正确的pl/sql。因此，我得出的结论是，我需要直接使用存储过程调用，代码应该是这样的：

private ProcedureResult ExecuteStoredProcedure(string procedureName)
{
    using (var connection = new OracleConnection(_connectionString))
    {
        var command = new OracleCommand(procedureName, connection) { CommandType = CommandType.StoredProcedure };
        connection.Open();
        command.Parameters.Add("NAME", OracleDbType.Varchar2, "New name", ParameterDirection.Input);
        command.Parameters.Add("FULLNAME", OracleDbType.Varchar2, "New fullname", ParameterDirection.Input);
        var attr = new EntityAttribute() { attribute1 = "id", attribute2 = "value"};
        command.Parameters.Add("ATTRS", EntityAttribute, "New fullname", ParameterDirection.Input);
        command.Parameters.Add("STATUS", OracleDbType.Varchar2, "Status", ParameterDirection.Input);
        command.Parameters.Add("RESULT", OracleDbType.Int32).Direction = ParameterDirection.Output;
        command.Parameters.Add("ERRORMSG", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

        command.ExecuteNonQuery();

        return new ProcedureResult()
        {
            StatusCode = int.Parse(command.Parameters["RESULT"].Value.ToString()),
            Message = command.Parameters["ERRORMSG"].Value.ToString()
        };
    }
}

在这里，我在PKG_ENTITY.ATTRS_TYPE类型定义方面遇到了困难。

TYPE ATTRS_TYPE IS TABLE OF VARCHAR2 (2000) INDEX BY VARCHAR2 (30);

我知道有一个IOracleCustomType接口，但我不明白如何正确实现它。

例如

[OracleCustomTypeMapping("PKG_ENTITY.ATTRS_TYPE")]
public class EntityAttribute : INullable, IOracleCustomType
{
    [OracleObjectMapping("ATTRIBUTE1")]
    public string attribute1 { get; set; }
    [OracleObjectMapping("ATTRIBUTE2")]
    public string attribute2 { get; set; }

    public bool IsNull => throw new System.NotImplementedException();
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        throw new NotImplementedException(); 
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        throw new NotImplementedException();
    }
}

这个类的字段名称应该是什么？我了解“ATTRIBUTE1”和“ATTRIBUTE2”不是有效名称。

标签： c#oraclestored-proceduresplsql

解决方案

这个答案表明您不能INDEX BY VARCHAR2在 C# 中传递关联数组。相反，您可以在匿名 PL/SQL 块中构建关联数组并从那里调用过程（就像您最初所做的那样）。

所以你可以使用：

DECLARE
  tATTRS PKG_ENTITY.ATTRS_TYPE;
BEGIN
  tATTRS(:pPropId) := :pPropValue;

  PKG_USER.ALTER_USER(
    NAME     => :pEntityId,
    USERNAME => NULL,
    ATTRS    => tATTRS,
    STATUS   => NULL,
    RESULT   => :pResult,
    ERRORMSG => :pErrorMsg
  );
END;

然后传入参数pPropId，pPropValue并按照你正在做的方向传递pEntityId， 并以 的方向传递。ParameterDirection.InputpResultpErrorMsgParameterDirection.Output

推荐阅读