本人对代码不太懂。特来请教下面的代码是什么意思。自己只看懂了：把库里所有WANGWANG开头的表的列取出，插到HEHE用户里。是这样吗？
PROCEDURE test(tablenames in varray_tables)
as
userid varchar2(20);
cursor userinfos is select username from dba_users where username like 'WANGWANG%';
strsql varchar2(32767);
idcolumn varchar2(30);
idsql varchar2(500);
tablename varchar2(100);
columnnames varchar2(32767);
querycolumnnamessql varchar2(32767);
cursor colnamescursor(un varchar2,tabname varchar2) is select COLUMN_NAME from dba_tab_columns where owner=un and table_name=tabname;
v_file_handle utl_file.file_type;
begin
v_file_handle:=utl_file.fopen('PLOG_DIR','hehelog.log','w',32767);
utl_file.put_line(v_file_handle,'begin merge server=========');
utl_file.fflush(v_file_handle);
For user in userinfos loop
begin
for x in 1..tablenames.count loop
begin
tablename :=tablenames(x);
userid := SUBSTR(user.username, INSTR(user.username, 'WANGWANG', 1,1)+length('WANGWANG'),length(user.username)-length('WANGWANG'));
idsql:=q''||tablename ||q''||user.username||q'';
dbms_output.put_line(idsql||'====== '||idcolumn);
utl_file.put_line(v_file_handle,idsql||'====== '||idcolumn);
utl_file.fflush(v_file_handle);
execute immediate idsql into idcolumn;
columnnames := '';
for colscur in colnamescursor(user.username,UPPER(tablename)) loop
columnnames := columnnames || colscur.COLUMN_NAME || ',';
end loop;
columnnames := substr(columnnames,1,length(columnnames)-1);
strsql := 'insert into hehe.'||tablename||'('||columnnames||')select'|| columnnames || ' from '||user.username||'.'||tablename||q''||tablename||q''||idcolumn||q''||userid||q''||idcolumn||q''||idcolumn||q'';
execute immediate strsql;
dbms_output.put_line(strsql||'======update '||SQL%ROWCOUNT ||' rows '|| columnnames);
utl_file.put_line(v_file_handle,tablename||'======update '||SQL%ROWCOUNT ||' rows' || columnnames);
utl_file.fflush(v_file_handle);
commit;
columnnames := '';
for colscur in colnamescursor(user.username,UPPER(tablename)||'_SEC') loop
columnnames := columnnames || colscur.COLUMN_NAME || ',';
end loop;
columnnames := substr(columnnames,1,length(columnnames)-1);
strsql := 'insert into hehe.'||tablename||'_SEC ('||columnnames||')
select'|| columnnames || ' from '||user.username||'.'||tablename||q''||tablename||q''||idcolumn||q''||userid||q''||idcolumn||q''||idcolumn||q'';
execute immediate strsql;
dbms_output.put_line(strsql||'======update '||SQL%ROWCOUNT ||' rows ' || columnnames);
utl_file.put_line(v_file_handle,tablename||'_SEC======update '||SQL%ROWCOUNT ||' rows ' || columnnames);
utl_file.fflush(v_file_handle);
commit;
EXCEPTION
WHEN OTHERS THEN
rollback;
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||strsql||idsql||'============='||SQLERRM);
utl_file.put_line(v_file_handle,SQLCODE||'---'||tablename||idsql||'============='||SQLERRM);
utl_file.fflush(v_file_handle);
end;
END LOOP;
end;
End loop;
DBMS_OUTPUT.PUT_LINE('end merge server=========');
utl_file.put_line(v_file_handle,'end merge server=========');
utl_file.fflush(v_file_handle);
utl_file.fclose(v_file_handle);
EXCEPTION
WHEN OTHERS THEN
rollback;
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---============='||SQLERRM);
utl_file.put_line(v_file_handle,SQLCODE||'---============='||SQLERRM);
utl_file.fclose(v_file_handle);
end;
|
