oracle 使用绑定变量动态传递表名和列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9723931/
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
passing table and column name dynamically using bind variables
提问by Gaurav Soni
Is there a way to pass column and table names dynamically to a query using bind variables? This could be done by using a simple concatenation operator ||, but I would like a different approach by which this can be achieved.
有没有办法使用绑定变量将列名和表名动态传递给查询？这可以通过使用简单的连接运算符来完成||，但我想要一种不同的方法来实现这一点。
EDIT
编辑
OPEN abc_cur FOR 'Select :column_name
from :table_name'
USING column_name,table_name;
In this example I am passing column_nameas empno,enameand table_nameas emp
在这个例子中，我column_name作为empno,ename和table_name作为emp
But this approach is not working for me. Is it possible to have a different approach other that the traditional approach of concatenation?
但是这种方法对我不起作用。除了传统的串联方法之外，是否有可能采用不同的方法？
回答by Justin Cave
Table and column names cannot be passed as bind variables, no. The whole point of bind variables is that Oracle can generate a query plan once for the statement and then execute it many times with different bind variable values. If the optimizer doesn't know what table is being accessed or what columns are being selected and filtered on, it can't generate a query plan.
表名和列名不能作为绑定变量传递，不能。绑定变量的全部意义在于，Oracle 可以为语句生成一次查询计划，然后使用不同的绑定变量值多次执行它。如果优化器不知道正在访问什么表或正在选择和过滤哪些列，则它无法生成查询计划。
If your concern relates to SQL injection attacks, and assuming that dynamic SQL is actually necessary (most of the time, the need to resort to dynamic SQL implies problems with the data model), you can use the DBMS_ASSERTpackageto validate that the table names and column names don't contain embedded SQL.
如果您关心的是 SQL 注入攻击，并假设动态 SQL 确实是必要的（大多数情况下，需要求助于动态 SQL 意味着数据模型存在问题），您可以使用该DBMS_ASSERT包来验证表名和列名不包含嵌入式 SQL。
回答by Tony Andrews
No you cannot. Changing the table or column names in a query changes the semanticsof that query - i.e. it becomes a differentquery.
你不能。更改查询中的表或列名称会更改该查询的语义- 即它变成了不同的查询。
Bind variables are all about passing different values to the samequery. The optimiser can reuse the query with different values without having to re-parse it and optimise it.
绑定变量都是关于将不同的值传递给同一个查询。优化器可以重用具有不同值的查询，而无需重新解析和优化它。
