c# - 从 C# 调用 Oracle 函数时没有返回值

Question

从 Oracle SQL Developer ( ) 执行此 Oracle 函数时，我得到了返回值SELECT OSIEXTN.Validate_ID ('pers',1315) FROM DUAL;：

create or replace FUNCTION Validate_ID(v_Type   IN VARCHAR2,
                                       v_ID     IN NUMBER)
    RETURN VARCHAR2
IS
    v_validation   VARCHAR2 (10);
    v_cnt          NUMBER;
    v_ext          NUMBER;
BEGIN
    IF v_type = 'org'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM org
         WHERE orgnbr = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                v_validation := 'False';
        END CASE;
    ELSIF v_type = 'pers'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM pers
         WHERE persnbr = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                v_validation := 'False';
        END CASE;
    ELSIF v_type = 'acct'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM acct
         WHERE acctnbr = v_ID;
         
        SELECT COUNT(*) INTO v_ext
          FROM EXTENTITY
        WHERE EXTENTITYID = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                CASE v_ext
                WHEN 1
                THEN
                  v_validation := 'True';
                ELSE
                  v_validation := 'False';
                END CASE;
        END CASE;
    END IF;

    RETURN v_validation;
END;

但是当从这个 C# 代码调用它时，返回的值只是{}.

using (IDbConnection conn = DatabaseFactory.CreateConnection())
{
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "OSIEXTN.Validate_ID";

        cmd.AddParameter(
            "v_Type",
            ParameterDirection.Input,
            OracleDbType.Varchar2,
            entityType);

        cmd.AddParameter(
            "v_ID",
            ParameterDirection.Input,
            OracleDbType.Long,
            id);

        OracleParameter retVal = new OracleParameter("retVal", OracleDbType.Varchar2);
        retVal.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(retVal);

        cmd.ExecuteNonQuery();
        object o = retVal.Value;
    }
}

为什么我没有看到“真”或“假”？

标签： c#.netoraclefunction

Answer 1

只是一个小评论；正如 Gary 评论的那样，函数调用名称不匹配。

出现此问题是因为您尝试使用以下方法调用存储过程之类的函数

cmd.CommandType = CommandType.StoredProcedure;

这不适用于功能。要么创建一个调用该函数的存储过程，要么使用以下方法（为了演示目的，模拟了 plsql 实现）；

CREATE OR REPLACE FUNCTION SAMPLEFUNCTION (v_Type   IN VARCHAR2,
                                       v_ID     IN NUMBER)
    RETURN VARCHAR2
IS
    
BEGIN
  RETURN 'True';
END SAMPLEFUNCTION;

然后使用以下代码调用该函数；

var sqlToExecute = "BEGIN " +
"SELECT SAMPLEFUNCTION(:v_Type, :v_ID) INTO :retVal FROM DUAL; " +
"END;";

            using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
            {
                conn.Open();

                using (var cmd = new OracleCommand(sqlToExecute, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlToExecute;

                    // Input parameters
                    var oraParamVtype = new OracleParameter("v_Type", OracleDbType.Varchar2, ParameterDirection.Input);
                    oraParamVtype.Value = "A";
                    cmd.Parameters.Add(oraParamVtype);

                    var oraParamVid = new OracleParameter("v_ID", OracleDbType.Long, ParameterDirection.Input);
                    oraParamVid.Value = 1;
                    cmd.Parameters.Add(oraParamVid);

                    // Return value
                    var retVal = new OracleParameter("retVal", OracleDbType.Varchar2, ParameterDirection.Output) { Size = 5 };
                    cmd.Parameters.Add(retVal);

                    cmd.ExecuteNonQuery();
                    Console.WriteLine($"Query result: {retVal.Value}");
                }
            }
        }

请注意，我使用了 CommandType.Text