create procedure pro
as
str varchar2(100);
begin
str:='GLOBAL TEMPORARY TABLE myTemp ON COMMIT PRESERVE ROWS as select * from yourtable where 1=2';
Execute immediate str;--要有此权限,grant create any table to 当前用户
end;
/
例子,过程里建临时表的,并用游标返回临时表中数据:
create or replace procedure ttable(i_cursor out ysq.aboutreport.t_cursor) is
strCreateTable varchar2(500);
vCount int;
begin
select count(*) into vCount from user_all_tables where lower(TABLE_NAME) = 'mytemp';
if vCount=0 then
--EXECUTE IMMEDIATE ' drop table myTemp';
strCreateTable:='CREATE GLOBAL TEMPORARY TABLE myTemp
(id VARCHAR(10),name varchar2(50))
ON COMMIT PRESERVE ROWS ';
Execute immediate strCreateTable;--建表
end if;
insert into mytemp values('1','sandy');
insert into mytemp values('2','may');
open i_cursor for select * from mytemp;
commit;
end ttable;