1，函数

create or replace function trans_user_object(v_fromuser varchar2,
v_touser varchar2)
/*数据，表，函数的移植
移植规则
方向：将对象从 v_fromuser 复制移动到 v_touser
细则：v_fromuser与v_touser共有的对象 以v_fromuser为准
v_fromuser 中没有的对象以 v_touser为准

*/

return number authid current_user is
/*1 处理 v_fromuser 中 有而 v_touser 中没有的表的游标*/
cursor tabcur1 is
select object_name
from all_objects a
where a.owner = upper(v_fromuser)
AND A.object_type = 'TABLE'
and not EXISTS (select object_name
from all_objects b
where b.owner = upper(v_touser)
and b.object_type = 'TABLE'
AND A.OBJECT_NAME = B.OBJECT_NAME);
/*2 处理 v_fromuser 与 v_touser 都有的表的游标*/
cursor tabcur2 is
select object_name
from all_objects a
where a.owner = upper(v_fromuser)
AND A.object_type = 'TABLE'
and EXISTS (select object_name
from all_objects b
where b.owner = upper(v_touser)
and b.object_type = 'TABLE'
AND A.OBJECT_NAME = B.OBJECT_NAME);
tabsql varchar2(1000);
prosql varchar2(1000);
renum number(10) := 0;
tabname1 tabcur1%rowtype;
tabname2 tabcur2%rowtype;
begin
/* if not tabcur1%isopen then
open tabcur1;
dbms_output.put_line(' open tabcur1;');

end if;*/
open tabcur1;
loop
fetch tabcur1
into tabname1;
exit when tabcur1%notfound;
renum := tabcur1%rowcount;
tabsql := 'create table ' || v_touser || '.' || tabname1.object_name ||
' as select * from ' || v_fromuser || '.' ||
tabname1.object_name;
dbms_output.put_line(tabsql);
execute immediate tabsql;
dbms_output.put_line(' execute immediate' || tabsql);
end loop;
/* if tabcur1%found then
for tabname1 in tabcur1 loop
renum := renum + 1;

tabsql := 'create table ' || v_touser || '.' || tabname1.object_name ||
' as select * from ' || v_fromuser || '.' ||
tabname1.object_name;
dbms_output.put_line(tabsql);
execute immediate tabsql;
dbms_output.put_line(' execute immediate' || tabsql);
end loop;
close tabcur1;
end if;*/
if tabcur1%isopen then
close tabcur1;
end if;
dbms_output.put_line(' close tabcur1');
/* begin transaction;*/
savepoint a1;
for tabname2 in tabcur2 loop
renum := renum + 1;
tabsql := 'truncate table ' || v_touser || '.' || tabname2.object_name;
dbms_output.put_line(' execute immediate' || tabsql);
execute immediate tabsql;
tabsql := 'insert into ' || v_touser || '.' || tabname2.object_name ||
' select * from ' || v_fromuser || '.' ||
tabname2.object_name;
dbms_output.put_line(' execute immediate' || tabsql);
execute immediate tabsql;
end loop;
if tabcur2%isopen then
close tabcur2;
end if;
return renum;
commit;

exception
when others then
renum := sqlcode;
dbms_output.put_line(sqlerrm);
return renum;
rollback to a1;

end;

2，要注意的地方

a,创建表时，权限不足

IIS 前面加authid current_user

b,取表时不要从all_object_table中取而是all_objects

c,for循环完毕默认是已关闭游标，此时再关，会报错，但是加多条件也未尝不可。

[@more@]