用动态sql
...
str :=' CREATE GLOBAL TEMPORARY TABLE TABLENAME ('||
COLname1 ||' VARCHAR2(10),'||
COLname2 ||' NUMBER
) ON COMMIT PRESERVE ROWS ';
execute immediate str;
...
...
str :=' CREATE GLOBAL TEMPORARY TABLE TABLENAME ('||
COLname1 ||' VARCHAR2(10),'||
COLname2 ||' NUMBER
) ON COMMIT PRESERVE ROWS ';
execute immediate str;
...
仅复制表结构,但不复制数据:
create table table1 as (select * from table2 where 1=0);
如:
create procedure pro
as
str varchar2(50);
begin
str:='CREATE GLOBAL TEMPORARY TABLE TABLENAME ON COMMIT PRESERVE ROWS as select * from table_a where 1=2';--此处没有条件,但临时依旧没有记录
execute immediate str;
insert into TABLENAME select * from table_a;
commit;
end;
/