编辑:我正在返回一个整数.
如何从ado.net正确调用oracle存储函数?
解决方法 我假设您正在使用ODP.net(.net的本机Oracle客户端).假设您有2个Oracle存储函数,如下所示:
FUNCTION my_func ( p_parm1 VARCHAR2,p_parm2 NUMBER ) RETURN VARCHAR2 AS BEGIN RETURN p_parm1 || to_char(p_parm2); END; FUNCTION my_func2 RETURN SYS_REFCURSOR AS v_cursor SYS_REFCURSOR; BEGIN OPEN v_cursor FOR SELECT 'hello there Sean' col1 FROM dual UNION ALL SELECT 'here is your answer' col1 FROM dual; RETURN v_cursor; END;
其中一个函数返回VARCHAR2,另一个函数返回ref cursor.在VB方面,你可以这样做:
Dim con As New OracleConnection("Data Source=xe;User ID=sandBox;Password=sandBox; Promotable Transaction=local")Try con.open() Dim cmd As OracleCommand = con.CreateCommand() cmd.CommandText = "test_pkg.my_func" cmd.CommandType = CommandType.StoredProcedure Dim parm As OracleParameter parm = New OracleParameter() parm.Direction = ParameterDirection.ReturnValue parm.OracleDbType = OracleDbType.Varchar2 parm.Size = 5000 cmd.Parameters.Add(parm) parm = New OracleParameter() parm.Direction = ParameterDirection.input parm.Value = "abc" parm.OracleDbType = OracleDbType.Varchar2 cmd.Parameters.Add(parm) parm = New OracleParameter() parm.Direction = ParameterDirection.input parm.Value = 42 parm.OracleDbType = OracleDbType.Int32 cmd.Parameters.Add(parm) cmd.ExecuteNonquery() Console.Writeline("result of first function is " + cmd.Parameters(0).Value) ''''''''''''''''''''''''''''''''''''''''''''' ' Now for the second query ''''''''''''''''''''''''''''''''''''''''''''' cmd = con.CreateCommand() cmd.CommandText = "test_pkg.my_func2" cmd.CommandType = CommandType.StoredProcedure parm = New OracleParameter() parm.Direction = ParameterDirection.ReturnValue parm.OracleDbType = OracleDbType.RefCursor cmd.Parameters.Add(parm) Dim dr As OracleDataReader = cmd.ExecuteReader() While (dr.Read()) Console.Writeline(dr(0)) End WhileFinally If (Not (con Is nothing)) Then con.Close() End IfEnd Try总结
以上是内存溢出为你收集整理的从ado.net调用Oracle存储函数并获得结果的正确方法是什么?全部内容,希望文章能够帮你解决从ado.net调用Oracle存储函数并获得结果的正确方法是什么?所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)