vb.net 您如何使用 ExecuteScalar 从 Oracle 数据库返回单个值？

How do you use ExecuteScalar to return a single value from an Oracle Database?

提问by user38349

Been using the code below to return a single record from the database. I have read that ExecuteScalar is the right way to return a single record. I have never been able to get ExecuteScalar to work though. How would I change this to return a single value in VB.Net using ExecuteScalar?

一直使用下面的代码从数据库中返回单个记录。我读过 ExecuteScalar 是返回单个记录的正确方法。不过，我一直无法让 ExecuteScalar 工作。我将如何更改它以使用 ExecuteScalar 在 VB.Net 中返回单个值？

    Dim oracleConnection As New OracleConnection
    oracleConnection.ConnectionString = LocalConnectionString()

    Dim cmd As New OracleCommand()
    Dim o racleDataAdapter As New OracleClient.OracleDataAdapter

    cmd.Connection = oracleConnection
    cmd.CommandText = "FALCON.CMS_DATA.GET_MAX_CMS_TH"
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add(New OracleParameter("i_FACID_C", OracleType.Char)).Value = facilityShortName
    cmd.Parameters.Add(New OracleParameter("RS_MAX", OracleType.Cursor)).Direction = ParameterDirection.Output

    Try
        Using oracleConnection
            oracleConnection.Open()
            Using oracleDataAdapter
                oracleDataAdapter = New OracleClient.OracleDataAdapter(cmd)

                Dim workingDataSet As DataSet
                oracleDataAdapter.TableMappings.Add("OutputSH", "RS_MAX")
                workingDataSet = New DataSet
                oracleDataAdapter.Fill(workingDataSet)


                For Each row As DataRow In workingDataSet.Tables(0).Rows

                    Return CDate(row("MAXDATE"))

                Next
            End Using
        End Using

采纳答案by user38349

From Microsoft

来自微软

"The ExecuteOracleScalar() method of the OracleCommand class is used to execute a SQL statement or stored procedure that returns a single value as an OracleType data type. If the command returns a result set, the method returns the value of the first column of the first row. The method returns a null reference if a REF CURSOR is returned rather than the value of the first column of the first row to which the REF CURSOR points. The ExecuteScalar() method of the OracleCommand class is similar to the ExecuteOracleScalar() method, except it returns a value as a .NET Framework data type.

"OracleCommand 类的 ExecuteOracleScalar() 方法用于执行返回单个值作为 OracleType 数据类型的 SQL 语句或存储过程。如果该命令返回一个结果集，则该方法返回的第一列的值第一行。如果返回的是 REF CURSOR 而不是 REF CURSOR 指向的第一行的第一列的值，则该方法返回空引用。OracleCommand 类的 ExecuteScalar() 方法类似于 ExecuteOracleScalar()方法，但它返回一个值作为 .NET Framework 数据类型。

Having said that, neither of these methods is useful when working with Oracle stored procedures. Oracle stored procedures cannot return a value as part of the RETURN statement, only as OUT parameters—see the Stored Procedures That Do Not Return Data section. Also, you cannot return a result set except through a REF CURSOR output parameter—this is discussed in the next section.

话虽如此，但在使用 Oracle 存储过程时，这两种方法都没有用。Oracle 存储过程不能作为 RETURN 语句的一部分返回值，只能作为 OUT 参数返回值——请参阅不返回数据的存储过程部分。此外，除非通过 REF CURSOR 输出参数，否则无法返回结果集 - 这将在下一节中讨论。

You can retrieve the return value for an Oracle function only by using a RETURN parameter (shown in the previous section) and not by using the one of the ExecuteScalar methods."

您只能通过使用 RETURN 参数（如上一节所示）而不是使用 ExecuteScalar 方法之一来检索 Oracle 函数的返回值。”

http://msdn.microsoft.com/en-us/library/ms971506.aspx

http://msdn.microsoft.com/en-us/library/ms971506.aspx

回答by Jim Anderson

ExecuteScalar returns a single value (scalar) not a record.

ExecuteScalar 返回单个值（标量）而不是记录。

回答by Brian Schmitt

not sure why the other answer is marked as accepted, as it doesn't appear to answer your question

不知道为什么另一个答案被标记为已接受，因为它似乎没有回答你的问题

How would I change this to return a single value in VB.Net using ExecuteScaler

我将如何更改它以使用 ExecuteScaler 在 VB.Net 中返回单个值

ExecuteScalar will only return a Single Value - so keep that in mind when writing the query portion of your command. The code to accomplish this would be as follows:

ExecuteScalar 只会返回一个单值 - 所以在编写命令的查询部分时请记住这一点。完成此操作的代码如下：

oracleConnection.Open
Dim obj as object 'Object to hold our return value
obj = cmd.ExecuteScalar()
oracleConnection.Close

If obj IsNot Nothing then
    Return CDate(obj)
end if