带你轻松接触一个可以自动创建过程的脚本

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
1000
 (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;