是否可以通过 Oracle 中的绑定变量引用列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/610056/
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
Is it possible to refer to column names via bind variables in Oracle?
提问by Scottm
I am trying to refer to a column name to order a query in an application communicating with an Oracle database. I want to use a bind variable so that I can dynamically change what to order the query by.
我试图在与 Oracle 数据库通信的应用程序中引用列名来订购查询。我想使用绑定变量，以便我可以动态更改查询的顺序。
The problem that I am having is that the database seems to be ignoring the order by column.
我遇到的问题是数据库似乎忽略了按列排序。
Does anyone know if there is a particular way to refer to a database column via a bind variable or if it is even possible?
有谁知道是否有一种特定的方法可以通过绑定变量来引用数据库列，或者是否有可能？
e.g my query is
例如我的查询是
SELECT * FROM PERSON ORDER BY :1
(where :1will be bound to PERSON.NAME)
The query is not returning results in alphabetical order, I am worried that the database is interpreting this as:-
(where :1will be bound to PERSON.NAME) 查询未按字母顺序返回结果，我担心数据库将其解释为：-
SELECT * FROM PERSON ORDER BY 'PERSON.NAME'
which will obviously not work.
这显然行不通。
Any suggestions are much appreciated.
任何建议都非常感谢。
回答by Thilo
No. You cannot use bind variables for table or column names.
不可以。您不能对表名或列名使用绑定变量。
This information is needed to create the execution plan. Without knowing what you want to order by, it would be impossible to figure out what index to use, for example.
创建执行计划需要此信息。例如，如果不知道您想按什么排序，就不可能弄清楚要使用什么索引。
Instead of bind variables, you have to directly interpolate the column name into the SQL statement when your program creates it. Assuming that you take precautions against SQL injection, there is no downside to that.
您必须在程序创建 SQL 语句时直接将列名插入到 SQL 语句中，而不是绑定变量。假设您对 SQL 注入采取了预防措施，那没有任何不利之处。
Update:If you really wanted to jump through hoops, you could probably do something like
更新：如果你真的想跳过箍，你可能会做类似的事情
order by decode(?, 'colA', colA, 'colB', colB)
but that is just silly. And slow. Don't.
但这太愚蠢了。而且很慢。别。
回答by Edwin
As you are using JDBC. You can rewrite your code, to something without bind variables. This way you can also dynamically change the order-by e.g.:
当您使用 JDBC 时。您可以将代码重写为没有绑定变量的内容。通过这种方式，您还可以动态更改顺序，例如：
String query = "SELECT * FROM PERS ";
if (condition1){
query = query+ " order by name ";
// insert more if/else or case statements
} else {
query = query+ " order by other_column ";
}
Statement select = conn.createStatement();
ResultSet result = select.executeQuery(query);
Or even:
甚至：
String columnName = getColumnName(input);
Statement select = conn.createStatement();
ResultSet result = select.executeQuery("SELECT * FROM PERS ORDER BY "+columnName);
回答by jrEving
ResultSet result = select.executeQuery(
"SELECT * FROM PERS ORDER BY " + columnName
);
will always be a newstatement to the database.
That means it is, like Thilo already explained, impossible to "reorder" an already bound, calculated, prepared, parsed statement. When using this result set over and over in your application and the only thing, which changes over time is the order of the presentation, try to order the set in your client code.
Otherwise, dynamic SQL is fine, but comes with a huge footprint.
将始终是对数据库的新语句。
这意味着，就像 Thilo 已经解释过的那样，不可能“重新排序”一个已经绑定、计算、准备、解析的语句。当在您的应用程序中一遍又一遍地使用此结果集时，唯一会随时间变化的是演示文稿的顺序，请尝试在您的客户端代码中对结果集进行排序。
否则，动态 SQL 很好，但会占用大量资源。
