如何在Oracle脚本中使用变量作为表名

Question

我有一个PL/SQL脚本，想要将其中使用的表名设置为变量。因此，我按照网上的一些示例编写了下面的代码。第一部分是可行的，所以我认为我的语法是正确的，但是第二部分，在尝试使用表名变量时会出现错误（"SQL Error: ORA-00903: invalid table name"）。

有人知道我做错了什么吗？我不经常使用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;

- Gern Blanston

---

仅供参考：根据您的示例，您并没有真正使用PL/SQL。我认为您的意思是您有一个SQLPlus脚本。从技术上讲，您的exec命令正在调用PL/SQL，但总体来说，您在这里展示的是SQLPlus命令。 - Dave Costa

从技术上讲，他同时使用了脚本中的变量命令来声明一个PL/SQL绑定变量。 - Steve Broberg

Answer 1

如果您是从sqlplus运行此脚本（似乎是这种情况），则要使用DEFINE命令，该命令允许您创建sqlplus替换变量，这些变量仅是直接字符串替换，例如：

define tableNm = 'customers2008'
select * from &tableNm;

请参考 使用 Sql*Plus 获取有关如何使用这些内容的更多信息。您可以通过预定义的位置替换变量从命令行传递值到脚本中，方法如下：

define tableNm = &1
select * from &tableNm;

然后像这样调用sqlplus：

sqlplus user/pwd@server @myscript.sql customers2008

如果您在命令行中没有传递值，脚本调用者将提示输入该值。

有关绑定变量和替换变量之间差异的详细信息，请参见下面的Dave Costa的答案。

- Steve Broberg

---

唯一的小问题是，您的SQLPlus调用示例无法正常工作（至少对我来说是这样）。为了在命令行上传递值，您必须调用一个脚本，例如 sqlplus user/pwd@server @myscript customers2008。 - Dave Costa

Answer 2

尝试添加一些解释：

你尝试使用的方法称为“绑定变量”。在Oracle SQL中，绑定变量由冒号后跟一个标识符来识别。绑定变量的目的是其值不需要在解析SQL语句时知道；语句可以被解析一次，然后执行多次，并将不同的值绑定到变量上。

为了解析SQL语句，必须知道涉及的表和列名。因此，表名不能用绑定变量表示，因为在解析时该值是未知的。

如果您只是通过SQLPlus执行SQL和内联PL/SQl，则替换变量是处理此问题的一种简单方法，如Steve所解释的那样。替换变量在SQLPlus客户端读取命令时被其值替换，甚至在将其发送到Oracle进行解析之前。

- Dave Costa

Answer 3

你需要做类似这样的事情：

EXECUTE IMMEDIATE 'select * from ' || tableNm;

这是因为Oracle不允许绑定变量用于表格（或任何其他对象名称）。

使用EXECUTE IMMEDIATE方法存在重大的安全隐患：当tableNm值由用户提供时，您将面临SQL注入攻击的风险。

- Arkady

Answer 4

替换变量是如何工作的：

SQL> select * from &table_name;
Enter value for table_name: dual
old   1: select * from &table_name
new   1: select * from dual

D
-
X

- Eddie Awad

---

哈哈！英雄所见略同——甚至文档链接都是一模一样的。 - Steve Broberg

确实！链接在锚点之前是相同的 :) - Eddie Awad

Answer 5

如果您正在使用SQL Developer，则：

VARIABLE tableNm CHAR;
VARIABLE cur     REFCURSOR;
EXEC :tableNm := 'customers2008';

DECLARE
  v_sql VARCHAR2(200) := 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(:tableNm);
BEGIN
  OPEN :cur FOR v_sql;
END;
/

PRINT :cur

- MT0