/*表间数据迁移:实现一个用户到另一个用户的表数据迁移*/
create or replace function MigrateTable(fromuser in varchar2,touser in varchar2,fromKey in varchar2,
fromtable in varchar2,totable in varchar2,toKey in varchar2)
return boolean
as
isSuc boolean:=false;
v_sql varchar2(500):='';
type refcursor is ref cursor;
v_fromcursor refcursor;
v_tocursor refcursor;
v_fieldcursor refcursor;
v_fromkeycursor refcursor;
v_tokeycursor refcursor;
v_columnName varchar2(200):='';
v_count integer:=0;
v_fields varchar2(2000):='';
v_keyValue varchar2(200):='';
v_tokeyType varchar2(20):='';
v_fromkeyType varchar2(20):='';
v_fromtable varchar2(200):='';
v_totable varchar2(200):='';
v_newindex number:=0;
v_updateindex number:=0;
begin
if fromKey is null or toKey is null then
return isSuc;
end if;
v_fromtable:=fromuser||'.'||fromtable;
v_totable:=touser||'.'||totable;
/*获取关键字字段的数据类型*/
v_sql:='select distinct DATA_TYPE from all_tab_columns where owner='''||fromuser||''' AND TABLE_NAME='''||fromtable||''' AND COLUMN_NAME='''||fromKey||'''';
open v_fromcursor for v_sql;
loop
fetch v_fromcursor into v_fromkeyType;
exit when v_fromcursor%notfound;
end loop;
v_sql:='select distinct DATA_TYPE from all_tab_columns where owner='''||fromuser||''' AND TABLE_NAME='''||fromtable||''' AND COLUMN_NAME='''||tokey||'''';
open v_tocursor for v_sql;
loop
fetch v_tocursor into v_tokeyType;
exit when v_tocursor%notfound;
end loop;
/*获取同名字段,以便进行同名字段间的数据迁移,非同名字段暂时不进行数据迁移*/
v_sql:='select distinct COLUMN_NAME from all_tab_columns where owner='''||fromuser||''' AND TABLE_NAME='''||fromtable||'''';
open v_fieldcursor for v_sql;
loop
fetch v_fieldcursor into v_columnName;
exit when v_fieldcursor%notfound;
if v_columnName is not null then
v_sql:='select count(COLUMN_NAME) from all_tab_columns where owner='''||touser||''' AND TABLE_NAME='''||totable||''' AND COLUMN_NAME='''||v_columnName||'''';
EXECUTE IMMEDIATE v_sql into v_count;
if v_count>0 then
v_fields:=v_fields||v_columnName||',';
end if;
end if;
end loop;
if v_fields is not null then
v_fields:=RTrim(v_fields,',');
end if;
dbms_output.put_line('获取表迁移字段完毕!,开始进行字段数据迁移。。');
/*判断源表中的某一主键值在目的表中是否存在,如果不存在则插入记录,如果存在则更新记录*/
v_sql:='select distinct to_char('||fromKey||') from '||fromtable;
open v_fromkeycursor for v_sql;
loop
fetch v_fromkeycursor into v_keyValue;
exit when v_fromkeycursor%notfound;
if v_tokeyType <>'NUMBER' then
v_keyValue:=''''||v_keyValue||'''';
end if;
if v_keyValue is not null then
v_sql:='select count('||tokey||') from '||v_totable||' where '||tokey||'='||v_keyValue;
open v_tokeycursor for v_sql;
EXECUTE IMMEDIATE v_sql INTO v_count;
if v_count>0 then
v_sql:='update '||v_totable||' A set ('||v_fields||')=(select '||v_fields||
' from '||v_fromtable||' B WHERE B.'
||fromKey||'='||v_keyValue||') WHERE A.'||tokey||'='||v_keyvalue;
EXECUTE IMMEDIATE v_sql;
COMMIT;
v_newindex:=v_newindex+1;
dbms_output.put_line('更新第'||v_newindex||'行成功');
else
v_sql:='insert into '||v_totable||'('||v_fields||')'||' select '||v_fields||' from '||v_fromtable||' where '||tokey||'='||v_keyValue;
EXECUTE IMMEDIATE v_sql;
COMMIT;
v_updateindex:=v_updateindex+1;
dbms_output.put_line('添加第'||v_updateindex||'行成功');
end if;
end if;
end loop;
commit;
return isSuc;
end;