create table xxx as select ...应该没有问题的,不知你块的语句怎么写的?
你的观点是mssqlserver 那种作法应用动态游标不能这样作。 CREATE OR REPLACE PROCEDURE "HR"."BAK_TABLE" as cursor my_table is select table_name from user_tables ; my_str varchar2(500); table_bak varchar(30) ; i_my_table my_table%rowtype ; i number ; begin
for i_my_table in my_table loop my_str := ''; table_bak :=i_my_table.table_name ; my_str :='create table '||table_bak ||'bak'||' as select * from '||table_bak ; execute immediate my_str ; end loop ;
然后执行select就可以了阿
CREATE OR REPLACE PROCEDURE "HR"."BAK_TABLE" as
cursor my_table is select table_name from user_tables ;
my_str varchar2(500);
table_bak varchar(30) ;
i_my_table my_table%rowtype ;
i number ;
begin
for i_my_table in my_table loop
my_str := '';
table_bak :=i_my_table.table_name ;
my_str :='create table '||table_bak ||'bak'||' as select * from '||table_bak ;
execute immediate my_str ;
end loop ;
end ;