我的代码如下:
declare
tablenames varchar2(50);
var_sql varchar2(200);
Cursor tablename_cursor is
select t.table_name from user_tab_comments t where t.table_name like'SHUANG%';
begin
open tablename_cursor;
loop
fetch tablename_cursor into tablenames;
exit when tablename_cursor%notfound;
var_sql:='create talbe test3.'||tablenames||' as select starttime from test.'||tablenames;
dbms_output.put_line(var_sql);
execute immediate var_sql;
end loop;
close tablename_cursor;
end;
结果输出为:CREATE TABLE TEST3.SHUANG1 AS SELECT STARTTIME FROM TEST.SHUANG1
请问如何才能在TEST3中建立新表?
declare
tablenames varchar2(50);
var_sql varchar2(200);
Cursor tablename_cursor is
select t.table_name from user_tab_comments t where t.table_name like'SHUANG%';
begin
open tablename_cursor;
loop
fetch tablename_cursor into tablenames;
exit when tablename_cursor%notfound;
var_sql:='create talbe test3.'||tablenames||' as select starttime from test.'||tablenames;
dbms_output.put_line(var_sql);
execute immediate var_sql;
end loop;
close tablename_cursor;
end;
结果输出为:CREATE TABLE TEST3.SHUANG1 AS SELECT STARTTIME FROM TEST.SHUANG1
请问如何才能在TEST3中建立新表?
tablenames varchar2(50);
var_sql varchar2(200);
Cursor tablename_cursor is
select t.table_name from user_tab_comments t where t.table_name like'SHUANG%';
begin
open tablename_cursor;
loop
fetch tablename_cursor into tablenames;
exit when tablename_cursor%notfound;
var_sql:=var_sql||'create talbe test3.'||tablenames||' as select starttime from test.'||tablenames;--这里
dbms_output.put_line(var_sql);
execute immediate var_sql;
end loop;
close tablename_cursor;
end;
begin
select wmsys.wm_concat(table_name) into v_sql from
(select 'create talbe test3.'||table_name||' as select starttime from test.'||table_name as table_name from user_tab_comments where table_name like'SHUANG%');
dbms_output.put_line(v_sql);
end;试试,10g以上