oracle数据库移植用户,ORACLE数据库基于用户的表移植（一）

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@]