Oracle SQL:用于代替表名的变量 [英] Oracle SQL: variables used in place of table names

问题描述

我正在将MSSQL脚本转换为Oracle，但我一直无法弄清楚使用变量代替表名或表列的语法.

I am converting a MSSQL script to Oracle, and I haven't been able to figure out the syntax to use a variable in place of a table name or column.

这是我一直尝试在Oracle SQL Developer中工作的一个简单示例，因此我可以更好地理解语法:

Here is a simple example that I've been try to make work in Oracle SQL Developer so I can better understand the syntax:

 set serveroutput on format wrapped;
declare 
  VR_TABLE VARCHAR2(256);
  VR_UPDATE VARCHAR2(256);
begin
  VR_TABLE :='SYSTEM_STATUS';
  EXECUTE IMMEDIATE 'select UPDATE_VERSION INTO VR_UPDATE from ' || VR_TABLE || 'where rownum < 2 ;'
end;

其中VR_TABLE是变量表名称，该变量表名称将在循环的每次迭代中更改. 有人可以指出我做错了什么，还是可以将我链接到一个对我来说有用的网站?我已经阅读了一些有关此的教程，但是到目前为止我还没有任何运气.

Where VR_TABLE is the variable table name that will get changed each iteration of the loop. Can somebody point out what I'm doing wrong, or link me to a site that would be useful for me to read? I've read a few tutorials on this, but I haven't had any luck thus far.

推荐答案

1. 您需要在表名和随后的WHERE子句之间留一个空格
2. INTO必须是EXECUTE IMMEDIATE的一部分，而不是动态SQL语句的一部分.
3. 动态SQL语句不应包含尾部分号
4. EXECUTE IMMEDIATE语句应以分号结尾

1. You need to have a space between the table name and the subsequent WHERE clause
2. The INTO needs to be part of the EXECUTE IMMEDIATE, not part of the dynamic SQL statement.
3. The dynamic SQL statement should not have a trailing semicolon
4. The EXECUTE IMMEDIATE statement should end with a semicolon

将它们放在一起，类似的东西应该起作用

Putting those together, something like this should work

declare 
  VR_TABLE VARCHAR2(256);
  VR_UPDATE VARCHAR2(256);
begin
  VR_TABLE :='SYSTEM_STATUS';
  EXECUTE IMMEDIATE 'select UPDATE_VERSION from ' || VR_TABLE || ' where rownum < 2'
               INTO VR_UPDATE;
end;

当然，由于您没有对VR_UPDATE进行任何操作，因此在执行此匿名块时将不会显示任何内容.

Of course, since you're not doing anything with VR_UPDATE, nothing will be displayed when this anonymous block is executed.

这篇关于Oracle SQL:用于代替表名的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！