SQL 如何在oracle脚本中使用变量作为表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1179652/
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
how to use a variable in oracle script for the table name
提问by Gern Blanston
I have an pl\sql script where I want to set the table name used in the script to a variable. So, from some examples I found on the web, I wrote the code below. The first section works, so I think my general syntax is correct,but the second section, where I attempt to use a variable for a table name it errors ("SQL Error: ORA-00903: invalid table name").
我有一个 pl\sql 脚本，我想将脚本中使用的表名设置为变量。所以，根据我在网上找到的一些例子，我写了下面的代码。第一部分有效，所以我认为我的一般语法是正确的，但第二部分，我尝试将变量用于表名，但它出错（“ SQL 错误：ORA-00903：无效表名”）。
Anybody know what I'm doing wrong...I don't do a lot of PL\SQL so maybe I'm just missing something obvious.
任何人都知道我做错了什么......我没有做很多 PL\SQL 所以也许我只是错过了一些明显的东西。
--works
variable numOfrecords number;
exec :numOfrecords := 10;
select * from customers2008 where rownum < :numOfrecords;
--does not work
variable tableNm CHAR;
exec :tableNm := 'customers2008';
print tableNm;
select * from :tableNm;
回答by Steve Broberg
If you are running this script from sqlplus (which looks to be the case), you want to use the DEFINE command, which allows you to create sqlplus substition variables that are just straight string substitution, e.g.:
如果你从 sqlplus 运行这个脚本（看起来是这样），你想使用 DEFINE 命令，它允许你创建 sqlplus 替换变量，这些变量只是直接的字符串替换，例如：
define tableNm = 'customers2008'
select * from &tableNm;
See Using Sql*Plusfor more information on how these are used. You can pass values into your script from the command line using the predefined positional substition variables, like this:
有关如何使用这些的更多信息，请参阅使用 Sql*Plus。您可以使用预定义的位置替换变量从命令行将值传递到脚本中，如下所示：
define tableNm = &1
select * from &tableNm;
... and then invoke sqlplus like so:
...然后像这样调用sqlplus：
sqlplus user/pwd@server @myscript.sql customers2008
If you don't pass in a value on the command line, the script invoker will be prompted for the value.
如果您没有在命令行上传递值，则会提示脚本调用者输入该值。
See Dave Costa's answer below for the differences between bind and substitution variables.
有关绑定变量和替换变量之间的差异，请参阅下面的 Dave Costa 的回答。
回答by Dave Costa
To try to add some explanation:
尝试添加一些解释：
The method that you were trying to use is called a bind variable. A bind variable is identified in Oracle SQL by a colon followed by an identifier. The purpose of a bind variable is that its value does not need to be known when parsing the SQL statement; the statement can be parsed once and then executed multiple times with different values bound to the variable.
您尝试使用的方法称为绑定变量。在 Oracle SQL 中，绑定变量通过冒号后跟标识符来标识。绑定变量的目的是在解析 SQL 语句时不需要知道它的值；该语句可以解析一次，然后使用绑定到变量的不同值多次执行。
In order for a SQL statement to be parsed, the table and column names involved must be known. So the table name can't be represented by a bind variable, because the value would not be known at parse time.
为了解析 SQL 语句，必须知道所涉及的表名和列名。所以表名不能用绑定变量表示，因为在解析时不知道该值。
If you are simply executing SQL and inline PL/SQl via SQLPlus, then substitution variables are an easy way to deal with this issue, as Steve explained. A substitution variable is replaced with its value when the SQLPlus client reads the command, before it even sends it to Oracle for parsing.
如果您只是通过 SQLPlus 执行 SQL 和内联 PL/SQL，那么正如 Steve 解释的那样，替换变量是处理此问题的简单方法。当 SQLPlus 客户端读取命令时，替换变量被替换为其值，甚至在将命令发送到 Oracle 进行解析之前。
回答by Arkady
You have to do something like this:
你必须做这样的事情：
EXECUTE IMMEDIATE 'select * from' || tableNm;
This is because Oracle does not allow bind variables for table (or any other object names).
这是因为 Oracle 不允许表（或任何其他对象名称）的绑定变量。
There are significant security implications with the EXECUTE IMMEDIATE approach: when the tableNm value is user-supplied, you are wide open to SQL injectionattacks.
EXECUTE IMMEDIATE 方法存在重大的安全隐患：当 tableNm 值是用户提供的时，您很容易受到SQL 注入攻击。
回答by Eddie Awad
替换变量工作：
SQL> select * from &table_name;
Enter value for table_name: dual
old 1: select * from &table_name
new 1: select * from dual
D
-
X
