java oracle 临时表,如何使用Oracle全局临时表？

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?

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;

解决方案

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

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;