c#调用oracle具有输入输出参数还有返回值的自定义函数

[ssq4706]

函数如下：
CREATE OR REPLACE FUNCTION function1(
f1 integer,
f2 out varchar2
) return integer is
//业务处理
if then
return 0;
elsif then
retrun -1;
end if;

c#怎么调用?谢谢

[wangnaisheng]

和调用存储过程是一样滴 http://bbs.csdn.net/topics/330222462 参考以前的帖子

[sunny906]

            OracleConnection con = new OracleConnection("");
            OracleCommand cmd = new OracleCommand();

            OracleParameter p1 = new OracleParameter("f1", OracleType.Int32);
            p1.Direction = ParameterDirection.Input;
            p1.Value = 5;
            OracleParameter p2 = new OracleParameter("f2", OracleType.VarChar);
            p2.Direction = ParameterDirection.Output;
            p2.Value = "f";
            OracleParameter p3 = new OracleParameter();
            p3.OracleType = OracleType.Int32;
            p3.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);

            cmd.Connection = con;
            cmd.CommandText = "function1";
            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

[ssq4706]

引用 1 楼 wangnaisheng 的回复:

和调用存储过程是一样滴 http://bbs.csdn.net/topics/330222462 参考以前的帖子

引用 2 楼 sunny906 的回复:

            OracleConnection con = new OracleConnection("");
            OracleCommand cmd = new OracleCommand();

            OracleParameter p1 = new OracleParameter("f1", OracleType.Int32);
            p1.Direction = ParameterDirection.Input;
            p1.Value = 5;
            OracleParameter p2 = new OracleParameter("f2", OracleType.VarChar);
            p2.Direction = ParameterDirection.Output;
            p2.Value = "f";
            OracleParameter p3 = new OracleParameter();
            p3.OracleType = OracleType.Int32;
            p3.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);

            cmd.Connection = con;
            cmd.CommandText = "function1";
            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

我是这么写的

object objChart;
            OracleParameter p1 = new OracleParameter("f1", OracleDbType.Int32, 100, 5, ParameterDirection.Input);
            OracleParameter p2 = new OracleParameter("f2", OracleDbType.Varchar2, 100, "", ParameterDirection.Output);
            OracleParameter p3 = new OracleParameter();
            p3.OracleDbType = OracleDbType.Int32;
            p3.Direction = ParameterDirection.ReturnValue;
            OracleParameter[] cmdParm1 = new OracleParameter[] { p1, p2, p3 };
            OracleDBHelper.ExecuteNonQuery("functionName", System.Data.CommandType.StoredProcedure, cmdParm1);
            objChart = p3.Value;
            return objChart;

会报ora-01403:未找到任何数据 的错误。

[ssq4706]

以上写法会导致输入参数传递不进去，折腾了一晚上，操，找到了解决方法：返回值参数应放到第一位，即：

object objChart;
            OracleParameter p1 = new OracleParameter("f1", OracleDbType.Int32, 100, 5, ParameterDirection.Input);
            OracleParameter p2 = new OracleParameter("f2", OracleDbType.Varchar2, 100, "", ParameterDirection.Output);
            OracleParameter p3 = new OracleParameter();
            p3.OracleDbType = OracleDbType.Int32;
            p3.Direction = ParameterDirection.ReturnValue;
            OracleParameter[] cmdParm1 = new OracleParameter[] {p3,  p1, p2 };
            OracleDBHelper.ExecuteNonQuery("functionName", System.Data.CommandType.StoredProcedure, cmdParm1);
            objChart = p3.Value;
            return objChart;