本文中，我们将介绍一个可以能自动创建过程的脚本，大家直接输入源泉表名和目标表名就可以创建以下链接中的全表复制过程。
create or replace procedure hyf_create_sql
(p_sour_tab varchar2,p_dst_tab varchar2) as
v_dstfile utl_file.file_type;
v_outname varchar2(50);
v_buffer varchar2(500);
type t_cur is ref cursor;
v_cur t_cur;
v_col_num number;
begin
select count(column_name) into v_col_num
from user_tab_columns
where table_name = upper(p_dst_tab);
v_outname := p_sour_tab || '.sql';
v_dstfile := utl_file.fopen('DIR1', v_outname, 'w', 32767);
v_buffer := 'create or replace procedure cp_' || p_sour_tab || ' as';
utl_file.put_line(v_dstfile, v_buffer);
open v_cur for
select 'type TYPE_' || column_name || ' is table of '
|| table_name || '.' ||column_name || '%type;' as dd
from user_tab_columns
where table_name = upper(p_dst_tab)
order by column_id asc;
for i in 1..v_col_num loop
fetch v_cur into v_buffer;
utl_file.put_line (v_dstfile,v_buffer);
end loop;
close v_cur ;
open v_cur for
select 'V_' || column_name || ' TYPE_' || column_name ||';'
from user_tab_columns
where table_name = upper(p_dst_tab)
order by column_id asc;
for i in 1..v_col_num loop
fetch v_cur into v_buffer;
utl_file.put_line (v_dstfile,v_buffer);
end loop;
close v_cur ;
utl_file.put_line (v_dstfile,'type t_cur is ref cursor;');
utl_file.put_line (v_dstfile,'c_table t_cur;');
utl_file.put_line (v_dstfile,'v_sql varchar2(500);');
utl_file.put_line (v_dstfile,'v_rows number := 5000;');
utl_file.put_line(v_dstfile, 'begin');
utl_file.put_line(v_dstfile, 'execute immediate ''truncate table '||p_dst_tab||''';');
utl_file.put_line(v_dstfile, 'open c_table for');
utl_file.put_line(v_dstfile, ' select * from '||p_sour_tab||';');
v_buffer:= 'v_sql := ''insert /*+ APPEND*/ into '||p_dst_tab||' (';
utl_file.put_line (v_dstfile,v_buffer);
open v_cur for
select column_name
from user_tab_columns
where table_name = upper(p_dst_tab)
order by column_id asc;
for i in 1..v_col_num loop
fetch v_cur into v_buffer;
if i<> v_col_num then
v_buffer:=v_buffer||',';
else
v_buffer:=v_buffer||')' ;
end if;
utl_file.put_line (v_dstfile,v_buffer);
end loop;
close v_cur ;
v_buffer:= 'values (' ;
for i in 1..v_col_num loop
if i<> v_col_num then
v_buffer:=v_buffer||':'||i||',';
else
v_buffer:=v_buffer||':'||i||')'';';
end if;
end loop;
utl_file.put_line (v_dstfile,v_buffer);
utl_file.put_line (v_dstfile,'loop ');
utl_file.put_line (v_dstfile,' fetch c_table ');
utl_file.put_line (v_dstfile, ' bulk collect into');
open v_cur for
select 'v_'||column_name
from user_tab_columns
where table_name = upper(p_dst_tab)
order by column_id asc;
for i in 1..v_col_num loop
fetch v_cur into v_buffer;
if i<> v_col_num then
v_buffer:=v_buffer||',';
end if;
utl_file.put_line (v_dstfile,v_buffer);
end loop;
close v_cur ;
utl_file.put_line (v_dstfile, ' limit v_rows;');
v_buffer:='forall i in 1 .. '||v_buffer||'.count execute immediate v_sql using';
utl_file.put_line (v_dstfile,v_buffer);
open v_cur for
select 'v_'||column_name||'(i)'
from user_tab_columns
where table_name = upper(p_dst_tab)
order by column_id asc;
for i in 1..v_col_num loop
fetch v_cur into v_buffer;
if i<> v_col_num then
v_buffer:=v_buffer||',';
else
v_buffer:=v_buffer||';';
end if;
utl_file.put_line (v_dstfile,v_buffer);
end loop;
close v_cur ;
utl_file.put_line(v_dstfile, ' commit;');
utl_file.put_line(v_dstfile, ' exit when c_table%notfound;');
utl_file.put_line(v_dstfile, 'end loop;');
utl_file.put_line(v_dstfile, ' close c_table;');
utl_file.put_line(v_dstfile, 'end;');
utl_file.fclose(v_dstfile);
exception
when others then
if utl_file.is_open(v_dstfile) then
utl_file.fclose(v_dstfile);
end if;
raise;
end;
