使用绑定变量动态传递表名和列名 [英] passing table and column name dynamically using bind variables

问题描述

是否存在使用绑定变量将列名和表名动态传递给查询的方法?可以使用简单的串联运算符||来完成此操作，但是我希望使用另一种方法可以实现此目的.

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.

编辑

OPEN abc_cur FOR 'Select :column_name
                  from :table_name' 
                USING column_name,table_name;

在此示例中，我将column_name传递为empno,ename，将table_name传递为emp

In this example I am passing column_name as empno,ename and table_name as emp

但是这种方法对我不起作用.除了传统的串联方法之外，是否可能有其他方法?

But this approach is not working for me. Is it possible to have a different approach other that the traditional approach of concatenation?

推荐答案

表和列的名称不能作为绑定变量传递.绑定变量的全部意义在于，Oracle可以为该语句生成一次查询计划，然后使用不同的绑定变量值多次执行该查询计划.如果优化器不知道正在访问哪个表或正在选择和过滤哪些列，则它无法生成查询计划.

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.

如果您担心与SQL注入攻击有关，并且假设实际上需要动态SQL(大多数情况下，诉诸动态SQL意味着数据模型存在问题)，则可以使用

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_ASSERT package to validate that the table names and column names don't contain embedded SQL.

这篇关于使用绑定变量动态传递表名和列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！