Oracle - 将 Select Count(*) from ... 作为 System.Data.OracleClient 中的输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2519625/
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
Oracle - Getting Select Count(*) from ... as an output parameter in System.Data.OracleClient
提问by cbeuker
Greetings all,
问候大家，
I have a question. I am trying to build a parametrized query to get me the number of rows from a table in Oracle. Rather simple. However I am an Oracle newbie..
我有个问题。我正在尝试构建一个参数化查询，以获取 Oracle 表中的行数。相当简单。但是我是 Oracle 新手..
I know in SQL Server you can do something like:
我知道在 SQL Server 中，您可以执行以下操作：
Select @outputVariable = count(*) from sometable where name = @SomeOtherVariable
Select @outputVariable = count(*) from sometable where name = @SomeOtherVariable
and then you can set up an Output parameter in the System.Data.SqlClient to get the @outputVariable.
然后你可以在 System.Data.SqlClient 中设置一个输出参数来获取@outputVariable。
Thinking that one should be able to do this in Oracle as well, I have the following query
认为在 Oracle 中也应该能够做到这一点，我有以下查询
Select count(*) into :theCount from sometable where name = :SomeValue
Select count(*) into :theCount from sometable where name = :SomeValue
I set up my oracle parameters (using System.Data.OracleClient - yes I know it will be deprecated in .Net 4 - but that's what I am working with for now) as follows
我设置了我的 oracle 参数（使用 System.Data.OracleClient - 是的，我知道它会在 .Net 4 中被弃用 - 但这是我现在正在使用的）如下
IDbCommand command = new OracleCommand();
command.CommandText = "Select count(*) into :theCount from sometable where name = :SomeValue";
command.CommandType = CommandType.Text;
OracleParameter parameterTheCount = new OracleParameter(":theCount", OracleType.Number);
parameterTheCount .Direction = ParameterDirection.Output;
command.Parameters.Add(parameterTheCount );
OracleParameter parameterSomeValue = new OracleParameter(":SomeValue", OracleType.VarChar, 40);
parameterSomeValue .Direction = ParameterDirection.Input;
parameterSomeValue .Value = "TheValueToLookFor";
command.Parameters.Add(parameterSomeValue );
command.Connection = myconnectionObject;
command.ExecuteNonQuery();
int theCount = (int)parameterTheCount.Value;
At which point I was hoping the count would be in the parameter parameterTheCountthat I could readily access.
在这一点上，我希望计数将在parameterTheCount我可以轻松访问的参数中。
I keep getting the error ora-01036 which http://ora-01036.ora-code.comtells me to check my binding in the sql statement. Am I messing something up in the SQL statement? Am I missing something simple elsewhere?
我不断收到错误 ora-01036，http://ora-01036.ora-code.com告诉我检查我在 sql 语句中的绑定。我在 SQL 语句中搞砸了什么吗？我在其他地方错过了一些简单的东西吗？
I could just use command.ExecuteScaler() as I am only getting one item, and am probably going to end up using that, but at this point, curiosity has got the better of me. What if I had two parameters I wanted back from my query (ie: select max(ColA), min(ColB) into :max, :min.....)
我可以只使用 command.ExecuteScaler() 因为我只得到一个项目，并且可能最终会使用它，但在这一点上，好奇心让我变得更好。如果我有两个我想从查询中返回的参数怎么办（即：选择 max(ColA), min(ColB) into :max, :min .....）
Thanks..
谢谢..
回答by FerranB
Some versions of the ADO does not need the colon :configuring OracleParameter.
某些版本的 ADO 不需要冒号:配置OracleParameter。
Instead of:
代替：
new OracleParameter(":theCount", OracleType.Number);
try
尝试
new OracleParameter("theCount", OracleType.Number);
Anyway, I think you have to use the ExecuteScalar()function of the IDbCommandand avoiding use of into(which I'm not sure it's valid on this context). I mean:
无论如何，我认为你必须使用ExecuteScalar()的功能IDbCommand，避免使用into（这我不知道它是有效的在这种情况下）。我的意思是：
IDbCommand command = new OracleCommand();
command.CommandText = "Select count(*) from sometable where name = :SomeValue";
command.CommandType = CommandType.Text;
OracleParameter parameterSomeValue = new OracleParameter("SomeValue", OracleType.VarChar, 40);
parameterSomeValue .Direction = ParameterDirection.Input;
parameterSomeValue .Value = "TheValueToLookFor";
command.Parameters.Add(parameterSomeValue );
command.Connection = myconnectionObject;
int theCount = (int)command.ExecuteScalar();
Disclaimer: The code have not been compiled, and may be have any little error.
免责声明：代码尚未编译，可能有任何小错误。
Update: If you take a look on the Oracle SELECTsyntax, you will see that The SELECT INTOsentence is not recognized. But it's valid in PLSQL syntax as you can see here. You can try one of the following to see if it works (not tested):
更新：如果您查看 Oracle SELECT语法，您会看到 The SELECT INTOsentence is not识别。但它在 PLSQL 语法中是有效的，正如您在此处看到的。您可以尝试以下方法之一，看看它是否有效（未测试）：
command.CommandText = "begin Select count(*) into :someCount from sometable where name = :SomeValue; end;";
回答by Klaus Byskov Pedersen
I think the problem is that you have a trailing space in the parameter name for parameterTheCount.
我认为问题在于您的参数名称中有一个尾随空格parameterTheCount。
Edit
编辑
Now remove the colons from the parameter names in the constructor to OracleParameter.
现在从构造函数中的参数名称中删除冒号到OracleParameter.
