是否可以通过Oracle中的绑定变量引用列名？ [英] Is it possible to refer to column names via bind variables in Oracle?
问题描述
我正在尝试引用列名，以便在与Oracle数据库通信的应用程序中订购查询。我想使用一个绑定变量，以便我可以动态地更改什么来排序查询。
我遇到的问题是数据库似乎忽略了列的顺序。有没有人知道有没有通过绑定变量引用数据库列的方法，或者甚至有可能吗？
例如我的查询是
SELECT * FROM PERSON ORDER BY： 1
（其中：1 将被绑定到 PERSON.NAME ）
查询不按字母顺序返回结果，我担心数据库会将其解释为： -
SELECT * FROM PERSON ORDER BY'PERSON.NAME'
这显然不起作用。
非常感谢任何建议。
不。您不能对表或列名使用绑定变量。
创建执行计划需要此信息。例如，不知道你要排序什么，就不可能找出要使用的索引。
代替绑定变量，你必须直接内插列名称在SQL语句中，当您的程序创建它。假设你采取预防SQL注入，那没有什么不利的。
更新：如果你真的想跳过篮球，你可能会执行类似
order by decode（？'colA'，colA，'colB'，colB）
但这只是愚蠢。而且慢不要。
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.
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 :1 will be bound to PERSON.NAME)
The query is not returning results in alphabetical order, I am worried that the database is interpreting this as:-
SELECT * FROM PERSON ORDER BY 'PERSON.NAME'
which will obviously not work.
Any suggestions are much appreciated.
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.
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.
这篇关于是否可以通过Oracle中的绑定变量引用列名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
