如何在oracle脚本中使用变量作为表名 [英] how to use a variable in oracle script for the table name
问题描述
我有一个pl \ sql脚本，我想在其中将脚本中使用的表名设置为变量.因此，根据我在网上找到的一些示例，我编写了以下代码.第一部分工作正常，因此我认为我的常规语法是正确的，但是第二部分尝试在表名中使用变量会出错(" SQL错误:ORA-00903:无效的表名).
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，所以也许我只是缺少明显的东西.
Anybody know what I'm doing wrong...I don't do a lot of PL\SQL so maybe I'm just missing something obvious.
--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;
推荐答案
如果您是从sqlplus运行此脚本(情况似乎如此)，则需要使用DEFINE命令，该命令允许您创建sqlplus替换变量.只是直接的字符串替换，例如:
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.:
define tableNm = 'customers2008'
select * from &tableNm;
请参见使用Sql * Plus 有关如何使用它们的更多信息.您可以使用预定义的位置替换变量从命令行将值传递到脚本中，如下所示:
See Using Sql*Plus for 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:
define tableNm = &1
select * from &tableNm;
...，然后像这样调用sqlplus:
... and then invoke sqlplus like so:
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.
有关绑定变量和替换变量之间的区别，请参见下面的Dave Costa回答.
See Dave Costa's answer below for the differences between bind and substitution variables.
这篇关于如何在oracle脚本中使用变量作为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
