Oracle 中的参数化查询问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5980615/
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
Parameterized query in Oracle trouble
提问by Jamie Taylor
I'm using Oracle.DataAccessrather than the obsolete System.Data.OracleClientand I seem to be having trouble passing multiple parameters to my update query
我正在使用Oracle.DataAccess而不是过时的System.Data.OracleClient，我似乎无法将多个参数传递给我的更新查询
This works
这有效
OracleCommand.CommandText = "UPDATE db SET column1 = :param1 WHERE column2 = 'Y'"
OracleCommand.Parameters.Add(New OracleParameter("param1", "1234"))
But I want to be able to pass multiple parameters
但我希望能够传递多个参数
Here's my full code
这是我的完整代码
OracleConn.Open()
OracleCommand = OracleConn.CreateCommand()
OracleCommand.CommandText = "UPDATE db SET column1 = :param1 WHERE column2 = :param2"
OracleCommand.CommandType = CommandType.Text
OracleCommand.Parameters.Add(New OracleParameter("param1", "1234"))
OracleCommand.Parameters.Add(New OracleParameter("param2", "Y"))
OracleCommand.ExecuteNonQuery()
My SELECTquery seems to work when passing multiple parameters but not the update one
我的SELECT查询在传递多个参数但不是更新参数时似乎有效
回答by batwad
Although I can't see anything wrong with your example, I wonder if you're being hit by the old BindByNameproblem. By default, ODP.NET binds parameters to the query in the order in which they are added to the collection, rather than based on their name as you'd like. Try setting BindByNameto trueon your OracleCommandobject and see if that fixes the problem.
虽然我看不出你的例子有什么问题，但我想知道你是否被老BindByName问题所困扰。默认情况下，ODP.NET 按照参数添加到集合的顺序将参数绑定到查询，而不是根据您希望的名称。尝试在您的对象上设置BindByName为，看看是否可以解决问题。trueOracleCommand
I've had this problem so many times that I use my own factory method to create commands which automatically sets this property to truefor me.
我多次遇到这个问题，以至于我使用自己的工厂方法来创建自动true为我设置此属性的命令。
Classic useless Oracle documentation here
经典无用的 Oracle 文档在这里
回答by Zach Green
To emulate the default behavior of the System.Data.OracleClient, you should set the OracleCommand to bind by name.
要模拟 System.Data.OracleClient 的默认行为，您应该将 OracleCommand 设置为按名称绑定。
OracleCommand.BindByName = True
回答by Brian Dishaw
Try newing up your OracleParameter with a the type specified. Set the value of the object before adding it to the parameters list.
尝试使用指定的类型更新您的 OracleParameter。在将对象添加到参数列表之前设置对象的值。
var param1 = new OracleParameter( "param1", OracleType.Int32 );
param1.Value = "1234";
OracleCommand.Parameters.Add( param1 );
回答by Harv
Try this, hope it works. It does compile.
Not sure if you also have to send a commit...
I always do this sort of thing through a stored procedure, so I have a commit after the update statement in the stored procedure.
试试这个，希望它有效。它确实编译。
不确定您是否还必须发送提交...
我总是通过存储过程来做这种事情，所以我在存储过程中的更新语句之后进行了提交。
Harvey Sather
哈维·萨瑟
OracleConnection ora_conn = new OracleConnection("connection string");
OracleCommand ora_cmd = new OracleCommand("UPDATE db SET column1 = :param1 WHERE column2 = :param2", ora_conn);
ora_cmd.CommandType = CommandType.Text;
ora_cmd.BindByName = true;
ora_cmd.Parameters.Add(":param1", OracleDbType.Varchar2, "1234", ParameterDirection.Input);
ora_cmd.Parameters.Add(":param2", OracleDbType.Varchar2, "Y", ParameterDirection.Input);
ora_cmd.ExecuteNonQuery();
回答by Datajam
The first code block is correct: use a colon in front of the parameter name, but not in the first argument to OracleParameter.
第一个代码块是正确的：在参数名称前使用冒号，但不在 OracleParameter 的第一个参数中。
If no errors are thrown, it could be that the UPDATE runs successfully, it just doesn't update any records based on the WHERE clause and its substituted parameter value. Try doing it on a test table with no WHERE clause in the UPDATE to make sure it does something.
如果没有抛出错误，则可能是 UPDATE 运行成功，它只是不会根据 WHERE 子句及其替换的参数值更新任何记录。尝试在 UPDATE 中没有 WHERE 子句的测试表上执行此操作，以确保它执行某些操作。
回答by chris
Here's the type of structure I usually use (sorry, this is from memory) :
这是我通常使用的结构类型（抱歉，这是凭记忆）：
int rows = 0;
using ( OracleConnection conn = new OracleConnection(connectionString) ) {
using ( OracleCommand cmd = conn.CreateCommand() ) {
cmd.CommandText = "UPDATE table SET column1 = ':p1 WHERE column2 = :p2";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue(":p1", p1Val);
cmd.Parameters.AddWithValue(":p2", p2Val);
rows = cmd.ExecuteNonQuery();
}
}
The key difference is the use of the AddWithValue - I don`t remember why I ended up using that, but do remember having problems with some of the other ways of doing it.
主要区别在于 AddWithValue 的使用 - 我不记得我为什么最终使用它，但请记住在使用其他一些方法时遇到问题。
