从 ado.net 调用 Oracle 存储函数并获得结果的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1773534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the right way to call an Oracle stored function from ado.net and get the result?
提问by Sean McMillan
I've got a vb.net codebase using ado to connect to an Oracle database. We have lots of stored procedures that we call, some with multiple out parameters. However, I now need to call a stored function, and it's not clear to me how to get the result of the function back into my VB code.
我有一个使用 ado 连接到 Oracle 数据库的 vb.net 代码库。我们有很多我们调用的存储过程，其中一些有多个输出参数。但是，我现在需要调用一个存储的函数，我不清楚如何将函数的结果返回到我的 VB 代码中。
Edit: I'm returning an integer.
编辑：我返回一个整数。
How do I properly call an oracle stored function from ado.net?
如何从 ado.net 正确调用 oracle 存储函数？
回答by dcp
I'll assume you are using ODP.net (native Oracle client for .net).
我假设您使用的是 ODP.net（用于 .net 的本机 Oracle 客户端）。
Let's say you have 2 Oracle stored functions like this:
假设您有 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;
One of the functions returns a VARCHAR2 and the other returns ref cursor. On VB side, you could do this:
其中一个函数返回一个 VARCHAR2，另一个返回引用游标。在 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 While
Finally
If (Not (con Is Nothing)) Then
con.Close()
End If
End Try
回答by James
' Create ODP database connection
Dim constr As String = (("User Id=" & Properties.Settings.[Default].DbUid & "; Password=") + Properties.Settings.[Default].DbPwd & "; Data Source=") + Properties.Settings.[Default].DbTnsName & "; Pooling=false"
Dim con As New OracleConnection(constr)
Dim cmd As New OracleCommand()
cmd.Connection = con
Try
cmd.CommandText = "test_pkg.test_function"
cmd.CommandType = CommandType.StoredProcedure
'Always add return parameter before other parameters when calling database functions in .net!!
cmd.Parameters.Add("result", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add("param1", OracleDbType.Varchar2).Value = "something"
cmd.Parameters.Add("param2", OracleDbType.Varchar2).Value = "something else"
con.Open()
cmd.ExecuteNonQuery()
Console.WriteLine(CInt(cmd.Parameters("result").Value))
Finally
' Cleanup
con.Close()
cmd.Dispose()
con.Dispose()
End Try
