oracle 如何使用Oracle全局临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20706724/
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 Oracle global temporary table?
提问by Deep in Development
I am attempting to use an Oracle global temporary table without physically creating a table in the database. The following code is not working. Can someone please explain the proper way to use global temporary tables?
我正在尝试使用 Oracle 全局临时表，而无需在数据库中物理创建表。以下代码不起作用。有人可以解释一下使用全局临时表的正确方法吗？
declare
global temporary table my_temp_table(column1 number) on commit preserve rows;
begin
insert into my_temp_table (column1) values (1);
select * from my_temp_table;
end;
采纳答案by psaraj12
Try the below using execute immediate: it uses exception handler to bypass if table already exists; also note that you cannot use SQL select inside PLSQL
尝试使用立即执行以下操作：如果表已存在，它使用异常处理程序绕过；另请注意，您不能在 PLSQL 中使用 SQL select
DECLARE
l_column1 number;
begin
begin
execute immediate 'create global temporary table my_temp_table(column1 number)
on commit preserve rows';
exception when others
then
dbms_output.put_line(sqlerrm);
end;
insert into my_temp_table (column1) values (1);
select * into l_column1 from my_temp_table where column1=1;
dbms_output.put_line('the temp value is '||l_column1);
end;
回答by Wernfried Domscheit
Unless you use EXECUTE IMMEDIATE you cannot create the table inside PL/SQL. Try this:
除非您使用 EXECUTE IMMEDIATE，否则您无法在 PL/SQL 中创建表。尝试这个：
create global temporary table my_temp_table(column1 number) on commit preserve rows;
insert into my_temp_table (column1) values (1);
select * from my_temp_table;
回答by Brian McGinity
Oracle global temp tables are a bit different than you might be expecting.
Oracle 全局临时表与您的预期略有不同。
You need to create the table and declare it as a global temp table.
您需要创建该表并将其声明为全局临时表。
Here is a good resource: http://www.oracle-base.com/articles/misc/temporary-tables.php
这是一个很好的资源：http: //www.oracle-base.com/articles/misc/temporary-tables.php
