//多加几个begin end. create procedure up_data
begin
begin
//要用动态sql
create table table_a as select * from user_b.table_a@link1;
exception when others then
....
end;
begin
create table table_a as select * from user_b.table_a@link2;
exception when others then
....
end;
....
create table table_a as select * from user_b.table_a@link3;
create table table_a as select * from user_b.table_a@link4;
......
end
/
begin
begin
//要用动态sql
create table table_a as select * from user_b.table_a@link1;
exception when others then
....
end;
begin
create table table_a as select * from user_b.table_a@link2;
exception when others then
....
end;
....
create table table_a as select * from user_b.table_a@link3;
create table table_a as select * from user_b.table_a@link4;
......
end
/
create procedure up_data
begin
begin
//要用动态sql
execute immediate 'create table table_a as select * from user_b.table_a@link1';
exception when others then
....
end;
begin
execute immed 'create table table_a as select * from user_b.table_a@link2';
exception when others then
....
end;
....end;
/
注意以下三点
1、在过程中不可以有直接的create table的ddl语句。
2、如果是8,需要用dbms_sql包来代替execute immediate
3、这类语句需要特别的权限,如以上语句就需要create any table的特权