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.
推荐答案
- 您需要在表名和随后的
WHERE
子句之间留一个空格 -
INTO
必须是EXECUTE IMMEDIATE
的一部分,而不是动态SQL语句的一部分. - 动态SQL语句不应包含尾部分号
-
EXECUTE IMMEDIATE
语句应以分号结尾
- You need to have a space between the table name and the subsequent
WHERE
clause - The
INTO
needs to be part of theEXECUTE IMMEDIATE
, not part of the dynamic SQL statement. - The dynamic SQL statement should not have a trailing semicolon
- 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屋!