以表名称为参数的动态SQL [英] Dynamic SQL with table name as a parameter

问题描述

我正在尝试执行一个程序，将表名和2个列名作为参数发送到其中:

I am trying to execute a procedure into which i send the table name and 2 column names as parameters:

EXECUTE IMMEDIATE 'select avg(@column1) from @Table1 where REF_D = @column2' into ATTR_AVG;

我尝试将变量组合使用'@'，':'，'||'，但似乎无济于事.

I have tried using the variables in combiations of '@', ':', '||' but nothing seems to work.

任何人都使用表名作为参数.除了SQL Server以外，这里还有一些解决方案

Has anyone used table names as a parameter. there are a few solutions here but for SQL Server

推荐答案

您只能将绑定变量(用冒号表示)用于值，而不能用于结构的某些部分.您必须将表名和列名连接到查询中:

You can only use bind variables (denoted by colons) for values, not for parts of the structure. You will have to concatenate the table and column names into the query:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

这暗示REF_D是一个固定的列名，它可以出现在您将为此调用的任何表中；在先前的问题中，这似乎是一个变量.如果它实际上是一个字符串变量，那么您需要绑定并设置它:

Which implies REF_D is a fixed column name that can appear in any table you'll call this for; in a previous question that seems to be a variable. If it is actually a string variable then you'd need to bind and set that:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

如果应该将日期设置为日期，则应确保本地变量是正确的类型，或显式转换它.

If it's supposed to be a date you should make sure the local variable is the right type, or explicitly convert it.

这篇关于以表名称为参数的动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！