从ado.net调用Oracle存储函数并获得结果的正确方法是什么？

我有一个使用ado连接到Oracle数据库的vb.net代码库.我们有很多存储过程可以调用,有些存在多个out参数.但是,我现在需要调用一个存储函数,我不清楚如何将函数的结果返回到我的VB代码中.

编辑：我正在返回一个整数.

如何从ado.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 While

Finally
    If (Not (con Is Nothing)) Then
        con.Close()
    End If
End Try

（编辑：济源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!